Page 98 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

● helpfile Optional string argument used to specify the help file used to provide
context-sensitive help. If helpfile is specified, context must also be specified.
● context Optional numerical argument that is the Help context number assigned to
the appropriate Help topic. If context is provided, helpfile must also be provided.
The InputBox function returns a string value, so if the text box is blank or the Cancel button
pressed, the returned string is empty.

The returned value from an input box is usually stored within a string variable so that it can
be processed further. The following code fragment displays a message box asking for the
user’s place of birth. All parameters are entered as variables, although literals or constants
could have also been used.

Sub BirthCity()
Dim strResponse as String, strPrompt as String, strTitle as String
strResponse = "New Orleans"
strPrompt = "Please enter your city of birth."
strTitle = "My InputBox"
strResponse = InputBox(strPrompt, strTitle, strResponse)
End Sub

Defining Object Variables

One of the strengths of VBA is that you are not limited to creating simple variables that hold
data assigned from another variable or derived from a calculation. In fact, if you want to
create a new worksheet, a chart, or any other “thing” in Excel that is represented as an object
in the Excel VBA object model, you can do so by declaring an object variable. Using the Dim
statement, you provide a name for the variable and then indicate the type of object you wish
to create, as in the following code fragment, which creates a reference to a worksheet:
Chapter 4
Dim myObj as Worksheet
Using the object variable is very different from using normal variables, mainly because object
variables are pointers to an object, not the object itself. That is, you haven’t actually created a
new worksheet yet—all you’ve done is tell VBA that you want the myObj variable to hold a
Worksheet object. Therefore, you need to actually provide the object you want assigned to
the variable, which you accomplish using the Set command. The following command sets the
object variable myObj to point to the first worksheet:

Set myObj = Worksheets(1)

Creating Custom Data Types

VBA provides all of the simple data types that you will ever need when writing macros for
Excel. In some instances, you might need a data type that is more complex. For example, if
you wanted to create a custom data type that would hold both the horizontal and vertical

72
Part 2: Visual Basic for Applications