Visual Basic

Syntax

to cut a statement into several lines, use _ as the last character of the partial lines
use &H to define an integer constant in hexadecimal, e.g. &H2026.

Types

Byte
unsigned 1 byte integer (range [0,255])
Boolean
can be True or False
stored with 2 bytes
equal to 0 (if False) or -1 (if True) when coerced into an integer type
when an integer is coerced into a boolean: False if the value is 0, True otherwise
Integer
signed 2 bytes integer (range [-32768,32767])
type declaration character is %
Long
signed 4 bytes integer (range [-2147483648,2147483647])
type declaration character is &
Single
32 bits float
type declaration character is !
Double
64 bits float
type declaration character is #
Currency
signed 8 bytes integer scaled by 10000 (range [922337203685477.5808,922337203685477.5807])
type declaration character is @
Decimal
signed 12 bytes integer scaled by a variable power of 10 ranging from 0 to 28
Date
string literals are enclosed within two double quotes

#31-Dec-00#

#12:00 am#

#12/31/92# ' is December 12, 1992 if the regional setting of the application is English (US)
Object
String
type declaration character is $
string literals are enclosed within two double quotes

"Doe, John"

It is possible to defines variable as string of a given length.

Dim foo as String * 13
Variant

Variables

a type character defines the type of the preceding identifier
when a type character is used, the type declaration (e.g. As) is optional, if present, it must agree with the type character
% Integer
& Long
@ Decimal
! Single
# Double
$ String
there is no type character for Byte and Short
An escaped identifier is an identifier delimited by square brackets. Escaped identifiers follow the same rules as regular identifiers except that they may match keywords and may not have type characters.
at the beginning of a module, use

Option Explicit

to oblige the declaration of variables (otherwise they are Variant by default)

Let (optional) assign a value to a variable

Let myString = "foobar"

Set assign an object to a variable

Dim myCell As Range
                                    

                                    Set myCell = Worksheets("Sheet1").Range("A1")

Const declares a constant variable

Const MyVar = 459
                                    

                                    Const version As String = "3.3"

Public and Private declare the visibility of a variable variable
Dim declares a variable

Dim X As Integer

If no type is indicated, the default type is Variant
It is possible to combine several Dim on the same line

Dim password As String, yourName As String, firstnum As Integer

As declares the type of a variable or routine parameter

Private Sub z_KeyPress(KeyAscii As Integer)
                                    

                                        If (KeyAscii = 13) Then
                                    

                                            OK_Click
                                    

                                        End If
                                    

                                    End Sub

use the following functions to coerce an expression to a given data type
CBool, CByte, CCur, CDate, CDbl, CDec, CInt, CLng, CSng, CStr, CVar

isEmpty test if a variable has been initialized

Dim table
                                    

                                    For i = 1 to oldDoc.Tables
                                    

                                        …
                                    

                                        If (isEmpty(table)) Then
                                    

                                            Set table = newDoc.table(1)
                                    

                                        End If
                                    

                                        …
                                    

                                    End i

VarType foobar return the type of the variable Variant foobar: vbEmpty (uninitialized), vbNull (no valid data), vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDate, vbString, vbObject, vbError, vbBoolean, vbDataObject, vbDecimal, vbByte and vbUserDefinedType. If the variable contains an array, the returned value is vbArray + one of the preceding types. For an array of Variants, vbArray+VbVariant is returned.
a variable can be declared as static by using the Static keyword

Static i as integer

it is possible to declare all variables of a function or procedure as static by preceding the function/procedure name by Static
typeName(var) return a string which is the type of the variable var

Arrays

Dim cusName(10) as String

declare an array that consists of 10 elements if the statement Option Base 1 appear in the declaration area, of 11 elements otherwise
Dim count(100 to 500) as Integer

declare an array that consists of the first element is count(100) and the last one is count(500)
Dim foo() as Integer

it is also possible to not precise the size of the array (dynamic array)
ReDim can be used to change the number of dimensions, to define the number of elements and to define the upper and lower bounds for each dimension; but the existing values in the array are lost.
ReDim Preserve keeps the values

