Home

Variables and Data Types
 

Variables
 

Introduction
 

To use some values in code, you must first create them. The computer memory is made of small storage areas used to hold the values of your application. When you use a value in your code, the computer puts it in a storage areas. When you need it, you let the computer know. The machine "picks it up", brings it to you, and then you can use it as you see fit.

In the world of computer programming, a variable is a value you ask the computer to temporarily store in its memory while the program is running.
 
 

Practical Learning: Using a Variable
  1. Start Microsoft Excel
  2. Save the file as Fundamentals1 in your Microsoft Excel VBA Exercises
  3. On the main menu, click Tools -> Visual Basic Editor
  4. To create a form, on the main menu, click Insert -> UserForm
  5. Right-click the form and click View Code
  6. To use a variable, implement the Click event as follows:
      
    Private Sub UserForm_Click()
    SomeColor = vbRed
    
    BackColor = SomeColor
End Sub
  7. Press F5 to test the form
  8. Click the form and notice that it appears red
     
  9. After using the form, close it and return to MSVB

Variable Declaration

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. Fortunately, to eliminate the possibility of confusion, you can first let Visual Basic know that you will be using a variable.

In order to reserve that storage area, you have to let the computer know. Letting the computer know is referred to as Declaring the variable. You declare a variable using the Dim keyword, like this:

Dim

Just like any of the controls we have used so far, a variable needs to have a name. There are rules you should follow when naming your variables. The name of a variable:

  • Must begin with a letter

  • After starting with a letter, can be made of letters, underscores, and digits in another order

  • Cannot have a period

  • Can have up to 255 characters.

  • Must be unique inside of the event (or procedure, function or module (we will learn what these things are)) it is used in.

To declare a variable, locate the event that you want to use the variable in. Follow the above syntax and the below suggestions and data types. Here is an example of declaring and using a variable:

Private Sub Form_Load()
    Dim BackgroundColor

    BackgroundColor = vbRed
    
    BackColor = 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 variable. 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.

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
     
    Options
  3. Click OK and return to Microsoft Excel
  4. Close Microsoft Excel without saving the document
  5. Start Microsoft Excel and, on the main menu, click Tools -> Visual Basic Editor
  6. To create a form, on the main menu, click Insert -> UserForm
  7. Right-click the form and click View Code
  8. Notice that the first line of code 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 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.

 

Data Types
 

Introduction

A data type tells the computer what kind of variable you are going to use in an operation or an expression. At this time, you should be familiar with the operators you can use in Microsoft Excel (and Visual Basic). There are different kinds of variables 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 amount 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

String

A string is a character or a combination of characters that constitute 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

Text-based controls such as the TextBox, the ListBox, or the ComboBox controls, have a property called Text. This property is of type String. Therefore, when using such a control, to  access the string that it holds, call its Text property. Imagine that you have a TextBox object named txtCountry, to access its string, you would use:

txtCountry.Text

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.

Practical Learning: Using Strings
  1. Design the form as follows:
     
    Control Name Caption
    Label First Name:
    TextBox txtFirstName  
    Label   Last Name:
    TextBox txtLastName  
    Label   Full Name:
    TextBox txtFullName  
  2. Double-click the top text box and implement its Change event as follows:
      
    Private Sub txtFirstName_Change()
    Dim FirstName As String
    Dim LastName As String
    Dim FullName As String
    
    FirstName = txtFirstName.Text
    LastName = txtLastName.Text
    FullName = FirstName & " " & LastName
    
    txtFullName.Text = FullName
End Sub
  3. In the Object combo box, select txtLastName and implement its Change event as follows:
      
    Private Sub txtLastName_Change()
    Dim FirstName As String
    Dim LastName As String
    Dim FullName As String
    
    FirstName = txtFirstName.Text
    LastName = txtLastName.Text
    FullName = FirstName & " " & LastName
    
    txtFullName.Text = FullName
End Sub
  4. To test the form, on the main menu of Visual Basic, click Run -> Run Sub/UserForm
  5. Click the top text box, type Julienne and press Tab
  6. In the other text box, start typing Pal and notice that the Full Name text box is changing
     
  7. Complete it with Palace and close the form

Byte

If you are planning to use a number in your program, you have a choice from different kinds of numbers that 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.

Integer

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

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.

Long

A long integer is a number that can be used for a field or 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

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().

Single

In computer programming, a decimal number is one that represents a fraction. Examples are 1.85 or 426.88. If you plane 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 have a value that needs to be converted, use CSng() with the following formula:

Number = CSng(Value to Convert)

 

Double

If you want to use a decimal number that requires a good deal of precision, declare a variable using the Double data type.
Author Note 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

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.

Currency

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().
 

Practical Learning: Using Currency Values
  1. To create another form, on the Standard toolbar of Microsoft Visual Basic, click the Insert UserForm button
  2. Design the form as follows:
     
    Control Name Caption Other Properties
    Label Hourly Salary:  
    TextBox txtHourlySalary   TextAlign: 3 - frmTextAlignRight
    Label   Weekly Hours:  
    TextBox txtWeeklyHours   TextAlign: 3 - frmTextAlignRight
    CommandButton cmdCalculate    
    Label   Weekly Salary:  
    TextBox txtWeeklySalary   TextAlign: 3 - frmTextAlignRight
  3. Double-click the Calculate button and implement its Click event as follows:
      
    Private Sub cmdCalculate_Click()
    Dim HourlySalary As Currency
    Dim WeeklyHours As Double
    Dim WeeklySalary As Currency
    
    HourlySalary = CCur(txtHourlySalary.Text)
    WeeklyHours = CDbl(txtWeeklyHours.Text)
    WeeklySalary = HourlySalary * WeeklyHours
    
    txtWeeklySalary.Text = CStr(WeeklySalary)
End Sub
  4. To test the form, on the main menu of Visual Basic, click Run -> Run Sub/UserForm
  5. Enter 15.48 in the Hourly Salary and 36.50 in the Weekly Hours text boxes and click Calculate
     
  6. Close the form

Date

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.

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.402823E38 to – 1.401298E – 45 (negative values)
1.401298E – 45 to 3.402823E38 (positive values)
Double 8-byte floating-point number – 1.79769313486231E308 to
– 4.94065645841247E – 324 (negative values)
4.94065645841247E – 324 to 1.79769313486231E308 (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. Here are a few suggestions:
Data Type Prefix Example
Boolean bln blnFound
Byte byt bytTracks
Date/Time dtm dteStartOfShift
Double dbl dblDistance
Error err errCantOpen
Integer int intNbrOfStudents
Long lng lngPopulation
Object obj objConnection
Single sng sngAge
String str strCountryName
Currency cur curHourlySalary
Variant var varFullName
 

Practical Learning: Closing Microsoft Excel
  1. Close Microsoft Excel
  2. When asked whether you want to save the changes, click Yes
Previous Copyright © 2004-2008 FunctionX, Inc. Next