Étiquette : first_program

In VBA, there is a wide variety of means for organizing loops, which can be divided into two main groups: loops with a condition (Do…Loop) and loops with enumeration (For…Next).

Loops of the Do…Loop type are used in cases where it is not known in advance how many times the block of statements forming the loop body should be executed. Such a loop continues running until a certain condition is met. There are four types of Do…Loop loops, which differ in the type of condition checked and the time when the check is performed. Table 2.10 shows the syntax of these four constructions.

Table 1. Syntax of Do…Loop statements

Construction	Description
Do While <condition> <statements> Loop	The condition is checked before executing the group of statements that form the loop body. The loop continues running as long as this condition is true. In other words, this construction specifies the condition for continuing the loop.
Do <statements> Loop While <condition>	The condition is checked after the statements that form the loop body have been executed at least once. The loop continues running as long as the condition remains true. In other words, this construction specifies the condition for continuing the loop.
Do Until <condition> <statements> Loop	The condition is checked before executing the group of statements that form the loop body. The loop continues to run if the condition has not yet been met and terminates when it becomes true. In other words, this construction specifies the condition for ending the loop.
Do <statements> Loop Until <condition>	The condition is checked after the statements that form the loop body have been executed at least once. The loop continues running if the condition has not yet been met, and when it becomes true, the loop ends. In other words, this construction specifies the condition for ending the loop.

There are also two variations of the For…Next loop. Very often, when working with arrays, and in cases where it is necessary to repeat the execution of a group of statements a specified number of times, a For…Next loop with a counter is used. Unlike Do…Loop loops, this type of loop uses a special variable called a counter, whose value is increased or decreased by a given increment with each execution of the loop body. When the value of this variable reaches the specified limit, the loop execution ends.

The syntax of this loop looks as follows (elements in square brackets are optional):

For <counter> = <startValue> To <endValue>
    [Step <increment>]
    <statements>
Next [<counter>]

In this loop construction:

• <increment> can be either a positive or a negative number. If a negative increment is used, the end value must be less than or equal to the start value so that the loop body executes at least once.
• After the For…Next loop finishes, the variable used as the counter receives a value that necessarily exceeds the end value if the increment is positive, or is strictly less than the end value if the increment is negative.
• If the start and end values are the same, the loop body is executed only once.

Let us also consider another variation of the For…Next loop, often used in VBA when processing objects that make up an array or a collection of similar objects. In this variation, the counter is absent, and the loop body executes for each element of the array or collection. The syntax of such a loop is as follows:

For Each <element> In <collection>
    <statements>
Next [<element>]

Here, <element> is a variable used to refer to elements of the collection, and <collection> is the name of the array or collection.

Exiting Loops and Procedures

Normally, the execution of a procedure ends after its last statement has been executed, and the execution of a loop ends after several iterations of the loop body, when the termination condition is met. However, in some cases, it may be necessary to stop the execution of a procedure or loop prematurely, thus avoiding unnecessary procedure statements or redundant loop iterations.

For example, if an error occurs during the execution of a procedure that makes its continuation meaningless, you can execute a command for immediate exit from the procedure. Another example: if a For…Next loop is used to search for a required value in an array, then once the desired element has been found, there is no need to continue scanning the rest of the array elements. Premature exit from a control structure can be performed using one of the Exit statements.

• For premature exit from Do…Loop loops, the Exit Do statement is used.
• For exit from For loops, the Exit For statement is used.
• For premature exit from procedures and functions, the Exit Sub and Exit Function statements are used, respectively.

It should be noted that although the use of the Exit statement can be fully justified, excessive use of this operator should be avoided and it should only be applied in critical situations. Frequent use of this operator makes it more difficult to understand the written program code and to debug it.

Examples of Using Loop Statements

The For…Next Statement

The For…Next operator is used to find the sum of the elements in an array.

Summation of array elements

Sub DemoFor()
    Dim A As Variant
    A = Array(1, 4, 12, 23, 34, 3, 23)
    s = 0
    For i = LBound(A) To UBound(A)
        s = s + A(i)
    Next
    MsgBox s
End Sub

In the next example, the product of the first n natural numbers (the factorial of n) is calculated.

Finding the product of the first n natural numbers

Sub Factor()
    n = 20
    Fact = 1
    For i = 1 To n
        Fact = Fact * i
    Next i
    MsgBox Format(Fact, "############")
    ' Output: 2432902008176640000
End Sub

The For Each Statement
The For Each statement can be used to sum up the elements of an array. 

Summing the elements of an array using the For…Each statement

Sub DemoForEach()
    Dim A As Variant, s As Double
    A = Array(1, 4, 12, 23, 34, 3, 23)
    s = 0
    For Each b In A
        s = s + b
    Next
    MsgBox s
End Sub

The For Each statement can also be used when iterating through the cells of a range, in particular, when calculating the sum of the values entered in the cells of a range. 

Summing values from a range of cells using the For Each statement

Sub DemoSumSelection()
    Dim c As Range
    Dim s As Double
    s = 0
    For Each c In Selection.Cells
        s = s + c.Value
    Next
    MsgBox s
End Sub

In the next example, the For Each loop is used to change the color of cells: all cells in the range A1:C4 with positive values are colored blue, and those with non-positive values are colored red.

Changing the color of cells

Sub DemoChangeColor()
    Dim c As Range
    For Each c In Range("A1:C4").Cells
        With c
            If .Value <= 0 Then
                .Interior.ColorIndex = 3
            Else
                .Interior.ColorIndex = 5
            End If
        End With
    Next