ReDim Preserve varArray(UBound(varArray) + 10)

enlarges the array varArray by 10 elements without losing the current values of the original elements
UBound(arrayname[,dimension]) return a Long containing the largest available subscript for the indicated dimension of an array
LBound(arrayname[,dimension]) return a Long containing the smallest available subscript for the indicated dimension of an array
IsArray test if a variable is an array
redefine the default lower bound for array subscripts (0 is the default) at the beginning of a module:

Option Base 0

or

Option Base 1

trick to initialize an array of strings

Dim foo() As String
                                    

                                    foo() = Split("Identifier|Requirement Statement|Type|Predecessor Allocated Requirement|Safety|CTQ", "|")

Array returns a Variant variable containing an array

Dim A As Variant
                                    

                                    A = Array(10,20,30)

multidimensional array


Dim data(4, 6, 5, 3) As Integer
                                    

                                    … 
                                    

                                    data(typ, impact, sev, col) = data(typ, impact, sev, col) + 1

Enums

use the Enum

Public Enum taskPriority
                                    

                                        taskTPEmpty
                                    

                                        taskTPUrgent
                                    

                                        taskTPHigh
                                    

                                        taskTPMedium
                                    

                                        taskTPLow
                                    

                                    End Enum

it is possbile to define the values

Public Enum taskPriority
                                    

                                        taskTPFirst = 0
                                    

                                        taskTPEmpty = 0
                                    

                                        taskTPUrgent = 1
                                    

                                        taskTPHigh = 2
                                    

                                        taskTPMedium = 3
                                    

                                        taskTPLow = 4
                                    

                                        taskTPLast
                                    

                                    End Enum

Operators

+
-
*
/
\ integer division
Mod
+ and & string concatenation

Comparison operators

<
<=
>
>=
=
<>
Is compare two object reference variables
Like

Boolean operators

And both expressions are always evaluated
AndAlso short-circuiting and
Eqv
Imp
Not

a = Not b
Or both expressions are always evaluated
OrElse short-circuiting or
Xor
Eqv logical equivalence
Imp logical implication

Flow control

test


If CommandBars("Standard").Visible Then
                                    

                                        CommandBars("Standard").Visible = False
                                    

                                        CommandBars("Formatting").Visible = True
                                    

                                    ElseIf CommandBars("Formatting").Visible Then
                                    

                                        CommandBars("Standard").Visible = False
                                    

                                        CommandBars("Formatting").Visible = False
                                    

                                    Else
                                    

                                        CommandBars("Standard").Visible = True
                                    

                                        CommandBars("Formatting").Visible = False
                                    

                                    End If

loop


For j = 2 To 10 Step 2
                                    

                                        total = total + j
                                    

                                    Next j

do while/until

Do While condition
                                    

                                        Block of one or more VB statements
                                    

                                    Loop

Do
                                    

                                        Block of one or more VB statements
                                    

                                    Loop While condition

Do Until condition
                                    

                                        Block of one or more VB statements
                                    

                                    Loop

Do
                                    

                                        Block of one or more VB statements
                                    

                                    Loop Until condition

loop on a collection content


For Each c In col
                                    

                                        …
                                    

                                    Next c

case

Dim grade As String
                                    

                                    Private Sub Compute_Click( )
                                    

                                        grade=txtgrade.Text
                                    

                                        Select Case grade
                                    

                                            Case "A"
                                    

                                                result.Caption="High Distinction"
                                    

                                            Case "A-"
                                    

                                                result.Caption="Distinction"
                                    

                                            Case "B"
                                    

                                                result.Caption="Credit"
                                    

                                            Case "C"
                                    

                                                result.Caption="Pass"
                                    

                                            Case Else
                                    

                                                result.Caption="Fail"
                                    

                                        End Select

