Intro to VBA and Macros

​

VBA stands for Visual Basic for Applications. Excel VBA is Microsoft’s programming language for Excel  and all the other Microsoft Office programs, like Word and PowerPoint. The Office suite programs all  share a common programming language. 

While users cannot directly manipulate the main Excel software through VBA, they can, however,  master the art of making macros to optimize their time in Excel. There are two ways to make Excel  macros. 

The first method is to use the Macro Recorder. After activating the recorder, Excel will record all the  steps a user makes and save it as a “process” known as a macro. When the user ends the recorder,  this macro is saved and can be assigned to a button that will run the exact same process again when  clicked. This method is relatively simple and requires no inherent knowledge of the VBA code. This  method will work for simple processes. 

However, the downfall of this method is that it is not very customizable, and the macro will mimic  the user’s input exactly. By default, recorder macros also use absolute referencing instead of relative  referencing. It means that macros made in this way are very hard to use with variables and “smart”  processes. 

The second and more powerful method of creating an Excel macro is to code one using VBA. 

To access the VBA window, press Alt + F11 within any Office program. When done properly, this will  open a window with a file structure tree on the top left, properties on the bottom left, a debug pane  at the bottom centre and bottom right, and the coding section that takes up the majority of the  screen in the centre and top right. This may seem overwhelming at first, but in reality, it’s simpler  than it appears. 

Most of the time, the user will be working in the coding section. The file structure section is only  used for creating a new macro file. The properties section in the bottom left will only be used for  more advanced macros that use User Forms to create graphical interfaces for the macro. 

The coding section is where most, if not all, the coding happens. The user will create, code, and save  macros here. After the macro code is written and saved, it can then be attached to certain triggers in  the Excel model. The macro can be activated at the push of a specific button on the worksheet, or  when certain cells are modified, for example. The easiest way to implement a macro is to attach it to  a button. 

To start coding, the user will have to create a Module file. Module files contain a group of macros. To  create a new module, press Insert > Module. Optionally, the user can name this module using the  properties window in the bottom left corner of the editor. Simply type in a new module name and  press enter. 

To start off, the macro must be given a unique name. This name cannot match other macros, and it  usually cannot match the name of other properties, functions, and tools within Excel. The macro  name is what the user will use to call the macro into action.

To define a macro name, the user must type Sub name () and press “enter” in the coding window of  the editor. Pressing enter will automatically fill the window with the general format of an Excel  macro. For example, to name the macro “CFI Macro”, a user should type “Sub abcMacro()” and  press enter. The VBA Editor will automatically add an “End Sub” line a few lines below the “Sub”. 

The Sub Name () line tells the editor the start of the macro code. The End Sub denotes the end. If the  user wanted to, he or she could create a second new macro by starting a new Sub Name () line  below the first End Sub. This is the basic structure of an Excel macro. The next step, before jumping  into the actual process coding, is to define the variables the user is going to use in the code.