Now that we have User Defined Functions under our belts, let’s take a look at Excel VBA subroutines.
What’s an Excel Subroutine?
Basically, Excel VBA subroutines are collections of code that carry out specified actions on a worksheet.
Unlike UDF’s, they can change formatting of a workbook, delete cells/rows/columns/worksheets, and add data to any cell on a worksheet.
Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to solve complex engineering challenges in Excel. Click here to get started.
They can even write to external files, open other applications and a lot more.
How to Create an Excel VBA Subroutine
There are two main ways to create a subroutine:
- Record some actions with the Macro Recorder
- Write the code from scratch in the VBA Editor
I’ll focus on recording a subroutine with the macro recorder and editing it in the VBA editor in this post.
Recording a Subroutine or Macro
To record a subroutine or macro, click the Record Macro icon in the lower left of the Excel window.
When the Record Macro Dialog box opens, you can give your macro a name, shortcut key, and description.
Click OK to start the Macro Recorder.
Next, perform some actions in the workbook.
One warning: Excel records everything you do in the workbook. Every scroll, click, etc. So try to be deliberate in what you do with the recorder running – it will make your code easier to understand later on.
In this example, I did three things:
- Selected Cell B2.
- Typed “Sample Text”
- Renamed “Sheet1” to “MySheet”
To view the code that was generated during the recording, open the Visual Basic editor by typing Alt+F11 and navigate to Module1 of the active workbook in the Project window.
Interpreting a Recorded Subroutine
Now, in the code window, we can see exactly how our actions in the worksheet were interpreted in VBA:
All subroutines start with “Sub” followed by the subroutine name and a pair of parentheses. Arguments can also be contained within the parentheses, but this is a little less common. The end of the subroutine is signaled by the command “End Sub”
The lines in green are comments. They are ignored when the subroutine runs, but are extremely useful to anyone trying to understand the purpose of the code. To create a comment, just type the apostrophe character, ” ‘ “, followed by the comment text.
Finally, the remaining lines are the actual commands that were recorded.
Line 1: Select Cell B2
Line 2: Type “Sample Text” into the selected or “Active” cell
Line 3: Select cell B3 (this command occurs by default when I typed “Enter” after entering the text into cell B2)
Line 4: Select the tab for Sheet1
Line 5: Rename “Sheet1” to “MySheet”
Subroutines created with the Macro Recorder are fairly straightforward to understand if you just go line by line.
Generalizing a Recorded Subroutine
One issue with subroutines created via the Macro Recorder is that they are very specific and lack any generalization. For example, this macro will fail if I try to run it, because it will look for “Sheet1”, which no longer exists.
We can generalize the subroutine by changing a few lines of code.
Let’s delete the last two lines of code and replace them with this line:
Now, the entire macro looks like this:
This will change the name of the active sheet to “MySheetName”, regardless of the current name of the sheet.
How to Run a Subroutine
To test it out, we can add a new worksheet to the workbook and run our subroutine.
There are a few different ways to run subroutines:
- Typing F5 in the VBA editor.
- Selecting “Macros” from the Developer tab and choosing the subroutine from a list
- Assigning the macro to a button or shape.
- Typing an assigned shortcut key.
The shortcut key “Ctrl+Shift+M” works nicely in this case because we assigned it when we recorded the macro.
So with Sheet2 active and any cell selected, I type Ctrl+Shift+M, and the commands are carried out on the active worksheet:
Excel VBA Subroutines are a huge topic, and I just started to scratch the surface here. But go ahead and play around with the Macro Recorder and see what it can do (on a workbook that’s not important, of course!).
Do you have an engineering spreadsheet that would benefit from a subroutine? Let me know about it in the comments below!