Dim mark As Single
                                    

                                    Private Sub Compute_Click()
                                    

                                        mark = mrk.Text
                                    

                                        Select Case mark
                                    

                                            Case Is >= 85
                                    

                                                comment.Caption = "Excellence"
                                    

                                            Case Is >= 70
                                    

                                                comment.Caption = "Good"
                                    

                                            Case Is >= 60
                                    

                                                comment.Caption = "Above Average"
                                    

                                            Case Is >= 50
                                    

                                                comment.Caption = "Average"
                                    

                                            Case Else
                                    

                                                comment.Caption = "Need to work harder"
                                    

                                        End Select
                                    

                                    End Sub

Dim mark As Single
                                    

                                    Private Sub Compute_Click()
                                    

                                        mark = mrk.Text
                                    

                                        Select Case mark
                                    

                                            Case 0 to 49
                                    

                                                comment.Caption = "Need to work harder"
                                    

                                            Case 50 to 59
                                    

                                                comment.Caption = "Average"
                                    

                                            Case 60 to 69
                                    

                                                comment.Caption = "Above Average"
                                    

                                            Case 70 to 84
                                    

                                                comment.Caption = "Good"
                                    

                                            Case Else
                                    

                                                comment.Caption = "Excellence"
                                    

                                        End Select
                                    

                                    End Sub

break from a loop, function, procedure, or property


Function …
                                    

                                        …
                                    

                                        Exit Function
                                    

                                        …
                                    

                                    End Function


Property …
                                    

                                        …
                                    

                                        Exit Property
                                    

                                        …
                                    

                                    End Property

goto


goto foobar
                                    

                                    …
                                    

                                    foobar:

Error handling

On Error GoTo line

If a run-time error occurs, control branches to line.
line can be any line label or line number, it must be in the same procedure as the On Error statement.
On Error Resume Next

When a run-time error occurs, control goes to the statement immediately following the statement where the error occurred.
On Error GoTo 0

Disables any enabled error handler in the current procedure.

Functions

The general format of a function is as follows:

[Public|Friend|Private] [Static] Function functionName (Arg As dataType,…) As dataType
                                    

                                    …
                                    

                                    End Function

Public the procedure is accessible to all other procedures in all modules. If used in a module that contains an Option Private, the procedure is not available outside the project.
Private the procedure is accessible only to other procedures in the module where it is declared.
Friend the procedure is visible throughout the project, but not visible to a controller of an instance of an object.
Static the procedure's local variables are preserved between calls. The Static attribute doesn't affect variables that are declared outside the Function, even if they are used in the procedure.
The return value is defined by setting the functionName variable is the function body.

Exit function exits from the function.

Procedure

The general format of a procedure is as follows:

[Public|Friend|Private] [Static] Sub routineName (Arg As dataType,…)
                                    

                                    …
                                    

                                    End Sub

Exit sub exits from the routine.

Function/procedure parameters

A parameter can be declared as optional by using the Optional keyword.

A default value can be provided for an optional parameter

Public Sub Display(Optional number as Long = -1) 
                                    

                                    …
                                    

                                    End Sub

The existence of an optional Variant parameter can be tested with IsMissing

Public Sub Display(Optional number as Variant)
                                    

                                    If IsMissing(number) then
                                    

                                        …
                                    

                                    Else
                                    

                                        …
                                    

                                    End If
                                    

                                    End Sub

ByRef indicates that the attribute is passed by reference (the default)
ByVal indicates that the attribute is passed by value

Private Sub copyToMaster(ByRef masterSheet As Worksheet)
                                    

                                    … 
                                    

                                    End Sub

When calling a routine/procedure without Call, the parentheses must not be present.
When calling a routine/procedure with Call, the parentheses must be present.

Class

create an object

Dim oCE As New MyClass
indicate that a class implements an interface or another class

Implements MyOtherClass

Collections

use the Item method to access the elements

Documents.Item(1)
some collections can be accessed also by name

Documents("Sales.doc")
some collections have predefined indexes

Selection.Paragraphs(1).Borders(wdBorderBottom)
use the Count attribute to get the number of elements
use Add and Remove methods to add or remove an element

Files

writing a file

Open "c:\My Documents\sample.txt" For Output As #1
                                    

                                    Print #1, "Hello, world!"
                                    

                                    Close #1

