It's always nice when you go to a forum and someone gives you a nice bit of VBA code that is supposed to accomplish your goals. But if you've never used VBA code before, it's kind of hard to know what to do with it! This article has been written to get you up and running and get that code in the right place.
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 won't 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 Steps
There 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 Editor
To 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:
Now, to be fair, you may not have all the toolbars and windows, but the important thing is it should be close.
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 Module
The 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:
Don't worry if yours doesn't say Option Explicit at the top. While we'd prefer it did, it's not entirely critical.
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 given
Next 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 Editor
This 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 WORKBOOK
This 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 macro
Now 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.