Does VBA always finish one line before running the next line?

Leon Lai
Lounger
Posts: 47
Joined: 12 Sep 2021, 14:50

Does VBA always finish one line before running the next line?

Post by Leon Lai »

Hello,

I have a problem, but I cannot give a precise formulation.
It concerns a VBA add-on I have developed. It works correctly most of the time, but occasionally it gives errors.
Rebooting the add-on seems to return everything back to normal.

-----

So, I do not really know where is my problem.
But I suspect something.

------
Suppose a VBA module has just 3 lines, with the foll. pseudo code:
1. Do something
2. Run a (very long) SQL stored procedure and copy it to col. A of a worksheet.
3. Copy Col A to Col B.
------
I understand that the codes are executed the the foll. order: 1 -> 2 -> 3

------

My question is:
Does VBA complete the execution of Line 2 before running line 3.
If this is the case, my add-on should not misbehave at all!
-------------------------
But does VBA start Line 2, and while line 2 is still executing, it starts line 3?

If this situation is possible, then it may be possible that Col A is copied to Col B even before Col A has been fully populated by the stored procedure.

-------------
Can such a situation happen?
How can we prevent it from happening?

Thanks
Leon

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Does VBA always finish one line before running the next line?

Post by HansV »

The SQL Stored Procedure does not run in Excel, so the VBA code may continue before it has completed.
Perhaps Using Stored Procedures with Return Values helps.
Best wishes,
Hans

Leon Lai
Lounger
Posts: 47
Joined: 12 Sep 2021, 14:50

Re: Does VBA always finish one line before running the next line?

Post by Leon Lai »

Hello HansV

But my problem is precisely this!

My add-in will work correctly ONLY if the stored procedure has completed and has put the data in Col. A.

If no data has yet been put in col A, then when VBA runs line 3, it will copy INCOMPLETE DATA! Right? Wrong?
And this is precisely what my users are complaining about! Incomplete data!

If this possibility is real, how can we put a time delay before line 3?
I had used this trick in the past, but as I don't know WHEN the stored procedure ends, I tend to put a longer than necessary time delay - and this obviously slows the program.

I am reading your link on return values. If I find it too difficult to implement, I'll just ask VBA to wait 1 second before running code3. Any other suggestion?


Thanks

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Does VBA always finish one line before running the next line?

Post by HansV »

I hope that it'll be possible to use the return value of the Stored Procedure - this should only become available when the procedure has finished.
But I don't know enough about T-SQL to know if and how this can be implemented from VBA.
Best wishes,
Hans

User avatar
SpeakEasy
4StarLounger
Posts: 535
Joined: 27 Jun 2021, 10:46

Re: Does VBA always finish one line before running the next line?

Post by SpeakEasy »

You might want to investigate Applicaton.CalculateUntilAsnycQueriesDone

Leon Lai
Lounger
Posts: 47
Joined: 12 Sep 2021, 14:50

Re: Does VBA always finish one line before running the next line?

Post by Leon Lai »

SpeakEasy wrote:
19 Nov 2021, 11:51
You might want to investigate Applicaton.CalculateUntilAsnycQueriesDone
Thanks for the tip.
I am working on it to see if it can solve my problem.

Regards
Leon

User avatar
p45cal
2StarLounger
Posts: 142
Joined: 11 Jun 2012, 20:37

Re: Does VBA always finish one line before running the next line?

Post by p45cal »

There should/might be a query property called BackGround, if so it should be set to False.

jstevens
GoldLounger
Posts: 2617
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Does VBA always finish one line before running the next line?

Post by jstevens »

Leon,

Try adding a "DoEvent" after your call to the stored procedure in your Excel VBA code.

Something like:
1. Do something
2. Run a (very long) SQL stored procedure and copy it to col. A of a worksheet.
3. DoEvent
4. Copy Col A to Col B.
Regards,
John

Leon Lai
Lounger
Posts: 47
Joined: 12 Sep 2021, 14:50

Re: Does VBA always finish one line before running the next line?

Post by Leon Lai »

p45cal wrote:
22 Nov 2021, 15:40
There should/might be a query property called BackGround, if so it should be set to False.
Hello,
Thanks for your reply.
I have read a little bit about setting Background to False, but I am not sure it will be appropriate for me.

This is because I am using an SQL Stored Procedure to generate my data. Something like this:
-----------------------------------------------------------------
Sub GetSqlData ()

....
ConnectionStr = "Provider=SQLOLEDB;Password=1234; User ID =sa; Data Source=NEWSERVER; Initial Catalog=XYZLTD"

Set MyRecordSet = New ADODB.Recordset

Source = "EXECUTE [dbo].[Mystoredprocedure1]"

