The Types of Values of a Spreadsheet
VBA in Visual Basic
In the spreadsheet you will create, you use Microsoft Excel to create normal documents using the default settings of the application. To apply some advanced features to a spreadsheet, we we introduced in the previous lesson, you can use Microsoft Visual Basic that is automatically installed with Microsoft Excel.
To create a spreadsheet with functionality beyond the defaults, you write code. Microsoft Visual Basic is a programming environment that uses a computer language. In the previous lesson, we saw that that language is called Visual Basic for Applications (VBA). Although VBA is a language of its own, it is in a reality derived from the big Visual Basic computer language developed by Microsoft. In our lessons, we will learn how to use VBA in Microsoft Excel.
To take advantage of the functionalities of the Microsoft Visual Basic environment, there are many suggestions you can use or should follow. Because VBA is normal computer language, there are various rules you must follow for the language to work.
In our lessons, we will use the word VBA sometimes but most of the time, we use the expression "Visual Basic Language". When we use "Visual Basic language", we refer to a concept that is recognized by all child languages of Visual Basic, including VBScript and VBA. When we will use the word VBA, we refer to a concept that either is proper to VBA as a language and is not necessarily applied to some other flavors of Visual Basic, or to the way the Visual Basic language is used in Microsoft Excel. For example, the word String is used in all Visual Basic languages but the word Variant is not used in the Visual Basic language version used in the Microsoft Visual Basic (2008) programming environment.
In the previous lesson, we saw different ways you could launch Microsoft Visual Basic. One of the techniques consisted of creating a macro. That's the technique we will use in our early lessons.
To create a macro, on the Ribbon:
In each case, the Record Macro dialog box would come up:
From there, accept or enter a name for the macro. As an option, you can type a description of the macro in the bottom text box. Once you are ready, click OK. This would bring you to the document in Microsoft Excel where you can do what you want. After doing what is necessary, to end the creation of the macro, on the Ribbon:
When you create a macro, skeleton code is generated for you. To access the code generated for a macro, on the Ribbon:
Any of these actions would open the Macros dialog box that would display the list of macros in the current document:
To see the code of a macro, click its name and click Edit.
We will try to reduce as much as possible the code that will be written for you. Still, there are some lines and words we will keep or use but will ignore them for now. As we move on in our lessons, you will understand what everyone of those words means. The code generated in the above Practical Learning section was:
Sub Exercise1() ActiveCell.FormulaR1C1 = "=2" End Sub
The first line of code has the word Sub. We will introduce it later on. Exercise1 is the name of the macro we created. We will come back to names in a few sections in this lesson. We will also come back to the role of parentheses. The section of code ends with the End Sub line. We will come back to it when we study the procedures. For now, consider the Sub Exercise1() and End Sub lines as the minimum requirements we need as this time, that we don't need to be concerned with, but whose roles we can simply ignore at this time.
The most important line of our code, and the only line we are concerned with, is:
ActiveCell.FormulaR1C1 = "=2"
This line has three main sections: ActiveCell.FormulaR1C1, =, and "=2". For now, understand that the ActiveCell.FormulaR1C1 expression means "whatever box is selected in the document".
The = sign is called the assignment operator. As its name indicates, the assignment operator is used to assign something to another, to give a value to something, or more precisely to store something somewhere.
The thing on the right side of = is called a value. Therefore, "=2" is a value. Based on this, the expression ActiveCell.FormulaR1C1 = "=2" means "Assign the thing on the right side of = to the thing on the left side of =." Another way to put it is, "Store the value on the right side of the assignment operator to the selected box on the left side of the assignment operator." For now, until indicated otherwise, consider that that's what that line of code means.
After creating a macro, you can use it to see its result. This is also referred to as executing a macro or running a macro.
To execute a macro, on the Ribbon:
In the Macro dialog box, click the name of the macro and click Run.
Indentation is a technique that allows you to write easily readable code. It consists of visually showing the beginning and end of a section of code. Indentation consists of moving code to the right side.
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 can be automatically set using the Tab Width text box of the Editor property page in the Options dialog box. To change it, on the main menu of Microsoft Visual Basic, you can click Tools -> Options and click the Editor tab:
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 to its default of 4 characters.
A comment is a piece of text in code that would not be considered when reading your code. As such, a comment can be written any way you want.
In the Visual Basic language, the line that contains a comment can start with a single quote. Here is an example:
This line will not be considered as part of the code
Alternatively, you can start a comment with the Rem keyword. Anything on the right side of rem, Rem, or REM would not be read. Here is an example:
' This line will not be considered as part of the code Rem I can write anything I want on this line
Comments are very useful and you are strongly suggested to use them regularly.
The code that was generated in our Practical Learning section contains a few lines of comment:
Sub Exercise1() ' ' Exercise1 Macro ' ' ActiveCell.FormulaR1C1 = "=2" End Sub
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 area. 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.
When writing your code, you can use any variable just by specifying its name. When you provide this name, the computer directly reserves 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. To declare a variable, you start with the Dim word, like this:
A variable must have a name. The name is written on the right side of the Dim word. There are rules you should follow when naming your variables:
There are some words you should (must) not use to name your variables. Those words are reserved for the VBA internal use. Therefore, those words are called keywords. Some of them are:
As mentioned already, to declare a variable, type Dim followed by a name. Here is an example:
Sub Exercise() Dim Something 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
This can also be done automatically for each file by checking the Require Variable Declaration in the Options dialog box.
In a regular application, it is not unusual to want to use many variables. Once again, you should make it a habit to always declare a variable before using it. To declare a new variable after declaring a first one, you can simply go to the next line and use the Dim keyword to declare the new variable. Here is an example:
Sub Exercise() Dim Something Dim Whatever End Sub
In the same way, you can declare as many variables as you want. Instead of declaring each variable on its own line, you can declare more than one variable on the same line. To do this, use one Dim keyword and separate the names of variables with commas. Here are examples:
Sub Exercise() Dim Father, Mother Dim Son, Daughter, Nephew, Niece Dim GrandMa End Sub
Notice that each line uses its own Dim keyword and every new line of declaration(s) must have a Dim keyword.
We saw that when you declare a variable, the computer reserves a memory space for it but the space is kept empty. After declaring the value, you can store a value you want in the memory that was reserved for it.
To store a value in the memory reserved for a variable, you can assign a value to the variable. To do this, type the name of the variable, followed by the assignment operator which is =, followed by the value you want to store. Here is an example:
Sub Exercise() Dim Value Value = 9374 End Sub
As we will learn in the next few lessons, there are different types of values you will use in your document. Also as we will see, the value you (decide to) store must be in accordance with the type of memory that the computer had reserved for the variable.
After assigning a value to a variable, you can use that variable knowing the value it is currently holding. At any time and when necessary, you can change the value held by a variable. That's why it is called a variable (because its value can vary or change). To change the value held by a variable, access the variable again and assign it the new desired value.
A data type tells the computer what kind of variable you are going to use. Before using a variable, you should know how much space it will occupy in memory. Different variables use different amount of space in memory. The information that specifies how much space a variable needs is called a data type. 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
We mentioned earlier that you could use various variables if you judge them necessary. When declaring such variables, we saw that you could declare each on its own line. To specify the data type of a variable, use the same formula as above. Here is an example:
Sub Exercise() Dim FirstName As DataType Dim LastName As DataType End Sub
We also saw that you could declare many variables on the same line as long as you separate the names of the variables with commas. If you are specifying the data type of each, type the comma after each variable. Here are examples:
Sub Exercise() Dim FirstName As DataType, LastName As DataType Dim Address As DataType, City As DataType, State As DataType Dim Gender As DataType End Sub
This code appears as if there is only one type of data. In the next few sections, we will review various types of values that are available. To declare variables of different data types, you declare each on its own line as we saw earlier:
Sub Exercise() Dim FullName As DataType1 Dim DateHired As DataType2 Dim EmploymentStatus As DataType3 End Sub
You can also declare variables of different data types on the same line. To do this, use one Dim keyword and separate the declarations with commas. Here are.examples:
Sub Exercise() Dim FullName As DataType1, DateHired As DataType2 Dim EmploymentStatus As DataType3 End Sub
To make variable declaration a little faster and even convenient, you can replace the As DataType expression with a special character that represents the intended data type. Such a character is called a type character and 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. We will see what characters are available and when it can be applied.
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 keyword adapted for the purpose and that depends on the type of value you want to convert it to. We will mention each when necessary.
If you are planning to use a number in your program, you have a choice from different kinds of numbers that the Visual Basic language can recognize. The Visual Basic language recognizes as a natural number any number that doesn't include a fractional part. In the Visual Basic language, the number is made of digits only as a combination of 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9. No other character is allowed. In future lessons, we will learn that in Microsoft Excel, you can use a comma to separate the thousands, which would make the number easy to read. Microsoft Excel recognizes the comma separator, the Visual Basic language doesn't.
By default, when we refer to a natural number, we expect it in decimal format as a combination of digits. The Visual Basic language also supports the hexadecimal format. A hexadecimal number starts with &H followed by a combination of 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, a, b, c, d, e, f, A, B, C, D, E, and F. An example would be &H28E4AABF.
To declare a variable that would hold natural numbers that range from 0 to 255, use the Byte data type. Here is an example:
Sub Exercise() Dim StudentAge As Byte End Sub
There is no type character for the Byte data type.
After declaring the variable, you can assign it a small positive number. Here is an example:
Sub Exercise() Dim Value As Byte Value = 246 End Sub
You can also use the number in hexadecimal format as long as the number is less than 255.
If you give either a negative value or a value higher to 255, when you attempt to access it, you would receive an error:
To convert a value to a small number, you can use CByte(). The formula to use would be:
Number = CByte(Value to Convert to Byte)
When using CByte(), enter the value to convert in the parentheses.
To declare a variable that would hold a number that ranges from -32768 to 32767, use the Integer data type. Here is an example of declaring an integer variable:
Sub Exercise() Dim Tracks As Integer End Sub
Instead of using As Integer, you can use the % type character. Therefore, the above declaration could be done as follows:
Sub Exercise() Dim Tracks% End Sub
After declaring the variable, you can assign the desired value to it. If you assign a value lower than -32768 or higher than 32767, when you decide to use it, you would receive an error.
If you have a value that needs to be converted into a natural number, you can use 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.
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:
Sub Exercise() Dim Population As Long End Sub
The type character for the Long data type is @. The above variable could be declared as:
Sub Exercise() Dim Population@ End Sub
A Long variable can store a value between 2,147,483,648 and 2,147,483,647 (remember that the commas are used to make the numbers easy to read; do not be used them in your code). Therefore, after declaring a Long variable, you can assign it a number in that range.
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().
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:
Sub Exercise() Dim Distance As Single End Sub
The type character for the Single data type is !. Based on this, the above declaration could be done as:
Sub Exercise() Dim Distance! End Sub
A Single variable can hold a number between 1.401298e45 and 3.402823e38. for negative values or between 1.401298e45 and 3.402823e38 for positive values.
If you have a value that needs to be converted, use CSng() with the following formula:
Number = CSng(Value to Convert)
In the parentheses of CSng(), enter the value to be converted.
If you want to use a decimal number that requires a good deal of precision, declare a variable using the Double data type. Here is an example of declaring a Double variable:
Sub Exercise() Dim Distance As Double End Sub
Instead of As Double, the type character you can use is #:
Sub Exercise() Dim Distance# End Sub
A Double variable can hold a number between 1.79769313486231e308
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.
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, use the String data type. Here is an example:
Sub Exercise() Dim CountryName As String End Sub
The type character for the String data type is $. Therefore, the above declaration could be written as:
Sub Exercise() Dim CountryName$ End Sub
As mentioned already, after declaring a variable, you can assign a value to it. The value of a string variable must be included inside of double-quotes. Here is an example:
Sub Exercise() Dim CountryName As String CountryName = "Brésil" 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 formula:
CStr(Value To Convert to String)
In the parentheses of the CStr(), enter the value that you want to convert to string.
The Currency data type is used to deal with monetary values. Here is an example of declaring it:
Sub Exercise() Dim StartingSalary As Currency End Sub
Instead of using the As Currency expression, you can use @ as the type character to declare a currency variable. Here is an example of declaring it:
Sub Exercise() Dim StartingSalary@ End Sub
A variable declared with the Currency keyword can store a value between 922,337,203,685,477.5808 and 922,337,203,685,477.5807. Once again, keep in mind that the commas here are used only to make the number easy to read. Don't use the commas in a number in your code. Also, when assigning a value to a currency-based variable, do not use the currency symbol.
Here is an example of assigning a currency number to a variable:
Sub Exercise() Dim StartingSalary As Currency StartingSalary = 66500 End Sub
If you want to convert a value to currency, use CCur() with the following formula:
Number = CCur(Value to Convert)
To perform this conversion, enter the value in the parentheses of CCur().
In Visual Basic, a Date data type can be used to store a date value. Therefore, to declare either a date or a time variables, use the Date data type. Here is an example:
Sub Exercise() Dim DateOfBirth As Date End Sub
After declaring the variable, you can assign it a value. A date value must be included between two # signs. Here is an example:
Sub Exercise() Dim DateOfBirth As Date DateOfBirth = #10/8/1988# End Sub
There are various formats you can use for a date. We will deal with them in another lesson.
If you have a string or an expression that you want to convert to a date value, 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.
In Visual Basic, the Date data type can also be used to store a time value. Here is an example of declaring a variable that can hold a time value:
Sub Exercise() Dim ShiftTimeIn As Date End Sub
After declaring the variable, to assign a value to it, include the value between two # signs. The value follows different rules from a date.
To convert a value or an expression to time, use CDate().
So far, we declared variables knowing the types of values we wanted them to hold. VBA provides a universal (or vague) data type you can use for any type of value. The Variant data type is used to declare a variable whose type is not explicitly specified. This means that a Variant data type can hold any type of value you want.
Here are examples of Variant-declared variables that hold different types of values:
Sub Exercise() Dim FullName As Variant Dim EmploymentStatus As Variant Dim HourlySalary As Variant Dim DateHired As Variant FullName = "Patricia Katts" EmploymentStatus = 2 HourlySalary = 35.65 DateHired = #6/22/2004# End Sub
In the variables we declared in the last few sections, we specified a data type for each. You can declare a variable without giving its data type. Here are examples:
Sub Exercise() Dim FullName Dim EmploymentStatus Dim HourlySalary Dim DateHired End Sub
Of course, you can declare more than one variable on the same line.
To indicate how much space is needed for the variable, you must assign it a value. Here are examples:
Sub Exercise() Dim FullName Dim EmploymentStatus Dim HourlySalary Dim DateHired FullName = "Patricia Katts" EmploymentStatus = 2 HourlySalary = 35.65 DateHired = #6/22/2004# End Sub
Once the variable holds a value, you can use it as you see fit.
So far, we were declaring our variables between the Sub Name and the End Sub lines. Such a variable is referred to as a local variable. A local variable is confined to the area where it is declared. Here is an example:
Option Explicit Sub Exercise() Dim FirstName As String FirstName = "Patricia" End Sub
You cannot use such a variable outside of its Sub Name and the End Sub lines.
A global variable is a variable declared outside of the Sub Name and the End Sub lines. Such a variable is usually declared in the top section of the file. Here is an example:
Option Explicit Dim LastName As String Sub Exercise() End Sub
After declaring a global variable, you can access it in the other areas of the file. Here is an example:
Option Explicit Dim LastName As String Sub Exercise() Dim FirstName As String FirstName = "Patricia" LastName = "Katts" End Sub
Although we declared our global variable inside of the file where it was used, you can also declare a global variable in a separate module to be able to use it in another module.
When using a global variable, the Visual Basic language allows you to control its access level. The access level of a variable is a process of controlling how much access a section of code has on the variable.
A variable is referred to as private if it can be accessed only by code from within the same file (the same module) where it is used. To declare such a variable, instead of Dim, you use the Private keyword. Here is an example:
Option Explicit Private LastName As String Sub Exercise() Dim FirstName As String FirstName = "Patricia" LastName = "Katts" End Sub
Remember that a private variable can be accessed by any code in the same module. In the next lesson, we will learn how to create other sections of code.
A variable is referred to as public if it can be accessed by code either from within the same file (the same module) where it is declared or from code outside its module. To declare a public variable, instead of Dim, you use the Public keyword. Here is an example:
Option Explicit Private LastName As String Public FullName As String Sub Exercise() Dim FirstName As String FirstName = "Patricia" LastName = "Katts" FullName = FirstName & " " & LastName End Sub
As a reminder, a public variable is available to code inside and outside of its module. This means that you can create a module, declare a public variable in it, and access that variable in another file (module) where needed.
A private variable is available inside its module but not outside its module. If you declare a private variable in a module and try accessing it in another module, you would receive an error:
Option Explicit Private FullName As String
Option Explicit Private LastName As String Private FirstName As String Sub Exercise() FirstName = "Patricia" LastName = "Katts" FullName = FirstName & " " & LastName ActiveCell.FormulaR1C1 = FullName End Sub
This would produce:
|Previous||Copyright © 2008 Yevol||Next|