Page 120 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

With the definition in mind, take a look at the following Sub procedure, which converts a
quantity of American dollars into Swedish krona at the current exchange rate, as drawn from
the Microsoft Money Central Web site and stored in cell C35:

Sub Krona()
sngInKrona = ActiveCell.Value * Range("C35").Value
MsgBox("The value of $" & ActiveCell.Value & " is " _
& sngInKrona & " krona.")
End Sub

You can find currency quotes on the Web by visiting http://moneycentral.msn.com/ and typing “currency
rates” into the Search box. For more information on getting information from the Web into your Excel
worksheets, see Chapter 24, “Excel and the Web.”

If you want to run this Sub procedure from another procedure, you could do so using one of
these three techniques:
● Type the name of the procedure and any arguments it takes. (If there are no arguments,
type an empty pair of parentheses.)
● Type the Call keyword, and then type the name of the procedure and any arguments it
takes. (If there are no arguments, type an empty pair of parentheses.)
● Use the Application object’s Run method to run the macro. This method is useful if you
want to use your VBA code to determine which of several macros to run, and to assign
the name of the macro you decide upon to a variable.
You’ve already seen the first technique in action, but it’s actually the same as the second tech­
nique. When Excel encounters an unfamiliar word followed by a set of open and close paren­
theses, it searches the available modules for Public procedures of the same name. You used to
be required to put the Call keyword before the procedure name, but that’s no longer manda­
tory. Even so, some programmers choose to put the Call keyword in front of procedures they
have created to ensure that they (and anyone else who examines the code in the module) will
understand that the procedure is not part of the standard Excel library.
As you might expect, you’re not limited to calling procedures from within the same code
module or even the same workbook. If you want to reach out to use procedures in other
modules, you can do so. In fact, if the Excel VBA engine doesn’t find the procedure you call
in the same module as the calling procedure, it will search the other modules in the active
workbook and, if it still hasn’t found the procedure you called, will go on to all other open
Chapter 5
workbooks in an attempt to find the code.
When you know the name of the module that contains the procedure you want to run and
the module is in the active workbook, you can put the name of the module in front of the
procedure name using dot notation, as in the following brief Sub procedure:

Sub CallOut()
Call CurrencyModule.Krona()
End Sub

94
Part 2: Visual Basic for Applications