Vba: the best way to repeat a macro indefinitely

[Nelson78]

Hello everybody.

I have to scrape data from a website.
This website is constantly updated, so my activity of scraping must be carried out without interruption.
In vba terms: the macro must be repeated indefinitely, day and night.

Now, I've been using the following code:

sub macro1

'....... activity of scraping (no problem in this part of the process)

call macro2

call macro1

end sub

macro2 is only needed to reset the data receiving sheet.


The problem is: the process stucks after a number of repetition (approximately from 5 to 10), probably at the end of the sub (it seems to me that the instruction "call macro1", at a certain point, is not processed).

Could somebody suggest me a safer way to repeat the macro?

 

[Jeffrey Mahoney]

Please try this. As you have found out, calling the SUB from the same SUB eventually gets VBA tied into a knot. Using OnTime should prevent that because Macro1 will have had time to complete before calling it again,

Sub macro1()

'....... activity of scraping (no problem in this part of the process)

Call macro2

Application.OnTime Now() + TimeSerial(0, 0, 10), "macro1", Now() + TimeSerial(0, 0, 20), True


End Sub