A statement in Visual Basic is a complete instruction that make declaration, defines an action, defines an item, or gives the value of a variable. It can contain keywords, operators, variables, constants, and expressions. A statement usually fits on one line, but you can continue a statement onto the next line using a line-continuation character, which is the combination of a space followed by an underscore. Visual Basic ignores comments when it runs your procedures. Comment lines begin with an apostrophe ( ' ) or with Rem followed by a space, and can be added anywhere in a procedure. Each statement belongs to one of the following three categories:

• Declaration statements, which name a variable, constant, or procedure and can also specify a data type.
• Assignment statements, which assign a value or expression to a variable or constant.
• 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.

Executable Statements

Declaration Statements

Before you can use a variable, you must first declare it. When you declare a variable, you are:
▪   instructing your computer which parts of your program have permission to store and retrieve data in a variable
▪   telling your computer the name of your variable
▪   defining the data type that your variable can hold

' In VB 6,  declaration creates variable Lng and assign value to it in the assignment statement
Dim Lng As Long
Lng = 256

' In VB.NET, declaration creates variable Lng and assigns a value 256 to it as part of its declaration
Dim Lng As Long = 256

 

Let’s refresh on the scope of declaration. Note that a variable declared as Public is visible to all procedures in all modules in a directly referencing project unless Option Private Module is in effect. When Option Private Module is in effect, the module itself is private and therefore not visible to referencing projects. Variables declared in a Option Private Module procedure are accessible only within the procedure and lose their value between calls unless they are declared Static. Variables declared with the Static statement retain their values as long as the code is running until the module is reset or restarted.
 

' Set the string comparison method to Binary (ie. case-sensitive). For example, "ABC” is less than "abc"
Option Compare Binary

' Set the string comparison method to Text (ie. case-insensitive). For example,  "ABC" is equal to "abc"
Option Compare Text

When you have the Option Compare Binary statement (case-sensitive) at the top of your module, there may be some times you want to override this and do a text case-insensitive Text comparison. Below is a simple re-usable function allowing you to do this so that you don't have to hard-code it every time. StrComp is given a return value of ‘0’ and performs a textual comparison so that String1 is now equal to String2.

Option Explicit
Option Compare Binary

Public Function BinaryToTextOptionCompare (String1 As String, String2 As String) As Boolean
      BinaryToTextOptionCompare = (StrComp(String1, String2, vbTextCompare) = 0)
End Function

The StrComp function has the following return values:

Return To Top

Sub Statement
Declares the name, arguments, and code of a Sub procedure. The general syntax of a subroutine is:

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

name
Required. Name of the Sub procedure, and must follow standard variable naming conventions.

Option Explicit

Private Static Sub ChangeFont(Addr As String, mActual As Single, mTarget As Single)
    With ActiveSheet.Range(Addr)
        If mActual > mTarget Then         ' if argument is > mTarget value of $13000000
            .Font.ColorIndex = 3          ‘ format cells with Red font
            .Interior.ColorIndex = 6      ‘ fill cells with Yellow
            .Font.Bold = True             ‘ bold cells
        Else                              ‘ do nothing
        End If
    End With
End Sub


Sub FormatValue()
Dim NColumns As Integer, NRows As Integer, i As Integer, j As Integer, _
mTarget As Single, Addr As String, mActual As Single     ' declare local variables
NColumns = Range("MyRange").Columns.Count           ‘count number of columns
NRows = Range("MyRange").Rows.Count                 ‘ count number of rows
mTarget = 13000000
  For j = 1 To NColumns
    For i = 1 To NRows
        Addr = Range("MyRange").Cells(i, j).Address ‘ address of the cell
        mActual = Range("MyRange").Cells(i, j)      ‘ values of the cells in named Range
        Call ChangeFont(Addr, mActual, mTarget)     ‘ pass arguments to ChangeFont sub
    Next
  Next
End Sub

The ParamArray keyword is used in these contexts: Declare Statement, Function Statement, and Sub statement. Unlike VB6, in VB.NET the ParamArray keyword defines a true array object, which you can process using any of the methods of the array class, and ParamArray keyword also let you define arrays of any type, not just objects. Each argument to a ParamArray parameter can be of a different data type. That means the ParamArray parameter itself must be declared as an array of type Variant. Each argument supplied in the call to the function becomes a corresponding element of the Variant array. Snippet code below adopted from ADezii, shows that several calls are made to the AverageNum() function, each with a varying number of arguments, including invalid arguments. You can see the output through the Immediate Window.

Public Function AverageNum(ParamArray varNumbers() As Variant)
Dim varX, varY, intAverageNum As Integer
intAverageNum = 0
  For Each varX In varNumbers
    If IsNumeric(varX) And Not IsNull(varNumbers) Then
       varY = varY + varX
       intAverageNum = intAverageNum + 1
       AverageNum = varY / intAverageNum
       Else
    End If
  Next
End Function

‘ you can see result from these sample calls in the Immediate Window:
Debug.Print AverageNum(10, 30, 20)                 ' returns average number 20
Debug.Print AverageNum(23.15, 45.105, 456.56)      ' returns 174.938333333333
Debug.Print AverageNum(Null, "AB", "A12", 20, 30)  ' returns 25

Dim NColumns As Integer, NRows As Integer     ' declare two variables as integer
Static Sub ChangeFont(Addr As String, mActual As Single, mTarget As Single)
Static X As New Worksheet             ‘ declare a variable for a new instance of a worksheet
Dim MyVar()                            ' declare a dynamic Variant array
Dim MyVar() As Integer            ' declare MyVar array and assign Integer data type
Dim MyVar(5)                          ' declare an array with 6 elements
Dim MyVar(5) As Date                  ' declare an array with 6 elements and assign Date data type
    MyVar(3)= Format("12-24-2005", _     ' declare a 3-element array and assign a date to the 3 elements
  "General Date")
Dim MyVar(1 To 5, 1 To 10) As Single  ' declare a 5-by-10 multidimensional array and assign data type
ReDim MyVar(10)                       ‘ declare an 11-element array but loses all the data in it
ReDim Preserve MyVar(15)          ‘ declare an 16-element array but keeps all the data in it

Static is used at procedure level to declare variables and allocate storage space. You should only declare with a Static statement within a procedure when you want to retain the value of the variables between procedure calls until the module is reset or restarted. In class modules, variables declared with the Static statement retain their value in each class instance until that instance is destroyed. In form modules, static variables retain their value until the form is closed.

• Option Base - Determines whether the lowest range of an array is 0 or 1
  • option base 1
• Erase - Erases all values of an array
  • erase (arrayname)
• Dim - Creates an array
  • dim arrayname(25)
• Redim - Resets the bounds of an array (has option to save values)
  • redim arrayname(28)
• UBound - Returns the upper dimension of an array
  • i = ubound (arrayname)
• LBound - Returns the lower dimension of an array
  • i = lbound (arrayname)
• Filter - Returns a subset of an array based on a filter
  • Filter (inputarray, searchstring)
• Array - Yes, there is a function called array. It returns an array that has been filled with data from a list. It allows you to put the actual data values in the code to avoid having the user input it or to avoid having to read it from a file
  • ArrayName = Array (10, 20, 30)
• Join - Concatenates strings within an array

Function Statement
Declares the name, arguments, and code that define a Function procedure. The syntax is:

arglist is optional. It is a list of variables representing arguments that are passed to the Function procedure when it is called. Multiple variables are separated by commas. Each argument in the arglist part has the following syntax and parts (you can refer to the Sub statement that was described in earlier pages, for explanation on the component elements of the Function statement syntax and arglist argument syntax):

 [Optional] [ByVal | ByRef] [ParamArray] varname[( )] [As type] [= defaultvalue]

Option Explicit
Function CombineText(myDelimeter As String, ParamArray myText()) As String
   Dim v As Variant
   Dim s As String
   For Each v In myText
      If Len(s) = 0 Then
         s = v
      ElseIf Len(v) > 0 Then
         s = s & myDelimeter & v
      End If
   Next
   CombineText = s
End Function

' You can invoke the above function as:
Dim Address As String
Dim MyVar(4) As String
MyVar(0) = "AMSEA"
MyVar(1) = "3 Business Park Vista"
MyVar(2) = "S486051"
MyVar(3) = "Singapore"
Address = CombineText(", ", MyVar(0), MyVar(1) & vbcrlf & MyVar(2), MyVar(3))
debug.print Address

' Using the ParamArray array parameter, this second example returns the sum of any number of values passed to it:
Function Sum(ParamArray myArgs() As Variant) As Single
Dim total As Single
Dim i As Integer
For i = 0 To UBound(myArgs)
    total = total + myArgs(i)
Next i
Sum = total
End Function

Dim MyVar(4) As Single, x As Single
MyVar(0) = "5.5"
MyVar(1) = "4.5"
MyVar(2) = "5"
MyVar(3) = "15"
x = Sum(MyVar(0), MyVar(1), MyVar(2), MyVar(3))

Like a Sub procedure, Function procedure is a separate procedure that can take arguments, perform a series of statements, and change the values of its arguments. For Function procedures, the data type of the procedure determines the data type it returns. You can use an As clause following arglist to specify the return type of the function. Within arglist, you can use an As clause to specify the data type of any of the arguments passed to the procedure. If you don’t explicitly specify using Public, Private, or Friend, Function procedures are Public by default. If Static isn't used, the value of local variables is not preserved between calls. The Friend keyword can only be used in class modules. However, Friend procedures can be accessed by procedures in any module of a project. You can define a Function procedure inside a module, class, interface, or structure, but you can't define a Function procedure inside another Function, or Sub procedure because all executable code must be in procedures.

You call a Function procedure using the function name, followed by the argument list in parentheses. You can omit the parentheses only if you are not supplying any arguments. A function can also be called using the Call statement, in which case the return value is ignored. To return a value from a function, assign the value to the function name or include it in a Return statement.. When the Function procedure returns to the calling program or your main procedure, execution continues with the statement following the statement that called it. Any number of value assignment statements and Exit Function statements can appear anywhere within the procedure. The Exit Function statement will cause an immediate exit from a Function procedure. If you use Exit Function without assigning a value to the variable name, the variables will initialize, and the function returns the default value appropriate to the argument type: a numeric function returns 0 (for data type Byte, Char, Decimal, Double, Integer, Long, Short, and Single), a string function returns a zero-length string (""), a Boolean function returns False, a Date function returns #1/1/0001 12:00 AM#, and a Variant function returns Empty. A function that returns an object or array reference returns Nothing if no object or array reference is assigned to the variable name (using Set statement) within the Function.

The following procedure example assigns the return value to the function name myFirstSheet and then uses the Exit Function statement to return.

Function myFirstSheet(i As String) As String

  ‘do something…
  myFirstSheet = "template"

  ‘do something…
  Exit Function

End Function

Some caution on Function statement:
1) Function procedures can be recursive; that is, they can call themselves to perform a given task. However, recursion can lead to stack overflow. The Static keyword usually isn't used with recursive Function procedures.

