Introduction to Variables

Variables

Introduction

Although you can create a complete database without writing code, in some cases, some tasks cannot be performed automatically. This means that you must temporarily use values that you can change at will and dismiss when not needed anymore. A variable is a value that you "put" into the computer memory when necessary and the value can be 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 (a-z or A-Z) or an underscore
• The name cannot contain a period (.) or a special character
• The name must not contain a 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 and, from the resources that accompany this site, open the Exercise1 database
2. In the Database window, click the Forms button
3. To create a form, double-click Create Form in Design View
4. On the Standard toolbar, click the Code button
5. In the Object combo box, select Detail
6. In the Procedure combo box, select Click
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
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 many confusions 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 to Visual Basic 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, 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 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 Department of Records and Statistics database you created in Lesson 1
6. When asked whether you want to save the form, click No
7. To create a form, in the Forms section of the Database window, double-click Create Form In Design View
8. On the Standard toolbar, click the 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 from that text. 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.