Please note... this article assumes you've been directed to add your code to a standard module, as 99% of code is housed there. If your coder told you to put your code in a worksheet module or the ThisWorkbook module, this wont' quite get you there. (You should still read this article, but also this one which lists the other types of Excel modules.)
Inserting Your Code Takes 6 Really Easy StepsThere are six very easy steps to getting your macro code in place and running. They are:
- Open the Visual Basic Editor
- Create a code module
- Paste in the code you've been given
- Close the Visual Basic Editor
- SAVE YOUR WORKBOOK
- Run the code
I'll walk you through the whole thing with pictures to show you just how easy it is.
Open the Visual Basic EditorTo start with, make sure you're in Excel. Then press Alt + F11. The window that opens up is called the Visual Basic Editor (or VBE for short) and looks like this:
The most important window in here is the one marked Project - VBAProject. That's called the Project Explorer, and it's how we navigate through the VBA code modules. If you dont' see that one anywhere, then press CTRL + R to display it.
Create a Code ModuleThe next thing we need to do is create a code module. To do that
- Find your workbook on the left -- it will be listed as VBAProject(your file's name) In the example above I have two workbooks open. XLGFileTools, and an unsaved "Book1"
- If your workbooks is comprssed, you'll see a + icon next to it. To expand it so it looks like the above, simply click that.
- Right click on Microsoft Excel Objects
- Choose Insert --> Module
At this point you'll see a new "Module1" in the project explorer, and a blank code pane will open on the right side:
From this point forward, you can come back into the VBE and browse into this code module by double clicking on it. Any code you've saved will be there, you don't need to create new modules every time.
Paste in the code you've been givenNext you want to paste in all the code that your helper has given you. Double click in the code pane and paste it underneath any line that starts with the word Option (if any). Lines beginning with Option must ALWAYS be at the very top of the code module, so they're not hard to find:
In addition, if you end up with two Option lines that say the same thing, then delete one of them. You aren't allowed duplicate Option statements.
Once you've pasted in your code, you'll notice that it changes the colour on key words:
- Keywords are blue
- Comments (lines that won't be executed at run time) are green
Exit the Visual Basic EditorThis is just as easy as it seems... press the big red X in the top right corner. When you do this, you'll be taken back to Excel.
SAVE YOUR WORKBOOKThis is REALLY important! ALWAYS save your workbook before you test the code. When you run VBA in Excel the Undo capability is cleared, so there is no going backwards short of closing the file without saving changes.
Run the macroNow that your workbook has been saved (you have saved it, haven't you?), you are ready to run the macro. Press Alt+F8 to trigger the run dialog.
Click the name of macro that you've been given, then click Run.
And that's it. Whatever the macro has been programmed to do will happen.