MyRecordSet.Open Source, ConnectionStr

'// All the above are SQL commands (not VBA)

'// When the Stored procedure has finished running, then VBA steps in:

'// Copy data from recordset to Excel Sheet
ActiveWorkbook.Worksheets("Sheet1").Range("A1").CopyFromRecordset MyRecordSet
....

End Sub
--------------------------------------------------------------------

How can I disable Background in this situation? Can it be done in VBA code?

Best Regards,
Leon

Leon Lai
Lounger
Posts: 47
Joined: 12 Sep 2021, 14:50

Re: Does VBA always finish one line before running the next line?

Post by Leon Lai »

jstevens wrote:
22 Nov 2021, 16:39
Leon,

Try adding a "DoEvent" after your call to the stored procedure in your Excel VBA code.

Something like:
1. Do something
2. Run a (very long) SQL stored procedure and copy it to col. A of a worksheet.
3. DoEvent
4. Copy Col A to Col B.
Hello John
Thanks for the tip.

Is there anyway I can test that DoEvent is really waiting for my stored procedure to finish before running the next code in the sub?

My problem is that the case of "missing data" does not occur everytime, but occasionally (and unpredictably!)
I am not even sure if running the (long) stored procedure is the cause of the problem.

This is the type of problem that's hard to sort out!

Best Regards,
Leon

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Does VBA always finish one line before running the next line?

Post by ChrisGreaves »

Leon Lai wrote:
23 Nov 2021, 06:34
My problem is that the case of "missing data" does not occur every time, but occasionally (and unpredictably!)
I am not even sure if running the (long) stored procedure is the cause of the problem.
Hi Leon. I had a similar problem in an unrelated technology; specifically using Selenium" drivers to link VBA code to web browsers talking with search engines.

Roughly and randomly one time in ten, when the VBA code asked for a web page (text) based on a search term, the page was not returned in full.

I wrote a procedure "blnFailed" which was given two parameters:-
(a) The string variable that was supposed to hold the text of the web page
(b) A sentinel string that always appeared at the foot of a page of web in a successful search.
Thus:

Code: Select all

Do
    strWebPageText = myBrowser.ActiveElement.Text
Loop While blnFailed(strWebPageText, strSentinel)
The sentinel would be "Powered by phpBB® Forum Software © phpBB Limited Privacy | Terms", if I were using Eileen's Lounge search engine. (Look at the foot of THIS page!)

When a web page did not get as far as returning the sentinel, I looped back and made a second or third request for it.
My reasoning was that since 9/10 times the full web page was returned, then it was most likely a small delay in the internet wiring (grin) that caused the hiccough, and if I were patient, the text would arrive on the next call.

You might want to consider a similar "sentinel" approach for your intermittent timing problem with SQL.

Cheers
Chris
An expensive day out: Wallet and Grimace

Leon Lai
Lounger
Posts: 47
Joined: 12 Sep 2021, 14:50

Re: Does VBA always finish one line before running the next line?

Post by Leon Lai »

ChrisGreaves wrote:
23 Nov 2021, 09:48

Hi Leon. I had a similar problem in an unrelated technology; specifically using Selenium" drivers to link VBA code to web browsers talking with search engines.

Hi, ChrisGreaves

Maybe your suggestion is not useful in the immediate. But it may come in handy some day.

These days, I am facing some strange problems which are occurring once in a while and very unpredictably.
They are driving me crazy!


Thanks, and Best Regards.
Leon

jstevens
GoldLounger
Posts: 2617
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Does VBA always finish one line before running the next line?

Post by jstevens »

Leon Lai wrote:
23 Nov 2021, 06:34
My problem is that the case of "missing data" does not occur everytime, but occasionally (and unpredictably!)
I am not even sure if running the (long) stored procedure is the cause of the problem.
Leon,

The SQL server usually has a timeout default of 30 seconds. So it may timeout before the stored procedure process finishes.

I would add two lines of code to your VBA:

....
ConnectionStr = "Provider=SQLOLEDB;Password=1234; User ID =sa; Data Source=NEWSERVER; Initial Catalog=XYZLTD"

Set MyRecordSet = New ADODB.Recordset

ConnectionStr.CommandTimeout = 0 '<<<<<<<<<<<< New Line

Source = "EXECUTE [dbo].[Mystoredprocedure1]"

MyRecordSet.Open Source, ConnectionStr

'// All the above are SQL commands (not VBA)

'// When the Stored procedure has finished running, then VBA steps in:

'// Copy data from recordset to Excel Sheet
ActiveWorkbook.Worksheets("Sheet1").Range("A1").CopyFromRecordset MyRecordSet

DoEvents '<<<<<<<<<<<< New Line

....
Regards,
John