Here we have created a series of 20 lessons that show you how to get started with Excel VBA macros. It’ll suit you if you haven’t spent much time playing with macros before because we jump in and show you how to get going straight away.
- 1. Start and open Excel’s developer tab
2. Record a macro
3. View macro code – recording macros creates computer code – you’ll probably want to take a look at it
4. Play a macro manually so you can check it’s working OK
5. Run a macro automatically: so that you can play a macro back super fast e.g. using a button and save yourself time in the future
6. Safely copy a macro e.g. from an online source like this
7. Deal with Excel’s macro security settings
8. Make a macro available to all worksheets in Excel – not just the spreadsheet your first recorded it in
9. Simplify our macro code (the code produced by the macro recorder can be pretty ugly)
10. Loop a macro: set your macro working until it gets to the bottom of a list
11. Use different kinds of macro loops
12. Macro variables: assign variables within a macro
13. Perform basic mathematical operations on variables
o Discover more about different kinds of macro variable
14. Use Excel functions on VBA variables
15. Manipulate variables with If Then statements
16. Reroute and control program flow with GoTo statements
17. Understand more about VBA code: the macro object-property-method structure
o Perform a method on an Excel object
o Set macro object properties
18. Perform multiple actions on an object by using a With statement
19. Use and create macro dialog boxes: message boxes, input boxes and your own user forms. It’s a big lesson this one and you might need to allow a bit more time for it than some of the others. We show you how to:
o Add option buttons, command buttons or other buttons and input boxes to a custom user form
o View your user form macro code (created when you add something to a user form)
o Structure the user form macro code – get each piece of your user form code doing exactly what you want it to
o Get the user form working by getting the right code in the right place, calling the user form and being careful how we declare our macro variables
20. Tap into other helpful sources of information on macros, test your knowledge using our (not that small but almost perfectly formed) macros glossary and browse our collection of handy VBA code examples from this course. This is another ‘chunky’ set of material and is designed to help you review and cement the learning from the previous lessons.
What’s this all about?
You’re looking at an online financial modelling course covering Excel macros.