In Lesson, we will learn…
Types of variable
Prompting for user input
Arrays
Interacting With Users
Types of variable
Prompting for user input
Arrays
Interacting With Users
As Excel programming focuses on a spreadsheet application, it’s only natural that I begin the subject of variables by asking you to think about the following: what types of values can be entered into a spreadsheet cell and how you might use them? You know that you can enter numbers and text in any spreadsheet cell in Excel.
Also, you may or may not know that the format of a spreadsheet cell can be changed to one of several possibilities. For example, a number can be formatted such that the value is Bold in style or red in color. It can also be formatted as currency or as a percentage. Other than that text entered can be automatically converted to a date or time. And etc...
1) Declaring Variables
Declaring a variable is to tell the computer to reserve space in memory for later use. As a result your VBA code run much more efficiently. To declare a variable use a Dim (short for Dimension) statement.
Dim myVar As Integer
The name of the variable is myVar. As you can see myVar begin with an alphabetic character and cannot exceed 255 characters or contain any spaces. Do not use punctuation marks or other unusual characters in the variable name, as many of them are not allowed.
The use of underscore character is allowed and works well for separating multiple words contained within a single variable name (for example, Car_Type).
Do not use VBA reserved keywords and don’t repeat variable names within the same scope (discussed later in this Part). As a convention, the variable name should be descriptive of the value it will hold. For example, if you use a variable to hold a type of car, then a good name for that variable might be carType or CarType. My preference is to begin a variable name with a lowercase letter and then capitalize the first letter of any subsequent words appearing in the name. Try to keep the length to a minimum so that it is more readable.
It is advisable to follow the naming conventions as they don’t contradict rules established by VBA. To ensure that variables are always properly declared, use the Option Explicit statement as the first statement in a module before you type any procedure code. This is shown in Figure 4.1.
Also, you may or may not know that the format of a spreadsheet cell can be changed to one of several possibilities. For example, a number can be formatted such that the value is Bold in style or red in color. It can also be formatted as currency or as a percentage. Other than that text entered can be automatically converted to a date or time. And etc...
1) Declaring Variables
Declaring a variable is to tell the computer to reserve space in memory for later use. As a result your VBA code run much more efficiently. To declare a variable use a Dim (short for Dimension) statement.
Dim myVar As Integer
The name of the variable is myVar. As you can see myVar begin with an alphabetic character and cannot exceed 255 characters or contain any spaces. Do not use punctuation marks or other unusual characters in the variable name, as many of them are not allowed.
The use of underscore character is allowed and works well for separating multiple words contained within a single variable name (for example, Car_Type).
Do not use VBA reserved keywords and don’t repeat variable names within the same scope (discussed later in this Part). As a convention, the variable name should be descriptive of the value it will hold. For example, if you use a variable to hold a type of car, then a good name for that variable might be carType or CarType. My preference is to begin a variable name with a lowercase letter and then capitalize the first letter of any subsequent words appearing in the name. Try to keep the length to a minimum so that it is more readable.
It is advisable to follow the naming conventions as they don’t contradict rules established by VBA. To ensure that variables are always properly declared, use the Option Explicit statement as the first statement in a module before you type any procedure code. This is shown in Figure 4.1.
Fig 4.1 - Click for larger image
2) Variable Scope
Fig 4.2 - Click for larger image
Fig 4.3 - Click for larger image
Fig 4.4 - Click for larger image
e) Boolean Data Type
Fig 4.6 - Click for larger image
Fig 4.7 - Click for larger image
2) Variable Scope
A variable scope which can be declared as Private or Public variables, refers to the time when a variable is visible or available to the program. When a variable is in its scope, it can be accessed and/or manipulated. When a variable is out of scope, it is unacessable—essentially invisible to the program.
A variable declared within the code block of a procedure (such as the Activate() event procedure of the Worsheet), is a procedural level variable. Procedural level variables are only available while program execution occurs within the procedure that the variable was declared. In Figure 4.2, the variable myVar4 is only visible to the program while the code in the Activate() event procedure of the worksheet executes.
A variable declared within the code block of a procedure (such as the Activate() event procedure of the Worsheet), is a procedural level variable. Procedural level variables are only available while program execution occurs within the procedure that the variable was declared. In Figure 4.2, the variable myVar4 is only visible to the program while the code in the Activate() event procedure of the worksheet executes.
Fig 4.2 - Click for larger image What happen is that when program execute i.e. triggered by the Activate() event, the variable myVar4 is dimensioned in memory. Program execution proceeds through the event procedure until reaching the End Sub line of code, after which the variable is released from memory and is no longer available. Each time the procedure executes, the variable is created and destroyed. Thus, myVar4 will not retain its value between calls to the procedure.
In this procedure the variable myVar4 will increment its value by one with each call to the procedure.
The two variables are declared with the Dim, Private, and Public keywords (see Figure 4.2). The Private and Public keywords are only allowed for variable declaration in the general declarations section of a module. The variable myVar5 are visible to any procedure within this module whereas myVar4 is only accessable from the Worksheet Activate() procedure. Variables declared in the general declarations section of a module (object or standard) with the Public keyword are commonly referred to as global.
Therefore, the keywords Dim and Private have the same function in variable declarations when used in the general declarations section of any module; the Public keyword can be used to declare global variables in a standard or object module.
In this procedure the variable myVar4 will increment its value by one with each call to the procedure.
The two variables are declared with the Dim, Private, and Public keywords (see Figure 4.2). The Private and Public keywords are only allowed for variable declaration in the general declarations section of a module. The variable myVar5 are visible to any procedure within this module whereas myVar4 is only accessable from the Worksheet Activate() procedure. Variables declared in the general declarations section of a module (object or standard) with the Public keyword are commonly referred to as global.
Therefore, the keywords Dim and Private have the same function in variable declarations when used in the general declarations section of any module; the Public keyword can be used to declare global variables in a standard or object module.
3) Data Types
When a data type is declared it means that you have define the kind of value that may be stored within the memory allocated for a variable. There are many data types; the most common are shown in Table 4.1 below.
For example,
Dim myInt As Integer
Here, you intend for the variable (myInt) to contain only integer values.
a) Numerical Data Types
The numerical data types listed in Table 4.1 below are integer, long, single, and double. If you declre a variable as an integer or long data type, it only can hold whole numbers or non-fractional values within the specified ranges. It cannot hold fractional or “floating point” values. For fractional or “floating point” values use a single or double data type. Pay attention to the value of the number that you declare within the variable. If the value gets too large for the data type, your program will crash.
For example, the following code will generate an overflow error because the value 60000 is outside the allowed range for an integer data type:
Dim myInt As Integer
myInt = 60000
When a data type is declared it means that you have define the kind of value that may be stored within the memory allocated for a variable. There are many data types; the most common are shown in Table 4.1 below.
For example,
Dim myInt As Integer
Here, you intend for the variable (myInt) to contain only integer values.
a) Numerical Data Types
The numerical data types listed in Table 4.1 below are integer, long, single, and double. If you declre a variable as an integer or long data type, it only can hold whole numbers or non-fractional values within the specified ranges. It cannot hold fractional or “floating point” values. For fractional or “floating point” values use a single or double data type. Pay attention to the value of the number that you declare within the variable. If the value gets too large for the data type, your program will crash.
For example, the following code will generate an overflow error because the value 60000 is outside the allowed range for an integer data type:
Dim myInt As Integer
myInt = 60000
You need be careful when mixing numerical data types because you may not get the desired result. The code below will execute without errors, but the variable answer will hold the value 11 after execution, not 10.8 as you might want as the value stored in answer is rounded to the nearest whole integer.
Dim answer As Integer
Dim num1 As Single
Dim num2 As Integer
num1 = 2.7
num2 = 4
answer = num1 * num2
To correct this eror, change the variable answer to a single data type like below.
Dim answer As Single
Dim num1 As Single
Dim num2 As Integer
num1 = 2.7
num2 = 4
answer = num1 * num2
You can perform many types of mathematical calculations within your macros. You can add, subtract, multiply, and divide variables; you can square and cube numerical variables or raise them to any desired power. See Table 4.2 for a list of the operators used for common mathematical operations in VBA.
i) Common Math Operators Used In VBA
Dim answer As Integer
Dim num1 As Single
Dim num2 As Integer
num1 = 2.7
num2 = 4
answer = num1 * num2
To correct this eror, change the variable answer to a single data type like below.
Dim answer As Single
Dim num1 As Single
Dim num2 As Integer
num1 = 2.7
num2 = 4
answer = num1 * num2
You can perform many types of mathematical calculations within your macros. You can add, subtract, multiply, and divide variables; you can square and cube numerical variables or raise them to any desired power. See Table 4.2 for a list of the operators used for common mathematical operations in VBA.
i) Common Math Operators Used In VBA
Fig 4.4 - Click for larger imageYou can be performed any mathematical operation on a numerical variable. The following are a few examples:
Dim num1 As Integer
Dim num2 As Integer
Dim answer As Integer
num1 = 4
num2 = 2
answer = num1 + num2 ‘ answer Holds 6
answer = num1 - num2 ‘ answer Holds 2
answer = num1 * num2 ‘ answer Holds 8
answer = num1 / num2 ‘ answer Holds 2
answer = num1 ^ 2 ‘ answer Holds 16
answer = 2 ^ num2 ‘ answer Holds 4
I believe, you frequently encounter this type of mathematical calculation in source code for all programming language.
b) String Data Types
To hold characters as text, the variables are declared as string data types . A string data type can hold can be numbers, letters, or special symbols (for example, punctuation marks). Basically, just about anything you can type on your keyboard can be held within a string variable. To do this, use the String keyword like the example below.
Dim myName As String
myName = “Peter Anderson”
There are two types of string variables, variable length and fixed length. The example above is that of a variable length string because myText can hold just about any length of text (see Table above). Following is an example of a declaration for a fixed length string:
You can declare a string variable to hold a fixed length as well like the code below.
Dim myString As String * 10
myString = “ABCDEFGHIJKL”
Here, the string variable myString can hold a maximum of ten characters.
When you run the code, only the first ten characters in this example will be stored in the variable i.e. “ABCDEFGHIJ”. Fixed length strings are often used as a part of a user-defined data type. In most cases, it is advisable that you use the variable length type as you will not know the length of the string to be stored in a variable.
When declaring a fixed-length string, do not use a comma in the number that specifies the string size. In fact, never use commas when entering a numeric value in VBA.
c) Dates As Data Types
What do you do if you need to calculate the numbers of days between two dates? Well. You declares the variables as date data type. If you declare a string variable to store dates, you are unable to do the calculation.
Dim Today as Date
Dim LastTime As Date = # 6/6/2007# ‘here the variable is a fix date
Dim NumDay As Integer
Today = Now
NumDay = Today – LastTime
When you run the code, NumDay will stored the numbers of days that have elapsed since he 6/6/07. Thus, the date data type gives your routines greater flexibility.
In VBA, place dates and times between two hash marks, as shown in the above examples.
Date variables display dates according to your system’s short date format, and display times according to your system’s time format (either 12- or 24-hour) so it depends on the settings for the system on which the application is running.
d) Variant Data Types
Variant data types are comparable to the General category in the number format of a spreadsheet cell in the Excel application. They are declared like this,
Dim myUnknown
Dim myUnknown2 As Variant
You can use Variant type variables to hold any type of data except a fixed length string. This way Variant data types give the programmer more flexibility; but if overused, they can be dangerous and slow down program execution. It is not advisable to use them due to reason given above. For example,
Dim myVar As Integer
myVar = 5
myVar = “Five”
The will create a type mismatch error because an attempt is made to enter the string “Five” into an integer variable. To solve this problem, you change the variable myVar to a variant, the code will execute and myVar will hold the string value “Five” when all is complete. The following code will run without error.
Dim myVar
myVar = 5
myVar = “Five”
These code enable the variable myVar to hold the integer value 5 (albeit briefly) before being assigned the string value “Five”.
With the example code above, a potential problem exist and will crash the program.
If the variable is wrongly initialize to store “Five” instead of the numerical value 5, a mathematical operation cannot be done and will taint the result of your program or even worst the program will crash.
Using the variant data type will also make your program difficult to debug. Although it may be tempting to use variants as a way to prevent errors that crash your program (like the example above), in actuality the use of variants make your code “loose,” and may cause logic errors that are difficult to find.
Dim num1 As Integer
Dim num2 As Integer
Dim answer As Integer
num1 = 4
num2 = 2
answer = num1 + num2 ‘ answer Holds 6
answer = num1 - num2 ‘ answer Holds 2
answer = num1 * num2 ‘ answer Holds 8
answer = num1 / num2 ‘ answer Holds 2
answer = num1 ^ 2 ‘ answer Holds 16
answer = 2 ^ num2 ‘ answer Holds 4
I believe, you frequently encounter this type of mathematical calculation in source code for all programming language.
b) String Data Types
To hold characters as text, the variables are declared as string data types . A string data type can hold can be numbers, letters, or special symbols (for example, punctuation marks). Basically, just about anything you can type on your keyboard can be held within a string variable. To do this, use the String keyword like the example below.
Dim myName As String
myName = “Peter Anderson”
There are two types of string variables, variable length and fixed length. The example above is that of a variable length string because myText can hold just about any length of text (see Table above). Following is an example of a declaration for a fixed length string:
You can declare a string variable to hold a fixed length as well like the code below.
Dim myString As String * 10
myString = “ABCDEFGHIJKL”
Here, the string variable myString can hold a maximum of ten characters.
When you run the code, only the first ten characters in this example will be stored in the variable i.e. “ABCDEFGHIJ”. Fixed length strings are often used as a part of a user-defined data type. In most cases, it is advisable that you use the variable length type as you will not know the length of the string to be stored in a variable.
When declaring a fixed-length string, do not use a comma in the number that specifies the string size. In fact, never use commas when entering a numeric value in VBA.
c) Dates As Data Types
What do you do if you need to calculate the numbers of days between two dates? Well. You declares the variables as date data type. If you declare a string variable to store dates, you are unable to do the calculation.
Dim Today as Date
Dim LastTime As Date = # 6/6/2007# ‘here the variable is a fix date
Dim NumDay As Integer
Today = Now
NumDay = Today – LastTime
When you run the code, NumDay will stored the numbers of days that have elapsed since he 6/6/07. Thus, the date data type gives your routines greater flexibility.
In VBA, place dates and times between two hash marks, as shown in the above examples.
Date variables display dates according to your system’s short date format, and display times according to your system’s time format (either 12- or 24-hour) so it depends on the settings for the system on which the application is running.
d) Variant Data Types
Variant data types are comparable to the General category in the number format of a spreadsheet cell in the Excel application. They are declared like this,
Dim myUnknown
Dim myUnknown2 As Variant
You can use Variant type variables to hold any type of data except a fixed length string. This way Variant data types give the programmer more flexibility; but if overused, they can be dangerous and slow down program execution. It is not advisable to use them due to reason given above. For example,
Dim myVar As Integer
myVar = 5
myVar = “Five”
The will create a type mismatch error because an attempt is made to enter the string “Five” into an integer variable. To solve this problem, you change the variable myVar to a variant, the code will execute and myVar will hold the string value “Five” when all is complete. The following code will run without error.
Dim myVar
myVar = 5
myVar = “Five”
These code enable the variable myVar to hold the integer value 5 (albeit briefly) before being assigned the string value “Five”.
With the example code above, a potential problem exist and will crash the program.
If the variable is wrongly initialize to store “Five” instead of the numerical value 5, a mathematical operation cannot be done and will taint the result of your program or even worst the program will crash.
Using the variant data type will also make your program difficult to debug. Although it may be tempting to use variants as a way to prevent errors that crash your program (like the example above), in actuality the use of variants make your code “loose,” and may cause logic errors that are difficult to find.
e) Boolean Data Type
The Boolean data type is simple. It holds the value true or false. It can also be represented true as a 1 and false as a 0. You usually use it when dealing with programming structures that use conditions. (I will explain this in another Lesson). Declare and initialize a Boolean variable as follows:
Dim condition As Boolean
condition = False
f) Constants Data Type
You may sometime have variables that do not change its value when you execute procedures. In which case it is good practice to declare it as a constant. It makes the code easier to read and it is easier to change its value later if needed. Compare this like using named ranges in your spreadsheet formulas.
Const PI As Double = 3.14159
Dim circumference As Single
Dim diameter As Single
diameter = 9.33
circumference = PI* diameter
4) Arrays
An array is a collection of variables that have a common name. For example, we refer a specific variable in the array by using the array name and an index number.
For example, you may define an array of 7 string variables to hold the number of days in a week. If you name the array NumDays, you can refer to the first element of the array as NumDays(1), the second element as NumDays(2), and so on.
a) Declaring arrays
Before you can use an array, you must declare it like a variable in VBA with a Dim or a Public statement. By default, VBA takes 0 as the lower index. You need to specify the number of elements in the array by specifying the first index number and the last index number. The following example shows how to declare the number of days in a week.
Dim NumDays(1 to 7) As Integer
When you declare an array, you can choose to specify only the upper index.
As VBA assumes that 0 is the lower index, the following code both declare the same 100 element array:
Dim MyArray(0 to 99) As Integer
Dim MyArray(99) As Integer
If you want VBA to assume that 1 is the lower index for your arrays, your need to add the following code in the Declarations section of your module:
Option Base 1
We assume the the lower index is 1 from now. The array code above, will now represent a 99 element array
b) Assigning values to elements in an array
Suppose we have an one-dimensional array called MyArray and we wish to assign the value 5 to the 3rd index slot, use the following
MyArray(3) = 5
c) Multidimensional arrays
We have look at one-dimensional arrays. Incredibly, VBA allow you to have as many as 60 dimensions. The following example declares a 50-integer array with two dimensions:
Dim MyArray(1 to 5, 1 to 5) As Integer
You can think of this array as occupying a 5-x-5 matrix. If we want to assign the value 5 to the entry in the 2nd row and 5th column of the array, use
MyArray(2, 5) = 5
In other word, the integer value 5 is stored in (row 2, column 5) of the matrix MyArray
For a three-dimensional array, think of it as a cube. Visualizing an array of more than three dimensions is more difficult. Well you don’t worry about the fourth dimension and beyond as I bet you’ll never use it.
d) Dynamic arrays
So far, we have been declaring arrays with a fixed number of elements. If you wish to declare an array without specifying its size, use
Dim MyArray() As Integer
This is how to create dynamic arrays. Notice a dynamic array has a blank set of parentheses:
To resize the array, you must use the ReDim statement to tell VBA how many elements the array has. Most of the time, the number of elements in the array is determined while your code is running. The ReDim statement can be used any number of times, to change the array’s size as often as you need.
For example if you need to resize MyArray to a 10 x 10 matrix then you
ReDim MyArray(10,10) As Integer
Now you have change the number of elements in a dynamic array. When you redimension an array by using ReDim, you clear all the values currently stored in the array elements.
I’ll discuss on arrays again in Lesson 6.
5) Simple Input and Output with VBA
You already know how to get input from the user through the use of the Value property of a spreadsheet cell. Apart from that, you can also generate output for the user through the spreadsheet. However there may be times when you want something more dynamic and dramatic way to interact with the user than using a spreadsheet cell. The most common method for gathering input from the user and sending output back is the InputBox() and MsgBox() functions.
a) Collecting User Input with InputBox()
The InputBox() function can be used to prompt and sometime to force a response from the user. What happened is that, it will sends to the screen a dialog box that must be addressed by the user before program execution continues.
Figure 4.5 below shows the dialog box.
Dim condition As Boolean
condition = False
f) Constants Data Type
You may sometime have variables that do not change its value when you execute procedures. In which case it is good practice to declare it as a constant. It makes the code easier to read and it is easier to change its value later if needed. Compare this like using named ranges in your spreadsheet formulas.
Const PI As Double = 3.14159
Dim circumference As Single
Dim diameter As Single
diameter = 9.33
circumference = PI* diameter
4) Arrays
An array is a collection of variables that have a common name. For example, we refer a specific variable in the array by using the array name and an index number.
For example, you may define an array of 7 string variables to hold the number of days in a week. If you name the array NumDays, you can refer to the first element of the array as NumDays(1), the second element as NumDays(2), and so on.
a) Declaring arrays
Before you can use an array, you must declare it like a variable in VBA with a Dim or a Public statement. By default, VBA takes 0 as the lower index. You need to specify the number of elements in the array by specifying the first index number and the last index number. The following example shows how to declare the number of days in a week.
Dim NumDays(1 to 7) As Integer
When you declare an array, you can choose to specify only the upper index.
As VBA assumes that 0 is the lower index, the following code both declare the same 100 element array:
Dim MyArray(0 to 99) As Integer
Dim MyArray(99) As Integer
If you want VBA to assume that 1 is the lower index for your arrays, your need to add the following code in the Declarations section of your module:
Option Base 1
We assume the the lower index is 1 from now. The array code above, will now represent a 99 element array
b) Assigning values to elements in an array
Suppose we have an one-dimensional array called MyArray and we wish to assign the value 5 to the 3rd index slot, use the following
MyArray(3) = 5
c) Multidimensional arrays
We have look at one-dimensional arrays. Incredibly, VBA allow you to have as many as 60 dimensions. The following example declares a 50-integer array with two dimensions:
Dim MyArray(1 to 5, 1 to 5) As Integer
You can think of this array as occupying a 5-x-5 matrix. If we want to assign the value 5 to the entry in the 2nd row and 5th column of the array, use
MyArray(2, 5) = 5
In other word, the integer value 5 is stored in (row 2, column 5) of the matrix MyArray
For a three-dimensional array, think of it as a cube. Visualizing an array of more than three dimensions is more difficult. Well you don’t worry about the fourth dimension and beyond as I bet you’ll never use it.
d) Dynamic arrays
So far, we have been declaring arrays with a fixed number of elements. If you wish to declare an array without specifying its size, use
Dim MyArray() As Integer
This is how to create dynamic arrays. Notice a dynamic array has a blank set of parentheses:
To resize the array, you must use the ReDim statement to tell VBA how many elements the array has. Most of the time, the number of elements in the array is determined while your code is running. The ReDim statement can be used any number of times, to change the array’s size as often as you need.
For example if you need to resize MyArray to a 10 x 10 matrix then you
ReDim MyArray(10,10) As Integer
Now you have change the number of elements in a dynamic array. When you redimension an array by using ReDim, you clear all the values currently stored in the array elements.
I’ll discuss on arrays again in Lesson 6.
5) Simple Input and Output with VBA
You already know how to get input from the user through the use of the Value property of a spreadsheet cell. Apart from that, you can also generate output for the user through the spreadsheet. However there may be times when you want something more dynamic and dramatic way to interact with the user than using a spreadsheet cell. The most common method for gathering input from the user and sending output back is the InputBox() and MsgBox() functions.
a) Collecting User Input with InputBox()
The InputBox() function can be used to prompt and sometime to force a response from the user. What happened is that, it will sends to the screen a dialog box that must be addressed by the user before program execution continues.
Figure 4.5 below shows the dialog box.
Fig 4.5 - Click for larger image
And the code is this,
Sub GetName()
Dim MyInput
MyInput = InputBox("Enter your name")
MsgBox ("Hello ") & MyInput
End Sub
The InputBox() function example above returns the data entered by the user as a string if the OK button is clicked or the Enter key is pressed on the keyboard. If the user clicks the Cancel button, then a zero-length string is returned (“”). The syntax required for creating an InputBox()(parameters in brackets are optional). It is rare that you will need to use [, xpos] [, ypos] or [, helpfile, context]. See Excel help for details on these.
InputBox(prompt [,title] [,default] [,xpos] [,ypos] [,helpfile, context], type)
Typically, the prompt, title, and sometimes the default are used. However, it is the last argument "Type" that allows us to specify the type of data we are going to collect. These are as shown below;
Type:=0 A formula
Type:=1 A number
Type:=2 Text (a string)
Type:=4 A logical value (True or False)
Type:=8 A cell reference, as a Range object
Type:=16 An error value, such as #N/A
Type:=64 An array of values
To look at another InputBox example that return number
Sub GetAge()
Dim MyInput As Integer
MyInput = Application.InputBox(Prompt:="How old are you ? " , Title:="ENTER _
AGE: ", Type:=1)
MsgBox ("You’re ") & MyInput
End Sub
As a rule, the prompt and title must be strings and enclosed in double quotation marks. The title parameter is displayed in the title bar of the dialog box. The default parameter is displayed in the text box of the dialog box.
b) Output with MsgBox()
By using the MsgBox() function, a message box like the one shown in Figure 4.3, outputs a message to the user or ask a question that require a yes / no answer.
This is also useful as a way to inform the user about some type of problem. The syntax for the MsgBox() function is as follow…
MsgBox(prompt[, buttons] [, title] [, helpfile, context])
Sub GetName()
Dim MyInput
MyInput = InputBox("Enter your name")
MsgBox ("Hello ") & MyInput
End Sub
The InputBox() function example above returns the data entered by the user as a string if the OK button is clicked or the Enter key is pressed on the keyboard. If the user clicks the Cancel button, then a zero-length string is returned (“”). The syntax required for creating an InputBox()(parameters in brackets are optional). It is rare that you will need to use [, xpos] [, ypos] or [, helpfile, context]. See Excel help for details on these.
InputBox(prompt [,title] [,default] [,xpos] [,ypos] [,helpfile, context], type)
Typically, the prompt, title, and sometimes the default are used. However, it is the last argument "Type" that allows us to specify the type of data we are going to collect. These are as shown below;
Type:=0 A formula
Type:=1 A number
Type:=2 Text (a string)
Type:=4 A logical value (True or False)
Type:=8 A cell reference, as a Range object
Type:=16 An error value, such as #N/A
Type:=64 An array of values
To look at another InputBox example that return number
Sub GetAge()
Dim MyInput As Integer
MyInput = Application.InputBox(Prompt:="How old are you ? " , Title:="ENTER _
AGE: ", Type:=1)
MsgBox ("You’re ") & MyInput
End Sub
As a rule, the prompt and title must be strings and enclosed in double quotation marks. The title parameter is displayed in the title bar of the dialog box. The default parameter is displayed in the text box of the dialog box.
b) Output with MsgBox()
By using the MsgBox() function, a message box like the one shown in Figure 4.3, outputs a message to the user or ask a question that require a yes / no answer.
This is also useful as a way to inform the user about some type of problem. The syntax for the MsgBox() function is as follow…
MsgBox(prompt[, buttons] [, title] [, helpfile, context])
You can see that I have use a string for the prompt, a vbOKOnly button and also a string title. The example below was used to generate the message box in Figure 4.3 above:
Msg = MsgBox(“How Are You”, vbOKOnly, “Message”)
The prompt must be a string or string variable and is used as the message you want the user to read. The buttons parameter requires a numeric expression (either an integer or constant) and tells VBA what buttons and/or icons are to be placed on the message box.
If you do not specify a button constant, the MsgBox() function uses the default vbOKOnly that displays only the OK button. For a complete list of button choices, look at Figure 4.4).
You can choose to display an icon (warnings or information type), a help button, and add some additional formatting with your choice of buttons.
Fig 4.7 - Click for larger imageThe MsgBox() function returns an integer between 1 and 7 depending on the button selected.
Obviously this is only useful when there is more than one button. The return value should then be used to select a course of action in your program.
Don’t put too many message boxes in your program as it is extremely annoying and a disturbance to have to answer a message box when it’s not really necessary.
6) Summary
On this Part, I've discussed some important basics of programming; including variables, data types, and constants. We also look at programming modules in VBA and their effect on the scope of a variable. Also, I show to you several functions used to manipulate values of type string and date.
Obviously this is only useful when there is more than one button. The return value should then be used to select a course of action in your program.
Don’t put too many message boxes in your program as it is extremely annoying and a disturbance to have to answer a message box when it’s not really necessary.
6) Summary
On this Part, I've discussed some important basics of programming; including variables, data types, and constants. We also look at programming modules in VBA and their effect on the scope of a variable. Also, I show to you several functions used to manipulate values of type string and date.
