Variables and Data Types

A variable is a value that you "put" into the computer memory when necessary. The value is lost when the application closes. To proceed, you must communicate to the computer that you will need a portion of its memory to hold a certain value. When you communicate this, the computer reserves the necessary portion for you and makes it available when you need it.

Communicating your intention is also referred to as declaring a variable. Because there can be various values used while the application is running, the computer would need two pieces of information to hold a value: a name that can be used to identify the portion of memory and the amount of memory that will be necessary to store the value.

Every variable you intend to use in your application must have a name. This name allows you to identify the area of memory that would have been reserved for a variable. There are rules you must observe when naming your variables. The rules are those of Microsoft Visual Basic (and not Microsoft Access):

• The name must begin with a letter (such as a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, A, B, C, D, E, F, G, H, I, J, K, L, M, N,  O, P, Q, R, S, T, U, V, W, X, Y or Z) or an underscore _
• The name cannot contain a period (.) or a special character (such as ! @ # $ % ^ & * ( ) _ + - = [ ] { } ; ' : " , . / < > ? \ | ` or ~)
• The name must not contain an empty space
• The name must not exceed 255 characters. You should limit the name of a variable to 30 characters
• The name must be unique in the same scope

Besides, or on top of, these rules, you can add your own conventions that would make your code easier to understand.

1. Start Microsoft Access
2. From the resources that accompany these lessons, open the Exercise1 database
3. In the Navigation Pane, on the Ribbon, click Create
4. In the Forms section, click Form Design
5. In the Tools section of the Ribbon, click the View Code button
6. In the Object combo box, select Detail
   In the Procedure combo box, select Click if neessary
7. Press Tab and type the following:
    

   Private Sub Detail_Click() SomeColor = vbRed Detail.BackColor = SomeColor End Sub

8. Return to Microsoft Access and display the form in Form View
9. Click the form and notice that it appears red. If that does not happen, click the Options button, click the Enabled radio button and click OK
10. After using the form, switch it back to Design View and return to Microsoft Visual Basic

When writing your code, you can use any variable just by specifying its name. When you provide this name, the computer directly creates an area in memory for it. Microsoft Visual Basic allows you to directly use any name for a variable as you see fit. If you use various variables like that, this could result in some confusion in your code. As mentioned earlier, you can first declare a variable before using it.

To declare a variable, you use the Dim keyword followed by the name of the variable. Here is an example:

Private Sub Form_Load()
    Dim BackgroundColor
End Sub

Declaring a variable simply communicates the name of that variable. You can still use a mix of declared and not-declared variables. If you declare one variable and then start using another variable with a similar but somewhat different name, Microsoft Visual Basic would still consider that you are using two variables. This can create a great deal of confusion because you may be trying to use the same variable referred to twice. The solution to this possible confusion is to tell Microsoft Visual Basic that a variable cannot be used if it has not been primarily declared. To communicate this, on top of each file you use in the Code Editor, type

Option Explicit

This can also be done automatically for each file by checking the Require Variable Declaration in the Options dialog box.

1. On the main menu of Microsoft Visual Basic, click Tools -> Options...
2. Click the Editor property page. In the Code Settings section, put a check mark in the Require Variable Declaration check box
    
   
3. Click OK and return to Microsoft Access
4. Close Microsoft Visual Basic
5. From Microsoft Access, open the Technical Support database you created in Lesson 1
6. When asked whether you want to save the form, click No
7. To create a form, on the Ribbon, click Create 
8. In the Forms section, click Form Design
9. In the Tools section of the Ribbon, click the View Code button and notice that the top section of the file now displays Option Explicit

Every time the user enters a value in an application. That value is primarily considered as text. This means that, if you want to use such a value in an expression or a calculation that expects a specific value other than text, you must convert it appropriately. Fortunately, Microsoft Visual Basic provides an effective mechanism to convert a text value to one of the other values we will see next.

To convert text to another value, there is a function adapted for the purpose and that depends on the type of value you want to convert it to. We will mention each when necessary.

A data type tells the computer the kind of value you are going to use. There are different kinds of values for various purposes. Before assigning a data type to a variable, you should know how much space a data type will occupy in memory. Different variables or different data types use different amounts of space in memory. The amount of space used by a data type is measured in bytes.

To specify the data type that will be used for a variable, after typing Dim followed by the name of the variable, type the As keyword, followed by one of the data types we will review next. The formula used is:

Dim VariableName As DataType

This technique allows you to declare one variable on its line. In many assignments, you will need to declare more than one variable. To do this, you have two alternatives. You can declare each variable on its own line. This would be done as follows:

Dim Variable1 As DataType1
Dim Variable2 As DataType2
Dim Variable3 As DataType3

You can also declare more than one variable on the same line. To do this, use only one Dim keyword but separate each combination of a name and data type with a comma. This would be done as follows:

Dim Variable1 As DataType1, Variable2 As DataType2
Dim Variable3 As DataType3

Microsoft Visual Basic also provides special characters for some data types so that, instead of specifying a data type, you can use that character. We will indicate what character for what type.

1. In the Object combo box, select Detail
2. Press Tab

A variable is considered Boolean if it can hold only one of two values, either true or false, 0 or no 0, Yes or No. To declare such a variable, use the Boolean keyword. Here is an example:

Private Sub Form_Load()
    Dim IsMarried As Boolean
End Sub

After declaring the variable and when using it, you can specify its value as True or as False. To convert a value or an expression to Boolean, you can call the CBool() function.

1. Type Dim EmployeeIsFullTime As Boolean and press Enter
    

   Private Sub Detail_Click() Dim EmployeeIsFullTime As Boolean End Sub

A string is a character or a combination of characters that constitutes text of any kind and almost any length. To declare a string variable, fuse the String data type. Here is an example:

Private Sub Form_Load()
    Dim CountryName As String
End Sub

You can omit the As String expression. Instead, to indicate that you are declaring a String variable, you can end its name with the $ symbol. Here is an example:

Private Sub Form_Load()
    Dim CountryName$
End Sub

If you have a value that is not primarily text and you want to convert it to a string, use CStr() with the following syntax:

CStr(Value To Convert to String)

In the parentheses of the CStr(), enter the value that you want to convert to string.

• Type Dim EmployeeName As String and press Enter
   

  Private Sub Detail_Click() Dim EmployeeIsFullTime As Boolean Dim FullName As String End Sub

If you are planning to use a numeric value in your program, you have a choice from different kinds of numbers that Microsoft Access and Microsoft Visual Basic can recognize.  You can use the Byte data type for a variable that would hold a natural number that ranges from 0 to 255. You can declare it as follows:

Private Sub Form_Load()
    Dim StudentAge As Byte
End Sub

If the user enters a certain value in a control and you want to convert it to a small number, you can use CByte(). The formula to use would be:

Number = CByte(Value to Convert to Byte)

When using CByte(), passing that value between the parentheses.

• Type Dim EmploymentStatus As Byte and press Enter
   

  Private Sub Detail_Click() Dim EmployeeIsFullTime As Boolean Dim FullName As String Dim EmploymentStatus As Byte End Sub

An integer is a natural number. To declare a variable that would hold a number that ranges from -32768 to 32767, use the Integer data type. The integer type should always be used when counting things such as books in a library or students in a school; in this case you would not use decimal values. Here is an example of declaring an integer variable:

Private Sub Form_Load()
    Dim Tracks As Integer
End Sub

When declaring an integer variable, you can omit the As Integer expression and terminate the name of the variable with %. Here is an example:

Private Sub Form_Load()
    Dim Tracks%
End Sub

If you have a value that needs to be converted into a natural number, you can call CInt() using the following formula:

Number = CInt(Value to Convert)

Between the parentheses of CInt(), enter the value, text, or expression that needs to be converted.

• Type Dim YearHired As Integer and press Enter
   

  Private Sub Detail_Click() Dim EmployeeIsFullTime As Boolean Dim FullName As String Dim EmploymentStatus As Byte Dim YearHired As Integer End Sub

A long integer is a number that can be used for a variable involving greater numbers than integers. To declare a variable that would hold such a large number, use the Long data type. Here is an example:

Private Sub Form_Load()
    Dim Population As Long
End Sub

Alternatively, you can omit the As Long expression and end the variable name with the @ symbol to indicate that you are declaring a Long integer variable. Here is an example:

Private Sub Form_Load()
    Dim Population@
End Sub

To convert a value to a long integer, call CLng() using the following formula:

Number = CLng(Value to Convert)

To convert a value to long, enter it in the parentheses of CLng().

• Type Dim DaysOnJob As Long and press Enter
   

  Private Sub Detail_Click() Dim EmployeeIsFullTime As Boolean Dim FullName As String Dim EmploymentStatus As Byte Dim YearHired As Integer Dim DaysOnJob As Long End Sub

In computer programming, a decimal number is one that represents a fraction. Examples are 1.85 or 426.88. If you plan to use a variable that would that type of number but precision is not your main concern, declare it using the Single data type. Here is an example:

Private Sub Form_Load()
    Dim Distance As Single
End Sub

If you want, you can omit the As Single expression in the declaration. Instead, you can type ! at the end the name of the variable to still indicate that you are declaring a Single variable. Here is an example:

Private Sub Form_Load()
    Dim Distance!
End Sub

If you have a value that needs to be converted, use CSng() with the following formula:

Number = CSng(Value to Convert)

• Type Dim SickTime As Single and press Enter
   

  Private Sub Detail_Click() Dim EmployeeIsFullTime As Boolean Dim FullName As String Dim EmploymentStatus As Byte Dim YearHired As Integer Dim DaysOnJob As Long Dim SickTime As Single End Sub

If you want to use a decimal number that requires a good deal of precision, declare a variable using the Double data type.

In most circumstances, it is preferable to use Double instead of Single when declaring a variable that would hold a decimal number. Although the Double takes more memory spaces (computer memory is not expensive anymore(!)), it provides more precision.

Here is an example of declaring a Double variable:

Private Sub Form_Load()
    Dim Distance As Double
End Sub

Instead of the AS Double expression, you can omit it and end the name of the variable with the # character to indicate that you are declaring a Double variable. Here is an example:

Private Sub Form_Load()
    Dim Distance#
End Sub

To convert a value to double-precision, use CDbl() with the following formula:

Number = CDbl(Value to Convert)

In the parentheses of CDbl(), enter the value that needs to be converted.

• Type Dim WeeklyTime As Double and press Enter
   

  Private Sub Detail_Click() Dim EmployeeIsFullTime As Boolean Dim FullName As String Dim EmploymentStatus As Byte Dim YearHired As Integer Dim DaysOnJob As Long Dim SickTime As Single Dim WeeklyTime As Double End Sub

The Currency data type is used to deal with monetary values. Here is an example of declaring it:

Private Sub Form_Load()
    Dim StartingSalary As Currency
End Sub

If you want to convert a string to a monetary value, use CCur() with the following formula:

Number = CCur(Value to Convert)

To perform this conversion, enter the value in the parentheses of CCur().

• Type Dim HourlySalary As Currency and press Enter
   

  Private Sub Detail_Click() Dim EmployeeIsFullTime As Boolean Dim FullName As String Dim EmploymentStatus As Byte Dim YearHired As Integer Dim DaysOnJob As Long Dim SickTime As Single Dim WeeklyTime As Double Dim HourlySalary As Currency End Sub

In Visual Basic, a Date data type is used to specify a date or time value. Therefore, to declare either a date or a time variables, use the Date data type. Here are two examples:

Private Sub Form_Load()
    Dim DateOfBirth As Date
    Dim KickOffTime As Date
End Sub

If you have a string or an expression that is supposed to hold a date or a time value, to convert it, use CDate() based on the following formula:

Result = CDate(Value to Convert)

In the parentheses of CDate(), enter the value that needs to be converted.

• Type Dim DateHired As Date and press Enter
   

  Private Sub Detail_Click() Dim EmployeeIsFullTime As Boolean Dim FullName As String Dim EmploymentStatus As Byte Dim YearHired As Integer Dim DaysOnJob As Long Dim SickTime As Single Dim WeeklyTime As Double Dim HourlySalary As Currency Dim DateHired As Date End Sub

A Variant can be used to declare any kind of variable. You can use a variant when you can't make up your mind regarding a variable but, as a beginning programmer, you should avoid it.

Here is a table of various data types and the amount of memory space each one uses:

When naming your variables, besides the rules reviewed previously, you can start a variable's name with a one to three letters prefix that could identify the data type used. Here are a few suggestions:

• Type Dim MaritalStatus As Variant
   

  Private Sub Detail_Click() Dim EmployeeIsFullTime As Boolean Dim FullName As String Dim EmploymentStatus As Byte Dim YearHired As Integer Dim DaysOnJob As Long Dim SickTime As Single Dim WeeklyTime As Double Dim HourlySalary As Currency Dim DateHired As Date Dim MaritalStatus As Variant End Sub

In the above sections, we saw how to declare a variable from a built-in data type. Besides these types, Microsoft Access and Microsoft Visual Basic ship with various objects and classes. Sometimes you will need to refer to such objects in your code. In most cases, you will need to first declare a variable of the desired type before using it.

To declare a variable of an object, you should first make sure you know the type of object you want.

Every object you will use in your application is primarily of type Object. In many cases, you will be able to directly use the object in your application. In some other cases, you will first need to declare the variable and initialize it before using it. Also, in many cases, you can declare a variable and specify its particular type. In some cases, you may not know or may not need to specify the particular type of the object you want to use. In this case, when declaring the variable, you can specify its type as Object. When using the Object type to declare a one, the variable should be one of the existing VBA types of object and not one of the basic data types we saw earlier. This would be done as follows:

Dim objVariable As Object

After this declaration, you should then initialize the variable and specify the actual type it would be. To initialize a variable declared as a VBA object, use the Set operator that we will see later.

A Microsoft Access database is an object of type Application. In your code, to declare a variable of this type, you can type:

Dim app As Application

If you want to refer to such an object outside of Microsoft Access, you must qualify it with the Access object. For example, from an application such as Microsoft Word, to declare a variable that refers to a Microsoft Access database, the above declaration would be made as:

Dim app As Access.Application

Even in Microsoft Access, you can still use Access.Application.

A constant is a value that does not change (this definition is redundant because the word value already suggests something that doesn't change). There are two types of constants you will use in your programs: those supplied to you and those you define yourself.

To assist you with identifying colors, Microsoft Visual Basic uses various constants:

Visual Basic provides the vbCrLf constant used to interrupt a line of text and move to the next line.

PI is a mathematical constant whose value is approximately equal to 3.1415926535897932. It is highly used in operations that involve circles or geometric variants of a circle: cylinder, sphere, cone, etc.

A variable is said to be null when its value is invalid or doesn't bear any significant or recognizable value.

An expression is said to be false if the result of its comparison is 0. Otherwise, the expression is said to bear a true result.