Writing Visual Basic statements

A statement in Visual Basic is a complete instruction. Each statement belongs to one of the following three categories:

• declaration statements: which name (declare) a variables, constant, or procedure and can also specify a data type.
• assignment statements: which assign a value or expression to a variable, constant or property.
• executable statements: which initiate actions. These statements can execute a method or function, and they can loop or branch through blocks of code. Executable statements often contain mathematical or conditional operators.

Writing declaration statements

You use declaration statements to name and define procedures, variables, arrays, and constants. When you declare a procedure, variable, or constant, you also define its scope, depending on where you place the declaration and what keywords you use to declare it.

The following example contains three declarations.

Sub ApplyFormat() 
    Const limit As Integer = 33 
    Dim myCell As Range 
    ' More statements 
End Sub

The Sub statement (with matching End Sub statement) declares a procedure named ApplyFormat. All the statements enclosed by the Sub and End Sub statements are executed whenever the ApplyFormat procedure is called or run.

The Const statement declares the constant limit specifying the Integer data type and a value of 33.

The Dim statement declares the myCell variable. The data type is an object, in this case, a Microsoft Excel Range object. You can declare a variable to be any object that is exposed in the application that you are using. Dim statements are one type of statement used to declare variables. Other keywords used in declarations are ReDim, Static, Public, Private, and Const.

Writing assignment statements

Assignment statements assign a value or expression to a variable or constant. Assignment statements always include an equal sign (=).

The following example assigns the return value of the InputBox function to the variable.

Sub Question() 
    Dim yourName As String 
    yourName = InputBox("What is your name?") 
    MsgBox "Your name is " & yourName 
End Sub

The Set statement is used to assign an object to a variable that has been declared as an object. The Set keyword is required. In the following example, the Set statement assigns a range on Sheet1 to the object variable myCell.

Sub ApplyFormat() 
    Dim myCell As Range 
    Set myCell = Worksheets("Sheet1").Range("A1") 
    With myCell.Font 
        .Bold = True 
        .Italic = True 
    End With 
End Sub

Statements that set property values are also assignment statements. The following example sets the Bold property of the Font object for the active cell.

ActiveCell.Font.Bold = True

Writing executable statements

An executable statement initiates action. It can execute a method or function, and it can loop or branch through blocks of code. Executable statements often contain mathematical or conditional operators.

The following example uses a For Each...Next control statement to iterate through each cell in a range named MyRange on Sheet1 of an active Microsoft Excel workbook. The variable cell is a cell in the collection of cells contained in MyRange.

Sub ApplyFormat() 
    Dim cell as Range
    Const limit As Integer = 33 
    For Each cell In Worksheets("Sheet1").Range("MyRange").Cells 
        If cell.Value > limit Then 
            With cell.Font 
                .Bold = True 
                .Italic = True 
            End With 
        End If 
    Next cell 
End Sub

The If...Then...Else control statement in the example checks the value of the cell. If the value is greater than 33, the With statement sets the Bold and Italic properties of the Font object for that cell. If...Then...Else statements end with End If. The With statement improves the code by showing on what single specified object all the following actions apply (readability) and also makes the execution of code faster. The Next statement calls the next cell in the collection of cells contained in MyRange.