Microsoft Office Tutorials and References
In Depth Information
Example 5-1. A Procedure with a Typo
To illustrate how dangerous this can be, consider the NewBook procedure in Example 5-1 , whose
purpose is to take the first open workbook, change its contents, ask the user for a name under
which to save the changed workbook, and then save the workbook under the new name.
Example 5-1. A Procedure with a Typo
Sub NewBook()
Dim Wbk As Workbook
Dim WbkName As String
' Get first open workbook
Set Wbk = Workbooks(1)
' Get the workbook name
WbkName = Wbk.Name
' Code to change the contents of the workbook
' goes here . . .
' Ask user for new name for document
WkbName = InputBox("Enter name for workbook " & WbkName)
' Save the workbook
Wbk.SaveAs WbkName
End Sub
Observe that there is a typographical error (the b and k are transposed) in the following line:
WkbName = InputBox("Enter name for workbook " & WbkName)
Since the variable WkbName is not declared, Excel will treat it as a new variable and give it the
Variant data type. Moreover, VBA will assume that we want the new filename to be assigned to
the variable WkbName , and will save the changed document under its original name, which is
stored in WbkName . Thus, we will lose the original workbook when it is inadvertently overwritten
without warning! Option Explicit
To avoid the problem described in the previous example, we need a way to make Excel refuse to
run a program if it contains any variables that we have not explicitly declared. This is done simply
by placing the line:
Option Explicit
in the Declarations section of each code module. Since it is easy to forget to do this, VBA
provides an option called "Require Variable Declaration" in its Options dialog box. When this
option is selected, VBA automatically inserts the Option Explicit line for us. Therefore, I
strongly recommend that you enable this option.
Now let us briefly discuss some of the data types in Table 5-1 .
5.4.3 Numeric Data Types
The numeric data types include Integer, Long, Single, Double, and Currency. A long is also
sometimes referred to as a long integer .
Search JabSto ::

Custom Search