End Sub

The For Each statement is also used when iterating through members of a collection. For example, the following code  demonstrates the use of the For Each loop for working with a collection of worksheets. The statement deletes the worksheet named Test from the workbook, if it exists.

Working with a collection of worksheets

Sub DemoDeleteSheet()
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name = "Тест" Then
            ws.Delete
        End If
    Next
End Sub

The While Statement

The While statement, as mentioned earlier, unlike For, does not repeat a specified number of times but instead continues as long as a condition is met.

In the following example, the rolling of a die is simulated until a six is rolled. When a six is rolled, the game ends, and a message is displayed indicating on which attempt it ended.

Rolling a die

Sub DemoWhile()
    Dim attempt As Integer
    Dim score As Integer
    Randomize
    score = Int(6 * Rnd()) + 1
    attempt = 1
    While score < 6
        attempt = attempt + 1
        score = Int(6 * Rnd()) + 1
    Wend
    MsgBox "Won on attempt: " & attempt
End Sub

Using the value True as the condition in a While statement creates an infinite loop. To break out of it, you must place an instruction inside the loop body that provides an exit when a certain condition is satisfied.

While True
    <block of statements>
Wend

The Do Statement

An example of using the Do…Until loop statement for repeats the loop until the word time is entered in the input box as a password.

Example of using the Do statement

Sub DemoPassword()
    Dim ps As String
    Do
        ps = InputBox("Enter password")
    Loop Until ps = "time"
End Sub

For the Do While loop statement, the following example is provided . It sequentially displays the names of all .jpg files in the root directory of drive D:. Here the file-handling function Dir() is used, which returns the name of the first matching file. If no matching file is found, the Dir() function returns an empty string. On subsequent calls to Dir(), its parameter with the search mask is omitted.

Sequentially displaying the names of files with the .jpg extension

Sub DemoShowFiles()
    Dim f As String
    Dim res As String
    f = Dir("D:\*.jpg")
    res = f & vbCr
    Do While Len(f)
        f = Dir
        res = res & f & vbCr
    Loop
    MsgBox res
End Sub

Here’s the full English translation of your passage, preserving the technical structure and VBA-specific terminology:

Alternative Exit from a Loop

An example of using the alternative exit Exit Do from a loop can be the following code, in which the entered numbers are summed. The loop ends if any string expression is entered.

Summing all entered numbers

Sub DemoExitDo()
    Dim s As Double, x As Variant
    s = 0
    Do
        x = InputBox("Enter a number")
        If Not IsNumeric(x) Then Exit Do
        s = s + x
    Loop
    MsgBox s
End Sub

Creating an Infinite Loop with the Do Statement

A Do statement without conditions creates an infinite loop. Such loops are used when programming various background processes such as document printing.

Do
Loop

The Unconditional GoTo Statement

The unconditional jump statement specifies a jump to a given line within a procedure. The required parameter line can be any line label or line number.

GoTo line

To use the unconditional jump statement, a label must be assigned to some line. A label must begin with a letter and end with a colon.

As an example of using the unconditional jump operator, consider a game in which the player is given ten attempts to roll a die. If a six is rolled on any of these attempts, the player wins, and the game ends.

Rolling a die

Sub DemoGoTo()
    Dim i As Integer
    Dim score As Integer
    Randomize
    For i = 1 To 10
        score = Int(6 * Rnd()) + 1
        If score = 6 Then GoTo lblMessage
    Next
    GoTo lblEnd
lblMessage:
    MsgBox "Won on roll " & i
lblEnd:
End Sub

It should be noted that in this example, the use of the GoTo statement appears unnecessarily complicated. Using a Do loop can significantly simplify and shorten the code.

Control structures for branching (conditional execution) allow you to test a condition and, depending on the result of that test, execute one group of statements or another. In VBA, branching is organized using various forms of the If statement and the Select Case statement.

Short Form of If

The simplest short form of the If statement is used to test a single condition and then either execute or skip a statement or a block of statements. The short form of If can be written in a single-line form or a block form.

Single-line form:

If <condition> Then <statement>

Block form:

If <condition> Then
    <statement1>
    <statement2>
    ...
End If

• The condition can be a logical expression that returns True or False, or any arithmetic expression.
• If an arithmetic expression is used, a zero value is equivalent to False, while any nonzero value is equivalent to True.
• If the condition returns False, the statement or block of statements between Then and End If (the body of the short If statement) will not be executed.

Note: When writing the short If statement in a single line, the keywords End If are not used.

Full Form of If

The full form of the If statement is used when there are two alternative blocks of statements, and depending on the result of the condition check, one of them must be executed. This form cannot be written in a single line and always uses the block form:

If <condition> Then
    <blockOfStatements1>
Else
    <blockOfStatements2>
End If

• If the condition is true, the first block (between Then and Else) is executed.
• Otherwise, the second block (between Else and End If) is executed.

TIP

To make the text of a procedure clear and easy to read, it is recommended to use indentation for groups of statements, as shown in the description of their syntax. In VBA there is a convenient tool for changing indentation: pressing the key increases the indentation to the right, while pressing + decreases it.

Sometimes it is necessary to choose one action out of a whole group of actions based on testing several different conditions. For this, you can use a chain of If…Then…ElseIf statements:

If <condition1> Then
    <blockOfStatements1>
ElseIf <condition2> Then
    <blockOfStatements2>
ElseIf <condition3> Then
    <blockOfStatements3>
