Boost Your Productivity: How to Automate Excel Macros

Automation is the name of the game, and in the world of Excel, VBA is the master key. With VBA, you can run Macros automatically when opening a workbook, on a timer, every time a cell is changed, or by key bindings to achieve greater automation and functionality from excel models.

Before Starting

Before automating your 9-5, we must become familiar with the tools we will be working with. To do this, we will be working in the VBA Editor. Open VBA Editor by pressing Alt + F11 or navigating to the Developer tab in the Excel Ribbon.

To truly unlock the potential of excel Macros, you must become intimately familiar with this screen. The "Record Macro" button is convenient and beginner-friendly; however, it severely limits automation and is prone to undesirable behavior. Learning how to navigate the VBA Editor and basic VBA coding will take your excel skills to new heights.

On the left-hand side of this screen, you will notice a window titled "Project - VBAProject". If you do not see this window, click "View" and "Project Explorer". This is file hierarchy organization that stores all Macros, User-Forms, etc. within open workbooks. Notice under the folder "Microsoft Excel Objects", we see an excel sheet and the entire workbook. This is where you will save Macros you would like to run automatically based on changes in a specific worksheet or change in the entire workbook.

The other item we need to be familiar with is the insert drop-down. Click the drop-down and select "Module". This will create a new folder in the VBAProject window titled "Modules". This is where other Macros can be stored that are not dependent on the workbook or specific worksheets.

Running Macros on Open

Why might you want a Macro to run when you open a workbook? One example is displaying automatic messages or warnings whenever a user opens the workbook! You don't want users opening a workbook and messing up data they are unfamiliar with, so automatic message boxes are a great way to catch their attention and ensure they get the message.

To do this, navigate to the "ThisWorkbook" object in the project window. Insert the following code:

Private Sub Workbook_Open()
  MsgBox("WORKBOOK WILL SELF DESTRUCT IN 5 SECONDS")
End Sub        

The text "Private Sub Workbook_Open()" is what designates this Macro as a Macro to run automatically upon workbook open. Instead of displaying a message, you can insert any code you desire in its place to really optimize your workbook. Some other user cases may include:

• Updating data pulls when workbook is open
• Opening other associated files together with the workbook
• Navigating to specific worksheets within the workbook on startup
• Create new worksheets
• Load a user form that collects necessary information for the workbook to function

The use cases for open-trigger Macros are limited only by your creativity, but VBA allows for other triggers to make Macros run automatically as well.

Running Macros When a Cell Changes

Within the VBA Editor, in the project window, click the worksheet with the cell which will trigger your Macro. In this file, we can use the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
  *** Insert Your Macro Code Here ***
End Sub        

This Macro will run any time a cell is changed within the chosen worksheet. We can narrow it down to a specific cell by checking what cell was changed with an If Statement.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$A$1" Then
    *** Insert Your Macro Code Here ***
  End If
End Sub        

This code now checks if the cell that was changed was A1. If A1 was changed, it will execute your Macro. If any other cell was changed, nothing will happen. This idea can be adapted to check if the cell has been changed to a certain value, check for a certain formatting, color, or more.

One weakness of this code is that formula recalculations will NOT trigger your macro. To run a macro every time a cell formula calculates a new value, use the following code:

Private Sub Worksheet_Calculate(ByVal Target As Range)
  *** Insert Your Macro Code Here ***
End Sub        

Running Macro on Worksheet Activation

Similar to how we can create Macros that run whenever the workbook is opened, we can run a Macro every time a specific worksheet within the workbook is opened. Within the specific worksheet in the project window, you can use the following code:

Private Sub Worksheet_Activate()
  *** Insert Your Macro Code Here ***
End Sub        

This could automatically unprotect the worksheet, automatically hide certain cells, update data, recalculate the worksheet, and more. The converse of this automation replaces "Worksheet_Activate" with "Worksheet_Deactivate". The implementation is the exact same and can be used to re-protect the worksheet, hide the worksheet, and more.

Running Macros on a Timer

Running Macros on a timer can allow the user to operate with the most up to date information, automatically generate reports on a daily basis, send notifications to the user, backing up data, validation checks, and more. User VBA's OnTime functionality, you can schedule Macros at a specific time or have tasks repeat on a time interval.

For scheduled Macros, go to the "ThisWorkbook" file in the project window. Insert the following code:

Sub Workbook_Open()
  Application.OnTime TimeValue("09:00:00"), "MyMacro"
End Sub

Sub MyMacro()
  *** Insert your Macro here ***
End Sub        

The first Macro is triggered automatically when the workbook is opened, then starts a timer. At 9 A.M., the Macro will then run the MyMacro. To run a Macro on a repeating interval, we can alter the code in the following way:

Sub Workbook_Open()
  alertTime = Now + TimeValue("00:02:00")
  Application.OnTime alertTime, "MyMacro"
End Sub

Sub MyMacro()
  *** Insert your Macro here ***
  alertTime = Now + TimeValue("00:02:00")
  Application.OnTime alertTime, "EventMacro"
End Sub        

This code will repeat every 2-minutes after opening. This code will operate even if the user shuts the workbook. Because the code will continue running, the user must stop the timer. This can be done be adding the argument "False" to MyMacro():

Sub Workbook_Open()
  alertTime = Now + TimeValue("00:02:00")
  Application.OnTime alertTime, "MyMacro"
End Sub

Sub MyMacro()
  *** Insert your Macro here ***
  alertTime = Now + TimeValue("00:02:00")
  Application.OnTime alertTime, "EventMacro", , False
End Sub        

This can also be done with a few lines of code that will stop the code from running when the workbook is closed:

Private Sub Workbook_Open()
  alertTime = Now + TimeValue("00:02:00")
  Application.OnTime alertTime, "MyMacro"
End Sub
 
Sub MyMacro()
  *** Insert your Macro here ***
  alertTime = Now + TimeValue("00:02:00")
  Application.OnTime alertTime, "EventMacro"
End Sub
 
Private Sub Workbook_BeforeClose(Cancel as Boolean)
  Application.OnTime alertTime, "EventMacro", , False
End Sub        

If all else fails to stop your timer, you can force stop it by pressing CTRL + BREAK or killing the process in Task Manager on your device.

Running Macros on Keystrokes

Using the OnKey functionality, the user can map macros to specific keystrokes. To see a full list of keystrokes, check out This Page.

Private Sub Worksheet_Activate()
  Application.OnKey "1", "MyMacro"
End Sub
 
Sub MyMacro()
  *** Insert your Macro here ***
End Sub        

The top sub assigns the macro when a worksheet is active to the “1” key and allows the user to call MyMacro() by pressing “1” or whatever key you choose. Alternatively, you could save this code under the “ThisWorkbook” and make the top sub run on open to make the key binding global.

Private Sub Worksheet_Open()
  Application.OnKey "1", "MyMacro"
End Sub
 
Sub MyMacro()
  *** Insert your Macro here ***
End Sub        

Other Methods

There are many other methods that can be used as triggers for Macros. These include:

• NewSheet
• BeforeSave
• AfterSave
• BeforeClose
• BeforePrint
• SelectionChange
• SheetBeforeDelete
• SheetBeforeRightClick
• SheetPivotTableUpdate
• WindowDeactivate
• BeforeDoubleClick
• BeforeRightClick

Macros are a powerful tool. With Macros, most repetitive tasks can be completely automated, workbooks can be cleaned, and advanced analysis can be performed that is not possible without VBA.

If you enjoyed this information and are curious to learn more about the limits of what can be done in excel and finance, check out my personal blog. I post full excel projects, mathematics, finance, and coding projects on that website and appreciate the support.