Home

Introduction to Variables and Types of Values

Variables

Introduction

Although you can create a complete database without writing code, in some cases, some tasks cannot be performed automatically. For these tasks, you must temporarily store some values in the computer.

A variable is a value that you "put" into the computer memory when necessary. In order to use a variable, you must let the computer know. This is referred to as declaring the variable.

The Name of a Variable

When you decide to declare a variable, the computer will need some basic information that includes at least a name to identify the variable. There are rules you must observe when naming a variable. The rules are those of Microsoft Visual Basic (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 section of code where it is being used (this is referred to as the scope)
  • The name should not be a reserved keyword

Besides, or on top of, these rules, you can add your own conventions that would make your code easier to understand. For example, avoid using the following keywords to name your variables:

AddressOf And Array As Binary Boolean ByRef Byte
ByVal Case CBool CByte CCur CDate CDbl CDec
CInt CLng Const CSng Currency Date Decimal Dim
Double Each Else Empty Eqv Error Event Exit
False FileCopy For Function Get GoSub GoTo If
Imp Implements Is Input Integer Len Like Line
Load Lock Long LongPtr LSet Mid MkDir Mod
Name Next Not Nothing Put Null On Option
Or Print Private Property Public RaiseEvent Randomize ReDim
Rem Reset Resume RmDir RSet Seek Select Set
Single Static Stop String Sub Then True Type
Variant Wend While With Write Xor    

Practical Learning: Introducing Variables

  1. Start Microsoft Access
  2. In the list of files, click Exercise1 from the previous lesson
  3. On the Ribbon, click Create
  4. In the Forms section, click Form Design
  5. Right-click the body of the form and click Build Event...
  6. In the Event Builder dialog box, click Code Builder

    Choose Builder

  7. Click OK

Declaring a Variable

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. The solution is to first declare a variable before using it.

To declare a variable, use the Dim keyword followed by the name of the variable.

Practical Learning: Declaring a Variable

  • As the caret is blinking, type Dim simple

    Declaring a Variable

Forcing a Variable Declaration

Declaring a variable simply communicates the name of that variable. You can use a mix of declared and not-declared variables. If you declare one variable and then start using another variable with a similar name, Microsoft Visual Basic would consider that you are using two different variables. This can create a great deal of confusion. The solution is to tell Microsoft Visual Basic that a variable cannot be used if it has not (yet) 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.

Practical Learning: Forcing a Variable Declaration

  1. On the main menu of Microsoft Visual Basic, click Tools and click Options
  2. In the Options dialog box, click Require Variable Declaration

    Require Variable Declaration

  3. Click OK
  4. To return to Microsoft Access, click the View Microsoft Access button Microsoft Access
  5. Close the form
  6. When asked whether you want to save the form, click No
  7. On the Ribbon, click Create
  8. In the Forms section, click Form Design
  9. In the Controls section of the Ribbon, click the Text Box Text Box
  10. Click an unoccupied area on the form
  11. In the Tools section of the Ribbon, click the View Code button View Code
  12. In the Object combo box, select Form
  13. As the caret is blinking, type Dim age

Initializing a Variable

Initializing a variable consists of storing an initial value in its reserved memory area. To assign a value to a variable, after declaring it, on another line, type the name of the variable, followed by = and the desired value for the variable. The formula is:

Dim variable-name

variable-name = desired-value

Practical Learning: Initializing a Variable

  • Press Enter twice and type age=12

Introduction to the Types of Values

The Data Type of a Variable

A data type tells the computer the kind of value you are going to use. There are different kinds of values for various goals. When declaring a variable, to specify the amount of memory its values must use, on the right side of the variable's name, type the As keyword followed by the data type. The formula to declare such a variable is:

Dim variable-name As data-type

The data-type must one of those recognized by Microsoft Visual Basic (not Microsoft Access).

Type Characters

To make variable declaration a little faster and convenient, you can replace the As DataType expression with a special character that represent the intended data type. Such a character is called a type character. It depends on the data type you intend to apply to a variable. When used, the type character must be the last character of the name of the variable.

Introduction to Characters and Strings

Introduction to Strings

A string is an empty space, a letter, a word, or a group of words. To let you declare a variable for a string, the Visual Basic language provides a type named String. Here is an example of declaring a text-based variable:

Private Sub cmdProperties_Click()
Dim fName As String
End Sub

After declaring a String variable, you can initialize it with an empty space, a letter, a symbol, a word, or a group of words. The value must be included between " and ". Here are two examples:

Private Sub cmdProperties_Click()
Dim fName As String
Dim lName As String
    
fName = "David"
lName = "Salomons"
    
txtFirstName = fName
txtLastName = lName
End Sub

The type character for the String data type is $. This means that you can replace the AS String expression with the $ symbol when declaring a string variable. This can be done as follows:

Private Sub cmdProperties_Click()
Dim fName$
Dim lName$
    
fName = "David"
lName = "Salomons"
    
txtFirstName = fName
txtLastName = lName
End Sub

If you want to include a double-quote inside a string, double it. Here is an example:

Private Sub cmdExercise_Click()
Dim sentence$

sentence$ = "Then she said, ""I don't love you no more."" and I left"
End Sub

ApplicationPractical Learning: Introducing Strings

  1. Replace the code you had written with the following:
    Private Sub Form_Load()
    Dim firstName
    
    firstName = "Ronald"
    
    Text0 = firstName
    End Sub
  2. Return to Microsoft Access
  3. To see the result, right-click the Form1 title bar and click Form View
  4. Right-click the title bar of the form and click Design View

Characters

A character is anything that can be displayed as a symbol. This means that a character is any one component of a string. Neither Microsoft Access nor the VBA provides a data type for a character but the VBA supports characters in other ways.

String Concatenation: &

String concatenation consists of adding one string to another. To perform this operation, separate the strings with the & operator. The formula to follow is:

value1 & value2

To display a concatenated expression, use the assignment operator on the field. To assign a concatenated expression to a variable, use the assignment operator the same way. To concatenate more than two expressions, you can use as many & operators between any two strings or expressions as necessary. After concatenating the expressions or values, you can assign the result to another variable or expression using the assignment operator.

ApplicationPractical Learning: Introducing Strings

  • Replace the code you had written with the following:
    Private Sub Form_Load()
    Dim firstName
    Dim lastName
    Dim fullName
        
    firstName = "Ronald "
    lastName = "Pottelson"
    fullName = firstName & lastName
    
    Text0 = fullName
    End Sub

Primary Topics on Declaring and Using Variables

Indentation

Indentation is a technique of aligning code so that it is easily to read. It consists of visually showing the beginning and end of a section of code. Indentation is done at various levels. For variable declaration, indentation consists of moving code to the right side so that the only line to the extreme left are those of the beginning and ending lines.

The easiest and most common way to apply indentation consists of pressing Tab before typing your code. By default, one indentation, done when pressing Tab, corresponds to 4 characters. This is controlled by the Editor property page of the Options dialog box. To change it, on the main menu of Microsoft Visual Basic, you would click Tools and click Options. Use the Tab Width text box of the Editor property page:

Options - Tab Width

If you don't want the pressing of Tab to be equivalent to 4 characters, change the value of the Tab Width text box to a reasonable value and click OK. Otherwise, it is (strongly) suggested that you keep it to its default of 4 characters.

Practical Learning: Indenting the Variables

  1. Indent the code you had written as follows:
    Private Sub Form_Load()
        Dim firstName
        Dim lastName
        Dim fullName
    
        firstName = "Francis "
        lastName = "Pottelson"
        fullName = firstName & lastName
    
        Text0 = fullName
    End Sub
  2. Return to Microsoft Access
  3. On the Ribbon, click File and click Open
  4. From the resources that accompany these lessons, locate and click the Variables file
  5. Click Open
  6. When asked whether you want to save, click No
  7. In the Navigation Pane, right-click Update and click Design View
  8. In the Tools section of the Ribbon, click the View Code button View Code
  9. In the Object combo box, select Form

Updating a Variable

After declaring a variable, you can change its value whenever you judge it necessary. That is, you can assign it any value of your choice.

Practical Learning: Updating a Variable

  1. Type the code as follows:
    Private Sub Form_Load()
        Dim value
    
        value = 245.9335
    
        txtValue1 = value
    
        value = "Jonathan"
    
        txtValue2 = value
    
        value = 468
    
        txtValue3 = value
    End Sub
    
  2. Return to Microsoft Access and switch the form to Form View to see the result

    Updating a Variable

  3. Save and close the form

Letting a Variable Have a Value

An option to initialize a variable is to precede it with a keyword named Let. Here are examples:

Private Sub cmdVariables_Click()
    Dim firstName
    Dim lastName
    Dim fullName

    Let firstName = "Francis "
    Let lastName = "Pottelson"
    Let fullName = firstName & lastName

    txtFullName = fullName
End Sub

You can also use the Let keyword when updating a variable with a new value. Here are examples:

Private Sub cmdVariables_Click()
    Dim firstName
    Dim lastName
    Dim fullName

    Let firstName = "Francis "
    Let lastName = "Pottelson"
    Let fullName = firstName & lastName

    txtFullName = fullName

    Let firstName = "Larry"
    Let lastName = "Charles"
    Let fullName = lastName & ", " & firstName

    txtEmployeeName = fullName
End Sub

Declaring Many Variables

A section of code can use many variables. You can declare each variable on its own line. Also, different variables can be declared with the same data type. Here is an example:

Private Sub cmdExercise_Click()
    Dim firstName As String
    Dim lastName As String
End Sub

Practical Learning: Declaring Many Variables

  1. In the Navigation Pane, right-click Variables and click Design View
  2. On the form, right-click the Variables button and click Build Event...
  3. In the Choose Builder dialog box, click Code Builder and click OK
  4. Implement the event as follows:
    Private Sub cmdVariables_Click()
        Dim fistName
        Dim lastName
        Dim age
    
        fistName = "Jimmy"
        lastName = "Jeffers"
        age = 36
    
        txtValue1 = fistName
        txtValue2 = lastName
        txtValue3 = age
    End Sub
  5. Return to Microsoft Accecss
  6. Click the Variables button

    Declaring Many Variables

  7. Return to Microsoft Visual Basic
  8. Instead of declaring each variable on its own line, you can declare two or more of the variables on the same line. Use only one Dim keyword and separate the names of the variables with commas.
    Change the code as follows:
    Private Sub cmdExercise_Click()
        Dim fistName, lastName, age
     
        fistName = "Jimmy"
        lastName = "Jeffers"
        age = 36
    
        txtValue1 = fistName
        txtValue2 = lastName
        txtValue3 = age
    End Sub
  9. When various variables use the same data type, instead of declaring each on its own line, you can combine their declaration on the same line. There are two techniques you can use.
    You can write the names of the variables on the same line, separated by commas, and ending the line with the common data type.
    Change the code as follows:
    Private Sub cmdVariables_Click()
        Dim fistName, lastName As String
        Dim age As Integer
    
        fistName = "Jimmy"
        lastName = "Jeffers"
        age = 36
    
        txtValue1 = fistName
        txtValue2 = lastName
        txtValue3 age
    End Sub
  10. On the same line, you can declare variables that use different data types. To do this, type the Dim keyword followed by the variable name and its data type with the As keyword. Then type a comma, followed by the other variable(s) and data type(s).
    Change the code as follows:
    Private Sub cmdExercise_Click()
        Dim fistName As String, lastName As String, age As Integer
    
        fistName = "Paul"
        astName = "Motto"
        age = 48
    
        txtValue1 = fistName
        txtValue2 = lastName
        txtValue3 = age
    End Sub
  11. Return to Microsoft Access
  12. Save and close the form

Integral Numeric Types

Introduction

An integer is a whole natural number, positive or negative. Both Microsoft Access and the VBA support various types of numbers. Each type depends on the amount of memory necessary to store the value.

A Byte

A byte is a small positive number between 0 and 255. To let you declare a variable for a small number, the VBA language provides a data type named Byte. By default, a Byte variable is initialized with 0. Otherwise, to initialize it, you can assign it a small number from 0 to 255. Here is an example:

Private Sub cmdExercise_Click()
    Dim age As Byte

    age = 88
End Sub

An Integer

An integer is a relatively semi-large number. It can be positive or negative. To let you declare a variable of integer type, the VBA provides a data type named Integer. Here is an example of declaring a variable of this type:

Private Sub cmdExercise_Click()
    Dim numberOfPages As Integer
End Sub

In Microsoft Access and the VBA, an Integer variable can hold a number between -32768 and 32767. After declaring an Integer variable, to initialize the variable with a regular natural number, simply assign it that number. Here is an example:

Private Sub cmdExercise_Click()
    Dim numberOfPages As Integer

    numberOfPages = 846
End Sub

A Long Integer

A long integer is a very large natural number between -2,147,483,648 and 2,147,483,647. To let you declare a variable that can hold a very large natural number, Microsoft Access provides a data type named Long. Here is an example:

Private Sub cmdExercise_Click()
    Dim population As Long
End Sub

Instead of the As Long expression, as an alternative, you can use the @ symbol. Here is an example:

Private Sub cmdExercise_Click()
    Dim population@
End Sub

Like all integers, a Long variable is initialized, by default, with 0. After declaring a Long variable, you can initialize with the necessary natural number. To indicate that the value must be treated as Long, type # on the right side of the number. Here is an example:

Private Sub cmdExercise_Click()
    Dim population@

    population@ = 9793759#
End Sub

Floating-Point Numbers

Introduction

A floating-point number is a number that is represents in two sections separated by a special character referred to as the decimal separator. In US English, the decimal separator is the period. On both sides of the separator are the digits. The portion is on the left side of the separator is the decimal number or decimal portion. The precision of the value, which is on the right side of the separator, is the number of digits necessary to express how close the number is to the actual value (in reality, the expression "floating-point" number is the scheme used to store the value in the computer memory).

A Floating-Point Number with Single-Precision

A floating-point number is said to have single precision if it ranges between -3.402823e38 and -1.401298e-45 if the number is negative, or 1.401298e-45 and 3.402823e38 if the number is positive.

To let you declare a variable for a number with single precision, Microsoft Access provides a data type named Single data type. Here is an example:

Private Sub cmdExercise_Click()
    Dim distance As Single
End Sub

Instead of the AS Single expression, you can use the ! symbol as the type character. Here is an example:

Private Sub cmdExercise_Click()
    Dim distance!
End Sub

The ! symbol can also be used in other scenarios in the Visual Basic language. Whenever you use it, make sure the word that comes to its right is not the name of a variable.

A Floating-Point Number with Double-Precision

A floating-point number with double-precision is a number between 1.79769313486231e308 and 4.94065645841247e324 if it is negative or between 1.79769313486231E308 and 4.94065645841247E324 if the number is positive. To let you declare a variable for such a number, the Visual Basic language provides the Double data type. Here is an example:

Private Sub cmdExercise_Click()
    Dim tempFactor As Double
End Sub

If you want, you can omit the AS Double expression but use the # symbol. This can be done as follows:

Private Sub cmdExercise_Click()
    Dim tempFactor#
End Sub

After declaring a Double variable, you can initialize it with the needed value. If the value contains only the decimal part, to indicate that the value being used must be treated with double precision, add # on the right side of the value. Here is an example:

Private Sub cmdExercise_Click()
    Dim tempFactor#

    tempFactor# = 482#
End Sub

Currency

The Currency data type is used to represent monetary values. Here is an example of declaring a variable for it:

Private Sub Form_Load()
    Dim startingSalary As Currency
End Sub

Variant

A Variant is a type that can be applied to any kind of variable. You can use a variant to declare a variable for anything, but you should avoid it.

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

+
Data Type Description Range
Byte 1-byte binary data 0 to 255
Integer 2-byte integer −32,768 to 32,767
Long 4-byte integer −2,147,483,648 to 2,147,483,647
Single 4-byte floating-point number -3.402823e+38 to −1.401298e−45 (negative values)
1.401298e−45 to 3.402823e+38 (positive values)
Double 8-byte floating-point number −1.79769313486231e+308 to
− 4.94065645841247e324 (negative values)
4.94065645841247e−324 to 1.79769313486231e+308 (positive values)
Currency 8-byte number with fixed decimal point −922,337,203,685,477.5808 to 922,337,203,685,477.5807
String String of characters Zero to approximately two billion characters
Date 8-byte date/time value January 1, 100 to December 31, 9999

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.

Techniques of Representing Numbers

Hexadecimal Numbers

As opposed to 10 digits of the decimal system, a hexadecimal number is a numeric system that uses 16 symbols that include the 10 digits of the decimal system plus the first 6 letters of the English alphabet. The letters can be in lowercase (a, b, c, d, e, or f) or uppercase (A, B, C, D, E, or F). This means that a hexadecimal number can be a digit, one of those letters, a combination of digits, a combination of those letters, or a combination of digits and those letters. A hexadecimal number can represent an integer or a floating-point number.

To indicate that a number uses the hexadecimal system, start the value with &h or &H. Here is an example:

Private Sub cmdExercise_Click()
    Dim NumberOfStudents As Integer

    NumberOfStudents = &H4A26EE
End Sub

Octal Numbers

An octal number is a technique used to represent a number using one or a combination of the first 8 digits: 0, 1, 2, 3, 4, 5, 6, and 7. To use such a value, start it with &o or &O (the letter O, not the digit 0). Here is an example:

Private Sub cmdExercise_Click()
    Dim NumberOfStudents As Integer

    NumberOfStudents = &O4260
End Sub

Constants

Introduction

A constant is a value that doesn't change. There are two types of constants you will use: those supplied to you and those you define yourself.

To create a constant, type the Const keyword. To formula to follow i:

Const variable-name "=" value

If the value is a string, make sure you provide it in double-quotes. If it is a number, simply assign it. Here are examples:

Private Sub cmdExercise_Click()
    Const FootItem = "Orange"
    Const nbr = 1251974
End Sub

After creating a constant, you can use it like any value. For example, you can assign it to the name of a control to display its value.

When creating a constant, if you want to indicate the type of its value, after its name, type As followed by the necessary data type. In the same way, instead of using the As data-type expression, you can use an appropriate type character. Here is an example:

Private Sub cmdExercise_Click()
    Const Temperature% = 52
End Sub

As mentioned earlier, the second category of constants are those that are built in the Visual Basic language. Because there are many of them and used in different circumstances, when we need one, we will introduce and then use it.

Practical Learning: Introducing Constants

  1. In the Navigation Pane, right-click Constants and click Design View
  2. In the Tools section of the Ribbon, click the View Code button View Code
  3. In the Object combo box, select Form
  4. Type code as follows:
    Private Sub Form_Load()
        Const FootItem = "Orange"
        Const Nbr = 1251974
        
        txtFoodItem = FootItem
        txtNumber = Nbr
    End Sub
  5. Return to Microsoft Access and switch the form to Form View:

    Introducing a Variant Variable

  6. Return to Microsoft Visual Basic and change the code as follows:
    Private Sub Form_Load()
        Const FootItem As String = "Orange"
        Const Nbr As Long = 1251974
        
        txtFoodItem = FootItem
        txtNumber = Nbr
    End Sub
  7. Save and close the form

Built-In Constants: The Carriage Return-Line Feed

If you are displaying a string but judge it too long, you can segment it in appropriate sections as you see fit. To do this, type vbCrLf at the end of the line, followed by the line continuation character.

Using a Tab

To get the effect of pressing the Tab key on the computer keyboard, you can use the vbTab operator of the Visual Basic language.

Constant Colors

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

Color Name Constant Value Color
Black vbBlack &h00  
Red vbRed &hFF  
Green vbGreen &hFF00  
Yellow vbYellow &hFFFF  
Blue vbBlue &hFF0000  
Magenta vbMagenta &hFF00FF  
Cyan vbCyan &hFFFF00  
White vbWhite &hFFFFFF  
 
 
 

Fundamental Visual Basic Operators

Introduction

An operation is an action performed on one at least one symbol and at least one value. The symbol used in an operation is called an operator. A value involved in an operation is called an operand.

A unary operator is an operator that performs its operation on only one operand. An operator is referred to as binary if it operates on two operands.

The Line Continuation Operator: _

If you plan to write a long piece of code, to make it easier to read, you may need to divide it in various lines. To do this, at the end of every line that must be continued, type the line continuation operator represented by a white space followed by an underscore and an empty space. Here is an example:

Private Sub cmdExercise_Click()
    Dim Message

    Message = _
            "Welcome to our website."
End Sub

The Colon Operator :

To make various statements easier to read, you usually write each on its own line. The Visual Basic language allows you to write as many statements as possible on the same line. The statements must be separated by colons. Here is an example:

Private Sub cmdDisplay_Click()
    Dim CustomerName
    Dim AccountNumber

    CustomerName = "James Reinder": AccountNumber = "928-3947-025"
End Sub

The Addition Operations

Introduction

The positive operator, represented by the + symbol, is used as a unary operator to indicate that a number is positive, but it is not required. The addition operation is used to add numeric values.

Practical Learning: Performing an Addition

  1. In the Navigation Pane, right-click Addition1 and click Design View
  2. In the Tools section of the Ribbon, click the View Code button View Code
  3. In the Object combo box, select Form
  4. Type code as follows:
    Private Sub Form_Load()
        Dim distance1, distance2
        Dim result
        
        distance1 = 9284.74
        distance2 = 305.82
        
        result = distance1 + distance2
        
        txtDistance1 = distance1
        txtDistance2 = distance2
        txtTotal = result
    End Sub
  5. Return to Microsoft Access and switch the form to Form View

    Performing an Addition

  6. Save and close the form

Incrementing a Variable

To increment a value, add 1 to it, and assign that expression to the variable.

Practical LearningPractical Learning: Incrementing a Variable

  1. In the Navigation Pane, right-click Addition2 and click Design View
  2. On the form, right-click the Increment button and click Build Event
  3. In the Choose Builder dialog box, double-click Code Builder and implement the event as follows:
    Private Sub cmdIncrement_Click()
        Dim age
        
        age = 84
        txtValue1 = age
        
        age = age + 1
        txtValue2 = age
    End Sub
  4. After doing this, the value or the variable is (permanently) modified and the variable would hold the new value.
    To see the results, return to Microsoft Access and switch the form to Form View
  5. Click the Incrment button:

    Incrementing a Variable

  6. Return to Microsoft Visual Basic
  7. As you may be aware already, you can add a constant value to a variable. For an example, change the code as follows:
    Private Sub cmdIncrement_Click()
        Dim distance
        Dim road
        
        distance = 938.74
        
        road = distance + 52.98
        
        txtValue1 = distance
        txtValue2 = road
    End SubEnd Sub
  8. To see the result, return to Microsoft Access and click the button:

    Compound Addition

  9. Return to Microsoft Visual Basic.
  10. If the value you are adding must be added to the variable itself, you don't have to declare a new variable. You can use the addition feature reviewed previously. For an example, change the code as follows:
    Private Sub cmdIncrement_Click()
        Dim distance
        
        distance = 938.74
        txtDistance = distance
        
        distance = distance + 52.98
        
        txtRoad = distance
    End Sub
  11. This code would produce the same result as above.
    Return to Microsoft Access
  12. Save and close the form

The Subtraction Operations

Introduction

The negative operator, represented with -, must be used to indicate that a number is negative. Examples are -12, -4.48, or -32706. The subtraction operation is used to subtract a value from another value.

A variable or an expression can also be represented as negative by prefixing it with a - sign. Examples are -Distance or -NbrOfPlayers. To initialize a variable with a negative value , use the assignment operator. Here is an example:

Private Sub Form_Load()
    Dim NumberOfTracks As Byte
    Dim Temperature As Integer
    
    NumberOfTracks = 16
    Temperature = -94
End Sub

Decrementing a Variable

Decrementing a variable consists of subtracting 1 from it. To do this, subtract 1 from the variable and assign the expression to the variable itself.

Practical LearningPractical Learning: Subtracting Some Values

  1. In the Navigation Pane, double-click Subtractions
  2. On the Ribbon, click Database Tools and, in the Macro section, click Visual Basic
  3. In the Project window, double-click Form_Subtractions
  4. Click the arrow of the Object combo box and select cmdDecrement
  5. Implement the event as follows:
    Private Sub cmdDecrement_Click()
        Dim debt
        
        debt = 2093840
        
        txtValue1 = debt
        
        debt = debt - 1
        
        txtValue2 = debt
    End Sub
  6. Return to Microsoft Access and click the Decrement button:

    Decrementing a Variable

  7. In the same way, you can decrement a variable by a bigger value by subtracting any number. To apply an example, change the code as follows:
    Private Sub cmdDecrement_Click()
        Dim debt
        
        debt = 2093840
        txtValue1 = debt
        
        debt = debt - 304928
        
        txtValue2 = debt
    End Sub
  8. Return to Microsoft Access and click the button:

    Compound Subtraction

  9. Return to Microsoft Access
  10. Save and close the form

The Multiplication Operations

Introduction

The multiplication allows adding one value to itself a certain number of times, set by a second value. The multiplication is performed using the * symbol.

Practical LearningPractical Learning: Multiplying

  1. In the Navigation Pane, double-click Multiplication
  2. On the Ribbon, click Database Tools and, in the Macro section, click Visual Basic
  3. In the Project window, double-click Form_Multiplication
  4. Click the arrow of the Object combo box and select cmdDecrement
  5. Implement the event as follows:
    Private Sub cmdMultiplication_Click()
        Dim nbr, value
        Dim result
        
        nbr = 27.95: value = 38.5
        
        result = nbr * value
        
        txtNumber = nbr
        txtResult = result
    End Sub
  6. Return to Microsoft Access and click the Multiplication button

    Compound Multiplication

  7. Return to Microsoft Visual Basic
  8. If you want to add a certain factor to the variable and change it, you can multiply it by a constant value and assign the expression to the variable. To see an example, change the code as follows:
    Private Sub cmdMultiplication_Click()
        Dim nbr
        
        nbr = 247.66
        
        txtNumber = nbr
        
        nbr = nbr * 14.25
        txtResult = nbr
    End Sub
  9. Return to Microsoft Access and click the Multiplication button

    Compound Multiplication

  10. Save and close the form

Exponentiation ^

Exponentiation is the ability to raise a number to the power of another number. This operation is performed using the ^ operator (Shift + 6). It uses the following formula:

yx

In Microsoft Visual Basic, this formula is written as:

y^x

and means the same thing. Either or both y and x can be constant values, variables, or expressions, but they must carry valid values that can be evaluated.

The multiplication allows adding one value to itself a certain number of times, set by a second value. The multiplication is performed using the * symbol.

Practical LearningPractical Learning: Power Up a Number

  1. In the Navigation Pane, right-click Exponentiation and click Design View
  2. On the form, right-click the button and in click Build Event...
  3. In the Choose Builder dialog box, double-click Code Builder
  4. Implement the event as follows:
    Private Sub cmdPowerUp_Click()
        Dim nbr, value
        Dim result
        
        nbr = 27.95: value = 38.5
        result = nbr ^ value
        
        txtNumber = nbr
        txtResult = result
    End Sub
  5. Return to Microsoft Access and switch the form to Form View
  6. Click the button

    Powering Up a Number

  7. Save and close the form

The Division Operations

Introduction

The division operation consists of cutting a number in pieces or fractions. When performing the division, be aware of its main rule to never divide by zero (0). The Visual Basic language supports two types of divisions.

Integer Division \

If you want the result of the operation to be an integer, use the backlash operator "\". The formula to follow is:

value1 \ value2

This operation can be performed on two types of valid numbers, with or without decimal parts. After the operation, the result would be a natural number.

Practical LearningPractical Learning: Performing Divisions

  1. In the Navigation Pane, right-click Divisions and click Design View
  2. On the form, right-click the button and in click Build Event...
  3. In the Choose Builder dialog box, double-click Code Builder
  4. Implement the event as follows:
    Private Sub cmdDivide_Click()
        Dim numerator, denominator, result
        
        numerator = txtNumerator
        denominator = txtDenominator
        
        result = numerator \ denominator
        
        txtResult = result
    End Sub
  5. Return to Microsoft Access and switch the form to Form View
  6. Click Numerator and type a number such as 1368.92
  7. Click Denominator and type a number such as 442.86

    Dividing a Number

  8. Click the button

    Dividing a Number

  9. Close the form
  10. When asked whether you want to save, click No
  11. In the Navigation Pane, right-click Compound Division and click Design View
  12. On the form, right-click the button and click Build Event
  13. In the Choose Builder dialog box, double-click Code Builder
  14. If you want to change the value of the variable by dividing it to a certain number, perform the division and assign the expression to itself. To apply an example, implement the event as follows::
    Private Sub cmdCompound_Click()
        Dim nbr, factor
        
        nbr = txtNumber
        factor = txtFactor
        
        nbr = nbr \ factor
        
        txtResult = nbr
    End Sub
  15. Return to Microsoft Access and switch the form to Form View
  16. Click Number and type a number such as 9203.428
  17. Click Factor and type a number such as 447.13

    Compound Division

  18. Click the button

    Compound Division

  19. Close the form
  20. When asked whether you want to save, click No

Decimal Division /

The second type of division results in a decimal number. It is performed with the forward slash "/". The formula to follow is:

value1 / value2

After the operation is performed, the result is a decimal number.

Practical LearningPractical Learning: Performing Decimal Divisions

  1. In the Navigation Pane, right-click Divisions and click Design View
  2. On the form, right-click the button and in click Build Event...
  3. In the Choose Builder dialog box, double-click Code Builder
  4. Implement the event as follows:
    Private Sub cmdDivide_Click()
        Dim numerator, denominator, result
        
        numerator = txtNumerator
        denominator = txtDenominator
        
        result = numerator / denominator
        
        txtResult = result
    End Sub
  5. Return to Microsoft Access and switch the form to Form View
  6. Click Numerator and type a number such as 1368.92
  7. Click Denominator and type a number such as 442.86

    Dividing a Number

  8. Click the button

    Dividing a Number

  9. Close the form
  10. When asked whether you want to save, click No
  11. In the Navigation Pane, right-click Compound Division and click Design View
  12. On the form, right-click the button and click Build Event
  13. In the Choose Builder dialog box, double-click Code Builder
  14. Implement the event as follows::
    Private Sub cmdCompound_Click()
        Dim nbr, factor
        
        nbr = txtNumber
        factor = txtFactor
        
        nbr = nbr / factor
        
        txtResult = nbr
    End Sub
  15. Return to Microsoft Access and switch the form to Form View
  16. Click Number and type a number such as 9203.428
  17. Click Factor and type a number such as 447.13

    Compound Division

  18. Click the button

    Compound Division

  19. Save and close the form

The Remainder

The remainder operation is used to get the value remaining after a division has been performed. The result is a natural number. The remainder operation is performed using the Mod operator. The formula to follow is:

value1 Mod value2

The result of the operation can be used as you see fit or you can display it in a control or be involved in another operation or expression. Here is an example:

Private Sub cmdMultiplication_Click()
    Dim nbr, remainder
    
    nbr = 18
    remainder = nbr Mod 11
    
    txtNumber = nbr
    
    txtRemainder = remainder
End Sub

As seen with the other arithmetic operators, you can find the remainder of a variable and assign the result to the variable itself.

Practical LearningPractical Learning: Finding the Remainder

  1. In the Navigation Pane, right-click Remainder and click Design View
  2. On the form, right-click the Calculate button and in click Build Event...
  3. In the Choose Builder dialog box, double-click Code Builder
  4. Implement the event as follows:
    Private Sub cmdCalculate_Click()
        Dim number, factor, remainder
        
        number = txtNumber
        factor = txtFactor
        
        remainder = number Mod factor
        
        txtRemainder = remainder
    End Sub
  5. Return to Microsoft Access and switch the form to Form View
  6. Click Numerator and type a number such as 1258
  7. Click Denominator and type a number such as 846

    Finding the Remainder of a Division

  8. Click the button

    Finding the Remainder of a Division

  9. Close the form
  10. When asked whether you want to save, click No

Bit Manipulations

Introduction

From our introduction to variables, you may remember that the computer stores its data in memory using small locations that look like boxes and each box contains a bit of information. Because a bit can be represented only either as 1 or 0, we can say that each box contains 1 or 0. Bit manipulation consists of changing the value (1 or 0, or 0 or 1) in a box. As we will see in the next few operations, it is not just about changing a value. It can involve reversing a value or kind of "moving" a box from its current position to the next position.

The operations on bits are performed on 1s and 0s only. This means that any number in decimal or hexadecimal format involved in a bit operation must be converted to binary first.

You will almost never perform some of the operations we are going to review. You will hardly perform some other operations. There is only one operation you will perform sometimes: the OR operation.

"Reversing" a Bit

Remember that, at any time, a box (or chunk) in memory contains either 1 or 0:

Bit 0 Bit 1
0 1

Bit reversal consists of reversing the value of a bit. If the box contains 1, you can reverse it to 0. If it contains 0, you can reverse it to 1. To support this operation, the Visual Basic language provides the Not Operator.

As an example, consider the number 286. The decimal number 286 converted to binary is 100011110. You can reverse each bit as follows:

286 1 0 0 0 1 1 1 1 0
Not 286 0 1 1 1 0 0 0 0 1

Bitwise Conjunction

Bitwise conjunction consists of adding the content of one box (a bit) to the content of another box (a bit). To support the bitwise conjunction operation, the Visual Basic language provides the And operator.

To perform the bit addition on two numbers, remember that they must be converted to binary first. Then:

  • If a bit with value 0 is added to a bit with value 0, the result is 0
     
    Bit0 0
    Bit1 0
    Bit0 And Bit1 0
  • If a bit with value 1 is added to a bit with value 0, the result is 0
     
    Bit0 1
    Bit1 0
    Bit0 And Bit1 0
  • If a bit with value 0 is added to a bit with value 1, the result is 0
     
    Bit0 0
    Bit1 1
    Bit0 And Bit1 0
  • If a bit with value 1 is added to a bit with value 1, the result is 1
     
    Bit0 1
    Bit1 1
    Bit0 And Bit1 1

As an example, consider the number 286 bit-added to 475. The decimal number 286 converted to binary is 100011110. The decimal number 4075 converted to binary is 111111101011. Based on the above 4 points, we can add these two numbers as follows:

286 0 0 0 1 0 0 0 1 1 1 1 0
4075 1 1 1 1 1 1 1 0 1 0 1 1
286 And 4075 0 0 0 1 0 0 0 0 1 0 1 0

Therefore, 286 And 4075 produces 100001010 which is equivalent to:

  Bit8 Bit7 Bit6 Bit5 Bit4 Bit3 Bit2 Bit1 Bit0
  256 128 64 32 16 8 4 2 1
286 And 4075 1 0 0 0 0 1 0 1 0
  256 0 0 0 0 8 0 2 0

This means that 286 And 4075 = 256 + 16 + 2 = 266

This can also be programmatically calculated as follows:

Sub Exercise()
    Dim Number1 As Integer
    Dim Number2 As Integer
    Dim Result As Integer
        
    Number1 = 286
    Number2 = 4075
    Result = Number1 And Number2
End Sub

Bitwise Disjunction

Bitwise disjunction consists of disjoining one a bit from another bit. To support this operation, the Visual Basic language provides the Or operator.

To perform a bitwise conjunction on two numbers, remember that they must be converted to binary first. Then:

  • If a bit with value 0 is added to a bit with value 0, the result is 0
     
    Bit0 0
    Bit1 0
    Bit0 Or Bit1 0
  • If a bit with value 1 is added to a bit with value 0, the result is 1
     
    Bit0 1
    Bit1 0
    Bit0 Or Bit1 1
  • If a bit with value 0 is added to a bit with value 1, the result is 1
     
    Bit0 0
    Bit1 1
    Bit0 Or Bit1 1
  • If a bit with value 1 is added to a bit with value 1, the result is 1
     
    Bit0 1
    Bit1 1
    Bit0 Or Bit1 1

As an example, consider the number 305 bit-disjoined to 2853. The decimal number 305 converted to binary is 100110001. The decimal number 2853 converted to binary is 101100100101. Based on the above 4 points, we can disjoin these two numbers as follows:

305 0 0 0 1 0 0 1 1 0 0 0 1
2853 1 0 1 1 0 0 1 0 0 1 0 1
305 Or 2853 1 0 1 1 0 0 1 1 0 1 0 1

Therefore, 305 Or 2853 produces 101100110101 which is equivalent to:

  Bit11 Bit10 Bit9 Bit8 Bit7 Bit6 Bit5 Bit4 Bit3 Bit2 Bit1 Bit0
  2048 1024 512 256 128 64 32 16 8 4 2 1
305 Or 2853 1 0 1 1 0 0 1 1 0 1 0 1
  2048 0 512 256 0 0 32 16 0 4 0 1

This means that 286 And 4075 = 2048 + 512 + 256 + 32 + 16 + 4 + 1 = 2869

This can also be programmatically calculated as follows:

Sub Exercise()
    Dim Number1 As Integer
    Dim Number2 As Integer
    Dim Result As Integer
        
       
    Number1 = 286
    Number2 = 4075
    Result = Number1 Or Number2
End Sub

Bitwise Exclusion

Bitwise exclusion consists of adding two bits with the following rules. To support bitwise exclusion, the Visual Basic language provides an operator named Xor:

  • If both bits have the same value, the result is 0
     
    Bit0 0 1
    Bit1 0 1
    Bit0 Xor Bit1 0 0
  • If both bits are different, the result is 1
     
    Bit0 0 1
    Bit1 1 0
    Bit0 Xor Bit1 1 1

As an example, consider the number 618 bit-excluded from 2548. The decimal number 618 converted to binary is 1001101010. The decimal number 2548 converted to binary is 100111110100. Based on the above 2 points, we can bit-exclude these two numbers as follows:

618 0 0 1 0 0 1 1 0 1 0 1 0
2548 1 0 0 1 1 1 1 1 0 1 0 0
618 Xor 2548 1 0 1 1 1 0 0 1 1 1 1 0

Therefore, 305 Or 2853 produces 101110011110 which is equivalent to:

  Bit11 Bit10 Bit9 Bit8 Bit7 Bit6 Bit5 Bit4 Bit3 Bit2 Bit1 Bit0
  2048 1024 512 256 128 64 32 16 8 4 2 1
618 Xor 2548 1 0 1 1 1 0 0 1 1 1 1 0
  2048 0 512 256 128 0 0 16 8 4 2 0

This means that 286 And 4075 = 2048 + 512 + 256 + 128 + 16 + 8 + 4 + 2 = 2974

This can also be programmatically calculated as follows:

Sub Exercise()
    Dim Number1 As Integer
    Dim Number2 As Integer
    Dim Result As Integer
        
       
    Number1 = 286
    Number2 = 4075
    Result = Number1 Xor Number2
End Sub

The Scope and Lifetime of a Variable

Introduction

The Scope and/or lifetime of a variable is the area of code where the variable can be accessed. To make this possible, a variable can be declared in an event, in the module of a for or report, or in an independent module.

Local Variables

A variable is referred to as local if it is declared in the body of an event. Those are the types of variables we have declared se far. A local variable can be accessed only from code in the same event. Here is an example:

Private Sub cmdExercise_Click()
    Dim NumberOfPages%
End Sub

A variable is said to be global if it can be accessed from many events. To have a global variable, you must declare it outside any event. There are two categories of global variables: module-based and application-based.

Module-Based Variables

A variable is said to be module based, or a variable has a module scope, if it can be accessed from any section of the same module. Such a variable cannot be accessed outside its module.

Private Code

A section of code is said to be private if it can be accessed only from its module. For example, a variable can be confined to its module so that code outside the module cannot access that variable.

To get a private variable, declare it in a module but outside any event. To indicate that the variable must be treated as private, instead of the Dim keyword, the Visual Basic language provides a keyword named Private.

Public Code

A section of code is public if it can be accessed from any code segment in the application. This means that code made public in a module can be accessed by code from any module of the same application. This can be applied to a variable that needs to be accessed from any part of the database.

To let you get a public variable, the Visual Basic language provides a keyword named Public. Use it instead of the Dim keyword to declare a global variable in a module.

Application-Based Variables

A variable is application based if it can be accessed by code from any part of the application, that is, from any module of the same application. To have an application-based variable, use the Public keyword to declare it in a module of your choice.

Practical Learning: Ending the Lesson

  • Close Microsoft Access
 
 
   
 

Previous Copyright © 2002-2016, FunctionX, Inc. Next