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!
Search JabSto ::




Custom Search