Microsoft Office Tutorials and References
In Depth Information
7.4.4 Units Conversions
FileType = Switch(FileExt = "xlt", "Template", _
FileExt = "xls", "Workbook", _
FileExt = "xla", "Addin")
' Display result
If Not IsNull(FileType) Then
MsgBox FileType
MsgBox "Unrecognized type"
End If
End Sub
There is one subtlety in this code. Since the Switch function can return a Null value, we cannot
assign the return value to a String variable, as we might first try to do:
Dim FileType As String
FileType = Switch(FileExt = "xlt", "Template", _
FileExt = "xls", "Workbook", _
FileExt = "xla", "Addin")
This will not produce an error unless FileExt is not "xlt," "xls," or "xla," in which case we will
get the very annoying error message, "Invalid use of Null." The solution is to declare FileType
as a Variant, which can hold any data type, including no data type, which is indicated by the Null
keyword. (This issue can also be avoided by using a Select Case statement, discussed in
Chapter 8 . )
7.4.4 Units Conversions
The InchesToPoints function converts a measurement given in inches to one given in points.
The reason this is important is that many Excel values need to be given (or are returned) in points,
but most of us prefer to think in inches (there are 72 points in one inch).
This applies especially to positioning properties, such as Top and Left. For instance, the Top
property of a ChartObject specifies the location of the top of the chart object, measured in points,
from Row 1 of the worksheet. Thus, to set this value to .25 inches, we would write:
ActiveChart.ChartObject.Top = InchesToPoints(.25)
There is also a PointsToInches function that is useful for displaying the return value of a
function in inches when the function returns the value in points.
7.4.5 The Beep Statement
This simple statement, whose syntax is:
sounds a single tone through the computer's speakers. It can be useful (when used with restraint) if
we want to get the user's attention. However, there is a caveat: the results are dependent upon the
computer's hardware and so the statement may not produce a sound at all! Thus, if you use this
statement in your code, be sure to warn the user. (It is possible, and probably better in general, to
use the Excel status bar to display messages to the user that do not interfere with execution of a
program. This is done using the StatusBar property of the Application object.)
Search JabSto ::

Custom Search