Using Methods

When you use a method in your procedure, the expression that contains the method can have several different forms. Some methods, such as the AutoFit method, take no arguments, whereas other methods take several arguments. These variations are described in the following sections.

Methods That Take No Arguments

To use a method that takes no arguments, build an expression to return the object, and then apply the method. For example, to automatically adjust the column width of columns A through D on Sheet1 to fit the contents of the cells, you could write the following code.

Worksheets("Sheet1").Columns("A:D").AutoFit

Methods That Take Arguments

When a method takes arguments, the way you write your code depends on whether you want to use the value returned by the method. If you don't want to use the return value of a method, don't enclose the arguments in parentheses. For example, to sort the contents of cells A1:A10 on Sheet1, you could write the following code.

Worksheets("Sheet1").Range("A1:A10").Sort _
    Worksheets("Sheet1").Range("A1")

In the preceding code, the first argument to the Sort method is the expression Worksheets("Sheet1").Range("A1"); it isn't enclosed in parentheses because you aren't using the Sort method's return value.

On the other hand, if you use the return value, you must enclose the argument in parentheses. Suppose that you use the CheckSpelling method to check the spelling of a single word. The word becomes the argument to the method, which returns True if the word is spelled correctly or False if it isn't. You could write the following procedure.

Sub CheckWord()
    returnValue = Application.CheckSpelling("recieve")
    If returnValue = True Then
        MsgBox "The word is spelled correctly"
    Else
        MsgBox "The word is misspelled!"
    End If
End Sub

Notice that the argument is enclosed in parentheses. If you run this code, you will get the second message box, because the word is intentionally misspelled.

Using Named Arguments in Methods

Many methods take more than one argument. For example, the Open method, which opens a workbook, takes 12 arguments. If you want to write code that opens the workbook BOOK2.XLS, which has the protection password "drowssap", you could write the following code.

Workbooks.Open "BOOK2.XLS", , , , "drowssap"

However, this code is difficult to write correctly without introducing bugs, because you have to count the number of commas to insert between the arguments. The code is also very difficult to read, and it gives no clues about what the arguments represent. The following example shows a better way to write this code.

Workbooks.Open fileName:="BOOK2.XLS", password:="drowssap"

Because every argument has a name, you can use the name and the := operator to assign a value to an argument. When you use named arguments, you don't have to remember the order of the arguments. For example, the preceding code could have been written with the order of the arguments reversed.

Workbooks.Open password:="drowssap", fileName:="BOOK2.XLS"

The preceding section's rules about parentheses apply to named arguments also. For example, the CheckWord procedure could use a named argument for the CheckSpelling method, as shown in the following code.

Sub CheckWord()
    returnValue = Application.CheckSpelling(word:="recieve")
    If returnValue = True Then
        MsgBox "The word is spelled correctly"
    Else
        MsgBox "The word is misspelled!"
    End If
End Sub

Using Built-in Constants as Argument Values

Many methods take built-in constants as their argument values. For example, the ActivateMicrosoftApp method takes as its argument values xlMicrosoftAccess, xlMicrosoftWord, and so on. The Help topic for any given method contains a list of built-in constants you can use for argument values. You should always use the built-in constant, not the value that the constant represents, because the value may change in future versions of Visual Basic.