Note: Please read the other VBA pages first from the resource center before proceeding.

Overview

Macros or procedures as they are known in VBA, are units that your type in a module that when run, perform some task in Excel like processing data. When typing a procedure, it is just like typing in Microsoft Word. However, merely typing a procedure in a module does nothing, it is just text, you must run it to carry out its instructions. Procedures can be run from the VBA Editor, by clicking buttons or pictures on a worksheet, by event like when a user types in a worksheet cell, the list is endless. The following set of instructions demonstrates how to run a Sub procedure from the VBA Editor. The procedure's task will be to flag data in column A on a worksheet red.

The Difference Between a Sub Procedure and Function Procedure

As just stated, you type procedures in a module to perform your Excel tasks. The next question which procedure do I use, a Sub procedure or Function procedure? This is actually very simply, Sub procedures are created when you want to automate Excel like process data, build charts, copy and paste, sort...etc. Function procedures are created when you want to perform a calculation like the Sum worksheet function does. Function procedures can be run directly from a worksheet formula allowing you to build your own custom worksheet functions. Be aware however that Sub procedures can also perform calculations. For our exercise, we will create a Sub procedure.

Building and Running a Sub Procedure

1. Close Excel than reopen it. Have only a single workbook open.

2. In Excel, press the Alt+F11 keys to launch the VBA Editor. When the Editor appears, this where you type and debug your VBA code.

3. Once the Editor is visible, proceed to the Editor menu bar and select View/Project Explorer. This will launch the Project Explorer window if it is not already visible. This window is where you track your VBA projects and add to them. Note that VBA projects are attached to workbooks automatically, therefore to save your code just save your workbook.

4. Find the Explorer window on the left hand side of the Editor window, find the VBAProject(Book1) icon, click on it, proceed to the Editor menu bar and select Insert/Module. A module will be inserted into the VBA Project and will open on the right hand side. This is where you type your macro or Sub procedure.

5. Click in the module Window and type the following:

Sub color_red( )

 

For r = 1 to 20

If Cells(r,1).Value=1 then Cells(r,1).Interior.ColorIndex=3

Next r

 

End Sub

6. The procedure uses a loop, For r = 1 to 20...Next r, to scan rows 1-20 on a worksheet. It is merely a counter but will loop the code inside its boundary 20 times. Loops are used to represent among other things the start and stopping rows on a worksheet you want to process. So switch it to 2 to 2000 to go from row 2 to row 2000 skipping the headers in row 1 if you have them. The Cells(r,1) command actually takes the counter r and uses it as an argument for its row argument. The Cells command takes a row column index and we typed the 1 in the command to represent column A on the worksheet. Want to look at column B, put 2 there and so forth but be sure to change out all the 1's in all the Cells commands. As the loop counts, the Cells command accesses A2, A3, A4 and so forth down the sheet. The IF code decides if 1 has been found and if so runs the code after Then. This code tells Excel to color the cell red via the number 3. If you want to learn how to really use VBA, you must learn the basics of programming, loops, logic. variables, how the code looks (syntax)...etc. Just switching on the Macro recorder does not provide any engine or brains for you procedure to operate with and the code is usually very twitchy.

7. Press Alt+F11 to toggle back to Excel, put some numbers in column A between rows 1 and 20. Be sure to type a 1 in some of the cells to flag.

8. Press Alt+F11 to toggle back to the Editor. To run the procedure, click on the first line and press the F5 key. Now toggle back to Excel and see if it colored the cells properly.

Now sit back and think about processing 30,000 or 500,000 rows of data, reconciling on another worksheet then charting automatically in about 5 to 10 seconds. Now think about doing it manually and the days or weeks it would take if the data is complex. This example is why you want to learn VBA and is only a small fraction of its power. What is shown above is what we introduce you to in the first hour of our Beginning VBA workshops. These workshops can be located below on our home page or via the links below.

Back to the Resource Center

How We Can Help With Your Training?