VBA    Visual Basic for Applications & Excel

Gilberto E. Urroz - ph. 435-797-3379 - gurro@cc.usu.edu - ENGR 223 - fax 435-797-1185 - Office Hours

Visual Basic for Applications (VBA) is a macro language available in most Microsoft Office applications and in other non-Microsoft software (e.g., ArcInfo).   VBA follows the syntax of Visual Basic, and a Integrated Development Environment (IDE) is available in the different applications that offer VBA.  In this page you will find some basic information on how to get started with VBA within the Microsoft Excel environment.

Links in this page

Introduction to the use of Visual Basic for Applications with MS Excel   

(From the Monday, March 18, 2002 lecture for my Graduate-level VB/VBA course)

• Click here to download a zip file containing instructions and examples. - Read the instructions document and try the examples included to get you started in using VBA with Excel.
• Here is a summary of the topics presented in this class:

1. Visual Basic for Applications (VBA) is a dialect of Visual Basic available in all Microsoft Office applications as well as in other MS software (e.g., Visio) or non-MS software (e.g., AutoCAD, ArcINFO).
2. To access the VBA IDE (Integrated Development Enviroment) find the Visual Basic toolbar by doing a right-click on any empty space of the Excel top tool bar and selecting the corresponding toolbar.  Click on the first button in the VB toolbar to get you to the VB IDE.             
3. The VBA IDE provides a project navigation tree window (labeled Project - VBA Project) that lists all Excel/VBA projects currently open.  This window may include a number of password-protected files which are VBA projects loaded with Excel -- e.g., Adobe Acrobat writer -- and which cannot be modified by the user.  Your current project will be listed, by default, as VBA Project(Book1).  If you save it with other name, the change will be reflected in the navigation tree window.   [VBA IDE Figure].
4. Under your current project (i.e., your current Excel workbook), you will find a list of worksheets (by default there will be three worksheets named Sheet1, Sheet2, Sheet3) followed by the workbook itself (default name, This Workbook).  Also, a branch of the file tree may link to a Modules folder with at least one module (default name, Module1).   If no Modules folder is available, and you want to add a module to your project (workbook), use the Insert>Module option in the VBA IDE. [Module Added Figure].
5. A Macro is a VBA program that can be either recorded by the user or typed as VB code.  
6. To record a macro use the option Tools>Macro>Record new macro... Any action you take between pressing the OK button and stopping the recording will be sent to a VBA program within the first VB Module in your project.  If no modules are available at this point, Module1 will be added to the project.
7. To see the macros available in your current project (workbook), use the option Tools>Macro>Macros.  This will produce a form listing all macros (if any) available to your workbook. [Macros window].
8. To run a macro, after opening the form listing the available macros, click on the macro name and press the Run button.
9. To edit a macro,  after opening the form listing the available macros, click on the Edit button.  This will take you to the location of the macro within the VB IDE.   The object to which the macro is attached (i.e., workbook, worksheet, form, or module) will be highlighted in the VB IDE's navigation tree window.
10. The Excel control toolbox is useful for adding command buttons and other VB controls to your spreadsheet.  Do a right click on any empty space on the Excel top tool bar and select the Control Toolbox.  [Excel control toolbox].
11. The Control Toolbox contains a Design Mode toggle button, a Properties button, and a Code button, as well as a number of controls (check button, command button, list box, combo box, toggle button, spin button, scroll bar, label, image, and additional controls).
12. Command buttons can be used to start a program from a worksheet.   Place a command button by clicking on the command button icon in the Excel control toolbox.  Place and dimension the command button within the worksheet of interest. 
13. To modify the properties of the control button do a right-click on the button and select the Properties option.  A properties window appears where you can modify the button's properties such as the name, caption, font, etc. [Command button properties window]
14. To add code to the control button double-click on the button.  This will send you to the code area for the current worksheet in the VBA IDE.   Type your code using Visual Basic.
15. If you want to assign a macro to a button, add the code Call Macro_name() to the button code.   
16. To get numerical data from the worksheet into your VBA code use functions Range or Cells.  For example, to get the value of cell "B2" into variable a in your VBA code use:     

    a = Range("B2").Value

    Alternatively, you could use 

    a = Cells(2,2).Value 

    to load your data value.

