Microsoft Office Tutorials and References
In Depth Information
Manipulating Objects and Collections
Sub ShowRoman()
Dim DecValue As Long
Dim RomanValue As String
DecValue = 1939
RomanValue = Application.WorksheetFunction.Roman(DecValue)
MsgBox RomanValue
End Sub
When you execute this procedure, the MsgBox function displays the string MCMXXXIX . Fans of
old movies are often dismayed when they learn that Excel doesn’t have a function to convert a
Roman numeral to its decimal equivalent.
Keep in mind that you can’t use worksheet functions that have an equivalent VBA function. For
example, VBA can’t access the Excel SQRT worksheet function because VBA has its own version
of that function: Sqr . Therefore, the following statement generates an error:
MsgBox Application.WorksheetFunction.Sqrt(123) ‘error
As I describe in Chapter 10, you can use VBA to create custom worksheet functions that
work just like Excel’s built-in worksheet functions.
Manipulating Objects and Collections
As an Excel programmer, you’ll spend a lot of time working with objects and collections.
Therefore, you want to know the most efficient ways to write your code to manipulate these
objects and collections. VBA offers two important constructs that can simplify working with
objects and collections:
h With-End With constructs
h For Each-Next constructs
With-End With constructs
The With-End With construct enables you to perform multiple operations on a single object.
To start understanding how the With-End With construct works, examine the following
procedure, which modifies six properties of a selection’s formatting (the selection is assumed to be a
Range object):
Sub ChangeFont1()
Selection.Font.Name = “Cambria”
Selection.Font.Bold = True
 
Search JabSto ::




Custom Search