Synchronizing VBA Script

[Guest]

How can I force the VBA script in Excel to wait till an Excel add-in function
completes retrieving data asynchronously from an external data source. I
tried Msgbox statement; but the OK button becomes active before the data
retrieval is completed.

 

[Gareth]

Do you have a way of knowing when the data retrieval has been completed?
If so, something like

'Retrieve data

Do
DoEvents
Loop Until AllDataReceivedTest

 

[Guest]

It is not possible to know whether data retrieval has been completed; data
just gets populated in various cells. Excel status bar shows the progress
such as "Calculating Cells : 75%".

 

[Gareth]

Providing Excel doesn't finish calculating until the data transfer has
finished... you could do something like:

'Wait until sheet finishes calculating
do
Doevents
loop until Application.CalculationState <> xlDone

That should wait until it's finished calulating. Of course, you need to
ensure it starts calculating in the first place. Although it seems to be
working now you might like to throw in an

Application.calculation = xlCalculationAutomatic

or at least check it is in your code before launching the import

or maybe check the current settting, set it to automatic and then change
back if necessary.

I haven't tried all this but I hope it works.

Gareth

 

[Guest]

I tried the logic you had suggested; but the control comes out of the do
loop before the data retrieval is completed; Excel calculation may be
completing before the data transfer is over. Could you please provide any
other option ?

Venu

 

[Gareth]

Umm.... without knowing more about the retrieval process it's difficult
to say.

- Is this an AddIn you wrote (and therefore have access to the code)?

- Where is data being retrieved from and how?

- What code is running that need to wait? Is it some code that calls
the retrieval process? An event that runs?

G

 

[Guest]

1. Add-in was supplied by the vendor and we don't have access to the code.

2. Data is being retrived from a database application which is running on
MS SQL server. The add-in function is used in Excel cells as formula
{=fn(input1, input2,..) }
which automatically recalculates when input1, input2 etc. change.

3. The code is refreshing a few pivot tables to properly arrange the data
retrieved from the database. Hence the code should run only after the
retrieval is completed.

Regards,

Venu

 

[Gareth]

OK - I think we're getting there... So, the AddIn provides a UDF which
(since it's a UDF) can only update its container cell.

My next question would be therefore, what is triggering the code to
refresh your PivotTable? Why is this firing before your data retrieval
is complete? Are you using events?

Without the answers to the above questions, I would guess you're using
an event to fire when any cell containing this UDF updates. However, if
you have multiple cells using the UDF and want to wait for all the cells
to be updated then this could be a headache e.g.

Cells with Input Data
A1 B1 C1 D1

Cells with the UDF (with above cells as arguments)
A2 B2 C2 D3
A3 B3 C3 D3
A4 B4 C4 D4

A1 is manually updated:
Cell A2 updates - Pivot table refreshes
Cell A3 updates - Pivot table refreshes
Cell A4 updates - Pivot table refreshes
etc.

What you want is for ALL of the cells to be updated and THEN for the
refresh to run. Have I understood the problem properly now?

 

[Guest]

Program execution flow is almost same as described by you.

- User updates A1 manually.
- A2,B2,C2 etc get updated automatically (there are around 5000 cells); this
is an asynchronous operation.
- Hence the code execution continues and updates pivot tables whose inputs
are A2,B2, C2 etc. This happens before A2,B2 etc are updated by the
asynchronous UDF.

We tried including a msgbox statement just before the pivot table update
statement. Sometimes the OK button of the msgbox statement gets activated
only when the data retrieval is completed and the program works perfectly;
but this does not happen always.

Regards,

Venu

 

[Guest]

Code written for refreshing the pivot tables is as follows:
{
NumTables = Worksheets("Pivot").PivotTables.Count
For i = 1 To NumTables
Worksheets("Pivot").PivotTables(i).RefreshTable
Next i
}
I am not using any events. Code is triggered by a command button. When the
code starts execution the following happen:
1. Contenets of A1,B1 etc are modified.
2. This causes UDF cells A2,B2 etc to retrieve new values from the database
3. Parallely, code continues execution to refresh the pivot tables.
4. Pivot table refreshing finishes before the data retrieval in step 3 is
completed.

Earlier, the msgbox statement (which is included between step 2 and step 4)
was sufficient to provide the necessary delay (since the OK button was
becoming active only when the data retrieval is complete).

I will try what you have sugguested and let you know the outcome.

Thanks & Regards,

Venu

Just to be clear then:

UDF - retrieves data.

Your VBA code - updates Pivot Table.

What I don't understand is this - what kicks off your pivot table
update? I assume it is the VBA code that you have written that performs
this task (refresh) - if so *how do you trigger it*?. Are you using
events? This would appear to me the crux of the issue.

I'm assuming (again) that you are using the Worksheet_Calculate event to
trap the updating of your UDF cells. If this is the case I would propose
something like the following (placed in the respective worksheet module)
would do the trick:

Private Sub Worksheet_Calculate()
Dim p As PivotTable

Application.EnableEvents = False
Set p = Me.PivotTables(1)
p.RefreshTable
Set p = Nothing
Application.EnableEvents = True
End Sub

[Note - without the disabling events the above will fire once for every
cell, so they're pretty crucial here.]

This works on the example I set up for myself. I am understanding
correctly and if so.... does this work for you?

G

Program execution flow is almost same as described by you.

- User updates A1 manually.
- A2,B2,C2 etc get updated automatically (there are around 5000 cells); this
is an asynchronous operation.
- Hence the code execution continues and updates pivot tables whose inputs
are A2,B2, C2 etc. This happens before A2,B2 etc are updated by the
asynchronous UDF.

We tried including a msgbox statement just before the pivot table update
statement. Sometimes the OK button of the msgbox statement gets activated
only when the data retrieval is completed and the program works perfectly;
but this does not happen always.

Regards,

Venu

: