Cyberdude
10-18-2006, 12:21 PM
I wrote the following function to be a shorthand (in worksheet formulas) for extracting the value of any cell that is a valid offset (column and/or row) from the cell containing the formula. I have a large number of applications that this is useful in.
?This Sub tests the next one
Sub CellVal_Test()
MsgBox CellVal(2, 2) ?Fetch value of cell 2 cols to right and 2 rows down
End Sub
?This is the main function giving trouble
Function CellVal(Optional ColOffset As Long = 0, Optional RowOffset As Long = 0) As Variant
'Application.Volatile (False) '<--(Has no apparent effect when off or on)
On Error GoTo ErrMsg ?<--(Has no effect if removed)
CellVal = Cells(ActiveCell.Row + RowOffset, ActiveCell.Column + ColOffset)
On Error GoTo 0
Exit Function
ErrMsg: ?(This part of the code is never executed)
MsgBox "Invalid offset argument ('" & ColOffset & "' or '" & RowOffset & "') in function 'CellVal'"
On Error GoTo 0
CellVal = CVErr(xlErrRef) ?(Error value returned is #REF!)
End Function
The following is a typical example of an application of this function:
=INDIRECT("Summary!$S"&CellVal(-9,-2))
When the function CellVal executes from a worksheet, it runs the statement shown above in red 3 times, as if it were in a loop. The first time the statement executes CellVal is shown as containing the value ?Empty?. The next two iterations return the correct value ?20?. The statement ?Exit Function? is executed, then the red statement executes again. Note that, when I run the function from a VBA macro, it runs just once.
It gets worse. If I have a similar formula on the same worksheet, the cell containing that formula suddenly shows the error value #REF!. If I run that formula is run again, it returns the correct value, but the first formula shows the value #REF!. In this case, the function always runs 4 times instead of 3 times.
The #REF! value is NOT generated by the function CellVal. In fact the ON ERROR statement is never triggered, so the error handler never executes. I even removed all error handling statements, and the function behaved exactly the same.
I put in the Application.Volatile (False) statement, and it had no apparent effect.
How can control go from the Exit Function statement back to a preceding statement twice in each execution, then terminate the function the third time??
How does executing the function in one formula have an effect on a formula in a different cell??
This is uber weird! :bug:
?This Sub tests the next one
Sub CellVal_Test()
MsgBox CellVal(2, 2) ?Fetch value of cell 2 cols to right and 2 rows down
End Sub
?This is the main function giving trouble
Function CellVal(Optional ColOffset As Long = 0, Optional RowOffset As Long = 0) As Variant
'Application.Volatile (False) '<--(Has no apparent effect when off or on)
On Error GoTo ErrMsg ?<--(Has no effect if removed)
CellVal = Cells(ActiveCell.Row + RowOffset, ActiveCell.Column + ColOffset)
On Error GoTo 0
Exit Function
ErrMsg: ?(This part of the code is never executed)
MsgBox "Invalid offset argument ('" & ColOffset & "' or '" & RowOffset & "') in function 'CellVal'"
On Error GoTo 0
CellVal = CVErr(xlErrRef) ?(Error value returned is #REF!)
End Function
The following is a typical example of an application of this function:
=INDIRECT("Summary!$S"&CellVal(-9,-2))
When the function CellVal executes from a worksheet, it runs the statement shown above in red 3 times, as if it were in a loop. The first time the statement executes CellVal is shown as containing the value ?Empty?. The next two iterations return the correct value ?20?. The statement ?Exit Function? is executed, then the red statement executes again. Note that, when I run the function from a VBA macro, it runs just once.
It gets worse. If I have a similar formula on the same worksheet, the cell containing that formula suddenly shows the error value #REF!. If I run that formula is run again, it returns the correct value, but the first formula shows the value #REF!. In this case, the function always runs 4 times instead of 3 times.
The #REF! value is NOT generated by the function CellVal. In fact the ON ERROR statement is never triggered, so the error handler never executes. I even removed all error handling statements, and the function behaved exactly the same.
I put in the Application.Volatile (False) statement, and it had no apparent effect.
How can control go from the Exit Function statement back to a preceding statement twice in each execution, then terminate the function the third time??
How does executing the function in one formula have an effect on a formula in a different cell??
This is uber weird! :bug: