Row = Application.Caller.Row

[Charles Woll]

I have 2 functions which call Row = Application.Caller.Row
The 2nd function bombs at this line. Any ideas?

\\\|///
\\ ~ ~ //
( @ @ )
--oOOo-(_)-oOOo---
Charlie Woll

 

[Bob Phillips]

Does your caller have a row property?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Charles Woll]

I am sorry, but I do not know what you mean. I am not very skilled at this.
charlie

 

[Bob Phillips]

Well, you are using the Caller property? What is it that is triggering this
code, that is what is the calling object? Some, such as a shape, don't have
a Row property, so that code would fail.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Charles Woll]

I have created worksheet function =avgpts() It gathers in scores that are
in a row coresponding to an individual week by week. The function avgpts()
calculates the best 7 of 10 scores by getting scores from right to left in
the spredsheet. I use Application.Caller.Row to tell the vb program what
row i am calculating and .column to telll it what column I am starting in.
This works.
Next I added another sheet to the workbook and copied the data from the
avgpts sheet and substituted a slightly revised vb program with the function
=GHIN() to convert the entered scores to USGA handicap scores. I needed to
use the same Application.Caller.Row and Application.Caller.Column to
determine starting point in the program.. The program in the debugger will
not go past Application.Caller.Row.

If I copy sheet to a new workbook, the function works. It does not like to
be married to the similar function in the same workbook.

Function Ghin()
'
' Ghin Macro
' Macro recorded 2/25/2005 by Charles Woll
'
On Error GoTo ErrorHandler
Row = 0
Col = 0


Total_plays = 10
Used_Plays = 8
Row = Application.Caller.Row
Col = Application.Caller.Column
i = 0
Cnt = 0
tot = 0
AvgPts = 0
FirstColumn = Range("First_Column").Column
Dim CellValue(10)

For i = 0 To 10 ' initialize CellValue
to 0
CellValue(i) = 0
Next i

For i = Col - 1 To FirstColumn + 1 Step -1 'steps from last to 1st
column


If Cells(Row, i) <> "" Then
Cnt = Cnt + 1 'count cells with data
CellValue(Cnt - 1) = Cells(Row, i) 'Keep a total of
columns with data

End If


If i = FirstColumn + 1 Then ' last column with
data?
GoTo Sort
End If

If Cnt = Total_plays Then
GoTo Sort
End If
Next i

Sort:
For j = 0 To Cnt - 2
For k = j + 1 To Cnt - 1
If CellValue(j) = "" Then GoTo SumLowestPlays
If CellValue(k) = "" Then GoTo NextJ
If CellValue(j) > CellValue(k) Then
temp = CellValue(j)
CellValue(j) = CellValue(k)
CellValue(k) = temp
End If
Next k
NextJ:
Next j


SumLowestPlays:

For i = 0 To Used_Plays - 1
tot = tot + CellValue(i)
Next i

If Cnt >= Used_Plays Then Ghin = tot / Used_Plays Else Ghin = tot / Cnt
Ghin = Ghin * 0.96

Exit Function

ErrorHandler:
Ghin = "ERR"
Exit Function


End Function

 

[Charles Woll]

May I ask another question. When using vb 6.3 help in excel the index and
answer wizard panes are not accessable! When I move the mouse into those
boxes, it turns into a horizontal double arrow. The one you get to resize a
window horizontally. This really limits what I can learn from the help
system. Is this normal, or do I have a problem?
charlie

 

[Bob Phillips]

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

No it is not just you, I get it all the time.

My workaround is to shutdown Excel, then go to the Task Manager and
terminate msohelp.exe.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Charles Woll]

The problem is in the line AvgPts = 0
Since I have a custom function AvgPts() in the workbook, It appears that
AvgPts is a reserve word. Changing this statement to Ghin = 0, which is the
variable in the new routine, the procedure works.
Thanks for all your help. Where do you live in England?
charlie