...
ElseIf <conditionN> Then
    <blockOfStatementsN>
Else
    <blockOfStatementsElse>
End If

Such chains of If…Then…ElseIf statements are very flexible and allow you to solve any problem. However, if the choice among several possibilities is always based on different values of the same expression, it is much more convenient to use the Select Case statement, which is specifically designed for this purpose.

Syntax:

Select Case <testExpression>
    Case <listOfValues1>
        <blockOfStatements1>
    Case <listOfValues2>
        <blockOfStatements2>
    Case <listOfValues3>
        <blockOfStatements3>
    ...
    Case Else
        <blockOfStatementsElse>
End Select

The test expression is evaluated at the start of the Select Case statement. This expression can return a value of any type — logical, numeric, or string.

The list of values can be one or several expressions separated by commas. When the statement is executed, VBA checks whether at least one of the elements in the list matches the value of the test expression. The elements of the value list can take the following forms:

• <expression> — checks whether the test expression is equal to this expression;
• <expression1> To <expression2> — checks whether the test expression lies within the specified range of values;
• Is <logicalOperator> <expression> — the test expression is compared with the specified value using the given logical operator (for example, the condition Is >= 10 is satisfied if the tested value is not less than 10).

If at least one element in the list matches the test expression, the corresponding block of statements is executed, and execution of the Select Case statement ends — the remaining lists of values are not checked. That is, only the first suitable match is found. If none of the lists match, the statements in the Else block are executed (if present).

Examples of Branching Statements

In Determining which interval an entered number belongs to  depending on the value of the entered number, a message is displayed about whether the number belongs to:

• the interval [0, 1];
• the interval (1, 2];
• or neither of these two intervals.

Determining which interval an entered number belongs to

Sub DemoElseIf()
    x = InputBox("Enter a number")
    If 0 <= x And x <= 1 Then
        MsgBox "The number is in the interval [0, 1]"
    ElseIf 1 < x And x <= 2 Then
        MsgBox "The number is in the interval (1, 2]"
    Else
        MsgBox "The number is either negative or greater than 2"
    End If
End Sub

In the example the use of the Select Case statement is demonstrated for displaying a message about which range the entered integer belongs to.

Example of using the Select Case statement

Sub DemoSelect()
    Dim x As Integer
    x = InputBox("Enter an integer")   
    Select Case x
        Case 1
            MsgBox "The number is equal to 1"
        Case 2, 3
            MsgBox "The number is equal to 2 or 3"
        Case 4 To 6
            MsgBox "The number is between 4 and 6"
        Case Is >= 7
            MsgBox "The number is greater than or equal to 7"
    End Select
End Sub

As in all other programming languages, VBA has various control structures that allow you to change the order of program execution. If a program has no control structures, statements are executed sequentially from the first to the last. In the simplest cases, this may be sufficient. However, usually it is necessary to change the order of execution under certain conditions, to skip execution of some statements, or, conversely, to repeat them multiple times.

It should be noted that to implement any algorithms it is enough to have two types of control structures: loops and branches. Let us take a closer look at the use of statements in VBA.

Assignment Statement

The assignment statement assigns the value of an expression to a variable, constant, or object property. It always includes the equal sign (=).

<variable> = <expression>

The assignment statement evaluates the <expression> on its right-hand side and assigns the result to the <variable> on its left-hand side.

For example, the following two statements:

x = 2
x = x + 2

will assign the value 4 to the variable x.

Assigning an Object Reference

To assign an object reference to a variable, the keyword Set is used in the assignment statement.

For example, the following code assigns to variable r a reference to cell A1, and through this variable, the value 3 is entered into cell A1:

Dim r As Range
Set r = Range("A1")
r.Value = 3

In general, the Set statement has the following syntax:

Set <variable> = {[New] <expression> | Nothing}

• The keyword New is used when creating a new instance of a class.
• The keyword Nothing is used to release all system and memory resources allocated to the object being referenced (in simple terms, it removes the object from memory).

The With Statement

The With statement frees the programmer from the tedious task of repeatedly writing the same object name when working with its properties and methods. In addition, it structures the code, making it clearer:

With Range("A1")
    .Value = 3
    .Font.Italic = True
End With

Nested With statements are also allowed:

With Range("A1")
    .Value = 3
    With .Font
        .Italic = True
        .Size = 12
        .Bold = True
        .Color = RGB(255, 30, 255)
    End With
End With

VBA contains a large set of built-in functions and procedures, the use of which significantly simplifies programming. Note that all necessary information regarding the use of available functions can be found in the VBA Help system.

• Go to the Visual Basic for Applications editor window and choose Help | Visual Basic for Applications Help (or press F1).
• In the Excel Help window, select in the left-hand contents: Visual Basic for Applications Language Reference | Visual Basic Language Reference | Functions.

From there, you can see the entire list of functions available in VBA, along with examples of their use.

Built-in Dialog Boxes

Two types of dialog boxes are often encountered in VBA projects: message boxes and input boxes. These are built into VBA, and if their capabilities are sufficient, you can avoid designing your own dialog boxes.

• A message box (MsgBox) displays simple messages for the user.
• An input box (InputBox) allows the user to enter information.

Input Box

The InputBox() function displays a dialog box on the screen containing a message, an input field, and two buttons: OK and Cancel. It waits for the user to enter text and press a button. When the OK button is pressed, it returns a String value containing the text entered in the input field. When the Cancel button is pressed, it returns an empty string (Empty).

