Sign in or start a free trial to avail of this feature.
1. What are Macros and VBA?
In this lesson, I explain what Macros and VBA exactly do and show you how to build a simple Macro in your spreadsheet
To explore more Kubicle data literacy subjects, please refer to our full library.
Excel as a Programming Platform (00:04)
Excel is not just a spreadsheet application. It’s also a platform on which additional programs can be built. These can be commercial applications, or programs written be Excel users. The programming language used to write these applications is called Visual Basic for Applications, or VBA.
Macros are small chunks of VBA code that can be used in Excel to automate simple tasks and improve your productivity, or to extend the functionality of Excel. You can create macros by writing out the VBA code, or by recording the macro in Excel.
Recording a Macro in Excel (01:27)
To create a macro, you must be able to see the Developer tab in the ribbon. If this is not showing, you can add it by going to the File tab of the ribbon, and selecting Options. Select Customize Ribbon in the sidebar, then check the box for the Developer tab to enable it in the ribbon.
To record a macro, we select Record Macro from the Developer tab. We can give the macro a name, and create a keyboard shortcut that will execute the macro. You should be careful not to select a shortcut that is already used for another purpose, for example Ctrl + C.
We then record the macro by working normally in Excel. In this lesson we apply some formatting to the selected cell. When we’re done, we stop the recording from the Developer tab.
After creating a macro, we can run it again using the keyboard shortcut we assigned. Any actions we took while recording the macro will be applied immediately. In our case, our macro applies the formatting steps we took with a single keyboard shortcut.
Most users of Excel know that it's a software program for data analysis, modeling and other business applications. What you might not know if that Excel is also a programming platform on which additional programs can be built. Many companies sell products that work on top of this platform such as Oracle's Crystal Ball application for advanced data analytics. Programs built on the Excel platform must be written in a language called VBA or Visual Basic for Applications. Excel makes it easy for individual users to write their own commands, functions and tasks in VBA and that's what we're going to be doing in this course. In the first few lessons, I'm going to show you how to create some simple chunks of VBA code called macros which are typically used for automating repetitive tasks. It's important to mention at this stage that most business users can easily get by without knowing macros or VBA. However, when used correctly, these tools can improve your productivity and extend Excel's functionality far beyond what you may think possible. Macros are the easiest type of VBA code to create because you don't need to actually write any code at all. I tend to use them for common formatting tasks that can save me quite a lot of time.
Let's now move to Excel and create our first macro.
To create a macro, you first need the Developer tab in the ribbon showing. On my screen, the tab is not showing, so I'll go to File, Options and then Customize Ribbon where I'll find the Developer option. So, I'll tick this box and press OK.
And now we have the Developer tab showing and I'll select it.
To record a macro, I'll simply press the Record Macro button, I give the macro a name, assign it a shortcut, I'll say control shift P.
It's important here to make sure that you don't override any important shortcuts such as control C when choosing a macro shortcut.
I'll store the macro in this workbook and I'll also add a quick description which will be converts selected cell to the input format.
I'll then press OK to start recording the macro.
To convert the selected cell to the input format, I'll need to give it a blue background color and also bolded blue text. So, let's go to the Home tab with alt H and then H to give it a blue background.
And then I'll press alt H F C to select the required blue text and then I'll press control B to bold this text and now our macro is complete.
So, if I return to the Developer tab, and press R, I can stop the recording.
Let's now try out our new macro. If I go to another input cell and I press control shift P, I can see that this is now formatted correctly as an input and saves me a bit of time when formatting these cells.
I can even select multiple cells and perform this shortcut.
While this example is a very straightforward macro, you can see just how easy it is to create simple keyboard shortcuts that automate repetitive commands. In the next lesson, I'll show you how to extend the functionality of this macro and find out what's really going on behind the scenes.