Automating Tasks in Microsoft Excel
[ad_1]
Avid users of Excel will know that often you will come across tasks that you do over and over. Maybe you work in a department that has to provide a weekly status report or quarterly earnings statements. Or maybe you have a standard report that starts with the same raw data file each time and you have to remove the unwanted rows or columns before you can start building it. In all of these cases, when you perform the same step by step actions to complete your task, you can benefit from the automation capabilities of Excel.
Method for Automating in Excel
Most people are aware of Excel's macro function. A macro is a step by step list of instructions that Excel can follow over and over again. Recording a macro is super easy. Simply take the raw data file or report that you are ready to work on, and start the macro recorder. Perform the steps of your tasks exactly as you want Excel to perform them in the future. When you've completed the steps for the macro, simply hit the stop recording button. You can assign keyboard shortcuts for macros you will use frequently.
Problems With Recorded Macros
The macro recorder is powerful, but it's not perfect. There may be some situations where you want to perform the same steps on your file but the file may not have the exact same number of rows or columns as the one you used to record the macro. If the data is similar but doesn't cover an identical range, you need a way to tell the macro to define the size of your data range before it runs. This is where you need to spend some time learning VBA for Excel.
VBA for Excel
VBA is the underlying code that is built when you record a macro. (Alternatively, you can build VBA code from scratch once you understand the structure and syntax of the programming language.) By accessing the VBA code directly after you record a macro, you can add advanced functionality, such as defining variables that will be used as the range for your new data file. You can tell Excel, for example, to find the bottom row of a spreadsheet before the macro begins, and then perform the repeated tasks a number of times that corresponds with this new data range size.
For more information on recording and editing the VBA macro code, you should explore a training class or video tutorial that will explain these concepts in a thorough manner.
[ad_2]
Source by Thomas Richardson
