Visual Basic for Applications (VBA) in Excel - Introduction to programming

From Training Material
Jump to: navigation, search

Visual Basic for Applications

Visual Basic for Applications is simplified impementation of Microsoft's programming language Visual Basic 6. VBA is used for automation operations in applications such as Microsoft Excel and also expands it's capabilities. You can find VBA in all Microsoft Office applications, AutoCAD, WordPerfect and many others.

Visual Basic for Application appeared in Excel (Excel version 5) in 1994, earlier (up to version 4) only allow macros to automate those tasks that you can perform using the keyboard. Now, in addition to recording macros you can also independently write your own procedures.

Using Record Macro functionality

While learning VBA and also later on during work, you might find that Record Macro is very useful. It allows to make few clicks and see how those clicks should be written in code.

While using Record Macro as a learning tool pay attention to:

  1. Recording only one step at once
  2. Using the Select and Selection
  3. Using With blocks
  4. Many unnecessary commands

Basics of programming in VBA

Keyboard shortcuts

in Excel

  • Alt + F8 --> View Macros, the list of all recorded and written macros (sub procedures only)
  • Alt + F11 --> Open the Visual Basic editor


in Visual Basic Editor (VBE)

  • F5 --> Run Sub / Run UserForm
  • F8 --> Run Sub in step-by-step mode
  • Ctrl + Space --> Run IntelliSense


Customization of Visual Basic Editor

When start working with VBE you should consider some customizations.

If you start from the standard configuration you should:

  1. In Options (menu Tools):
    1. disable Auto Syntax Check (disables annoying notifications about syntax errors, only annoying but not all of them)
    2. enable Require Variable Declarations (adds Option Explicit to every new module)
  2. In Toolbars (menu View):
    1. Turn on Edit toolbar (contains comment and uncomment block option)


Types of procedures

There are two types of procedures: Sub and Function

Writing a Sub Procedure

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.

Syntax of Sub procedure

[Private | Public | Friend] [Static] Sub name [(arglist)]
[statements]
[Exit Sub]
[statements]
End Sub

Example:

 Sub SayHello()
   MsgBox "Hello world!"
 End Sub

Writing a Function Procedure

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.

Syntax of Function procedure

[Public | Private | Friend] [Static] Function name [(arglist)] [As type]
[statements]
[name = expression]
[Exit Function]
[statements]
[name = expression]
End Function

Example:

 Function RectangleArea(SideA As Double, SideB As Double) As Double
   RectangleArea= SideA * SideB
 End Function

Optional Arguments

You can also define functions to have Optional arguments. These are arguments that the user can supply if they want, but if they are omitted, the function will assign a default value to them.

 Function Rectangle(A As Double, B As Double, _
                    Optional Parimeter As Boolean = False) As Double
     
    If Parimeter = False Then
        Rectangle = A * B
     Else
        Rectangle = 2 * A + 2 * B
    End If
 End Function

Variables

Variable is a named storage location that can contain data that can be modified during program execution.

Each variable has a name that uniquely identifies it within its scope. A data type can be specified or not.

Variable names:

  • must begin with an alphabetic character,
  • must be unique within the same scope,
  • can't be longer than 255 characters,
  • can't be the same as VBA function or keywords used in VBA,
  • can't contain spaces.


Data types

This table contain mostly used types of variables.

To see all types please look up into Data Type Summary topic in VBE Help (in Visual Basic Editor press F1 and type Data Type Summary into search box).


Data type

Storage size

Range

Byte

1 byte

0 to 255

Boolean

2 bytes

True or False

Integer

2 bytes

-32,768 to 32,767

Long

4 bytes on 32-bit systems,
8 bytes on 64-bit systems

-2,147,483,648 to 2,147,483,647 on 32-bit systems,
-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 on 64-bit systems

Single
(single-precision floating-point)

4 bytes

