Visual Basic Tutorial for Excel Macros
Visual Basic (VB) macros are user-written programs tasked with automating single or multiple processes to manipulate data in some way. These macros can significantly enhance the Excel user experience by reducing difficult, mundane and repetitive actions often used when organizing or managing data. Although writing a VB macro may seem like a daunting task, with a little help, it can become second nature.
Other People Are Reading
Instructions
-
Write Your First VB Macro - "Hello World"
-
1
Open Microsoft Excel to a new blank worksheet.
-
2
Go to the Tools menu and select "Macro," and then "Visual Basic Editor" to open a new VB window. Pressing the Alt+F11 keys together will also open this window.
-
-
3
Click the Insert menu and then select "Module" within the VB window. A new module will appear under the Modules tree in the left-hand pane of the window. A "module" is a window where a particular Visual Basic macro is saved.
-
4
Click in the right-hand pane of the window and type (or copy and paste) the following text into the window.
Sub showMessage()
MsgBox "Hello World!"
End Sub -
5
Click the Run menu at the top of the window, and select "Run Sub/UserForm" to run the new macro. Pressing F5 will also run the macro. A message box with "Hello World!" will appear within the worksheet window of Excel. Congratulations, you have just created your first macro.
Record Your First Macro - Table With Colors
-
6
Close the VB window where your "Hello World" macro was created. On the Excel Tools menu, click "Macro" and then "Record New Macro." Recording a macro allows Excel to create and record the Visual Basic code needed to complete a task or series of tasks for you. You don't have to know how to write the code; Excel will create it for you.
-
7
Type "TableColor" as the name of the macro in the new window that appears. Click OK to begin recording the macro.
-
8
Place your cursor in the worksheet's top left cell, A1. Type "Name" and press the Tab key. You'll move to cell B1. Type "Age" and press Enter.
Type "Aaron" in cell A2, and press the Tab key. Type "29" in cell B2 and press Enter.
-
9
Drag your mouse cursor across cells A1 and B1 to highlight and select them. There should be a dark black box around both cells.
Find the button up in the toolbar area that looks like a paint bucket with a yellow stripe under it and click it. Notice that now the "Name" and "Age" cells have yellow backgrounds.
-
10
Click the Tools menu, then Macro, and then Stop Recording. This will stop recording the macro.
-
11
Press Alt+F11 on your keyboard to open the VB editor window.
-
12
Double-click in the left-hand pane on Module 2. You will notice the code for the newly recorded macro in the right-hand portion window pane.
-
13
In the Excel window, highlight all the rows with the text you typed earlier to record the macro and delete it. Switch back to the Visual Basic Editor window.
Press the "F5" key to run the macro and switch to the Excel window to see that all the text and colors have been created again.
-
1
Tips & Warnings
Recording a macro is an excellent way to quickly and efficiently automate any simple repetitive actions, especially if you aren't up to speed on VB syntax.
Use the Shortcut Key option in the Record New Macro window by typing in a letter or number to run your macro easily without having to run it through the Tools menu.
When you open any Excel file that has macros saved in it, a security window will warn you. If you plan to run the macros, you'll need to select "Enable Macros" to be able to use them.
When opening an Excel file with embedded macros, make sure you trust the creator of the file. Viruses that can be harmful to your computer can be transmitted within Visual Basic macros.
Resources
- Photo Credit binary digits - computer science image by drx from Fotolia.com