17. To place data from your VBA code to the worksheet use the same functions Range and Cells, e.g.,

    Range("C2").Value = r1

    Cells(3,2).Value = r1

     

18. To place string data from your VBA code to the worksheet use, for example:

    Range("M2") = "Int. = "

    Cells(15,2) = "Int. = "

     

19. Notice that function Range takes as argument a string referring to the cell of interest, e.g., Range("F15"), while the function Cells takes two arguments, namely, the indices for the row and column of the cell of interest, e.g., Cells(25,31).
20. You can use functions MsgBox and InputBox for output and input, respectively, into your VBA program.
21. Excel provides a number of functions that are not available in Visual Basic, e.g., ACOS, ASIN, BESSELI, etc.   You can use the cells in the worksheet to evaluate formulas that use these functions and then bring those values into your VBA code.
22. To see a list of the functions available in Excel, click on a cell in a worksheet, place and equal sign in it and then click on the button labeled fx in the Excel toolbar.  A listing of the functions by categories will be provided.  The categories include All, Date & Time, Math & Trig, Statistical, etc.   Click on any category to get a listing of functions available. [Functions list window]
23. There is a category of functions referred to as Engineering functions that include advanced engineering mathematical functions, e.g., Bessel functions, complex number conversion, error function, etc.  If that category is not available in your Excel functions, use the option Tools>Add Ins... and mark the boxes Analysis Toolpack and Analysis Toolpack - VBA to add the engineering functions.  Press OK to load the functions.
24. To label Excel cells with variable names, use the option Insert > Name > Define after selecting the cell (or cells) of interest.  Type a name for the cell and that name will serve as the cell identifier.   This identifiers are only available for operations within Excel (e.g., formulas defined in cells).  The identifier's names are not passed on to VBA code.
25. Advantages and disadvantages of using Excel-VBA over Visual Basic: 
    • The spreadsheet is the interface itself, you don't need to design and create an input form -- although you can create one within Excel.
    • Many mathematical and non-mathematical functions are accessible through the spreadsheet (see items 22 and 23 above) which are not available in Visual Basic.
    • Excel provides graphics which are relatively easy to manipulate compared with graphics within Visual Basic.
    • Main disadvantage: cannot create stand-alone program with Excel/VBA.  The user need to have Excel in his or her computer to be able to use your Excel/VBA programs.

Return to Top of Page

More examples on Visual Basic for Applications and Excel

(From the Monday, April 15, 2002 lecture for my Graduate-level VB/VBA course)

 A number of examples were presented on Visual Basic for Applications with Excel.  Click here to download the examples.  The examples included the following subjects:

1. Examples of numerical methods in Excel spreadsheets.
2. Use of the Range function for manipulating data in the Excel spreadsheet.
3. Use of the method Evaluate(Application.WorksheetFunction.Substitute(expression, "x", variable)) for evaluating expressions in worksheet cells.
4. Use of user forms for input/output in Excel/VBA.
5. Use of the VBA Object Browser to identify Excel objects, their properties and methods.
6. Manipulation of Chart object properties.

Return to Top of Page

Books on Visual Basic for Applications (VBA)

1. Walkenbach, J., 1999, EXCEL 2000 PROGRAMMING FOR DUMMIES(R), IDG Books Worldwide, Inc., Foster City, CA 94404, ISBN 0-7645-0566-1 [Reference for Visual Basic for Applications with MS Excel - Link above links to the Excel 97 version of the book, I couldn't find the listing for the Excel 2000 version of the book].
2. Roman, Steven, 1999, Writing Excel Macros, O'REILLY, Sebastopol, CA 95472, ISBN 1-56592-587-4
3. Walkenbach, J., 1999, Microsoft Excel 2000 Power Programming with VBA, Hungry Minds Inc., New York, N.Y., ISBN 0-7645-3263-4 (Click here for author's webpage)

Return to Top of Page

VBA Internet links

1. A directory of Excel/VBA-related pages\
2. Anthony's Excel VBA web page - lots of information in this one!
3. OzGrid's VBA/Excel page
4. XLStatistics - an Excel statistics workbook
5. The Spreadsheet page (see reference 3 in books)
6. Tommy Flynn's VBA/Excel page
7. VBA 101 by MicroSoft
8. VBA Online book by MicroSoft

Return to Top of Page