Syntax:

InputBox(Prompt[, Title][, Default][, Xpos][, Ypos] [, Helpfile, Context])

• Prompt — a string expression displayed as a message in the dialog box. The string expression may contain multiple lines. To separate lines, you can use the carriage return character (Chr(13)), the line feed character (Chr(10)), or a combination (Chr(13) & Chr(10)).
• Title — a string expression displayed in the dialog box title bar. If omitted, the application name is placed in the title bar.
• Default — a string expression displayed in the input field as the default if the user does not enter another string. If omitted, the input field is empty.
• Xpos — a numeric expression that sets the horizontal distance between the left edge of the dialog box and the left edge of the screen. If omitted, the box is centered horizontally on the screen.
• Ypos — a numeric expression that sets the vertical distance between the top of the dialog box and the top of the screen. If omitted, the box is placed about one-third down the screen.
• Helpfile — a string expression specifying the name of the Help file containing information about the dialog box. If this parameter is specified, the Context parameter must also be provided.
• Context — a numeric expression specifying the context ID of the relevant Help topic. If specified, the Helpfile parameter must also be provided.

Example: The following code (also in the file 2-Examples of using built-in dialog boxes.xlsm on the CD) displays an input box shown in:

Sub DemoInputBox1()
    Dim n As String
    n = InputBox("Enter your name", "Input Box Example")
    Debug.Print n
End Sub

Handling the Cancel Button

When entering data with InputBox(), it is reasonable to handle the event of pressing the Cancel button. For example, in the following case:

x = InputBox("Enter x", "Example")
y = x ^ 2

If the user presses Cancel, the code execution is interrupted with a type mismatch error. This situation can easily be avoided by adding just one line of code, which checks whether the input field is empty (which is exactly what happens when Cancel is pressed).

Sub DemoInputBox2()
    Dim x As String
    Dim y As Double
    x = InputBox("Enter x", "Example")
    If x = Empty Then Exit Sub
    y = x ^ 2
    Debug.Print y
End Sub

Message Box

The MsgBox() procedure displays a dialog box containing a message, waits for the user to press a button, and then returns an Integer value indicating which button was pressed.

Syntax:

MsgBox(Prompt[, Buttons] [, Title] [, Helpfile, Context])

• Prompt — a string expression displayed as a message in the dialog box.
• Buttons — a numeric expression representing the sum of values that specify the number and type of buttons displayed, the type of icon used, the default button, and the modality of the message box. The default value of this parameter is 0. The values of the constants defining the number, type of buttons, and type of icon used are listed in Tables 1–3
• Title — a string expression displayed in the dialog box title bar. If omitted, the application name is used.
• Helpfile — a string expression specifying the name of the Help file containing information about the dialog box. If this parameter is provided, the Context parameter must also be specified.
• Context — a numeric expression specifying the context ID of the relevant Help topic. If this parameter is provided, the Helpfile parameter must also be specified.

Table 1. Values of the Buttons parameter of the MsgBox() procedure defining which buttons are displayed in the dialog box

Constant	Value	Buttons Displayed
vbOKOnly	0
vbOKCancel	1
vbAbortRetryIgnore	2
vbYesNoCancel	3
vbYesNo	4
vbRetryCancel	5

Table 2. Values of the Buttons parameter of the MsgBox() procedure defining the information icons displayed in the dialog box

Constant	Value	Message Icon
vbCritical	16
vbQuestion	32
vbExclamation	48
vbInformation	64

Table 3. Values of the Buttons parameter of the MsgBox() procedure defining the default button in the dialog box

Constant	Value	Default Button Number
vbDefaultButton1	0	1
vbDefaultButton2	256	2
vbDefaultButton3	512	3
vbDefaultButton4	768	4

When writing programs that respond depending on which dialog box button is pressed, it is more convenient to use the VBA constants listed in Table 4 instead of return values. These make the program code easier to read and also easier to remember.

Table 4. Constants identifying the pressed button

Constant	Value	Pressed Button
vbOK	1	OK
vbCancel	2	Cancel
vbAbort	3	Abort
vbRetry	4	Retry
vbIgnore	5	Ignore
vbYes	6	Yes
vbNo	7	No

An example of using message boxes is provided: the square of an entered value is calculated, and the result is then sequentially displayed in five message boxes.

• The first box is a simple message box.

• The second is a message box displaying text on two lines .

• The third is a message box with a message and an information icon.

• The fourth is a message box with a message, an information icon, and two buttons, with the Yes button set as the default.

• The fifth is a message box with a message, an information icon, and a custom title.

Determining the Pressed Button in a Message Box

The MsgBox() procedure is convenient for displaying information. However, if it is necessary to know which choice the user made by pressing one of the dialog box buttons, then MsgBox() must be used as a function. In this case, the value returned by MsgBox() should be assigned to a variable, and its parameters must be placed in parentheses.

Example of using a message box with three buttons

Sub DemoThreeButtonsMsgBox()
    Dim structure As Integer
    Dim btn As Integer   
    structure = vbYesNoCancel + vbQuestion + vbDefaultButton1
    btn = MsgBox("Choose <Yes>, <No> or <Cancel>", structure, "Another Example")  
    Select Case btn
        Case vbYes
            MsgBox "You selected <Yes>", vbInformation, "Another Example"
        Case vbNo
            MsgBox "You selected <No>", vbInformation, "Another Example"
        Case vbCancel
            MsgBox "You selected <Cancel>", vbInformation, "Another Example"
    End Select