2) A Function procedure can use a variable that is not explicitly declared in the procedure, but a naming conflict can occur if anything you defined at the module level has the same name. If your procedure refers to an undeclared variable that has the same name as another procedure, constant, or variable, it is assumed that your procedure refers to that module-level name. Explicitly declare variables to avoid this kind of conflict. Always use Option Explicit statement to force explicit declaration of variables.
 

' objX and objY now refer to the same object
objX.Text = "wiliam"                   ' initialize property
MyStr = objY.Text                      ' returns ‘william’

Sub GoSubReturnTest()
Dim i As Long, Total As Currency, itemCost As Currency
itemCost = 3500
i = InputBox("Enter your discount $amount")     ' solicit a value from user
    If i > 0 Then GoSub MyRoutine               ' use routine if user enters a positive number
    Exit Sub    ' Use Exit to prevent an error
MyRoutine:
    Total = i + itemCost      ' execute multiplication
    Debug.Print "Total is " & Format(Total, "currency")
    Return                    ' returns control to statement
End Sub
 

DefInt A-K                 ' variable names beginning with A through K default to Integer

DefStr L-Z                 ' variable names beginning with L through Z default to String

iNum = 2                   ' initialize Integer

strVar = "Nice day"        ' initialize String

anyVar = " Nice day "      ' causes "Type mismatch" error