-3.402823E38 to -1.401298E-45 for negative values;
1.401298E-45 to 3.402823E38 for positive values

Double
(double-precision floating-point)

8 bytes

-1.79769313486231E308 to -4.94065645841247E-324 for negative values;
4.94065645841247E-324 to 1.79769313486232E308 for positive values

Currency
(scaled integer)

8 bytes

-922,337,203,685,477.5808 to 922,337,203,685,477.5807

Date

8 bytes

January 1, 100 to December 31, 9999

Object

4 bytes

Any Object reference

String
(variable-length)

10 bytes + string length

0 to approximately 2 billion

Variant
(with numbers)

16 bytes

Any numeric value up to the range of a Double

Variant
(with characters)

22 bytes + string length (24 bytes on 64-bit systems)

Same range as for variable-length String


Declaring variables

Variables declared implicitly

Variables declared implicitly receive default data type VARIANT

Example:

a = 3

If you have enabled Option Explicit - implicit declaration of variables is not possible.

Declaration by the Dim statement

Dim VariableName As DataType

Declaring several variables at once

Dim a, b, c as Integer

What type are the variables a, b and c?

The correct statements

Dim a as Integer, b as Integer, c as Integer

or each variable separately

Dim a as Integer
Dim b as Integer
Dim c as Integer

Scope and Visibility

Scope refers to the availability of a variable, constant, or procedure for use by another procedure.

There are three scoping levels: procedure-level, private module-level, and public module-level.

You determine the scope of a variable when you declare it. It's a good idea to declare all variables explicitly to avoid naming-conflict errors between variables with different scopes.


DIM - local variable; a variable or constant defined within a procedure is not visible outside that procedure - only the procedure that contains the variable declaration can use it.

PRIVATE - private variables are available only to procedures in that module or user form

PUBLIC - public variables are available to all procedures in all modules in a project, public variables and constants has to be declared in the Declarations section of a module

STATIC - variables declared with the Static statement retain their values as long as the code is running

Example:

 Static FirstVariable as Integer
 Public SecondVariable As String
 Public Const FirstConstatn= "value of constant"

Private and Public variables and constants has to be declared in the Declarations section of a module (above first procedure).

Lifetime of variables

The time during which a variable retains its value is known as its lifetime. The value of a variable may change over its lifetime, but it retains some value. When a variable loses scope, it no longer has a value.

a) A procedure-level variable declared with the Dim statement retains a value until the procedure is finished running. If the procedure calls other procedures, the variable retains its value while those procedures are running as well.

b) In a standard module or a class module, it retains its value until you stop running your code.


Data types checking

TypeName - returns a String that provides information about a variable.

Sub CheckDataType()
Dim a, b, c, d, e, f
    a = "text"
    b = 23
    c = 4565768
    d = 45.67
    e = True
    f = #1/1/1999#
 
'Debug.Print result is returned to the Immediate Window (Ctrl + G)
Debug.Print TypeName(a)   'String
Debug.Print TypeName(b)   'Integer
Debug.Print TypeName(c)   'Long
Debug.Print TypeName(d)   'Double
Debug.Print TypeName(e)   'Boolean
Debug.Print TypeName(f)   'Date

End Sub


IsNumeric - Returns a Boolean value indicating whether an expression can be evaluated as a number.

IsNumeric(7)         'returns True
IsNumeric("7")       'returns True
IsNumeric("seven")   'returns False

Constants

Declares constants for use in place of literal values. Constans are Private by default.

Syntax:

[Public | Private] Const constname [As type] = expression

Example:

 Const VAT As Double = 0.23


Operators

Operators are used to:

  • perform mathematical calculations
  • perform comparisons
  • combine strings
  • perform logical operations


Arithmetic Operators


^ raise a number to the power of an exponent

- difference between two numbers or indicate the negative value of a numeric expression

* multiply two numbers

/ divide two numbers and return a floating-point result

\ divide two numbers and return an integer result