End Sub

In VBA programs, a standard set of operations on data can be used. There are three types of operations:

• mathematical — performed on numbers, and their result is numerical;
• relational — applied not only to numbers, and their result is logical values, for example, x > y;
• logical — applied to logical expressions, and their result is logical values, for example, Not x And y.

Mathematical Operations

VBA supports a standard set of mathematical operations from addition to exponentiation.

Table. Mathematical operations

Operation	Description
exp1 + exp2	Addition
exp1 – exp2	Subtraction
–exp	Negation (change of sign)
exp1 * exp2	Multiplication
exp1 / exp2	Division
exp1 \ exp2	Integer division
exp1 Mod exp2	Modulo (remainder)
exp1 ^ exp2	Exponentiation

Relational Operations

Table presents the relational operations used in VBA.

Table. Relational operations

Operation	Description
exp1 < exp2	Less than
exp1 > exp2	Greater than
exp1 <= exp2	Less than or equal to
exp1 >= exp2	Greater than or equal to
exp1 <> exp2	Not equal to
exp1 = exp2	Equal to
exp1 Is exp2	Comparison of two operands containing object references
exp1 Like exp2	Comparison of two string expressions

Logical Operations

Table presents the main logical operations used in VBA.

Table. Main logical operations

Operation	Description
exp1 And exp2	Logical AND
exp1 Or exp2	Logical OR
exp1 Xor exp2	Exclusive OR (returns True if and only if exactly one operand is True)
Not exp	Logical negation

The Option Compare Directive

The behavior of the Like comparison operation depends on the Option Compare directive, which is placed in the module declaration section. By default, each module is considered to have the Option Compare Binary instruction, in which uppercase and lowercase letters are distinguished. That is, the following operator will return False:

Debug.Print « AA » Like « aa »

If, however, the Option Compare Text instruction is specified in the module declaration section, then uppercase and lowercase letters are not distinguished, and the same operator will now return True.

In the following example The Option Compare Directive , in the input box of a dialog window the user must enter their name in Latin letters. When pressing the OK button, the program analyzes the input and displays a message. Specifically:

• if the user forgot to enter a name, they are informed about it;
• if the entered name contains characters other than Latin letters, they are informed about it;
• if the name consists only of Latin letters, the program greets them.

Listing. The Option Compare Directive

Option Compare Text
Sub DemoCompare()
    Dim name As String, lng As Integer, i As Integer
    name = InputBox("Введите имя")
    lng = Len(Trim(name))
    If lng = 0 Then
        MsgBox "You forgot to enter a name"
        Exit Sub
    Else
        For i = 1 To lng
            If Not Mid(name, i, 1) Like "[A-Z]" Then
                MsgBox "The name must consist only" & vbCr _
                       & "of Latin alphabet letters"
                MsgBox "Hello, " & name
                Exit Sub
            End If
        Next i
    End If
End Sub

Operation Priorities

VBA performs operations according to their priorities, which ensures unambiguous interpretation of expression values. Table lists the execution priorities of operations.

Table . Operation priorities

Priority	Operation
01	Function call and parentheses
02	^ (exponentiation)
03	– (negation)
04	* and /
05	\ (integer division)
06	Mod (modulo remainder)
07	+ and –
08	>, <, >=, <=, <>, and =
09	Not
10	And
11	Or
12	Xor
13	Eqv
14	Imp

 

How Do They Help When Writing Programs Comments
The text in a program that follows the ‘ symbol up to the end of the line is ignored by the compiler and represents a comment.
Comments allow you to add descriptions and explanations for those programmers who will later need to understand your program. By commenting the entire program, you save time. In addition, comments make the program easier to understand both for the author and for those to whom, if necessary, you will explain the program code.
Comments are also useful when debugging programs. They allow you to temporarily disable lines of program code.

Below are possible ways to use comments in program code:

Dim a As Integer
' **************************
' * a — integer variable   *
' **************************
Dim b As String   ' b — string variable
' b = sin(2) — this operator is disabled

Line Continuation

Placing a space character and an underscore (_) at the end of a line makes it possible to split a single statement into several lines, while the compiler will treat them as a single statement. It should be remembered that:

• string constants cannot be split by line continuation;
• no more than seven continuations of the same line are allowed;
• a line itself cannot exceed 1024 characters.

In the following example, the first construction is a line-broken version of the second:

ActiveCell.Offset(rowoffset:=0, _
    columnoffset:=1).Value = Sum

and

ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Value = Sum

To split a string constant across lines, it must be represented as the result of concatenating several string constants, and the line break must occur at the concatenation operation (&).

Here is an example of correct and incorrect line breaks for the string « Visual Basic for Applications »:

Incorrect break:

s = "Visual Basic for _
Applications"

Correct break:

s = "Visual Basic " _
    & "for Applications"

Placing Several Statements on One Line

Using the colon sign (:) allows you to place several statements on one line. Thus, the following two constructions are equivalent:

x = 1    ' variable x contains 1
x = x + 2 ' variable x contains 3

and

x = 1 : x = x + 2

Quite often when writing programs, a data type is needed that allows the use of various types together. A record is a collection of several elements, each of which may have its own type. An element of a record is called a field. A record is a special case of a class in which no properties or methods are defined:

[Private | Public] Type TypeName
    ElementName [([Size])] As DataType
    ElementName [([Size])] As DataType
    ...
End Type