Dim iNum As Double         ' Dim statement explicitly set the data type to Double

iNum = 2.345678            ' assign a Double data type

Declare Statement
Visual Basic provides a lot of functionality in its native functions and methods. However, sometimes you need some feature that simply isn't available using the native VB methods. This is where the Win32 API (Application Programming Interface) comes in. Windows provides several thousand functions that are available to a VB programmer (or any other language that supports them such as C\C++). To use an API in your code procedure all you need to do is include the appropriate Declare statement.

Declare statement is used at module level to declare references to external procedures in a dynamic-link library (DLL). A DLL file is nothing more than a collection of functions and subroutines that can be loaded dynamically, when your Visual Basic program requests one of its components at run time. Because DLL procedures reside in files that are external to your Visual Basic application, you must specify where the procedures are located and identify the arguments with which they should be called. DLL procedures declared in standard modules are public by default and can be called from anywhere in your application. You can place the Declare statements in class modules if the Declare statements are Private. Procedure names are case-sensitive in 32-bit versions of Visual Basic.

To declare a DLL procedure, you add a Declare statement to the declarations section of the code window. If the procedure does not returns a value, write Declare as a Sub:
[Public | Private] Declare Sub name Lib "libname" [Alias "aliasname"] [([arglist])]

If the procedure returns a value, write the declare as a function:
[Public | Private] Declare Function name Lib "libname" [Alias "aliasname"] [([arglist])] [As type]

