Guest Author - Chris Curtis
Simply put, a Macro is a Visual Basic for Applications (VBA) program that executes a sequential series of commands and functions within an application such as Excel. You use them to carry out repetitive tasks that you would otherwise have to click through a series of commands and dialog boxes to execute manually. Thoughtful use of macros can save you time and improve the accuracy of your work.
Macros can range from being as simple as applying a consistent format style to a selected range of cells to being as complex as managing a complete functional application design such as an accounts receivable program. Keep in mind, if you can execute it manually, then you can create a macro to execute it. You are limited only by your skill and understanding of the application program and your imagination as to what may be possible to get the job done. Essentially, you can automate and customize your program application using macros. Let’s look at some Excel basics.
In Excel the tools that you would use to create a simple macro are found in four toolbars and the VBA editor.
Stop Recording Toolbar
The stop recording toolbar is used when you are recording macros. It is an adaptive toolbar, meaning it opens as soon as you begin the recording process. In Excel it contains two command buttons: The “Stop Recording” and the “Relative Reference Button”. Be careful not to X this toolbar because it may refuse to open automatically during the recording process the next time you need it.
Visual Basic Toolbar
The Visual Basic Toolbar contains commands that you will use frequently in your macro development. It is good practice to have it open throughout your Macro work. It consists of the following six commands:
• Run Macro
• Record Macro
• Resume Macro
• Launch Visual Basic Editor
• Control Toolbox
• Design Mode
Forms Toolbar
The Forms Toolbar contains the Active X controls you will use to interface with the users of the application you are designing. A simple and frequently used example is a button on the worksheet that executes the Macro. This toolbar should be kept open during the process of constructing your Macro.
Control Toolbox
This toolbar is used primarily within the VBA Editor. You use it to create forms and dialog boxes for user defined variables. For example, suppose you have certain data that needs to be input into the worksheet by the user in order to perform an analysis and produce a chart. With the Control Toolbox, you can design an input dialog box to collect the data from the user. A mortgage amortization table needs to know the amount of the loan, the interest rate, and the number of years to pay back the loan. A simple dialog box can be used to prompt users for this input data.
Macros and VBA are powerful tools within the MS Office applications that can help you or other users be more efficient in completing the work. Macro writing and editing requires a deep understand of the objects, methods and commands in the program in which you are designing your application.
