Visual Basic for Applications

This document contains a short introduction to VisualBasic for applications. In need of further information? Send me an e-mail.
A pdf-version (130kByte) is available at http://www.mahlitz-net.de/.
The zipped pdf-version is still available (123kByte).

Content
1. Introduction to programming "VisualBasic for Applications" (VBA)
  1.1 variables, constants und types

  1.2 Procedures and functions

  1.3 Loops
    1.3.1 For ... To ... Next
    1.3.2 For Each ... Next
    1.3.3 Do While/Until ... Loop
    1.3.4 Do Loop ... While/Until

  1.4 Branches
    1.4.1 If ... Then
    1.4.2 Select Case

  1.5 Objects
    1.5.1 Attributes
    1.5.2 Methods

  1.6 Comments

2. Appendix
  2.1 Sources

1. Introduction to programming "VisualBasic for Applications" (VBA)

Every "Microsoft Office"-program (and some others too) put an object-library at the programmers disposal. This library contains the interfaces to any function the program has.
VBA uses this object-library.

1.1 variables, constants und types

Variables and constants are located in reserved places of memory. They store numbers, letters or even whole types. Variables are declared with the Dim statement:

Dim VariablesName As VariablesType

Dim iColumn As Integer

allocates a 2 bytes of dynamic memory large area named iColumn for storing values of integer numbers (-32.768 to 32.767).
At this point you can assign values to the variable iColumn.

iColumn = 12345

Global variables are declared on top of the module.

Public g_iColumn As Integer

Now, any function or procedure can access the variable g_iColumn.

On top of the module user-defined types can be declared by using the statement Type.

Type TTesttyp
  Variablesname1 As Integer
  Variablesname2 As String
End Type

Now you can assign variables to this type (look for chapter variables). This variables behave like objects, Variablesname1 and Variablesname2 are the "properties" of the type TTesttyp.

Important keywords for declaring variables are Public, Private and Static. The usage of one of these describe, how other procedures or functions can access these variables.

"Public" declared variables can be accessed program-wide, any procedure is allowed to access this variable. "Private" declared variables are visible only in the module, they were declared in. If a variable is declared "Static" in a procedure or function, it is accessible after the procedure has finished.

1.2 Procedures and functions

Procedures and functions are sub-programs, which can take parameters. Procedures and functions are used if equal "jobs" are done at different places in the source code, for example the printing of "done" or calculating the square root of a number.

Procedures in VBA are defined with the Sub-statement followed by the name and an optional list of parameters.

This procedure sets the values of the cells in row Var1 in the columns 2 and 3 to zero

Sub Test (Var1 As Integer)
  Cells(Var1, 2).Value = 0
  Cells(Var1, 3).Value = 0
End Sub

Functions are able to, in difference to procedures, return values. They are inevitable, if complex algorithms are used to calculate values.

Functions are declared with the Function-statement followed by the name of the function, the (optional) list of parameters and the type of return.

This function computes the product of the square root of Var1 and the sine of Var1 and adds the cosine of Var1.

Function Test2(Var1 As Single) As Single
  Test2 = Sqrt(Var1) * Sin(Var1) + Cos(Var1)
End Function

Procedures and functions are called with its name and the (optional) list of parameters.

Sub mainprogram ()
  Test (5)         'sets the cells in row 5 an columns 2 and 3 to zero
  xyz = test2 (7)  'the variable xyz now contains the square root of 7 *sine of 7 + cosine of 7
End Sub

The mainprogram sets the values of the cells (5,2) and (5,3) to zero and stores the result of the multiplication of square root of 7 and sine of 7 added to cosine of 7 in the variable xyz.

Parameters are passed in different ways. ByRef ahead of the variable's name says, that the variable is referenced, the procedure accesses the variable directly and is able to change the value of it. ByVal facing the variables name says, that a copy of the variable is made, changes doesn't result in changes outside of the sub-program. If the statement Optional is used, every parameter declared after this one must be declared Optional. Optional says, that this parameter doesn't need to be passed.

1.3 Loops

Loops are used to define repeated actions. The different types of loops supported by VBA variate in the type of stopping-condition and the minimal number of runs.

1.3.1 For ...To ... Next

A For-To-Next-Loop runs until the counter (a variable of a countable type) exceeds the stopping value (in this example the value of the variable endvariable). Counting starts at the beginning value (in this example the value of the variable startvariable). If the loop need to be counting backwards, the variable stepvariable needs to be negative. In this case the loop stops after the counter falls below the endvariable.

In "normal" cases, that means if the counter isn't changed in the loop, For-To-Next-Loops run "stopping value minus beginning value"-times. The optional stepping value divides the number of go-throughs.

For counter = startvariable To endvariable [Step stepvariable]
  [statements]
Next [counter]

The loop can be exited at any time with the following statement:

Exit For

1.3.2 For Each ... Next

