What is VBA?
Visual Basic for Applications, VBA, is a programming language included with MS Office. VBA is a fairly simple programming language, but it has most of the components of other larger languages: classes, objects and events, and a visual environment for development (VBE). For this reason VBA is a good programming language to learn (or at least very good one to start learning).
Why VBA?
MS Excel is the most used spreadsheet application in businesses and as an OR practitioner you will use it frequently. Coding in VBA allows you to automate, simplify and customize tasks. Therefore, knowing how to use VBA for Excel modeling and applications development is valuable skill in any company.
What is a Macro?
MS Excel consists of a number of objects, or named parts of a computer’s memory. Objects have properties, and methods that manipulate these properties. A macro is a collection of instructions for the computer to handle these Excel objects. It is a small computer program that runs within MS Office.
Getting Started
Before starting you need to set up the developer tab and the security settings:
Developer Tab: Here you can access the Visual Basic Editor and other developer tools.
Office 2010 does not display the Developer tab by default: you must enable it yourself.
Security issues: you cannot save a macro in the default Excel file format (.xlsx); instead, you must save the macro in a file with a special extension .xlsm. If the Security Warning bar appears when you open a workbook that contains a macro, you can click the ‘Enable Content’ button to enable the macros. Click the Macro Security button on the developer tab to specify which macros can run and under what conditions.
Visual Basic Editor (VBE)
VBE is the environment in which you work with VBA code. To access it you can either:
- Go to the menu Tools > Macro > Visual Basic Editor,
- Click the VBE icon from VBE Toolbar, or
- Press ALT + F11
VBA has three main windows: project explorer, properties, and code. There are two other windows used for debugging: immediate and watch.
Project Explorer
Lists all projects in any open workbook. Each workbook has a project, and each project can have several parts: workbook and worksheets, modules and forms.
To add a new module or form to the current project use Insert > Module or Insert > User Form; or you can also use the icon from the standard toolbar.
Properties Window
Contains detailed information about any selected part of a project in the Project Explorer. Some basic naming and formatting properties can be modified for worksheets and workbooks. Properties are very important for user forms, for formatting, setting position, adding pictures, setting special behavior, and so forth.
Code Window
Displays the VBA code associated with the Excel object selected in the Project Explorer. It is the tool that we use to create macros and handle the corresponding object. You can also store the code in a separate module which you can use to refer to another object.
At the top of the code editor there are two drop-down lists. The first one is the Class Name list, where you can select any of the objects located within the selected object, such as spreadsheet, buttons, etc. The second list is the Method Name list, which provides access to any event associated with the currently selected object. Use these lists to locate any code procedure.
A nice feature of the code window is IntelliSense, which provides dynamic assistance as you enter the code, displaying all the possible coding options available. IntelliSense displays automatically and you can press Ctrl + Space to manually load it.
Recording Macros
Macros are a way to automate a series of actions in a spreadsheet application. They can either be created directly with VBA code in the Visual Basic Editor, or recorded in Excel. To record a macro in Excel do the following steps:
1. Prepare: to record a macro, we must know exactly the actions we wish to perform and then use the Macro Recorder.
2. Record: click the record button from the developer tab. When the Record Macro dialog box appears, we enter a name for the macro.
3. Stop: once you begin recording, notice that the Record button transforms to a Stop button. After finishing the steps needed to copy and paste the information, you can stop recording.
4. Play: Once you have recorded a macro, you can play it to make sure it works correctly. Click the Play button from VBE Toolbar
5. Review the code: Each time a macro is recorded in Excel, VBA code is automatically generated in a new module. Go the VBE Project Explorer and select the module to see the code in the Code Window.
Since we can study the VBA code generated when we record a macro, we can easily learn how to create a similar macro directly from VBA by copying the code generated.