Mod divide two numbers and return only the remainder

+ sum two numbers


Comparisons Operators


< less than

<= less than or equal to

> greater than

>= greater than or equal to

= equal to

<> not equal to

Is compare two object reference variables (object1 Is object2, SourceRange Is Nothing)

Like compare two strings (string Like pattern, "something" Like "s*")


Concatenation Operators


+ sum two numbers or string concatenation

& string concatenation of two expressions


Logical Operators


Not logical negation

And logical conjunction (true when all of expressions are true)

Or logical disjunction (true when at least one of expressions is true)

Xor logical exclusion (true if one, and only one expression is true)


Operator Precedence

When several operations occur in an expression, each part is evaluated and resolved in a predetermined order called operator precedence.

When expressions contain operators from more than one category, arithmetic operators are evaluated first, comparison operators are evaluated next, and logical operators are evaluated last. Comparison operators all have equal precedence; that is, they are evaluated in the left-to-right order in which they appear. Arithmetic and logical operators are evaluated in the following order of precedence:

Arithmetic Operators

Comparision Operators

Logical Operators

raise a number to the power (^)

equal (=)

Not

negative value (-)

difference(<>)

And

multiply, divide(*, /)

less than(<)

Or

dividing (\)

greater than (>)

Xor

modulo (Mod)

less than or equal to (<=)

Eqv

sum, subtract(+, )

greater than or equal to (>=)

Imp

concatenation (&)

Like
Is

 


Conditional Expressions

Conditional statements evaluate whether a condition is True or False, and then specify one or more statements to run, depending on the result. Usually, a condition is an expression that uses a comparison operator to compare one value or variable with another.

If...Then...Else Statement

Conditionally executes a group of statements, depending on the value of an expression.

Syntax:

 If condition Then [statement] [Else elseStatement]

