Calling a recursive Sub in a 'for' loop in another Sub

[excelworker_1]

Hi,

I have written a code of following format -

Please Login or Register  to view this content.

As you see, I have 2 subs Sub1 and Sub2. Sub1 has a 'for' loop inside which Sub2 is called. Sub2 is itself a recursive Sub which keeps on looping till p becomes Nothing. I observed that even before Sub2 finishes its first pass, the index i in Sub1 advances and starts advances to second pass. Can somebody advise me on how to remove this problem?

Thanks a lot,
excelworker_1

[Md Aejaz]

Dear,



Sub aaa()

'does some calculations
Dim a As Long
a = 1
Dim b As Long
b = 100
For i = a To b
Call bbb
Next i

End Sub

Sub bbb()

Dim p
'define p

If IsEmpty(p) Then
'do some calculations
Else
Call aaa
End If

End Sub


as you can see you don't have any loop in sub2.so it not repeat as many any loop.then no times it will in loop will depend on no of times for statement executes in sub 1.

and you are calling sub 1 from sub2 only if your object is not nothing 1.e: it has some object assigned to it.

the whole of this looping will depend on other parts of your code.which is missing.
specially your object variable p.

[excelworker_1]

Hi Md Aejez,

Thanks for the prompt reply. I did not exactly understand what you said. Here is a more detailed version of my code -

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Could you now please guide me?

Thanks a lot,
excelworker_1

[Md Aejaz]

Dear,

you are calling the Sub2 from Sub2 so it is going under loop .

in order to avoid the loop in sub2 remove the last 3rd line(call sub2).

[excelworker_1]

Hmm. But removing the loop will negate the purpose of why I'm doing this. I mean, I want to loop through Sub2 till p is set to Nothing and once that happens, I want to exit the Sub2. That is why I've written the code that way.

[rylo]

Hi

Why don't you just loop through whatever it is that you are doing to set the unionrange and process each one on its merits?

rylo

[shg]

I agree with rylo -- recursion is always a choice, not a requirement. There is nothing that can be achieved by recursion that can't be achieved by iteration -- maybe a shade less elegantly, but probably faster in execution.

[excelworker_1]

True. But I tried the following iteration-type loop too -

Please Login or Register  to view this content.

This one doesnt work either. It goes in a perpetual loop and Excel gets stuck.

[shg]

That won't work at all -- unionrange is not initialized.

If it were initialized, and the value was found, it would loop forever because the loop doesn't do anything to affect the contents of the range, so the same value would be found repetitively (unless you have so NOW() formula in there).

Rather than beating around hypothetical code fragments, why not just explain what you're trying to do?

[excelworker_1]

Sorry for the typo. I forgot to add initialization of unionrange1 in that. In my actual code, I have in fact initialized unionrange1 and assigned a value to it.

[Md Aejaz]

Sub Sub2()
Dim p as Variant
Set p = unionrange1.Find("#N/A Requesting Data...", LookIn:=xlValues)
Dim n As Long
if p.cells.count <> 0 then
for y= 1 to p.cells.count
unionrange1(y).Calculate
DoEvents
next y
end if
'then do some calculations
Exit Sub





Try this

[shg]

In my actual code, I have in fact initialized unionrange1 and assigned a value to it.

That doesn't diminish my second point, and reinforces the third.

[excelworker_1]

Ok I'm sorry. Here I'll try to explain my attempt a bit better -

I have a folder with a main workbook and 200 other workbooks. In Sub1, the 'for loop' loops through each workbook, opens it, does some calculations, then calls Sub2 (which is used for pulling data from Bloomberg), then does some calculations, saves and closes that workbook and moves to next workbook.

Thus for Sub2, I cannot do these 'some more calculations' until all data has been pulled from Bloomberg for the workbook in question. Hence by the code which I've written, I've tried to introduce a mechanism which ensures *all* data has been pulled (and saved in cells in unionrange1) and then proceed to do the calculations.

Hope this puts my code and doubts in perspective now. Please help me. I've spent a lot of time in past 3-4 days trying to finish this, but have failed.

Thanks,
excelworker_1

[shg]

How does the data come in from Bloomberg? What's the code to do that?

[excelworker_1]

Just above "Call Sub2" in the for loop in Sub1, I have "Call FetchBloombergData" where FetchBloombergData is a Sub. The code is as follows -

Sub FetchBloombergData()

Worksheets("DataSheet").Cells(2, 31).Formula = "=bds(" & Chr(34) & Worksheets("DataSheet").Cells(2, 1) & " IS Equity" & _
Chr(34) & ", " & Chr(34) & "dvd_hist" & Chr(34) & ")"
End Sub

Basically I've 'hard-wired' Excel 's Bloomberg formula into VBA code. I have checked if there is any error in this code, and there isn't. It does manage to pull data successfully from Bloomberg.

[romperstomper]

For context see this thread.

[excelworker_1]

Thanks for the link romperstomper. Hope it will help others understand this problem better. I tried all suggestions made by you (and also some stuff on my own), but have not been able to figure this out.

[shg]

Why wouldn't you have just continued in that thread instead of starting a new one?? It shouldn't be necessary to have 15 cat-and-mouse posts to drag out what you're trying to do -- again.