While many have criticisms about Excel, it is still a dominant tool used by many companies and does not seem to be going away anytime soon. Excel is easy to learn, even for non-tech-savvy individuals, and allows them to create analytical decisions and visuals by simply clicking a few buttons. Learning to code in Excel is beneficial when working with others who aren’t familiar with coding languages and navigating integrated development environments (IDE); this allows them to run those complex tasks and make decisions all in a comfortable realm.
What is VBA?
Visual Basic for Applications (VBA) is Microsoft’s programming language built into most desktop Microsoft Office applications. The language allows users to create user-defined functions and automate processes. VBA is stored in Access, Excel, Word, PowerPoint, and Outlook; however, most scripting is usually done in Excel files.
VBA is a beginner-friendly programming language to learn because of its simple characteristics and in-house applications. It is fully integrated into Microsoft Office programs, so no additional software or packages are required. The language uses similar features as other programming languages, including for loops, if-then-else logic, and while loops. The language is fairly intuitive and follows many of the same conventions a typical Excel user would know. For instance, the code to select a cell: Range(“A1”).Select, to get the row number of a cell: Range(“A1”).Row, and to bold a cell’s text: Range(“A1”).Font.Bold = True.
Getting Started with the Visual Basic Editor
All coding is done within the Visual Basic Editor, a separate application that opens whenever a workbook is opened. The editor is hidden by default and can be accessed by activating the ‘Developer’ tab. To activate the Developer tab, go to File > Options > Customize Ribbon and check “Developer” in the right pane. On the Developer tab, select Visual Basic to open the editor.
The VBA Editor has four main components: Project Explorer, Code Window, Immediate Window, and Toolbar. Figure 1 shows the default view for all windows; however, panes can be shifted around or removed.
- Project Explorer: The project explorer shows all objects currently open in Excel as a directory tree. All open workbooks and their associated worksheets, modules, and user forms can be navigated by expanding their folder paths.
- Code Window: Each project explorer object has an associated code window where code can be written either as a function or subroutine. Functions and subroutines operate similarly with one main difference: a function returns a value, whereas a subroutine is used to perform a specific task without returning a value.
- Immediate Window: After writing code in the code window, the immediate window can be used during code debugging. This window gives the user info about the workbook and debugs variables. A question mark at the beginning of a statement lets the immediate window know a question is being asked and an answer is expected. For example, typing ‘?Worksheets.Count’ will tell you the number of sheets in the current workbook.
- Toolbar: The toolbar contains the necessary buttons for running, breaking, and resetting macros, as well as an object browser, which lists all of an application’s properties and methods.
Record Your Own Code
An alternative to manually coding is to use the Macro Recorder on Excel. Recording a macro tracks all steps done by the user, such as selecting or formatting cells, typing text, changing sheets, and clicking commands on the ribbon. A macro recording is excellent for automating small, repetitive tasks. Additionally, all actions captured in the recording are transcribed into VBA code, which is a great way to learn the syntax. To record, go to Developer > Record Macro and begin to perform the task you wish to automate.
Creating Your First Macro
The following code example is a great way to learn how to read text from the spreadsheet and print out the result as a message box. Message boxes are a great way to inform users about the macro status without having them view the VBA editor.
Figure 2 shows the overall structure of a subroutine. We must first declare the subroutine’s name by writing “Sub”and our subroutine’s name (without spaces), followed by an empty set of parentheses. If we were writing a function, we would write “Function” followed by the function name and any arguments within the parentheses. The “Dim” line is short for dimension and is used to declare every variable used in the subroutine. For this code, we only use one variable — name — and declare it as a string variable. For our print statement, we include the text from cell A1 on the current sheet by storing the cell value in the name variable. Lastly, we use a Call statement to launch our message box. Our text inside the message box will say “Hello there,” and the value stored in A1. We can concatenate strings and variable values using an ampersand (&). The vbOKOnly tells Excel to only include an “OK” button in the message box; by default, message boxes have “OK” and “Cancel” buttons. Notice that we can use the immediate window to query questions about the workbook, such as asking what the current cell value is.
Now that our macro is finished, we can return to the workbook and run the macro by going to Developer > Macros and selecting our ‘HelloThere’ subroutine. When the macro finishes running, you should see a screen similar to Figure 3:
You’ve now created your first macro! From here, there are more extensive scripts to run that give the user more data control. Possible tasks include inserting graphs or charts, creating user-defined functions, writing results in a Word doc and saving them as a PDF file, composing and adding attachments to an email in Outlook, or querying SharePoint databases. You can even run Python code by creating shell scripts that connect to a Python IDE. Now you can run the analysis using Python and return the results to Excel for either formatting or visualization.
“Enable Macros” is not the same as “Accept All Cookies”
VBA is a powerful tool that allows a user to perform complex tasks but can also be used with malicious intent. Ensure that your Microsoft settings default to disabling macros when downloading an Excel file and only enable them if you are confident the file can be trusted. Do not think you will be safe if you don’t click the big, red button that says “PUSH.” Macros can be set to run anytime the user changes sheets, presses a key, or even opens the workbook. Thoroughly review all modules in the VBA editor before running any macros.
Resources to Use
Lastly, there are tons of resources for people interested in learning VBA. Below is a list of forums and blogs where people can post questions or share techniques for coding VBA.
- https://learn.microsoft.com/en-us/office/vba/
- https://www.mrexcel.com/board/
- https://stackoverflow.com
- https://excelchamps.com/blog/
- https://www.thespreadsheetguru.com/blog/
- https://www.automateexcel.com
- https://trumpexcel.com
- https://www.excel-easy.com
- https://spreadsheeto.com
Columnist: Derek Summers