Row = Application.Caller.Row

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

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

Does your caller have a row property?

--

HTH

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


"Charles Woll" <cwoll@citlink.net> wrote in message
news:eLW03ONHFHA.2936@TK2MSFTNGP15.phx.gbl...
> I have 2 functions which call Row = Application.Caller.Row
> The 2nd function bombs at this line. Any ideas?
>
> \\\|///
> \\ ~ ~ //
> ( @ @ )
> --oOOo-(_)-oOOo---
> Charlie Woll
>
>
>

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

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:uTVyObNHFHA.3092@tk2msftngp13.phx.gbl...
> Does your caller have a row property?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Charles Woll" <cwoll@citlink.net> wrote in message
> news:eLW03ONHFHA.2936@TK2MSFTNGP15.phx.gbl...
>> I have 2 functions which call Row = Application.Caller.Row
>> The 2nd function bombs at this line. Any ideas?
>>
>> \\\|///
>> \\ ~ ~ //
>> ( @ @ )
>> --oOOo-(_)-oOOo---
>> Charlie Woll
>>
>>
>>
>
>

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" <cwoll@citlink.net> wrote in message
news:euFjHOQHFHA.1500@TK2MSFTNGP09.phx.gbl...
> I am sorry, but I do not know what you mean. I am not very skilled at
this.
> charlie
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:uTVyObNHFHA.3092@tk2msftngp13.phx.gbl...
> > Does your caller have a row property?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Charles Woll" <cwoll@citlink.net> wrote in message
> > news:eLW03ONHFHA.2936@TK2MSFTNGP15.phx.gbl...
> >> I have 2 functions which call Row = Application.Caller.Row
> >> The 2nd function bombs at this line. Any ideas?
> >>
> >> \\\|///
> >> \\ ~ ~ //
> >> ( @ @ )
> >> --oOOo-(_)-oOOo---
> >> Charlie 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



"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23AAm9SQHFHA.3076@tk2msftngp13.phx.gbl...
> 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" <cwoll@citlink.net> wrote in message
> news:euFjHOQHFHA.1500@TK2MSFTNGP09.phx.gbl...
>> I am sorry, but I do not know what you mean. I am not very skilled at
> this.
>> charlie
>>
>> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> news:uTVyObNHFHA.3092@tk2msftngp13.phx.gbl...
>> > Does your caller have a row property?
>> >
>> > --
>> >
>> > HTH
>> >
>> > RP
>> > (remove nothere from the email address if mailing direct)
>> >
>> >
>> > "Charles Woll" <cwoll@citlink.net> wrote in message
>> > news:eLW03ONHFHA.2936@TK2MSFTNGP15.phx.gbl...
>> >> I have 2 functions which call Row = Application.Caller.Row
>> >> The 2nd function bombs at this line. Any ideas?
>> >>
>> >> \\\|///
>> >> \\ ~ ~ //
>> >> ( @ @ )
>> >> --oOOo-(_)-oOOo---
>> >> Charlie 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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23AAm9SQHFHA.3076@tk2msftngp13.phx.gbl...
> 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" <cwoll@citlink.net> wrote in message
> news:euFjHOQHFHA.1500@TK2MSFTNGP09.phx.gbl...
>> I am sorry, but I do not know what you mean. I am not very skilled at
> this.
>> charlie
>>
>> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> news:uTVyObNHFHA.3092@tk2msftngp13.phx.gbl...
>> > Does your caller have a row property?
>> >
>> > --
>> >
>> > HTH
>> >
>> > RP
>> > (remove nothere from the email address if mailing direct)
>> >
>> >
>> > "Charles Woll" <cwoll@citlink.net> wrote in message
>> > news:eLW03ONHFHA.2936@TK2MSFTNGP15.phx.gbl...
>> >> I have 2 functions which call Row = Application.Caller.Row
>> >> The 2nd function bombs at this line. Any ideas?
>> >>
>> >> \\\|///
>> >> \\ ~ ~ //
>> >> ( @ @ )
>> >> --oOOo-(_)-oOOo---
>> >> Charlie Woll
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

