Microsoft Office Tutorials and References
In Depth Information
MaxVal = Wksht.Range(Addr).Value
End If
End If
Next Wksht
If MaxVal = –9.9E+307 Then MaxVal = CVErr(xlErrValue)
End Function
The For Each statement uses the following expression to access the workbook:
The parent of the cell is a worksheet, and the parent of the worksheet is the workbook. Therefore, the For Each-
Next loop cycles among all worksheets in the workbook. The first If statement inside the loop checks whether
the cell being checked is the cell that contains the function. If so, that cell is ignored to avoid a circular referen-
ce error.
You can easily modify the MAXALLSHEETS function to perform other cross-worksheet
calculations: Minimum, Average, Sum, and so on.
The SHEETOFFSET function
A recurring complaint about Excel (including Excel 2013) is its poor support for relative sheet references. For
example, suppose that you have a multisheet workbook, and you enter a formula like the following on Sheet2:
This formula works fine. However, if you copy the formula to the next sheet (Sheet3), the formula continues to
refer to Sheet1. Or if you insert a sheet between Sheet1 and Sheet2, the formula continues to refer to Sheet1,
when most likely, you want it to refer to the newly inserted sheet. In fact, you can't create formulas that refer to
worksheets in a relative manner. However, you can use the SHEETOFFSET function to overcome this limita-
Following is a VBA Function procedure named SHEETOFFSET:
Function SHEETOFFSET(Offset As Long, Optional cell As Variant)
Returns cell contents at Ref, in sheet offset
Dim WksIndex As Long, WksNum As Long
Dim wks As Worksheet
If IsMissing(cell) Then Set cell = Application.Caller
WksNum = 1
For Each wks In Application.Caller.Parent.Parent.Worksheets
Search JabSto ::

Custom Search