Recursion in VBA macros?

[Peter Chatterton]

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

 

[Bob Phillips]

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]

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

 

[Guest]

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

 

[JE McGimpsey]

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

 

[Peter Chatterton]

Thanks, that's much better than recursion.
Peter

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