How do I add VBA in MS Office?

Question

I've seen posts showing answers which use VBA (Visual Basic for Applications) code/macros, however, I've also noted additional comments afterwards about how to use the VBA (how to implement and execute it). I'm hoping this will help just to get the code executed.

So, my question is:

I have some VBA code. How do I add VBA to my MS Excel or Word or Outlook project?

Answer 1

To open the VBA editor in any Microsoft Office product¹, press Alt+F11.

Next, you need to determine how you want your VBA to be implemented. Do you want it to be triggered by some event in the software, such as the file being saved or the selection on the spreadsheet being changed? Or do you want to be able to execute the code yourself whenever you like?

If the former, take a look at the Project Explorer and find the project associated with the file you are currently working on.

If you want your code to be triggered by an event associated with a particular sheet (e.g., the code should run every time a value changes on Sheet1), you should double click that specific sheet object listed in the Project Explorer. This will open a text box where you can paste the code. If the event is associated with the entire workbook instead of a particular sheet (e.g., the code should run when the workbook is saved), double click the ThisWorkbook object in the Project Explorer.

If however, you do not want your code to be triggered by an event, you will want to place the code in a module. If there is other code in the document or workbook, say from a recorded macro, it will appear in a module. If there is an existing module, in most cases you will want to paste your code into the same module below any existing code. Existing modules will be listed in the Project Explorer.

If no module exists, you will need to insert a new one. To do this, choose Module from the Insert drop-down menu in the VBA editor window. This will insert a new module and display a text window inside the editor window. Paste your code there.

If you want to execute the code in a module, you have a few options. One is to run the code from the VBA editor window. You may simply place the cursor somewhere in the code you want to run and press F5, or click the (Run) button in the toolbar below the menu bar, to execute the code.

Another option is use the Macros dialog box.  To open it, do one of the following:

• on the View tab of the ribbon, in the Macros pane (far right), click the Macros button
• on the Developer tab, in the Code pane (far left), click the Macros button
• press Alt+F8

The dialog box looks like this:

In the example above, you can run YourCode by

• selecting it from the list and then clicking Run, or
• selecting it from the list and double clicking it.

______
¹ According to Can I use VBA in Excel 2010 Starter Edition?, you can't use VBA in Excel 2010 Starter Edition.

Answer 2

These steps are done using Office 2010 (except where noted otherwise) but should be similar enough for 2007 and 2013 as well. I'm doing it with Excel but the differences should be subtle.

The first step is to add the Developer tab to the ribbon.

In Office 2007,

1. Click “Office Menu” -> “(Program) Options”.  (“Office Menu” refers to the button in the top left corner.) This brings up a menu that has an “Excel Options”, “Word Options”, etc., button at the bottom.

2. In the options dialog box, check “Show Developer tab in the Ribbon”: and click “OK”.

3. Your new “Developer” tab should be showing.

In Office 2010 and 2013,

1. Click File -> Options.

2. Choose Customize Ribbon.

3. On the right side, find Developer and check the box. Click OK.

4. Your new tab should be showing, click on it and then click on Visual Basic.

A daunting screen is shown, which is the VBA code editing window.

Side note: You can also open the VBA code editing window by using the Alt+F11 keyboard shortcut (even if the Developer tab is not showing).

Inside the code editing window, on the left side you should have a pane called Project-VBAProject. In this, under the Microsoft Excel Objects (this name is different depending on which program (Word, Outlook, etc.) you are using) is a 'thing' called ThisWorkbook. Double click on ThisWorkbook

In this example, we'll use the following code.

sub WarnMeOfDanger()
   MsgBox "Argh, a dragon is behind you!"
end sub 

All the above does is show open a dialog box to show a message on-screen.

Copy the code, and paste it into the white sheet in the VBA code editing window.

Now, at the top of the VBA code editor window, click the green play button and you should see a message.

So, every time you want to run the code, return to this screen and press play!

However, you may prefer to add your own button. If you want to add a button then, first, note the first line of the code has WarnMeOfDanger(). This is actually the name of our 'subroutine'. We'll remember this for later. Now, simply close the Window you've been using (the code is automatically saved).

Still in the Developer ribbon, click on Insert, and choose the first item, which is a button. Your mouse cursor becomes a little cross (not angry, I mean its appearance). Draw the size of the button. A new window appears, and it should show us the option to assign the button to a function. In this case, we should see something like ThisWorkbook.WarnMeOfDanger. Click on this and click OK.

Now, if you press your new button, the message will show. Right-click the button to get options to move it, change the text, resize etc.