--

HTH

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


"Charles Woll" <cwoll@citlink.net> wrote in message
news:esTB0zSHFHA.2924@TK2MSFTNGP15.phx.gbl...
> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:%23AAm9SQHFHA.3076@tk2msftngp13.phx.gbl...
> > 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" <cwoll@citlink.net> wrote in message
> > news:euFjHOQHFHA.1500@TK2MSFTNGP09.phx.gbl...
> >> I am sorry, but I do not know what you mean. I am not very skilled at
> > this.
> >> charlie
> >>
> >> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> >> news:uTVyObNHFHA.3092@tk2msftngp13.phx.gbl...
> >> > Does your caller have a row property?
> >> >
> >> > --
> >> >
> >> > HTH
> >> >
> >> > RP
> >> > (remove nothere from the email address if mailing direct)
> >> >
> >> >
> >> > "Charles Woll" <cwoll@citlink.net> wrote in message
> >> > news:eLW03ONHFHA.2936@TK2MSFTNGP15.phx.gbl...
> >> >> I have 2 functions which call Row = Application.Caller.Row
> >> >> The 2nd function bombs at this line. Any ideas?
> >> >>
> >> >> \\\|///
> >> >> \\ ~ ~ //
> >> >> ( @ @ )
> >> >> --oOOo-(_)-oOOo---
> >> >> Charlie Woll
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

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" <cwoll@citlink.net> wrote in message
news:esTB0zSHFHA.2924@TK2MSFTNGP15.phx.gbl...
> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:%23AAm9SQHFHA.3076@tk2msftngp13.phx.gbl...
> > 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" <cwoll@citlink.net> wrote in message
> > news:euFjHOQHFHA.1500@TK2MSFTNGP09.phx.gbl...
> >> I am sorry, but I do not know what you mean. I am not very skilled at
> > this.
> >> charlie
> >>
> >> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> >> news:uTVyObNHFHA.3092@tk2msftngp13.phx.gbl...
> >> > Does your caller have a row property?
> >> >
> >> > --
> >> >
> >> > HTH
> >> >
> >> > RP
> >> > (remove nothere from the email address if mailing direct)
> >> >
> >> >
> >> > "Charles Woll" <cwoll@citlink.net> wrote in message
> >> > news:eLW03ONHFHA.2936@TK2MSFTNGP15.phx.gbl...
> >> >> I have 2 functions which call Row = Application.Caller.Row
> >> >> The 2nd function bombs at this line. Any ideas?
> >> >>
> >> >> \\\|///
> >> >> \\ ~ ~ //
> >> >> ( @ @ )
> >> >> --oOOo-(_)-oOOo---
> >> >> Charlie 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

"Charles Woll" <cwoll@citlink.net> wrote in message
news:ugMdYyRHFHA.3156@TK2MSFTNGP10.phx.gbl...
>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
>
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:%23AAm9SQHFHA.3076@tk2msftngp13.phx.gbl...
>> 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" <cwoll@citlink.net> wrote in message
>> news:euFjHOQHFHA.1500@TK2MSFTNGP09.phx.gbl...
>>> I am sorry, but I do not know what you mean. I am not very skilled at
>> this.
>>> charlie
>>>
>>> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>>> news:uTVyObNHFHA.3092@tk2msftngp13.phx.gbl...
>>> > Does your caller have a row property?
>>> >
>>> > --
>>> >
>>> > HTH
>>> >
>>> > RP
>>> > (remove nothere from the email address if mailing direct)
>>> >
>>> >
>>> > "Charles Woll" <cwoll@citlink.net> wrote in message
>>> > news:eLW03ONHFHA.2936@TK2MSFTNGP15.phx.gbl...
>>> >> I have 2 functions which call Row = Application.Caller.Row
>>> >> The 2nd function bombs at this line. Any ideas?
>>> >>
>>> >> \\\|///
>>> >> \\ ~ ~ //
>>> >> ( @ @ )
>>> >> --oOOo-(_)-oOOo---
>>> >> Charlie Woll
>>> >>
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>
>>
>
>