or, you can use the block form syntax:

 If condition Then
     [statements] 
 
   [ElseIf condition-n Then
     [elseIfStatements] ... 
 
   [Else
     [elseStatements]]
 
 End If

You can use the single-line form (first syntax) for short, simple tests. However, the block form (second syntax) provides more structure and flexibility than the single-line form and is usually easier to read, maintain, and debug.


Select Case Statement

Executes one of several groups of statements, depending on the value of an expression.

Syntax:

 Select Case testExpression
   [Case expressionList-n
       [statements-n]] ...
   [Case Else
       [elseStatements]]
 
 End Select


Exercises

  1. Write a procedure which asks for your name using INPUTBOX and then displays a welcome message using MSGBOX.
  2. Write a procedure that will check if the number is greater, smaller or equal to 7. The program at the end should display the number introduced by the user and the appropriate message.
  3. Write a procedure that will check if the number is odd or even. The program at the end should display the number introduced by the user and the appropriate message.
  4. Use MsgBox to display a message asking user whether they want to continue or not, and process answer.
  5. Write a program that asks you to enter two numbers and then display greater of them.
  6. Write a program that asks you to enter three numbers, then displays the greatest of them.
  7. Write a program evaluating the students tests. The program should asks you to enter number of points and then display the number of points and the score:
    • points 91 to 100 - A
    • points 81 to 90 - B
    • points 71 to 80 - C
    • points 61 to 70 - D
    • points 51 to 60 - E
    • points 0 to 50 - F
    • > 100 or < 0 - error message
  8. When you finish all exercises try to find weak points of your procedures. For example try to type wrong input data (seven as a word instead of a digit).


Loops

Looping allows you to run a group of statements repeatedly. Some loops repeat statements until a condition is False; others repeat statements until a condition is True. There are also loops that repeat statements a specific number of times or for each object in a collection.

For ... Next

You can use For...Next statements to repeat a block of statements a specific number of times. For loops use a counter variable whose value is increased or decreased with each repetition of the loop.

syntax:

For counter = start To end [Step step]
  [statements]
  [Exit For]
  [statements]
Next [counter]


example:

Sub ForLoop_example()
  Dim i As Long
  For i = 1 to 5
    MsgBox i
  Next 
End Sub


For Each ... Next

For Each...Next statements repeat a block of statements for each object in a collection or each element in an array. Visual Basic automatically sets a variable each time the loop runs.

syntax:

Dim variableName As Variant
For Each variableName In collectionName
  [statements]
  [Exit For]
  [statements]
Next variableName


example:

Sub ForEachLoop_example()
  Dim wks As Variant
  For Each wks In Worksheets
    MsgBox wks.Name
  Next arkusz
End Sub


Do While (or Until) ... Loop

You can use Do...Loop statements to run a block of statements an indefinite number of times. The statements are repeated either while a condition is True or until a condition becomes True.

Repeating Statements While a Condition is True
Repeating Statements Until a Condition Becomes True

There are two ways to use the While (and Until) keyword to check a condition in a Do...Loop statement. You can check the condition before you enter the loop, or you can check it after the loop has run at least once.

syntax:

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


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


example:

Sub DoUntil_example()
  Dim x
  Do
    x = InputBox("")
    Debug.Print x
  Loop Until x = ""
End Sub


Exit For/Do

You can exit a For...Next statement before the counter reaches its end value by using the Exit For statement. The same applies for the For Each ... Next statement and Do ... Loop. In the last case use Exit Do.

Exercises

1. What will be the output of the following loops?

A

Dim i As Integer
i = 10
Do While i < 10
  MsgBox i
  i = i + 1
Loop

B

Dim i As Integer
i = 10
Do Until i >= 10
  MsgBox i
  i = i + 1
Loop

C

Dim i As Integer
i = 10
Do
  MsgBox i
  i = i + 1
Loop While i < 10

D

Dim i As Integer
i = 1
Do
  MsgBox i
  i = i + 1
Loop Until i >= 10

E

Dim i As Integer
i = 13
Do
  MsgBox i
  i = i - 2
Loop Until i < 2

F

Dim i As Double
For i = 0.9 To 0 Step -0.3
  Debug.Print i
Next

G

Dim i As Integer
For i = 1 To 0 Step -0.3
  Debug.Print i
Next

H

Dim i As Integer
For i = 30 To 0 Step -0.6
  Debug.Print i
Next


2. Write a procedure that will perform a countdown from 10 to 0, and then displays the message "end of the countdown".
3. Write a program that calculates the sum of 5 consecutive integers, starting from 1.

Working with data in Worksheet

Range

Range("A1").Value = "The value in cell A1"
Range("A3:B4").Value = "The range of cells"
Range("B5,B7,B9").Value = "various cells"
Range("D:D 6:6").Value = "value at the intersection of column D and row 6"
Range("RangeName").Value = "a named range"
Range("BoldRange").Font.Bold = True 'set bold font for the named range

Cells

Cells(2,4).Value = "row 2 column 4"
Workbooks("test.xls").Worksheets("Arkusz1").Cells(5, 6).Value = "test"

ActiveCell

ActiveCell.Value = "Active cell"
ActiveCell.Offset(1,0).Value = "under"
ActiveCell.Offset(-1,0).Value = "above"
ActiveCell.Offset(0,1).Value = "right"
ActiveCell.Offset(0,-1).Value = "left"

Sorting data

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A3") _
       , SortOn:=xlSortOnValues, Order:=xlDescending
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:A3")
       .Apply
    End With
Range("F1:F3").Sort key1:=Range("F1:F3"), _
  order1:=xlAscending

Exercises

  1. Write a program that enters into the cells from A1 to A20 all the numbers from 1 to 20.
  2. Create a macro that will ask you about cell address then will show you the data from this cell. If the cell is empty macro will show message "Cell [cell address] is empty"
  3. Write a program that will fill 56 cells in column B with different interior colors (from 1 to 56). Use the collection of ColorIndex (Cells.Interior.ColorIndex = 12).
  4. Write a program that, starting from the active cell will introduce 20 consecutive natural numbers (starting with 1) and then below will introduce the sum of these 20 numbers.
  5. Write a program that generates a multiplication table to 100.
  6. Write a program that will create a checkerboard pattern.

See for the example below:

Checkerboard.jpg

Arrays

An array is a group of elements of the same type that have a common name. You refer to a specific element in the array by using the array name and an index number For example, you can define an array of 7 string variables to hold the names of the weekdays. If you name the array WeekDaysNames, you can refer to the first element of the array as WeekDaysNames(1), the second element as WeekDaysNames(2), and so on.

Declaring static arrays

Dim staticArray(10) As Integer
lub
i = Array(1, 2, "eer", 5, 6)

Static array at declaration receives dimension and size, which can not be modified in the next steps of the program. The value of 10 in parentheses array tells us the dimension and the size of the array. In this example we are dealing with an array consisting of 11 elements and 1 dimension. By default, arrays are indexed from 0, if you want to change the starting index you can do so in accordance with the following example.

Dim staticArray(1 to 10) as Integer


If you would like VBA to assume that 1 is the lower index for all arrays that declare only the upper index, include the following statement before any procedures in your module:

Option Base 1 

Dim staticArray(10)  'Now the first index number is 1


Declaring dynamic arrays

A dynamic array doesn’t have a preset number of elements. You declare a dynamic array with a blankset of parentheses:.

Dim dynamicArray() as String

Before you can use a dynamic array in your code, you must use the ReDim statement to tell VBA how many elements are in the array.

Redim dynamicArray(1 to 3)

Any subsequent change size of the array by default deletes its contents. If you want to preserve the existing values, use ReDim Preserve.

ReDim Preserve dynamicArray(1 to 4)

Declaring multidimensional arrays

Dim MyArray(-10 to 0, 1 to 100, 200 to 250) as Integer
'Three-dimensional Array. The first dimension indexed from -10 to 0, the second from 1 to 100 and the third from 200 to 250.

An array in VBA can have up to 60 dimensions.

Example1

Sub testing1()

Dim counter as Integer
Dim myArray(1 to 3) as string
    myArray(1) = "one"
    myArray(2) = "two"
    myArray(3) = "three"

For counter = LBound(myArray) To UBound(myArray)
 Debug.Print myArray(counter)
Next
     
End Sub

Example2

Sub testing2()

Dim counter1 As Integer
Dim counter2 As Integer

Dim myArray(1 To 3, 1 To 2) As String
    myArray(1, 1) = "one"
    myArray(2, 1) = "two"
    myArray(3, 1) = "three"
    
    myArray(1, 2) = "alpha"
    myArray(2, 2) = "beta"
    myArray(3, 2) = "gamma"
    
For counter1 = LBound(myArray, 1) To UBound(myArray, 1)
   For counter2 = LBound(myArray, 2) To UBound(myArray, 2)
       Debug.Print myArray(counter1, counter2)
   Next
Next
     
End Sub


Useful Functions

Strings functions

"String" the type of data used to store the text

Sub Examlpe1()
Dim strVaraible As String
    strVaraible= "VBA"
    strVaraible = strVaraible & " - Visual Basic for Applications"

    MsgBox strVaraible
End Sub


Mid

Mid(string, start[, length])

Returns a Variant (String) containing a specified number of characters from a string.

Sub Example2()
Dim text As String
    text = "She sells sea shells"
    
    MsgBox Mid(text, 11, 3)
End Sub

Left, Right

Left(string, length)
Right(string, length)

Returns a Variant (String) containing a specified number of characters from the left/right side of a string

Sub Example3()
Dim text As String
    text  = "She sells sea shells"
    
    MsgBox Right(text , 6)
End Sub

LTrim, RTrim, Trim

LTrim(string)
RTrim(string)
Trim(string) 

Returns a Variant (String) containing a copy of a specified string without leading spaces (LTrim), trailing spaces (RTrim), or both leading and trailing spaces (Trim).

Sub Example4()
Dim text As String
    text = "     OOOOO      OOOOOO    "
    MsgBox LTrim(text) & vbCrLf & RTrim(text) & vbCrLf & Trim(text)
End Sub

Split

Split(expression[, delimiter[, limit[, compare]]])

Returns a zero-based, one-dimensional array containing a specified number of substrings.

Sub Example5()
Dim text As String, i As Integer
Dim MyArray() As String

text = "NobleProg Training how YOU want it!"
MyArray = Split(text, " ")

    For i = 0 To UBound(MyArray)
        MsgBox MyArray(i)
    Next i

End Sub

Len

Len(string)

Returns a Long containing the number of characters in a string or the number of bytes required to store a variable

Sub Example6()
Dim text As String
    text = "Supercalifragilisticexpialidocious"
    MsgBox Len(text)
End Sub

UCase, LCase

UCase(String)
LCase(String)

Returns a Variant (String) containing the specified string, converted to uppercase

Sub Example7()
Dim text As String
    text = "AbCdEfGhIjKlMnOpRsTuWxYz"

    MsgBox UCase(text)
    MsgBox LCase(text)
End Sub

Replace

Replace(expression, find, replace[, start[, count[, compare]]])

Returns a string in which a specified substring has been replaced with another substring a specified number of times.

Sub Example8()
Dim text As String
    text = "Jack and Jue"
    MsgBox Replace(text, "J", "Bl")
End Sub


Exercises

  1. Write a program that based on the user-specified name will create a code consisting of the first three letters and number of characters, for example: Adam -> Ada4
  2. Create a program that will download expression from the cell "A1", and write it in uppercase in the second sheet
  3. The user enters three words (use inputbox). Write these words on the sheet and next to them in a separate cell, enter the length of each of them (number of letters). Then, in a separate cell, write which one is the longest.


Files

ChDir

ChDir path

Changes the current directory or folder.

chDir "C:\"


CurDir

Returns a Variant (String) representing the current path

ChDrive

ChDrive drive

Changes the current drive

ChDrive ("D")

Name

Name oldpathname As newpathname

Renames a disk file, directory, or folder

Name "c:\course\oldName.txt" As "c:\course\newName.txt”

FileCopy

FileCopy source, destination

Copies a file.

FileCopy "file.txt", "b.txt"
FileCopy "c:\a.txt", "c:\b.txt"


Kill

Kill pathname

Deletes files from a disk.

Kill "c:\b.txt"


Mkdir

MkDir path

Creates a new directory or folder.

MkDir "c:\course"


Rmdir

RmDir path

Removes an existing directory or folder. An error occurs if you try to use RmDir on a directory or folder containing files. Use the Kill statement to delete all files before attempting to remove a directory or folder.

RmDir "C:\course"

FileDateTime

FileDateTime(pathname)

Returns a Variant (Date) that indicates the date and time when a file was created or last modified.

FileDateTime("C:\file.txt")

Dir

Dir[(pathname[, attributes])]

Returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.

MsgBox Dir("") 'displays the name of the first file in the current directory
MsgBox Dir 'Another name of the file in the current directory


FileLen

Returns a Long specifying the length of a file in bytes.

FileLen("c:\a.txt")


GetAttr,SetAttr

GetAttr - Returns an Integer representing the attributes of a file, directory, or folder.

SetAttr - Sets attribute information for a file.

Dim attr As String
attr = GetAttr("c:\a.txt")
If attr = vbReadOnly Then
  Debug.Print "tylko do odczytu"
End If
SetAttr "c:\a.txt", vbNormal + vbReadOnly
SetAttr "c:\a.txt", (32 + 1)

These two lines above perform the same task - set the attributes for a file