PDA

View Full Version : Solved: My UDF Runs 3 Times When Called



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:

Ken Puls
10-18-2006, 12:47 PM
Hi Sid,

I just tried your code, adding .value to the line Cellval line and it worked fine:


CellVal = Cells(ActiveCell.Row + RowOffset, ActiveCell.Column + ColOffset).Value
HTH,

Bob Phillips
10-18-2006, 12:53 PM
I can't reproduce the problem, but you have no error checking, the rowoffset could take row into negative territory, so could column. And what happens when the formula calculates and the activecell is somewhere else.

But when I did have bad conditions, I got the error message.

Cyberdude
10-18-2006, 01:40 PM
Hi, Bob!
Thanks for the assist. I haven't tested adding .Value yet, but you struck a bell when you pointed out that the cell using the function usually won't be the active cell. Duh! I'll work on that angle.
I'm not sure what you meant when you said "you have no error checking". Sure I do, and it works ... at least for the incorrect offset values, which is what the main threat is.
This one really had me scratching my head. I still don't understand the looping. It happens so fast I almost didn't notice it. Maybe that is a consequence of not having the active cell.