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
Excel as a programming platform
- Excel is actually a programming platform upon which additional programs can be built
- Commercial products such as Oracle's Crystal Ball application are built on Excel
- Users can also build their own mini-functions or Macros in Excel
Add the Developer tab to the ribbon
- The full Macros menu is available in the Developer tab in the ribbon.
- If the Developer tab is not showing, go to File -> Options -> Customize Ribbon
Recording Macros in Excel
- It's possible to record simple Macros without writing any Visual Basic code
- Macros are normally used to save time on repetitive, formatting tasks
- Each Macro is assigned a keyboard shortcut, which should not conflict with common shortcuts
ALT + L , R: Stop / Start recording Macro
ALT + H , H : Change background color
ALT + H , F, C : Change font color
CTRL + B: Bold text
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.