• Private — used for user-defined types that are accessible only in the module containing the definition.
• Public — used for user-defined types that are accessible to all procedures in all modules of all projects.
• TypeName — the name of the user-defined type.
• ElementName — the name of an element of the user-defined type.
• Size — the size of the element if it is an array.
• DataType — the data type of the element. Supported types: Byte, Boolean, Integer, Long, Currency, Single, Double, Date, String (for variable-length strings), String * length (for fixed-length strings), Object, Variant, another user-defined type, or an object type.

The Type statement is used only at the module level. If a Type statement appears in a class module, it must be preceded by the keyword Private.

In Examples of using some structured , the Type statement is used to define a data type encapsulating information about an employee of a certain company.

Example of a user-defined type

Type Employee
    FirstName As String
    LastName As String
    Position As String
    BirthDate As Date
End Type

Sub InitialData()
    Dim emp As Employee
    With emp
        .FirstName = "James"
        .LastName = "Bond"
        .Position = "Secret agent 007"
        .BirthDate = #05/17/80#
    End With
    With emp
        MsgBox .FirstName & vbCr & .LastName & vbCr & .Position & vbCr & _
        .BirthDate
    End With
End Sub

NOTE
In Examples of using some structured, the MsgBox statement uses this technique: line continuation with a space and the underscore _ at the end of the line. In addition, the built-in constant vbCr is specified in the MsgBox statement, which allows the required information to be displayed on a new line in the message box.

It is also possible to create an array containing elements of a user-defined type. For example, the following array consists of information about 20 employees of a company’s sales department.

Example of an array whose elements have a user-defined type

Sub InitialData()
    Dim emp(3) As Employee
    With emp(0)
        .FirstName = "James"
        .LastName = "Bond"
        .Position = "Secret agent 007"
        .BirthDate = #05/17/80#
    End With
    With emp(1)
        .FirstName = "Alice"
        .LastName = "Smith"
        .Position = "Just a secret agent"
        .BirthDate = #09/06/89#
    End With
End Sub

Strings

A string represents a sequence of characters, each of which has the type Char.
A sequence of characters assigned to a string variable must be enclosed in quotation marks.

Dim str As String
str = "This is a string"

A string variable can be declared as a variable-length string or as a fixed-length string. In the following example, the variable LastName is declared as a variable-length string, and the variable State is declared as a fixed-length string consisting of two characters. If the variable State is assigned a string expression that consists of more than two characters, the extra characters on the right will be truncated upon assignment.

Dim LastName As String
Dim State As String * 2

Note that in VBA there is only one string operation — concatenation. It is used to combine several strings into one. The concatenation operation is denoted by the ampersand symbol (&) or the plus symbol (+). To avoid confusion, concatenation with the & sign is generally used. When two strings are combined, the second string is appended directly to the end of the first. The result is a larger string that fully contains both of the original strings.

In the following example, the variable s is assigned the value « Visual Basic for Applications ».

Dim s As String
s = "Visual Basic " & "for Applications"

The keyword Empty returns a reference to an empty string. The same effect can be achieved by placing a pair of quotation marks («  »). For example, the following statement will display a message box with the text « Equivalent ».

If Empty = "" Then MsgBox "Equivalent"

Enumerated Type

The enumerated type provides a convenient way to work with constants and allows associating constant values with their names.

[Public | Private] Enum TypeName
    ElementName [= Expression]
    ElementName [= Expression]
    ...
End Enum

• TypeName — the name of the enumerated type.
• ElementName — the name of the constant. By default, the first constant has the value 0, the second has the value 1, and so on. Using the Expression parameter, you can assign arbitrary values to constants.
• Expression — the value of the constant.

In the following example , values are defined using an enumerated type to identify the sides of a coin.

Using an enumerated type in the coin toss example

Enum Coin
    Head = 1
    Tail = -1
End Enum

Sub Attemp()
    Dim r As Integer
    Randomize
    r = 2 * Int(2 * Rnd()) - 1
    Select Case r
        Case Head
            MsgBox "Heads"
        Case Tail
            MsgBox "Tails"
    End Select
End Sub

An array is a variable that simultaneously stores several values of the same type. Thus, an array represents a collection of homogeneous indexed variables.

The number of indices of an array may also vary. Most often, arrays with one or two indices are used; less often—with three; arrays with even more indices are extremely rare. In VBA, it is allowed to use up to 60 indices. The number of indices of an array is usually referred to as the dimension of the array. Arrays with one index are called one-dimensional, with two—two-dimensional, and so on. Arrays with a larger number of dimensions can occupy very large amounts of memory, so one should be cautious in their use.

Before using an array, it must be declared with the Dim statement and the type of the stored values must be specified. All values in an array belong to one data type. This restriction can be bypassed in practice by declaring the array as type Variant—in that case, the elements of the array may take values of different types. The syntax of the array declaration statement is as follows:

Dim <ArrayName>(<size1>, <size2>, ...) As <DataType>

where the values <size1>, <size2> specified in parentheses define the dimensions of the array—the number of indices and the maximum permissible value for each specific index. By default, array elements are indexed starting at zero. For example, the declaration:

Dim Array1(9) As Integer

defines a one-dimensional array of 10 elements, which are integer variables, while the declaration:

Dim Array2(4, 9) As Variant

defines a two-dimensional array of 50 elements, which are variables of the universal type Variant.

NOTE
The default lower bound of an array (the index) does not have to be zero. To change this default value, use the Option Base statement. For example, if you place the statement Option Base 1 at the beginning of your module, array indexing will by default start from one instead of zero.

