vbaccess2010 lesson03
TRANSCRIPT
-
8/12/2019 vbaccess2010 Lesson03
1/18
Variables and Data Types
ariables
Introduction
Although you can create a complete database without writing code, in some cases, some taskscannot be performed automatically. For these tasks, you must temporarily use values that youcan change at will and dismiss when not needed anymore.
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 willneed a portion of its memory to hold a certain value. When you communicate this, the computerreserves the necessary portion for you and makes it available when you need it.
Communicating your intention is also referred to as declaringa variable. Because there can bevarious values used while the application is running, the computer would need two pieces ofinformation to hold a value: a name that can be used to identify the portion of memory and theamount of memory that will be necessary to store the value.
The Name of a Variable
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 youmust observe when naming your variables. The rules are those of Microsoft Visual Basic (and notMicrosoft 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 ~)
http://functionx.com/vbaccess2010/index.htm -
8/12/2019 vbaccess2010 Lesson03
2/18
The name must not contain an empty space
The name must not exceed 255 characters. You should limit the name of a variable to 30characters
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 codeeasier to understand.
Practical Learning: Introducing Variables
1. Start Microsoft Access
2. Open the Exercise1 database from the previous lesson (or from the resources thataccompany these lessons)
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 DetailIn the Procedure combo box, select Click if neessary
7. Press Tab and type the following:
8. Private Sub Detail_Click()
9.
SomeColor = vbRed10.11. Detail.BackColor = SomeColor
End Sub
12.Return to Microsoft Access and display the form in Form View
13.Click the form and notice that it appears red
14.After using the form, switch it back to Design View and return to Microsoft Visual Basic
Variable Declaration
-
8/12/2019 vbaccess2010 Lesson03
3/18
When writing your code, you can use any variable just by specifying its name. When you providethis name, the computer directly creates an area in memory for it. Microsoft Visual Basic allowsyou 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 Dimkeyword followed by the name of the variable. Here is anexample:
Private Sub Form_Load()Dim BackgroundColor
End Sub
Declaring a variable simply communicates the name of that variable. You can still use a mix ofdeclared and not-declared variables. If you declare one variable and then start using anothervariable with a similar but somewhat different name, Microsoft Visual Basic would still considerthat you are using two variables. This can create a great deal of confusion because you may betrying to use the same variable referred to twice. The solution to this possible confusion is to tellMicrosoft Visual Basic that a variable cannot be used if it has not been primarily declared. Tocommunicate 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 VariableDeclarationin the Options dialog box.
Practical Learning: Using a Variable
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
-
8/12/2019 vbaccess2010 Lesson03
4/18
3. Click OK and return to Microsoft Access
4. To create a form, on the Ribbon, click Create
5. In the Forms section, click Form Design
6. In the Tools section of the Ribbon, click the View Code button and notice thatthe top section of the file now displays Option Explicit
Value Conversion
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
-
8/12/2019 vbaccess2010 Lesson03
5/18
specific value other than text, you must convert it appropriately. Fortunately, Microsoft VisualBasic provides an effective mechanism to convert a text value to one of the other values we willsee 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.
Data Types
Introduction
A data type tells the computer the kind of value you are going to use. There are different kinds ofvalues for various purposes. Before assigning a data type to a variable, you should know howmuch space a data type will occupy in memory. Different variables or different data types usedifferent amounts of space in memory. The amount of space used by a data type is measuredinbytes.
To specify the data type that will be used for a variable, after typing Dimfollowed by the name ofthe variable, type the Askeyword, followed by one of the data types we will review next. Theformula used is:
Dim VariableNameAs DataType
This technique allows you to declare one variable on its line. In many assignments, you will needto declare more than one variable. To do this, you have two alternatives. You can declare eachvariable on its own line. This would be done as follows:
Dim Variable1As DataType1
Dim Variable2As DataType2Dim Variable3 As DataType3
You can also declare more than one variable on the same line. To do this, use onlyone Dimkeyword but separate each combination of a name and data type with a comma. Thiswould be done as follows:
Dim Variable1As DataType1, Variable2As DataType2Dim Variable3 As DataType3
Microsoft Visual Basic also provides special characters for some data types so that, instead of
http://functionx.com/vbaccess2010/topics/numsystem.htmhttp://functionx.com/vbaccess2010/topics/numsystem.htmhttp://functionx.com/vbaccess2010/topics/numsystem.htmhttp://functionx.com/vbaccess2010/topics/numsystem.htm -
8/12/2019 vbaccess2010 Lesson03
6/18
specifying a data type, you can use that character. We will indicate what character for what type.
Practical Learning: Introducing Data Types
1. In the Object combo box, select Detail
2. Press Tab
The Boolean Data Type
A variable is considered Boolean if it can hold only one of two values, either true or false, 0 or no0, 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
To actually use a Boolean variable, you can assign a value to it. To initialize a Boolean variable,
assign it a Trueor a Falsevalue. By default, if you declare a Boolean variable but do notinitialized it, it receives a value of False. A Boolean variable can also deal with numeric values.The Falsevalue is equivalent to 0. For example, instead of False, you can initialize a Booleanvariable with 0. Any other numeric value, whether positive or negative, corresponds to True:
Private Sub cmdBooleanVariable_Click()Dim isBoolean As Boolean
isBoolean = -792730End Sub
The number can be decimal or hexadecimal:
Sub cmdBooleanVariable_Click()Dim EmployeeIsMarried As Boolean
EmployeeIsMarried = &HFA26B5
End Sub
After declaring the variable and when using it, you can specify its value as True or as False. Toconvert a value or an expression to Boolean, you can call the CBool()function.
-
8/12/2019 vbaccess2010 Lesson03
7/18
As mentioned already, value is referred to as Boolean if it can be either true or false. As you mayimagine, the essence of a Boolean value is to check that a condition is true or false, valid orinvalid. In your code, you can stored a Boolean value in a variable. To declare such a variable, usethe Booleankeyword. Here is an example:
Sub cmdBooleanVariable_Click()Dim EmployeeIsMarried As Boolean
End Sub
Practical Learning: Declaring a Boolean Variable
Type Dim EmployeeIsFullTime As Booleanand press Enter
Private Sub Detail_Click()
Dim EmployeeIsFullTime As Boolean
End Sub
StringA string is a character or a combination of characters that constitutes text of any kind and almostany length. To declare a string variable, fuse the Stringdata 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 declaringaStringvariable, 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.
-
8/12/2019 vbaccess2010 Lesson03
8/18
Practical Learning: Declaring a String Variable
Type Dim EmployeeName As Stringand press Enter
Private Sub Detail_Click()
Dim EmployeeIsFullTime As Boolean Dim FullName As String
End Sub
Byte
If you are planning to use a numeric value in your program, you have a choice from differentkinds of numbers that Microsoft Access and Microsoft Visual Basic can recognize. You can usethe Bytedata 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 ByteEnd Sub
If the user enters a certain value in a control and you want to convert it to a small number, youcan use CByte(). The formula to use would be:
Number= CByte(Value to Convert to Byte)
When using CByte(), passing that value between the parentheses.
Practical Learning: Declaring a Byte Variable
Type Dim EmploymentStatus As Byteand press Enter
Private Sub Detail_Click()
Dim EmployeeIsFullTime As Boolean
Dim FullName As String
Dim EmploymentStatus As Byte
End Sub
Integer
-
8/12/2019 vbaccess2010 Lesson03
9/18
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 whencounting things such as books in a library or students in a school; in this case you would not usedecimal 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 Integerexpression and terminate thename 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 beconverted.
Practical Learning: Declaring an Integer Variable
Type Dim YearHired As Integerand press Enter
Private Sub Detail_Click()
Dim EmployeeIsFullTime As Boolean Dim FullName As String
Dim EmploymentStatus As Byte
Dim YearHired As Integer
End Sub
Long
-
8/12/2019 vbaccess2010 Lesson03
10/18
A long integer is a number that can be used for a variable involving greater numbers than integers. To declare a variable thatwould hold such a large number, use the Longdata type. Here is an example:
Private Sub Form_Load()Dim Population As Long
End Sub
Alternatively, you can omit the As Longexpression and end the variable name with the @symbol to indicate that you aredeclaring a Longinteger 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().
Practical Learning: Declaring a Long Integer Variable
Type Dim DaysOnJob As Longand 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
Single
In computer programming, a decimal number is one that represents a fraction. Examples are 1.85 or 426.88. If you plan to use avariable that would that type of number but precision is not your main concern, declare it using the Singledata type. Here is anexample:
Private Sub Form_Load()
-
8/12/2019 vbaccess2010 Lesson03
11/18
Dim Distance As SingleEnd Sub
If you want, you can omit the As Singleexpression in the declaration. Instead, you can type !at the end the name of the variableto still indicate that you are declaring a Singlevariable. 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)
Practical Learning: Declaring a Single-Precision Decimal Variable
Type Dim SickTime As Singleand 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
Double
If you want to use a decimal number that requires a good deal of precision, declare a variable using the Doubledata type. Here
is an example of declaring a Doublevariable:
Private Sub Form_Load()Dim Distance As Double
End Sub
Instead of the AS Doubleexpression, you can omit it and end the name of the variable with the#character to indicate that youare declaring a Doublevariable. Here is an example:
Private Sub Form_Load()Dim Distance#
-
8/12/2019 vbaccess2010 Lesson03
12/18
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.
Practical Learning: Declaring a Double-Precision Decimal Variable
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
Currency
The Currencydata 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().
Practical Learning: Declaring a Currency Variable
Type Dim HourlySalary As Currency and press Enter
-
8/12/2019 vbaccess2010 Lesson03
13/18
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
Date
In Visual Basic, a Datedata type is used to specify a date or time value. Therefore, to declare either a date or a time variables,use the Datedata type. Here are two examples:
Private Sub Form_Load()Dim DateOfBirth As DateDim 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 thefollowing formula:
Result= CDate(Value to Convert)
In the parentheses of CDate(), enter the value that needs to be converted.
Practical Learning: Declaring a Date Variable 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
-
8/12/2019 vbaccess2010 Lesson03
14/18
Dim SickTime As Single
Dim WeeklyTime As Double
Dim HourlySalary As Currency
Dim DateHired As Date
End Sub
Variant
A Variantcan be used to declare any kind of variable. You can use a variant when you can't make up your mind regarding avariable 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:
+
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
Single4-byte floating-pointnumber
3.402823e+38to 1.401298e45(negative values)
1.401298e45to3.402823e+38(positive values)
Double8-byte floating-pointnumber
1.79769313486231e+308to 4.94065645841247e324(negativevalues)
4.94065645841247e324to1.79769313486231e+308(positivevalues)
Currency8-byte number withfixed decimal point
922,337,203,685,477.5808 to922,337,203,685,477.5807
-
8/12/2019 vbaccess2010 Lesson03
15/18
String String of charactersZero to approximately two billioncharacters
Date8-byte date/timevalue
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 lettersprefix that could identify the data type used.
Practical Learning: Declaring a Variant Variable
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
ariables of Built-In Objects
Introduction
In the above sections, we saw how to declare a variable from a built-in data type. Besides these types, Microsoft Access andMicrosoft Visual Basic ship with various objects and classes. Sometimes you will need to refer to such objects in your code. Inmost 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.
-
8/12/2019 vbaccess2010 Lesson03
16/18
A Variable of Type Object
Every object you will use in your application is primarily of type Object. In many cases, you will be able to directly use the objectin your application. In some other cases, you will first need to declare the variable and initialize it before using it. Also, in manycases, 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. Whenusing the Objecttype to declare a variable, the variable should be one of the existing VBA types of object and not one ofthebasic data typeswe 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 variabledeclared as a VBA object, use theSetoperator that we will see later.
We mentioned (in the previous lesson) that the IntelliSense does not work on all objects. The Objectobject do not use theIntelliSense.
The Application Object
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 theAccessobject. For example, froman application such as Microsoft Word, to declare a variable that refers to a Microsoft Access database, the above declarationwould be made as:
Dim app As Access.Application
Even in Microsoft Access, you can still use Access.Application.
The Database Object
To support databases, Microsoft Access provides a data type named Database. This data type allows you to get a rereference tothe database you are using. To get a reference to a database, declare a variable of this type. Here is an example:
Dim curDatabase As Database
Constants
http://functionx.com/vbaccess2010/Lesson03.htm#DataTypeshttp://functionx.com/vbaccess2010/Lesson03.htm#DataTypeshttp://functionx.com/vbaccess2010/Lesson03.htm#DataTypeshttp://functionx.com/vbaccess2010/Lesson03.htm#Sethttp://functionx.com/vbaccess2010/Lesson03.htm#Sethttp://functionx.com/vbaccess2010/Lesson03.htm#Sethttp://functionx.com/vbaccess2010/Lesson03.htm#Sethttp://functionx.com/vbaccess2010/Lesson03.htm#DataTypes -
8/12/2019 vbaccess2010 Lesson03
17/18
Introduction
A constant is a value that does not change (this definition is redundant because the word value already suggests something thatdoesn't change). There are two types of constants you will use in your programs: those supplied to you and those you defineyourself.
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
The Carriage Return-Line Feed Constant
Visual Basic provides the vbCrLfconstant used to interrupt a line of text and move to the next line.
Built-in Constants: PI
PI is a mathematical constant whose value is approximately equal to 3.1415926535897932. It is highly used in operations thatinvolve circles or geometric variants of a circle: cylinder, sphere, cone, etc.
Built-in Logical Constants: NULL
A variable is said to be null when its value is invalid or doesn't bear any significant or recognizable value.
-
8/12/2019 vbaccess2010 Lesson03
18/18
Built-in Logical Constants: TRUE and FALSE
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.
Practical Learning: Ending the Lesson1. To close Microsoft Access, click File -> Exit
2. When asked whether you want to save, click No To All