Here’s a reader question:
I have a co-worker 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 much appreciated.
Introduction to Excel 365 Macros
Mastering Macro development is probably one of the most important skills required to become a power Excel user. In essence, we use VBA Macros in order to automate tasks in Excel (also in Word, Macros are pretty useful) that would have been otherwise mundane, boring and very time consuming.
Here are a few examples of tasks which we are able to automate with Macros in Excel:
- Creating and manipulating workbooks and worksheets.
- Creating and manipulating charts
- Import data from files or other systems.
- Analyze data and create/refresh charts and dashboards
- Customize forms to capture feedback
- Send emails
- And much more…
Create macros in Excel 365
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) Record a sequence of user actions which is 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) Write 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.
- Then, we will then copy a table range from Worksheet Sheet1 into the DEMO worksheet.
Record Excel 365 / 2019 Macros
Proceed as following to record a macro in Excel:
- In Excel, open an existing workbook or create a brand new one.
- Navigate to the View tab in the upper Ribbon.
- In the Right hand side, you’ll notice the Macro section.
- Hit View and select Record Macro.
- The form below will open up. Now provide a name to your Macro and store its content into the existing workbook. You might notice that here you can easily assign a key shortcut so you can more easily run the Macro.
Tip: Make sure to enter a detailed description to your Macro.
- Now, hit OK.
- At this point, go ahead and manually perform the steps you would like to record – in our case i will go ahead and manually create the new spreadsheet, rename it and then copy the table from Sheet1.
- Once done, hit Macros again and select Stop Recording.
- If you now hit Macros and then select View Macros, you’ll see a list of all Macros stored in all open workbooks (or specific ones)
- If you hit the Edit button, the Microsoft VBA Editor will open.
- The VBA editor shows the code that Excel has just generated for you in the background. In the next section of this tutorial i will show you how you can write a similar piece of code on your own.
- Close the VBA Editor.
Saving your Macro enabled file
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)
- Hit File, then Save
- Select a location in your Computer or OneDrive in which you’ll save your file.
- Provide it a descriptive name
- Select .xslm as the File Type.
- Hit OK.
- Nice job, you have just recorded a simple macro!
Create Excel VBA Macros
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.
Setting up the Excel development environment
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:
Writing your VBA macro
- Open your Excel file.
- From the Ribbon, hit Developer.
- Now hit the Visual Basic button (Alternatively, hit Alt+F11).
- GO to Module1 and paste the following code:
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
- Close your VBA Editor.
- Back in Excel, hit Developer.
- Now hit on Macro and look into the list of Macros in your Workbook. You’ll notice the Write_Macro entry, which we just write on our own.
- Save your spreadsheet as a .xslm (Macro enabled Excel file)
Running your Macro
Executing your macro is very simple: From the Macro Dialog shown above, highlight your Write_Macro entry and hit Run.
Enabling VBA security permissions
In Microsoft Office, Macros are disabled by default, 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.
- Hit Developer in the Ribbon.
- Select Macro security.
- Select the Disable all Macros with notification option.
- Hit OK.
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.