reading a file

Open "c:\My Documents\sample.txt" For Input As #1
                                    

                                    Input #1, var
                                    

                                    Close #1

FileSystemObject

it is defined in the Scripting Runtime Library
Drives, Files, and Folders collections are not accessible with an integer index. The must be accessed by a string key: d.SubFolders("test.doc")

FileSystemObject

`GetDrive`	return a `Drive` from a pathname
`CreateFolder`	create a folder
`GetFolder`	return a `Folder` from a pathname
`MoveFolder`	move a folder
`CopyFolder`	copy a folder
`DeleteFolder`	delete a folder
`FolderExists`	test if a folder exists
`GetParentFolderName`	return the name of a folder’s parent folder
`GetSpecialFolder`	return the path of system folders
`CreateTextFile`	create a text file
`OpenTextFile`	open/create a text file
`GetFile`	return a `File` from a pathname
`CopyFile`	copy a file
`DeleteFile`	delete a file

Drive

`TotalSize`	total size of the drive in bytes
`AvailableSpace`	available on the drive in bytes
`FreeSpace`	available on the drive in bytes
`DriveLetter`	letter assigned to the drive
`DriveType`	type of the drive
`SerialNumber`	drive’s serial number
`FileSystem`	drive’s file system (e.g. FAT32)
`IsReady`	is the drive available?
`ShareName`	name of the share
`VolumeName`	name of the volume
`Path`	path of the drive
`RootFolder`	root folder of the drive

Folder and File

Name name of the folder/file

ShortName 8.3 name of the folder/file

Path path of the folder/file

ShortPath 8.3 path of the folder/file

Type return information about the type of the folder/file

Delete delete the folder/file

Move move the folder/file

Copy copy the folder/file

DateCreated date and time of creation of the folder/file

DateLastAccessed date and time of last access to the folder/file

DateLastModified date and time of last modification to the folder/file

Attributes

attributes of the folder/file

Constant	Value	Description
Normal	0	Normal file. No attributes are set.
ReadOnly	1	Read-only file. Attribute is read/write.
Hidden	2	Hidden file. Attribute is read/write.
System	4	System file. Attribute is read/write.
Volume	8	Disk drive volume label. Attribute is read-only.
Directory	16	Folder or directory. Attribute is read-only.
Archive	32	File has changed since last backup. Attribute is read/write.
Alias	1024	Link or shortcut. Attribute is read-only.
Compressed	2048	Compressed file. Attribute is read-only.

Drive return the letter of the drive where is the folder/file

ParentFolder return the folder containing the folder/file

Folder

`Files`	return the files contained in the folder
`SubFolders`	return the folders contained in the folder
`IsRootFolder`	test if the folder is a root folder
`Size`	return the size, in bytes, of all files and subfolders contained in the folder

File

Size return the size, in bytes, of the file

TextStream

`Read`	read a specified number of characters from a file
`ReadLine`	read an entire line (up to, but not including, the newline character)
`ReadAll`	read the entire contents of a text file
`Skip`	skip a specified number of characters
`SkipLine`	skip the next line
`Write`	write data to an open text file without a trailing newline character
`WriteLine`	write data to an open text file with a trailing newline character
`WriteBlankLines`	write one or more blank lines to an open text file
`Close`	close the file
`Copy`	copy the file
`Move`	move the file

an example

Dim fso As Scripting.FileSystemObject
                                    

                                    Dim folder, dir As Scripting.folder
                                    

                                    Dim file As Scripting.file
                                    

                                    
                                    

                                    Set fso = CreateObject("Scripting.FileSystemObject")
                                    

                                    
                                    

                                    Set folder = fso.GetFolder("D:\Docs\human resources\timesheets")
                                    

                                    For Each dir In folder.SubFolders
                                    

                                        For Each file In dir.Files
                                    

                                            …
                                    

                                        Next file
                                    

                                    Next dir

Numerics