For instance, the following operator declares a vector consisting of 11 elements:

Option Base 1
Dim A(11) As Integer

Another way to change the base index is to use the keyword To when declaring an array:

Dim B(1 To 3, 1 To 3) As Single
Dim A(1 To 12) As Integer

When declaring an array, you can specify not only the upper bound of the index but also its lower bound, i.e., explicitly define the range of a given array index. The lower bound can be any integer, not necessarily non-negative. The syntax of such a definition looks like this:

Dim <ArrayName>(<min1> To <max1>, ...) As <DataType>

For example, if you intend to work with an array of meteorological data representing the average daily temperatures for the last two weeks, it may be convenient to define the array as follows:

Dim Temperature(-14 To 0) As Single

In this case, for example, Temperature(-2) would correspond to the temperature of the day before yesterday, and to determine the required index for the day of interest, it is enough to use the difference between the dates.

In the examples given above, we dealt with fixed-size arrays, where the number of elements was explicitly specified at the time of declaration with the Dim statement. Such arrays are called static. In VBA, it is also possible to use dynamic arrays, whose size is not fixed at the time of declaration. The definition of the size of a dynamic array can be made directly during program execution.

When defining a dynamic array, the Dim statement contains only the name of the array followed by empty parentheses and the data type. The number of indices and their ranges are not specified. However, before using the array, the ReDim statement must be executed, which defines the dimensions and ranges of the dynamic array indices.

The syntax for declaring and defining the size of a dynamic array is:

Dim <ArrayName>() As <DataType>
ReDim <ArrayName>(<size1>, <size2>, ...)

Here is an example of declaring, sizing, and using a dynamic array, and then changing the size and dimensions of the same array:

Dim dArray() As Variant
ReDim dArray(1, 2)
dArray(0, 0) = 2
dArray(0, 1) = 3
k = dArray(0, 0) + dArray(0, 1)
ReDim dArray(k)
dArray(0) = "String1"

In this example, the array dArray is first defined as a two-dimensional array of six elements, and then redefined as a one-dimensional array, with the upper bound of the index set by the value of variable k.

NOTE
To determine the current lower or upper bound of an array, you can use the functions LBound() and UBound(), respectively.

For example, the following code will display 100 and 5:

Dim A(1 To 100, 0 To 5)
MsgBox UBound(A, 1) & vbCr & UBound(A, 2)

The following instructions allow you to iterate through the elements of an array without explicitly specifying its size:

Dim d As Variant
Dim i As Integer
d = Array("Mon", "Tue", "Wed", "Thu", "Fri")
For i = LBound(d) To UBound(d)
    MsgBox d(i)
Next

Keep in mind that by default, when the size of an array is changed, new memory is allocated for it, and the current values of its elements are lost. To preserve the current values of the array when changing its size, the keyword Preserve is used.

For example, to increase the size of the array dArray by one element without losing the values of the existing elements, you can do the following:

ReDim Preserve dArray(UBound(dArray) + 1)

Element-by-Element Initialization of an Array

An array can be initialized element by element in the following ways:

• With a sequence of assignment statements:

Dim B(1, 1) As Single
B(0, 0) = 2
B(0, 1) = 4
B(1, 0) = 1
B(1, 1) = 6
Dim M(1 To 9, 1 To 9) As Integer

• With a loop statement:

Dim i As Integer
Dim j As Integer
For i = 1 To 9
    For j = 1 To 9
        M(i, j) = i * j
    Next
Next

Array Initialization Using the Array() Function

As mentioned earlier, a convenient way to define one-dimensional arrays is the Array() function, which converts a list of elements separated by commas into a vector of these values and assigns them to a variable of type Variant. Initialization of both one-dimensional and multi-dimensional arrays is possible by using nested Array() function constructions.

• Initialization of a one-dimensional array:

Dim num As Variant
Dim s As Double
num = Array(10, 20)
s = num(0) + num(1)
MsgBox s

• Initialization of a multi-dimensional array:

Dim CityCountry As Variant
CityCountry = Array(Array("Saint Petersburg", "Russia"), _
                    Array("Cape Town", "South Africa"))
MsgBox CityCountry(0)(0)
' Displays Saint Petersburg
MsgBox CityCountry(0)(1)
' Displays Russia

Array and Range

In VBA, there is a close relationship between ranges and arrays. It is possible both to fill an array with values from the cells of a range using a single assignment operator, and conversely, to fill a range of cells with the elements of an array using a single assignment operator. In such cases, the array must be declared as a variable of type Variant.

• Initializing an array from a range with a single assignment operator:

Dim r As Range
Set r = Range("C1:D3")
Dim M As Variant
M = r.Value
Dim i As Integer
Dim j As Integer
For i = 1 To r.Rows.Count
    For j = 1 To r.Columns.Count
        Cells(i, j).Value = M(i, j)
    Next
Next

• Filling a range from an array with a single assignment operator:

Dim M(1 To 9, 1 To 9)
Dim i As Integer
Dim j As Integer
For i = 1 To 9
    For j = 1 To 9
        M(i, j) = i * j
    Next
Next
Dim r As Range
Set r = Range(Cells(1, 1), Cells(9, 9))
r.Value = M

Using Dynamic Arrays
Let us give an example of using the ReDim statement to change the number of elements and dimensions of an array (Listing 2.2). In the given example, an array is created to store the results of coin tosses. The coin is tossed until heads appears three times. The dimension of the dynamic array is adjusted after each toss while preserving the previously recorded toss results, thanks to the use of the keyword Preserve.

