How to set up macros in VBA?
How to set up macros in VBA?
VBA Macros use the Visual Basic Application in Excel to create custom user-generated functions and speed up manual tasks by creating automated processes. Additionally, VBA can be used access the Windows Application Programming Interface (API). One of its main uses is to change and customize the user interface by creating personalized toolbars, menus, dialog boxes and forms.
To learn more, launch CFI’s Excel VBA course now!
In a separate article, CFI discussed what VBA is and how to access the VBA Editor. As a summary, pressing Alt + F11 in Excel opens up the VBA window, and allows the user to begin coding macros.
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 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 cfiMacro()” and press enter. The VBA Editor will automatically and an “End Sub” line a few lines below the “Sub”.
Note: The general convention for typing names for any macro, function or variable in VBA is to use lower case if there is only one word, and to use an uppercase letter at the start of every new word. VBA names generally cannot contain space. Since CFI Macro is two words, this should be written as cfiMacro. However, these are just best practice guidelines and need not be adhered to.
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. Try this out and you should notice that Excel will automatically create a line between the two different macros.
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. Alternatively, you may be interested in our fully fledged Excel VBA macro course. Click here to launch the course now!
Thank you for reading the CFI introductory guide to VBA macros in Excel. To keep learning and advancing your career, check out these additional resources:
To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!