IsNumeric test if an expression can be evaluated as a number
Int return the integer value of a Double for positive values
for negative values, return the first negative integer less than or equal to number
Int(-8.4) is equal to -9
Fix return the integer value of a Double for positive values
Fix(-8.4) is equal to -8

Val transform a string into an integer


Dim v As Integer
                                    

                                    v = Val("100 Blah Blah")
                                    

                                    Print v   ' prints 100
                                    

                                    v = Val("Nothing")
                                    

                                    Print v  ' Could not convert, prints 0

String manipulation

use "" to insert a " in a constant string

str = "the ""foobar"" word"
vbCr, vbLf, vbCrLf and vbTab are predefined constants, other values can be obtained with Chr().
StrComp(string1, string2[, compare])compares two strings
Join(sourcearray[, delimiter]) (Win2000) create a string which is the concatenation of an array of strings
Split(expression[, delimiter[, limit[, compare]]]) (Win2000) split a string into an array of substrings according to a given delimiter
Mid(string, start[, length]), Left(string, length) and Right(string, length) extract a substring from a string
Len(string) return the length (number of characters) of a string
LenB(string) return the number of bytes of a string
Replace(expression, find, replace[, start[, count[, compare]]])) return a string where instance of a substring have been replaced by another substring
InStr([start,]string1,string2[,compare]) return the position of the first occurrence of one string within another
InStrRev(string1,string2[,start[,compare]]) return the position of the last occurrence of one string within another
LTrim(string) return a copy of the string without leading spaces
RTrim(string) return a copy of the string without trailing spaces
Trim(string) return a copy of the string without both leading and trailing spaces
CleanString(string) return a copy of the string without control characters (they are removed or replaced by space)
StrConv(str,vbUpperCase) convert the string to uppercase characters
StrConv(str,vbLowerCase) convert the string to lowercase characters
StrConv(str,vbProperCase) convert the first letter of every word in string to uppercase
StrConv(str,vbUnicode) convert the string to Unicode using the default code page of the system
StrConv(str,vbFromUnicode) convert the string from Unicode to the default code page of the system
Chr(val) return a string containing the character defined by the character code val
ChrW(val) return a string containing the character defined by the Unicode value val
ChrB(val) return a string containing the character defined by the byte val
(this is not really clear…)
Asc(string) return the character code of the first letter of string
AscB(string) return the first byte of the first letter of string
AscW(string) return the Unicode code of the first letter of string
Converting integers/float/dates into strings
- Str(number) convert an integer or float into a string
  the first character is reserved for the sign, so there is always a space at the beginning for positive numbers
  to avoid this, use FormatNumber(incr,0) instead of Str(incr)
- Format(Format(expression[, format[, firstdayofweek[, firstweekofyear]]])) convert a value into a string
- FormatNumber(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]]) convert a number into a string
- FormatPercent(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]]) convert a percentage into a string
- FormatDateTime(Date[,NamedFormat]) convert a date into a string
- FormatCurrency(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]]) convert a currency into a string
Space(number) return a string that consists of a specified number of spaces

Date manipulation

IsDate(expr) test if an expression can be translated into a date
Date() return the current date
Time() return the current time
Now() return the current date and time
DateSerial(year, month, day) return the date corresponding to a year, month and day
TimeSerial(hours, minutes, seconds) return the date corresponding to a hours, minutes and seconds
DateValue(val) extract a date from a string
TimeValue(val) extract a time from a string
Year(val) return the year of a date
Month(val) return the month of a date
Day(val) return the day of a date
WeekDay(val) return the week day of a date
Hour(val) return the hours of a date
Minute(val) return the minutes of a date
Second(val) return the seconds of a date

Associative array

add "Microsoft Scripting Runtime" in the project references
use the Dictionary class (take care, this class also exists in Word)

properties

`CompareMode`	Sets or returns the comparison mode for comparing keys in a Dictionary object
`Count`	Returns the number of key/item pairs in a Dictionary object
`Item`	Sets or returns the value of an item in a Dictionary object
`Key`	Sets a new key value for an existing key value in a Dictionary object

properties

