Excel Training, Excel VBA Training, Excel Software Design

How to Type a VBA Procedure and Run a VBA Procedure

How to Type a VBA Procedure and Run a VBA Procedure

Microsoft Excel VBA Tutorial

Sub procedures are capable of doing a weeks worth of work in Microsoft Excel in seconds.

VBA Procedure Overview

Macros or procedures as they are known in VBA, are blocks that your type in a module that when run, perform some task in Excel like processing data. When you type a procedure in a module, 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 and so on. 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. 1. Close Excel than reopen it. Have only a single workbook open.
  2. 2. In Excel, press the Alt+F11 keys to launch the VBA Editor. When the Editor appears, this is 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. Note that in Excel versions 2007 and above you will save the workbook as a Macro enabled workbook.
  4. 3. 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 Sub procedure.
  5. 4. Click in the module Window and type the following:
    1. Sub color_red( )
      1. For r = 1 To 20
        1. If Cells(r,1).Value=1 Then Cells(r,1).Interior.ColorIndex = 3
      2. Next r
    2. End Sub
  6. 5. 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. 6. 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. 7. Press Alt+F11 to toggle back to the Editor. To run the procedure, click on the first line of the procedure 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 1 min. 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 on our Excel Training Options page (menu bar).

Our Available Public Excel Seminars/Webinars
Microsoft Excel Seminars/Webinars
Beginning Excel Training for Business Professionals Intermediate Excel Training for Business Professionals Advanced Excel Training for Business Professionals Beginning Excel VBA Training for Business Professionals Intermediate Excel VBA Training for Business Professionals Advanced Excel VBA Training for Business Professionals Advanced Excel Training for Engineers / Scientists Beginning Excel VBA Training for Engineers / Scientists Intermediate Excel VBA Training for Engineers / Scientists Advanced Excel VBA Training for Engineers / Scientists Microsoft Excel Dashboards Training
Beginning Excel Training for Business Intermediate Excel Training for Business Advanced Excel Training for Business Beginning Microsoft Excel VBA Training for Business Intermediate Microsoft Excel VBA Training for Business Advanced Microsoft Excel VBA Training for Business Advanced Excel Training for Engineers / Scientists Beginning Excel VBA Training for Engineers / Scientists Intermediate Excel VBA Training for Engineers / Scientists Advanced Excel VBA Training for Engineers / Scientists Microsoft Excel Dashboard Training
We can also train your company onsite. For a list of all of our onsite Excel classes click here or contact us at 805.498.7162.


Site Map

Copyright © 2002-2017

EMAGENIT All Rights Reserved