How to add VBA in MS Office

community-faq-proposedmicrosoft-officevba

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?

Best Answer

To open the VBA editor in any Microsoft Office product1, 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 “play” icon — green triangle pointing to the right (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:

enter image description here

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.

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

Related Question