Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recursion in VBA macros?
I'm writing macros under Excel 2003/XP on a fairly powerful PC to process
financial data. When I'm searching an almost full column for the next non-blank entry I don't have any problem using recursion, but when the column is mostly blank I wonder whether I'm doing the right thing. Are there any guidelines for using recursion under VBA? I use instance variables instead of locals, but that's about all I know. Thanks for your help, Peter |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recursion in VBA macros?
Why would you use recursion for the next blank row?
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Peter Chatterton" wrote in message ... I'm writing macros under Excel 2003/XP on a fairly powerful PC to process financial data. When I'm searching an almost full column for the next non-blank entry I don't have any problem using recursion, but when the column is mostly blank I wonder whether I'm doing the right thing. Are there any guidelines for using recursion under VBA? I use instance variables instead of locals, but that's about all I know. Thanks for your help, Peter |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recursion in VBA macros?
I'm calling 'getNextRow' and everytime it hits a blank row it calls itself
again. Public Function getNextRow() As Boolean lRow = lRow + 1 If lRow <= lMaxRow Then getNextRow = True Set r = rFundNameCol.Cells(lRow) s = r If s = "" Then _ getNextRow Else getNextRow = False End If End Function "Bob Phillips" wrote in message ... Why would you use recursion for the next blank row? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Peter Chatterton" wrote in message ... I'm writing macros under Excel 2003/XP on a fairly powerful PC to process financial data. When I'm searching an almost full column for the next non-blank entry I don't have any problem using recursion, but when the column is mostly blank I wonder whether I'm doing the right thing. Are there any guidelines for using recursion under VBA? I use instance variables instead of locals, but that's about all I know. Thanks for your help, Peter |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recursion in VBA macros?
A couple of things... One is that I would be inclined to avoid the instance
varables and stick with locals. The instance variables ( I assume you mean globals) can be quite difficult to debug. As for the first blank cell you can do that something like this dim rng1 as range 'First blank dim rng2 as range 'Last Blank Sheets("Sheet1").select set rng1= Sheets("Sheet1").Range("A1").end(xlDown) msgbox rng1.address set rng2 = Sheets("Sheet1").cells(rows.count, "a").end(xlUp).Offset(1,0) msgbox rng2.address -- HTH... Jim Thomlinson "Peter Chatterton" wrote: I'm writing macros under Excel 2003/XP on a fairly powerful PC to process financial data. When I'm searching an almost full column for the next non-blank entry I don't have any problem using recursion, but when the column is mostly blank I wonder whether I'm doing the right thing. Are there any guidelines for using recursion under VBA? I use instance variables instead of locals, but that's about all I know. Thanks for your help, Peter |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recursion in VBA macros?
That's incredibly inefficient, given the overhead that calling a
function requires. It also requires tons of stack space. A simpler function would be: Public Function getNextRow() As Boolean For lRow = lRow + 1 To lMaxRow If rFundNameCol.Cells(lRow).Value < vbNullString Then Exit For Next lRow getNextRow = lRow <= lMaxRow End Function In article , "Peter Chatterton" wrote: Public Function getNextRow() As Boolean lRow = lRow + 1 If lRow <= lMaxRow Then getNextRow = True Set r = rFundNameCol.Cells(lRow) s = r If s = "" Then _ getNextRow Else getNextRow = False End If End Function |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recursion in VBA macros?
Thanks, that's much better than recursion.
Peter "JE McGimpsey" wrote in message ... That's incredibly inefficient, given the overhead that calling a function requires. It also requires tons of stack space. A simpler function would be: Public Function getNextRow() As Boolean For lRow = lRow + 1 To lMaxRow If rFundNameCol.Cells(lRow).Value < vbNullString Then Exit For Next lRow getNextRow = lRow <= lMaxRow End Function In article , "Peter Chatterton" wrote: Public Function getNextRow() As Boolean lRow = lRow + 1 If lRow <= lMaxRow Then getNextRow = True Set r = rFundNameCol.Cells(lRow) s = r If s = "" Then _ getNextRow Else getNextRow = False End If End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Abort recursion | Excel Programming | |||
Recursion | Excel Programming | |||
Worksheet_Change Recursion ARGHH! | Excel Programming | |||
recursion depth, 'Out of stack space' in Quicksort | Excel Programming | |||
Recursion with user defined functions | Excel Programming |