How to Type a VBA Procedure and Run a VBA Procedure

How to Type a VBA Procedure and Run a VBA Procedure

How to Type a VBA Procedure and Run a VBA Procedure

Sub procedures are capable of doing a week's worth of work in Microsoft Excel in seconds.

Microsoft Excel Visual Basic for Applications (VBA) is one the most advanced versatile programming environments on the market today for developing advanced business, engineering and scientific tools. Below is a brief introduction on how to type and run a VBA Sub procedure.

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. 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 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. 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. 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. 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 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).

Next Excel Training Dates - Public Training and Live Online Courses

Our next public Excel training workshops and live online Excel courses are listed below. For all of the dates and times, please visit our Training Schedule page using the link above in the main menu.

Business, Engineering and Science
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 for Combined Audience
Beginning Excel Training for Business
Next Date:
See Schedule
Intermediate Excel Training for Business
Next Date:
See Schedule
Advanced Excel Training for Business
Next Date:
See Schedule
Beginning Microsoft Excel VBA Training for Business
Next Date:
See Schedule
Intermediate Microsoft Excel VBA Training for Business
Next Date:
See Schedule
Advanced Microsoft Excel VBA Training for Business
Next Date:
See Schedule
Advanced Excel Training for Engineers / Scientists
Next Date:
See Schedule
Beginning Excel VBA Training for Engineers / Scientists
Next Date:
See Schedule
Intermediate Excel VBA Training for Engineers / Scientists
Next Date:
See Schedule
Advanced Excel VBA Training for Engineers / Scientists
Next Date:
See Schedule
Microsoft Excel Dashboard Training
Next Date:
See Schedule

Public Training Location

  • Santa Barbara, CA
  • Los Angeles, CA
  • Pasadena, CA
  • Reseda, CA
  • Chatsworth, CA
  • Topanga, CA
  • Santa Clarita, CA
  • Newbury Park, CA
  • Camarillo, CA
  • Calabasas, CA
  • Valencia, CA
  • West Hollywood, CA
  • Goleta, CA
  • Palmdale, CA
  • San Fernando, CA
  • Burbank, CA
  • La Canada, CA
  • Altadena, CA
  • Culver City, CA
  • Rosemead, CA
  • Anaheim, CA
  • El Segundo, CA
  • Fillmore, CA
  • Gardena, CA
  • Encino, CA
  • Oxnard, CA
  • Mojave, CA
  • La Crescenta, CA
  • Glendale, CA
  • Flintridge, CA
  • Beverly Hills
  • Arcadia, CA
  • Inglewood, CA
  • Hawthorne, CA
  • Manhattan Beach, CA
  • Santa Paula, CA
  • Century City, CA
  • Sherman Oaks, CA
  • Reseda, CA
  • Woodland Hills, CA
  • Hollywood, CA
  • Studio City, CA
  • Fillmore, CA
  • Ventura, CA
  • Moorpark, CA
  • Simi Valley, CA
  • Santa Monica, CA
  • Encino, CA

EMAGENIT performs Excel onsite company training in the cities listed above as well as the entire Northern and Southern California areas, United States and Canada.

LinkedIn
Facebook
Information
Need Help? Please call us at 1.866.924.6244

Copyright © 2002-2017

EMAGENIT All Rights Reserved