`Add`	Adds a new key/item pair to a Dictionary object
`Exists`	Returns a Boolean value that indicates whether a specified key exists in the Dictionary object
`Items`	Returns an array of all the items in a Dictionary object
`Keys`	Returns an array of all the keys in a Dictionary object
`Remove`	Removes one specified key/item pair from the Dictionary object
`RemoveAll`	Removes all the key/item pairs in the Dictionary object

example

Dim dict As Scripting.Dictionary
                                    

                                    Set dict = New Scripting.Dictionary
                                    

                                    dict.Add "ACTI", 1
                                    

                                    dict.Add "ADMI", 1
                                    

                                    dict.Add "BUIL", 1
                                    

                                    … 
                                    

                                        Set rg = ActiveDocument.range(c2.range.Start, c2.range.End - 1)
                                    

                                        Dim txt As String
                                    

                                        txt = rg.Text
                                    

                                        txt = Left(txt, 4)
                                    

                                        Dim incr As Integer
                                    

                                        incr = dict.Item(txt)
                                    

                                        dict.Remove txt
                                    

                                        dict.Add txt, incr + 1
                                    

                                        ActiveDocument.range(c2.range.Start + 5, c2.range.End - 1).InsertBefore Format(incr, "000")
                                    

                                        ActiveDocument.range(c2.range.Start + 8, c2.range.Start + 11).Delete

loop on the array content

Dim dict As Scripting.Dictionary
                                    

                                    Dim i As Integer
                                    

                                    Dim t As String
                                    

                                    … 
                                    

                                    For i = 0 To dict.Count - 1
                                    

                                        t = dictSRS(0).Keys(i) 
                                    

                                        Selection.InsertAfter (t) 
                                    

                                        Selection.InsertAfter (" => ")
                                    

                                        Selection.InsertAfter (dict.Item(t)) 
                                    

                                    Next i

Versioning

ScriptEngine return the scripting language in use. It can return one of the following strings:
- VBScript - Indicates that Microsoft Visual Basic Scripting Edition is the current scripting engine
- JScript - Indicates that Microsoft JScript is the current scripting engine
- VBA - Indicates that Microsoft Visual Basic for Applications is the current scripting engine
ScriptEngineBuildVersion return the build version number of the scripting engine in use
ScriptEngineMajorVersion return the major version number of the scripting engine in use
ScriptEngineMinorVersion return the minor version number of the scripting engine in use

Application management

start an application

Shell "notepad", vbNormalFocus

use Excel to get the name of an existing file

Function browseForFileUsingExcel(description As String, formats As String, title As String) As Variant
                                    

                                        Dim xl As Variant
                                    

                                        Set xl = CreateObject("Excel.Application")
                                    

                                        browseForFileUsingExcel = xl.GetOpenFilename(description & " ("& formats & ")," & formats, , title) 
                                    

                                    End Function

used for example by


Dim inputFile As Variant, theInputFile As Variant
                                    

                                    inputFile = browseForFileUsingExcel("Word file", "*.rtf;*.doc", "Input file")
                                    

                                    If (VarType(inputFile) = vbString) Then
                                    

                                        theInputFile = inputFile
                                    

                                        … use theInputFile … 
                                    

                                    End If

Message box

displays a message in a dialog box, waits for the user to click a button, and return an integer indicating which button the user clicked.

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

Buttons can be vbOKOnly, vbOkCancel, vbYesNo, vbYesNoCancel, vbAbortRetryIgnore or vbRetryCancel.
Icons can be vbQuestion, vbInformation, vbExclamation or vbCritical.
vbDefaultButton1, vbDefaultButton2… can be used to define the default button.

Msgbox "Do you want to save changes?", vbYesNo+vbExclamation+vbDefaultButton1, "Front Page"

The returned value indicates the clicked button: vbOK, vbYes, vbNo, vbAbort, vbRetry, vbCancel or vbIgnore.
displays a prompt in a dialog box, waits for the user to input text or click a button, and return a String containing the contents of the text box.

InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

Debugging

print a message in the immediate window

Debug.Print "Total Unread items = " & oFldr.UnReadItemCount