Here’s a reader question:
I have a coworker that is using Excel VBA Macros to analyze a bunch of data. Can you explain how Macros work, and probably suggest a way for me to pick that skill up? Any simple tips for beginners are very appreciated.
Thanks for the question.
Mastering Macro development is probably one of the most important tasks in order to become a power Excel user. In essence, we use Macros in order to automate tasks in Excel (or any other Office application) that would have been otherwise mundane, boring and very time consuming.
Here are a few examples of tasks we could automate with Macros in Excel
Due to the fact that Excel ships with a built in Macro recorder (unlike Outlook and PowerPoint), there are two main options for adding a macro to an Excel workbook:
1) Recording a sequence of user actions which si probably easy to get started with, but doesn’t scale up for complex tasks which might involve interaction with another Office applications or manipulating bigger data subsets.
2) Writing your Macros by leveraging Visual Basic for Applications (VBA). This is a relatively simple procedural language that allows you to quickly develop more complex automation.
In this post, we’ll be covering both cases. We’ll start by showing you how to record your macro and continue by providing a detailed procedure for inserting VBA macros to spreadsheets.
We’ll demo both options using a very simple task: We’ll automate the addition a worksheet name “DEMO” to an existing workbook and copying a specific table from Sheet1 to it.
We’ll use a very simple example to outline the process. Please proceed as following:
Tip: Make sure to enter a detailed description to your Macro.
Now we’ll need to save the workbook. As it contains a Macro, we should save it as a XSLM file (Macro Enabled Excel File)
As we previously mentioned, in many instances the Excel Macro recorder will fall short and won’t meet all your needs. In these cases, you’ll need to write your own Code.
Tip: There are cases that you might want to use an hybrid approach to Macro creation. You could start by recording your Macro and then just make manual adjustments to the auto-generated code with VBA.
This one sounds pretty fancy, but is actually quite simple. If you don’t see a tab named Developer in your Ribbon, start by enabling the Developer tab.
After the Developer tab is made visible, your Ribbon will look as following:
Sub Write_Macro() 'This code creates a Macro named Write_Macro Dim MySheet As New Worksheet Dim MyTable As Range ' Create a worksheet programatically and call it TEST Set MySheet = ThisWorkbook.Sheets.Add(After:=Sheets(Sheets.Count)) MySheet.Name = "TEST" 'Copy the table from Sheet1 into TEST worksheet Set MyTable = Sheet1.Range("A1:C11") With MyTable .Copy Sheets("TEST").Range("A1") End With End Sub
Executing your macro is very simple: From the Macro Dialog shown above, highlight your Write_Macro entry and hit Run.
In Microsoft Office Macros come by default disabled, in order to avoid running harmful code. If you would like to run your Macros you’ll need to allow that. Execute this optional step in case you have received an error message when running either of the two macros we have created above.
Note: Disable all Macros with notification is the recommended security setting for Excel workbooks that contains Macros. It works as following: when a user opens such a workbook, Excel posts a “MACROS has been Disabled” notification right beyond the formula bar. It is then up to the user decision to enable the macros in the workbook.
In today’s short tutorial we learnt how to insert Macros either by recording them or writing them by using Visual Basic for Applications directly.
This is just a starting step for beginners to get familiar with Excel development. Feel free to post any questions and comments in the Comments section.