visual basic error

[Charlie Woll]

In the following code, I get an error at the last 2 commands:

Function AvgPts()
'
' AvgRounds Macro
' Macro recorded 11/06/99 by Charles G. Woll

Application.Volatile
'On Error GoTo ErrorHandler

Dim total_plays As Byte, used_plays As Byte
Dim I As Double, cnt As Double
Dim Row As Double, col As Double

total_plays = 7
used_plays = 5
Row = Application.Caller.Row
col = Application.Caller.Column

 

[Bob Phillips]

It worked fine for me. How did you cal; it?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

It worked for me too.

And the question Bob is asking you is "How did you call it?" (his finger
slipped on the second "L" and he hit the key next to it).

Rick

 

[Charlie Woll]

The procedure is called by the custom function =AVGPTS()
The troublesome lines of code are supposed to return the active column and
row. Has worked for years, now after fixing the range dimensions of a named
range not used in this procedure, it returns error.
Charlie

 

[Bob Phillips]

Well, it doesn't return anything, because the return value is not set. In VB
functions, the return value is set by loading into the function variable
itself. But perhaps the code that you didn't show us does that, I see that
there is no End Function, so maybe more code.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Charlie Woll]

Bob, you are right, I did not show the rest of the code which returns a
calculated value or an error message from the Error Handler.
The problem is that the statement:
Row = Application.Caller.Row
sends the program directly to the error handler. I have had this problem in
the past and fixed it by adding the
Application.Volatile
statement or commenting it out. This time, I can't get the program to go
past the row = statement.
Charlie

 

[Bob Phillips]

Try using a variable name different to Row, Row is a VBA keyword, so it
certainly won't hurt.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)