Microsoft Office Tutorials and References

In Depth Information

If Application.Caller.Parent.Name = wks.Name Then

SHEETOFFSET = Worksheets(WksNum + Off-

set)_

.Range(cell(1).Address).Value

Exit Function

Else

WksNum = WksNum + 1

End If

Next wks

End Function

The SHEETOFFSET function accepts two arguments:

• offset: The sheet offset, which can be positive, negative, or 0.

• cell: (Optional) A single-cell reference. If this argument is omitted, the function uses the same cell reference

as the cell that contains the formula.

For more information about optional arguments, see the section, “Using optional arguments,” later in this

chapter.

The following formula returns the value in cell A1 of the sheet before the sheet that contains the formula:

=SHEETOFFSET(–1,A1)

The following formula returns the value in cell A1 of the sheet after the sheet that contains the formula:

=SHEETOFFSET(1,A1)

Advanced Function Techniques

In this section, I explore some even more advanced functions. The examples in this section demonstrate some

special techniques that you can use with your custom functions.

Returning an error value

In some cases, you may want your custom function to return a particular error value. Consider the simple

REVERSETEXT function, which I presented earlier in this chapter:

Function REVERSETEXT(text As String) As String

‘ Returns its argument, reversed

REVERSETEXT = StrReverse(text)

End Function

This function reverses the contents of its single-cell argument (which can be text or a value). If the argument is

a multicell range, the function returns #VALUE!