Changing the dimension of a dynamic array while preserving its contents

Dim Attempt()
Dim i As Integer
Dim score As Integer
Dim coin As Integer
i = 0
score = 0
Do
    i = i + 1
    coin = Int(2 * Rnd())
    ' 0 — tails
    ' 1 — heads
    If coin = 1 Then score = score + 1
    ReDim Preserve Attempt(i)
    Attempt(i) = coin
Loop Until score = 3

How to check whether a Variant variable contains an array of values?
The IsArray() function returns True if the specified variable of type Variant contains an array, and False otherwise. For example, the following procedure (Listing 2.3), which handles the SelectionChange event of the Worksheet object, displays a message if the selected range contains an array of cells.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim A As Variant
    A = Target.Value
    If IsArray(A) Then
        MsgBox "Contains an array of selected cells"
    Else
        MsgBox "Does not contain an array of selected cells"
    End If
End Sub

Reinitializing an Array and Releasing the Memory Allocated to the Array
The Erase statement reinitializes the elements of fixed-size arrays and releases the memory allocated to a dynamic array. The Erase statement sets the elements of fixed-size arrays as follows:

• a numeric array or an array of fixed-length strings (assigns the value 0 to each element);
• an array of variable-length strings (assigns an empty string «  » to each element);
• an array of type Variant (assigns the value Empty to each element).

For example, in the following code, the value 1 will be displayed first, and then 0:

Dim A(2) As Integer
A(2) = 1
MsgBox A(2)
Erase A
MsgBox A(2)

The Erase statement also releases the memory used by dynamic arrays. Before the program can reference the dynamic array again, it is necessary to redefine the dimensions of the array variable using the ReDim statement. For example:

Dim B() As Integer
ReDim B(6)
Erase B
ReDim B(3)

All procedures, functions, variables, and constants in VBA have their own scope. This means that they can only be used in a specific place in the program code—precisely where they are defined. For example, if variable A is declared with the Dim statement inside the body of the procedure Pro1(), then that procedure is its scope. Thus, if there is another procedure Pro2(), you cannot use the same variable inside it. If you attempt to do so, you will either receive an error message due to the use of an undeclared variable (in case the previously mentioned Option Explicit statement is active), or you will simply get a different variable—with the same name, but completely unrelated to the variable of the same name from the first procedure.

There are three types of variable scope:

• Procedure-level variables are recognized only within the procedure in which they are declared using the Dim or Static statement. Such variables are called local variables.
• Module-level variables are used only within the module in which they are declared, but not in other modules of the given project. They are declared with Dim or Private in the module declaration area, i.e., before the description of procedures.
• Module-level variables declared with Public are accessible to all procedures in the project. Such variables are called public variables.

A private variable retains its value only while the procedure in which it is declared is running. Once the procedure ends, the variable’s value is lost, and it must be reinitialized when the procedure is run again. Variables declared with the Static statement retain their value after the procedure ends, as long as the program continues to run.

Now let us consider the scope of procedures and functions. Procedures and functions have only two levels of scope: module-level and project-level. By default, project-level scope is applied. Thus, a procedure or function can be called by any other procedure or function within the same project. When describing procedures and functions at the project level, the optional keyword Public may also be used. The presence or absence of this word has no effect on the procedure.

If you want to define a procedure that is used only at the module level, the keyword Private is applied. Note that such a declaration not only restricts the scope of the procedure but also prohibits its use as a stand-alone procedure—it can only be called from another procedure.

Finally, when describing procedures or functions, the keyword Static can also be used. It does not affect the scope of the procedure, but it influences all variables declared within that procedure or function. In this case, all local variables acquire the status of Static, which means they remain in memory after the procedure ends and retain their previous values when the procedure is called again.

Let us now look at an example of a module.

Example of a Module

Public A1 As String
Private A2 As Integer
Dim A3 As Single

Sub Pro1()
    Dim A4 As Integer
    Static A5 As Integer
    A1 = "Text string 1"
    A2 = 2
    A3 = 3.14
    A4 = A4 + 4
    A5 = A5 + 5
    MsgBox A4
    MsgBox A5
End Sub

Sub Pro2()
    Pro1
    MsgBox A1
    MsgBox A2
    MsgBox A3
    MsgBox A4
    MsgBox A5
    Proc1
End Sub

In this example, variable A1 is defined at the project level (the keyword Public is used), variables A2 and A3 are defined at the module level, variable A4 is defined at the procedure level inside Pro1(), and variable A5, although defined inside the body of Pro1(), is declared as static.

When procedure Pro2() is called, the following happens: from this procedure, Pro1() is called, which assigns values to all five variables (A1, A2, A3, A4, and A5), and then displays the current values of variables A4 and A5 in message boxes.

After Pro1() ends, the current values of variables A1–A5 are displayed. At this point, it turns out that variables A1–A3 have retained their values, since they are defined at the module level, while variables A4 and A5 have empty values, because their scope is limited to the procedures in which they are used. Any changes to these variables inside one procedure have nothing to do with similarly named variables in another procedure—these are, in fact, different variables that just happen to have the same names.

After that, Pro1() is called once again, and it again modifies and displays the values of variables A4 and A5. In this case, variable A4 once more takes the value 4, because each time the procedure is called, memory is newly allocated for this variable and it is initialized with an empty value. Unlike A4, the variable A5, declared as static, retains its previous value from the earlier call of this procedure. As a result, upon the second call its value becomes 10.