In the VBA for Microsoft Excel course, you will not only learn how to record macros in Excel, but write the code behind them.
Like any foreign language, you will learn about most of the structures of the language, such as several of Excels objects, collections, their properties and methods. Learn how to write complex decision structures and loops containing variables.
Create message boxes to give users relevant feedback. Code is written in a Module, that is attached to a workbook via the VBA editor. One way to access the Visual Basic Editor, press the ALT F11 keys on the keyboard.
- Locate the file in the project Explorer and click to select it.
- Insert menu > Module (a blank module is inserted).
- Edit > Paste the code in the module.
- Check that the code has good structure – a sub line to start, and an end sub. All commands between will be included in your macro.
- This piece of code will hide all the sheets except for one nominated (in this example, Invoice).
- Rename the string (this sits between the quotation marks) to the sheet name to keep.
- Save the file as a macro enabled workbook. Code cannot be saved in a regular xlsx file.
- To ‘test’ the code, press the Run button, or press F5. Watch the spreadsheet carefully. All the sheets, except for the sheet named Invoice are hidden.
- To write code that does the exact opposite (display all sheets), simply copy the entire code structure, and paste underneath.
- Rename the code, and change one word – change xlsheethidden to xlsheetvisible.
- Run the code again. All sheets should be unhidden.
Come along to the course for all this, and more, including how to create buttons to run the code, or had trigger it to run automatically!