A For-Each-Next-Loop runs till every member of a group was accessed.

For Each Element In Group
  [statements]
Next [Element]

For-Each-Next-Loops can be left, like the For-To-Next-Loop, with this statement:

Exit For

1.3.3 Do While/Until ... Loop

A Do-While-Loop loop runs if the condition set at the beginning of the loop is true. The minimal number of runs is zero, in this case the condition was false at the time of entering the loop. A Do-Until-Loop loop runs till the condition set at the beginning of the loop is true.

Do [{While | Until} condition]
  [statements]
Loop

An early exit is done with:

Exit Do

1.3.4 Do Loop ... While/Until

A Do-Loop-While loop runs if the condition set at the end of the loop is true. The minimal number of runs is 1. A Do-Loop-Until loop proceeds till the condition set at the end of the loop is true.

Do
  [statements]
Loop [{While | Until} condition]

The command for early exit is:

Exit Do

1.4 Branches

Branches allow the execution of different statements belonging to different conditions.

1.4.1 If ... Then

If, for example, statement "a" need to be performed if the variable b is 5, following instructions will work:

If b = 5 Then statement a

For all other cases (b <> 5), this code need to be implemented:

If b = 5 Then
  statement a
Else
  statement c
End If

The universal syntax:

If condition_1 Then
  [statements_1]
[ElseIf condition_n Then
  [statements_n] ...
[Else
  [statements_0]]
End If

The statements_1 are executed if condition_1 is true, in all other cases the program continues checking the condition_2 and to stements_n.

1.4.2 Select Case

If there's only one variable, which needs to be checked for different values the "Select-Case" branch is very efficient. An If-Then construction can be used everytime you can use a "Select-Case" but the source code is easier to read and the program runs a little faster if you use "Select-Case".

Select Case Testvariable
[Case conditions
  [statements]] ...
[Case Else
  [statements]]
End Select


Select Case z
  Case 1
    statement a
  Case 2
    statement b
  Case 3
    statement c
  Case Else
    statement d
End Select

Executes statement a if z = 1, if z = 3 statement c will be run and so on.

If z = 1 Then
  statement a
Else
  If z = 2 Then
    statement b
  Else
    If z = 3 Then
      statement c
    Else
      statement d
    End If
  End If
End if

Imagining a lot of source code for each statement a "Select-Case" will do better.

1.5 Objects

Objects are very important in VisualBasic for Applications. They have attributes (comparable to variables) and methods (similar to procedures). The Excel-Object "cells" has the attribute column and the method select. Attributes can, but not need to, be objects too.

Using objects, structures and dependencies will come much clearer.

1.5.1 Attributes

Attributes are accessed like variables. Some attributes are read-only, assigning values to such variables leads to errors. Usually this attributes can be changed by methods. The advantage of such a construction comes clear while thinking about a variables value affecting other variables. One application's inside counter, pointer or variables can be set, for instance.

Application.Workbooks.Count is a read-only attribute.

Application.Workbooks.Count = Application.Workbooks.Count + 1

directly leads to an error-message, while

set Variable = Application.Workbooks.Add

processes without failure.

Repeatedly accessing attributes of an object can be simplified by using the With-statement

With Application
  .ScreenUpdating = False
  .Statusbar = "Working..."
  .Height = 400
End With

instead of

Application.ScreenUpdating = False
Application.Statusbar = "Working..."
Application.Height = 400

increases readability.

1.5.2 Methods

Methods access attributes of objects, without letting the programmer know, which special attributes the method changes.

Assistant.Move 100, 100

for instant, moves the assistant to the coordinates 100, 100 of the screen. Additionally the attributes "left" and "top" of the assistant-object are changed. It is absolutely possible to set these attributes by hand to 100, the effect stays the same. With more complex methods the number of modified attributes grows and it is much more comfortable (despite the possibilities of the "with"-construction) to use the corresponding methods.

1.6 Comments

Using comments is recommended, it is quite easier to understand a source code with comments than code without. Comments are text, which doesn't influence the program's running, they are only visible in the source code but not in the compiled program (VBA doesn't support compilers, so this advantage is just for readability).

Comments are introduced by a leading '. It isn't possible to write source code after a comment, the comment starts at the ' and stops at the end of line.

2. Appendix

2.1 Sources

Microsoft Excel 97 VisualBasic
Reed Jacobson
Microsoft Press Deutschland

Microsoft Excel 97 - Das Handbuch
Reinke Solutions Team
Microsoft Press Deutschland

Microsoft Office 97 - Visual Basic Programmierung
Günter Born
Microsoft Press Deutschland

Newsgroup: microsoft.public.de.excel

Newsgroup: microsoft.public.office.developer.vba

Google-Groups: http://groups.google.com

Metager searchengine: http://www.metager.de

Microsoft Excel 97 VisualBasic Online-Help