Programming Excel with VBA and .NET

Named Arguments

Visual Basic lets you include the name of arguments when you call a procedure. This is most obvious in recorded code:

     Selection.AutoFormat Format:=xlRangeAutoFormatSimple, Number:=True, Font _
            :=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True

The name before the := is the name of the argument and the item after it is the value of that argument. This is handy if you want to use mostly default values; for instance, the following code reformats a selection without adding borders or changing column widths:

    Selection.AutoFormat Format:=xlRangeAutoFormatSimple, Border:=False, Width:=False

You can do the same thing without names by relying on the positions of the arguments instead. The following line does exactly the same thing as the preceding one:

    Selection.AutoFormat xlRangeAutoFormatSimple, , , , False, , False

Tip

I tend to omit named arguments because I feel they are often too verbose and because the next generation of Visual Basic (Visual Basic .NET) doesn’t use them. Feel free to disagree with me on this one, though.

An approach that works better than named arguments in my opinion is using Visual Basic’s Auto Complete feature. That feature doesn’t work with generic types of objects like Selection and ActiveSheet, so you must first get the specific type of object as shown in Figure 2-7.

Figure 2-7. Auto Complete makes named arguments unnecessary in my opinion ...