VBA Programming Basics

The VBA programming language provides the tools needed to create solutions when the macro recorder can’t get the job done.  VBA is such a powerful programming language that you can (if you have the programming ability) create a solution to virtually any problem you encounter in Excel.  In fact you can completely customize Excel through VBA and you can even change the entire Excel interface.

Object Oriented Programming
VBA is a structured programming language where sentences (called statements) are constructed of building blocks such as objects, methods, and properties.  These VBA statements are grouped in larger blocks called procedures.  A procedure is a set of VBA statements that performs a specific task or calculates a specific result.  The first step to learning how to create procedures is to learn about the building blocks.

Objects
VBA is an object-oriented programming language, which means the statements you create in VBA act on specific objects rather than begin general commands.  Excel is made of objects that you can manipulate through the VBA statements.  The entire workbook file is an object, an individual sheet is an object, a range within a sheet can be an object, and an individual cell is an object.  There are many more types of objects, and as you see objects can be containers for (called collections) other objects.

Collections
Some objects are collections of other objects.  For example, a workbook is a collection of all the objects it contains (ex. sheets, cells, ranges, charts, VBA modules, etc.).  A VBA statement that makes reference to the workbook names “Loan Calculator.xls” would appear like this:   

Workbooks(“Loan Calculator.xls”)

Methods
A method is an action that can be performed on an object. Excel VBA objects are separated from their methods by a period.  For example, if you wanted to save a particular file as part of a VBA program you could include the following sentence in the code: 

Workbooks(“Loan Calculator.xls”).Save

Properties
Properties are used to describe an object.  Some properties are read-only, while others are read/write.  These properties describe Excel objects.  For example, an Excel workbook has a particular path on your hard drive or network where it is saved.  That path is a property of the workbook.  That path is read-only as it cannot be changed without saving the file to a different location.  Properties are separated from objects by periods just as methods are.  The following sentence will display the current path of the Loan Calculator.xls file in an onscreen message box: 

Msgbox Workbooks(“Loan Calculator.xls”).Path 

Note: Msgbox is a function.  Functions will be discussed later in this section. 

If you want to set a read/write property equal to something, it changes the current value of that particular object.  If you don’t set a read/write property equal to something, Excel will tell you the object’s current value.  For example, the following sentence will set the sheet name of the first sheet in the workbook to “Cover” 

Sheets(“Sheet1”).Name = “Cover”

Functions
A VBA function is a lot like a workbook function in an Excel spreadsheet.  It performs a calculation and then returns the appropriate result.  Functions provide information that is useful in building VBA procedures.  In the previous example, the Msgbox function was used to display the path information on the screen.  

VBA Functions should not be confused with Function Procedures.  A Function Procedure is a series of Visual Basic statements enclosed by the Function and End Function statements. A Function procedure is similar to a Sub procedure, but a function can also return a value. A Function procedure can take arguments, such as constants, variables, or expressions that are passed to it by a calling procedure. If a Function procedure has no arguments, its Function statement must include an empty set of parentheses. A function returns a value by assigning a value to its name in one or more statements of the procedure.

Procedures
When you create VBA code inside an Excel workbook it will be stored as a “Module” within the workbook.  Unlike, Lotus 1-2-3 macros, these modules are not entered in cells but are still a part of the workbook files.  They can only be viewed by using the VBA Editor.

 

Code within a module is organized into procedures. A procedure tells the application how to perform a specific task. Use procedures to divide complex code tasks into more manageable units.  There are three types of VBA procedures: Sub, Function, and Property. 

The Insert Procedure Dialog Box 

The most common type of procedure is the Sub.  A Sub procedure is a series of Visual Basic statements enclosed by the Sub and End Sub statements that performs actions but doesn't return a value. A Sub procedure can take arguments, such as constants, variables, or expressions that are passed by a calling procedure. If a Sub procedure has no arguments, the Sub statement must include an empty set of parentheses.  The following is an example of a simple Sub: 

Sub Center_Across_Selection()

' Center_Across_Selection Macro
          ' Macro recorded 4/19/2003 by Carlton Collins
    With Selection
        .HorizontalAlignment = xlCenterAcrossSelection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.Style = "Comma"
    Selection.Font.Underline = xlUnderlineStyleSingleAccounting
End Sub

Variables and Constants
During the execution of some VBA macros there will be times when information that is either gathered from the user, returned by a function, or defined by the programmer that must be stored temporarily for use later on in the macro.  Sometimes this information will change during the execution of the code and sometimes it will be static.  Variables and constants are used to store this type of information.  In the following macro CoName is a variable: 

Sub Add_Footer()
    CoName = InputBox("Name your Company?", "Add Company Name to  Footer")
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = CoName
        .CenterFooter = ""
        .RightFooter = "&N"
    End With
End Sub 

Like a variable, a constant is a temporary holding place for some information that is used in a procedure.  However, as the name implies a constant never changes.  Constants must be declared.  A declaration statement in a VBA macro is used to define the value of a constant.  In the following macro the Company Name is declared in the first statement of the VBA code: 

Sub Add_Footer()
    Const CoName = “Accounting Software Advisor”
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = CoName
        .CenterFooter = ""
        .RightFooter = "&N"
    End With
End Sub 

The company name is enclosed in quotation marks.  All literal text (this is what strings are referred to in VBA programming), which is placed in a procedure, must be surrounded by quotation marks.

- END -