Below are explanations for some of the different elements of the Declare statement syntax:

name
Required. Any valid procedure name. Note that DLL entry points are case sensitive.

Lib    
Required. Indicates that a DLL or code resource contains the procedure being declared.
The Lib clause is required for all declarations.

Libname
Required. Name of the DLL or code resource that contains the declared procedure.

Alias
Optional. Indicates that the procedure being called has another name in the DLL. This is useful when the external procedure name is the same as a keyword. You can also use Alias when a DLL procedure has the same name as a public variable, constant, or any other procedure in the same scope. Alias is also useful if any characters in the DLL procedure name aren't allowed by the DLL naming convention.

aliasname   
Optional. Name of the procedure in the DLL or code resource. If the first character is not a number sign (#), aliasname is the name of the procedure's entry point in the DLL. If (#) is the first character, all characters that follow must indicate the ordinal number of the procedure's entry point.

The arglist argument has the following syntax and parts:
[Optional] [ByVal | ByRef] [ParamArray] varname[( )] [As type]

Empty parentheses indicate that the Sub or Function procedure has no arguments and that Visual Basic ensure that none are passed. In example below, First takes no arguments. If you use arguments in a call to First, an error occurs:
Declare Sub First Lib "MyLib"()

If you include an argument list, the number and type of arguments are checked each time the procedure is called. In the following example, First takes one Long argument:
Declare Sub First Lib "MyLib" (X As Long)

Note: You can't have fixed-length strings in the argument list of a Declare statement; only variable-length strings can be passed to procedures. Fixed-length strings can appear as procedure arguments, but they are converted to variable-length strings before being passed. The vbNullString constant is used when calling external procedures, where the external procedure requires a string whose value is zero. This is not the same thing as a zero-length string ("").

To find the appropriate Declare statement you need to use you can use a tool called the Win32 API Viewer that comes with VB6. Open your API Text Viewer, go to the File Menu/Load Text File and open the file called WIN32API.TXT (it is resided in C:\Program Files\Microsoft Visual Studio\Common\Tool\Winapi). Once you have loaded the text file the Available Items list will populate with all the available API's. You can type in the name of API or use the scrollbar to look for the API, and double-click it (but ensure the Public option is selected). Click Copy to copy it to the clipboard and go to your VB to add this in your module. Once you have done this, you can use that function in your VB project as if it was a native VB function. Your help files or MSDN have the documentation that explain for each particular API you wish to use. Below is a simple example of using the GetTickCount() API to benchmark some code. GetTickCount() returns the number of milliseconds that have elapsed since the system was started, up to 49.7 days. This can also be used for timing how long it takes for a certain chunk of code to execute (by calling it before and after and calculating the difference). GetTickCount() API uses Kernel32.lib Kernel32.dll file.

Public Declare Function GetTickCount Lib "kernel32" Alias "GetTickCount" () As Long

Public Sub BenchmarkSomeCode()
Dim lngStart As Long
Dim i As Long
Dim lngSum As Long
lngStart = GetTickCount()

' benchmarking this code
For i = 1 to 100000000
lngSum = lngSum + 1
Next i
MsgBox CStr(GetTickCount() - lngStart)
End Sub
 

Return To Top

 

Summary note on Declaration statements

Declaration statements in general are pretty simple, but getting to the details and get it right can sometimes be tricky even for experienced programmers. Focus on understanding Dim/ReDim/Public/Private/Sub/Function/Type and Set, and commit these to your memory as you will use it in almost every procedure. The summary list below will give a memory refresher. You have to refer to MSDN library and books for examples to get better understanding.

• Dim - Used to define a variable as a certain type
  • Dim i as integer, r as single
  • You can use the Option Explicit to make sure that VB forces you to declare every variable you use. DIM is that simplest way to declare a variable
• ReDim - Used to change the dimensions of a dynamic array
  • Redim arrayname(37)
  • Don't be afraid of this one. You can use ReDim to create an array whose size grows by 1 every time you want to add a number to it. Then, the UBound tells you how many numbers you've added.
• Static - Establishes a procedure variable which keeps its value between calls
  • Static i as integer
  • For example, if you want to keep track of how many times you've been in a procedure, set a counter as STATIC and increment it by one for each visit to the procedure. It will never go away until the program is terminated.
• Public - Creates a variable which can be accessed outside its own procedure
  • Public i as integer
  • Even if you're the only programmer writing code in your application, use of Private vs Public will help catch errors if you inadvertently try to access an out-of-scope variable
• Private - Creates a variable that is read only in its own procedure or module, according to where the declaration took place.
  • Private i as integer
  • Use this as often as possible to avoid unnecessary exposure of your variables to coding mistakes.
• Sub - Defines a procedure which can execute a block of code
  • Sub NewProcedure (var1 as integer, var2 as string)
  • Be sure to check out HELP for how to handle Sub arguments. There are more questions and mistakes made concerning the use of arguments than just about anything else I've seen.
• Function - Declares a procedure which can return a value
  • Function NewFunction (var1 as integer, var2 as string) as SINGLE
  • This is actually the most versatile of the Sub/Function procedure types. It can do anything a Sub can do as well as returning a value for use in an expression.
• Call - Transfers control to a Sub or Function (is optional)
  • Call Procedure 1
  • Since the use of CALL is optional, forget you ever saw it
• CallByName - Executes a method of an object or set/returns a property
  • CallByName(form1,procedurename,vbMethod)
  • The really cool thing about this is that you don't have to hardcode a procedure call. Just use a string variable with the name of the procedure to call.
• Option Explicit - Instructs VB to force an explicit declaration of all variables
  • Option Explicit
  • You're borderline stupid if you don't use it to catch typing errors. Set up the VB IDE to automatically include this in all projects.
• Option Compare - Instructs VB on how to make string comparisons
  • Option Compare Binary
  • This can add case-insensitivity for those times when you don't want to hard-code it
• Option Private - Prevents a module's content from being referenced outside a project.
  • Option Private Module
  • Generally doesn't apply to most VB applications. If you find a good use for it let me know.
• Property Get - Declares how to get the value of a property
  • Property Get Name()
  • You won't use this much until you get into creating classes of your own
• Property Let - Declares how to assign a value to a property
  • Property Let Name()
  • You won't use this much until you get into creating classes of your own
• Property Set - Declares how to set a variable reference to an object
  • You won't use this much until you get into creating classes of your own
• Set - Assigns an object reference to a variable
  • Set X = form1.txtInputFromUser
  • Very useful for making code more readable or simply to cut down on how much typing you have to do!
• Let - Precedes assignment of a value to a variable
  • Let i = 3
  • It's optional, no one uses, so forget you ever saw it
• Type...End Type - Creates a user defined part type which consists of standard VB data types
  • type anytypename
  • one as string
  • two as integer
  • three as boolean
  • End Type
  • This is a really excellent way to keep several kinds of data under one variable name. Plus, you can PUT or GET a user-defined type with a single line of code.
• Const - Creates a variable whose value is fixed
  • const anyname
  • Basically, use this to give easy to remember names to values. For example, suppose you use the value 37.2 a lot in your code, then if you put CONST MyAge = 37.2 in your code you'll be able to insert the MyAge where the 37.2 should have gone. Easier to type and easier to read. Also, you can chane the value of the constant by changing only the declaration line of code, rather than searching out every place the value was used!
• Declare - Used to define a procedure that exists in another file
  • declare functionname (arg1 as integer, arg2 as string) as integer
  • ArrayName = Array (10, 20, 30)
  • Implements - Specifies a class to be implemented in a module
  • Friend - Allows procedure to be callable from modules outside the class
  • GetObject - Return a reference to an ActiveX component
  • CreateObject - Creates and returns a reference to an ActiveX object
  • GetAutoServerSettings - Returns information about the state of an ActiveX component's registration.
  • Enum - Declares a type for an enumeration
  • Event - Declares a user-defined event
  • TypeName - Returns the type of data in a variable
  • VarType - Returns the type of data in a variable
  • DefType - Sets the default data type of variables DefInt A-Z
  • IS - A variety of data type or status checking options IsArray, IsBindable, IsBroken, IsDate, IsDirty, IsEmpty, IsError, IsMissing, IsNull, IsNumber, IsObject, IsReady, IsRootFolder

Return To Top

 

Assignment Statements

Assignment statement sets a variable or property of an object to a specific value. In a layman term, Assignment statement takes the value on the right side of the assignment operator (=) and storing it in the element on the left. For example, x=123, it means the assignment statement stores the literal value 123 in the variable x. See more examples below.

‘ it sets the value of the MyNextNumber variable to the sum of the MyNumber variable plus 12.
MyNextNumber = MyNumber + 12

‘ you use the Set statement when you’re assigning values to object variables
Dim myRng As Range
Set myRng = Worksheets("Main").Range("A1:K1")
With myRng.Font
   .Bold = True
   .ColorIndex = 3
End With

’ you use Let statements to assign one record variable to another only when both variables are of the same user-defined type. Let statement is optional, and nobody uses it any longer, so you can omit it.
Dim yourPWD As String
Let yourPWD = InputBox(key in your password:"

*= Operator

\= Operator

-= Operator

The following contains a list of the Relational Comparison operators and the example 

conditions that determine whether result is True or False.

Operator                                     True if         False if
< (Less than)                               2 < 3          2 >= 4
<= (Less than or equal to)            2 <= 2        2 > 3
> (Greater than)                          2 > 1          2 <= 1
>= (Greater than or equal to)        2 >= 1        2 < 1
= (Equal to)                                 2 = 2          2 <> 2
<> (Not equal to)                         2 <> 3        2 = 3

Note: The = Operator is also used as an assignment operator

NOT Operator

XOR Operator