Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Does your caller have a row property?
-- HTH RP (remove nothere from the email address if mailing direct) "Charles Woll" wrote in message ... I have 2 functions which call Row = Application.Caller.Row The 2nd function bombs at this line. Any ideas? \\\|/// \\ ~ ~ // ( @ @ ) --oOOo-(_)-oOOo--- Charlie Woll |
#3
|
|||
|
|||
I am sorry, but I do not know what you mean. I am not very skilled at this.
charlie "Bob Phillips" wrote in message ... Does your caller have a row property? -- HTH RP (remove nothere from the email address if mailing direct) "Charles Woll" wrote in message ... I have 2 functions which call Row = Application.Caller.Row The 2nd function bombs at this line. Any ideas? \\\|/// \\ ~ ~ // ( @ @ ) --oOOo-(_)-oOOo--- Charlie Woll |
#4
|
|||
|
|||
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" wrote in message ... I am sorry, but I do not know what you mean. I am not very skilled at this. charlie "Bob Phillips" wrote in message ... Does your caller have a row property? -- HTH RP (remove nothere from the email address if mailing direct) "Charles Woll" wrote in message ... I have 2 functions which call Row = Application.Caller.Row The 2nd function bombs at this line. Any ideas? \\\|/// \\ ~ ~ // ( @ @ ) --oOOo-(_)-oOOo--- Charlie Woll |
#5
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... I am sorry, but I do not know what you mean. I am not very skilled at this. charlie "Bob Phillips" wrote in message ... Does your caller have a row property? -- HTH RP (remove nothere from the email address if mailing direct) "Charles Woll" wrote in message ... I have 2 functions which call Row = Application.Caller.Row The 2nd function bombs at this line. Any ideas? \\\|/// \\ ~ ~ // ( @ @ ) --oOOo-(_)-oOOo--- Charlie Woll |
#6
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... I am sorry, but I do not know what you mean. I am not very skilled at this. charlie "Bob Phillips" wrote in message ... Does your caller have a row property? -- HTH RP (remove nothere from the email address if mailing direct) "Charles Woll" wrote in message ... I have 2 functions which call Row = Application.Caller.Row The 2nd function bombs at this line. Any ideas? \\\|/// \\ ~ ~ // ( @ @ ) --oOOo-(_)-oOOo--- Charlie Woll |
#7
|
|||
|
|||
-- HTH RP (remove nothere from the email address if mailing direct) "Charles Woll" wrote in message ... 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" wrote in message ... 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" wrote in message ... I am sorry, but I do not know what you mean. I am not very skilled at this. charlie "Bob Phillips" wrote in message ... Does your caller have a row property? -- HTH RP (remove nothere from the email address if mailing direct) "Charles Woll" wrote in message ... I have 2 functions which call Row = Application.Caller.Row The 2nd function bombs at this line. Any ideas? \\\|/// \\ ~ ~ // ( @ @ ) --oOOo-(_)-oOOo--- Charlie Woll |
#8
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... I am sorry, but I do not know what you mean. I am not very skilled at this. charlie "Bob Phillips" wrote in message ... Does your caller have a row property? -- HTH RP (remove nothere from the email address if mailing direct) "Charles Woll" wrote in message ... I have 2 functions which call Row = Application.Caller.Row The 2nd function bombs at this line. Any ideas? \\\|/// \\ ~ ~ // ( @ @ ) --oOOo-(_)-oOOo--- Charlie Woll |
#9
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... I am sorry, but I do not know what you mean. I am not very skilled at this. charlie "Bob Phillips" wrote in message ... Does your caller have a row property? -- HTH RP (remove nothere from the email address if mailing direct) "Charles Woll" wrote in message ... I have 2 functions which call Row = Application.Caller.Row The 2nd function bombs at this line. Any ideas? \\\|/// \\ ~ ~ // ( @ @ ) --oOOo-(_)-oOOo--- Charlie Woll |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|