Skip to main content
D
DM_1012731612-Amethyst
12-Amethyst
January 17, 2022
Solved

API: Excel and Prime. VBA timing issues?

  • January 17, 2022
  • 1 reply
  • 2061 views

Hi,

Following my previous thread, I can successfully pass data from Excel to Mathcad, calculate, and then take the outputs back to Excel. Thank you @terryhendicott  !!!

 

This is a summary of the code I am using.

- Get inputs, Aliases, units from Excel.

- Open Prime and worksheet.

- Get Prime Input Aliases.

- Loop through each Prime Input Alias and match them with Excel Aliases. If there is a match, add the corresponding value and units to Prime.

- Calculate

- Obtain Prime output as a matrix.

- Loop through each Output element, store its value and send the element back to Excel.

 

 

This is the weird part that I can't figure it out.

 

If the program runs by clicking the ActiveX Command Button, the Outputs are not added to the Excel.

If the program runs in VBA and there are break stops, the program adds the Outputs to Excel as intended.

 

The minimum number of break stops seems to be two. One after the I get the Outputs and one after the loops that moves the Output data back to Excel.

 

This issue indicates that there is a timing issue between VBA and Prime. This is what I have tried:

1.- Increase DefaultCalculationTimeout to 600 seconds

2.- Add Application.Wait(Now + TimeValue("0:00:30") after each break stop.

3.- Add Application.Wait(Now + TimeValue("0:00:2") inside each loop to slowly pass the data.

 

Unfortunately, none of that seems to work. Any ideas on how to tackle this issue?

Regards,

 

Best answer by DM_10127316

Hi, thank you for your response. I managed to solve it. Hope this can help someone else.

 

The problem was that VBA tried to communicate while Prime was still calculating. This occurs in heavy worksheets, where Prime takes several seconds to resolve. In my particular case, the worksheet took at least 90 seconds to finish. Through VBA, I usually pause the calculations after opening the Prime file, transfer the values and units from Excel, and resume and synchronise Prime. When it is synchronising, Prime will calculate the worksheet using the new inputs. But VBA will immediately try to get the outputs.

 

The solution for me was to place Application.Wait (Now + TimeValue("0:xx:xx")) after WS.Synchronize. This solution didn't work before because the time I setup was too low and because I paused after opening Prime and not when it was calculating.

1 reply

T
21-Topaz II
terryhendicott21-Topaz II
21-Topaz II
January 17, 2022

Hi,

Never had this problem but clearly it exists.  Thought hard about how I would handle it.

 

One suggestion would be to break up the process into a number of subroutines.

Use Public variables in the Declarations section that can be shared between subroutines.

 

Create a form with a command button and associated textbox for each step(subroutine).

The last statement in each subroutine is to put text into the relevant text box that the step is finished.

In this way you are using the existing event manager loop of the form to synchronize the bits of the full process.

 

Capture.JPG

Cheers

Terry

    D
    DM_1012731612-AmethystAuthorAnswer
    12-Amethyst
    January 20, 2022

    Hi, thank you for your response. I managed to solve it. Hope this can help someone else.

     

    The problem was that VBA tried to communicate while Prime was still calculating. This occurs in heavy worksheets, where Prime takes several seconds to resolve. In my particular case, the worksheet took at least 90 seconds to finish. Through VBA, I usually pause the calculations after opening the Prime file, transfer the values and units from Excel, and resume and synchronise Prime. When it is synchronising, Prime will calculate the worksheet using the new inputs. But VBA will immediately try to get the outputs.

     

    The solution for me was to place Application.Wait (Now + TimeValue("0:xx:xx")) after WS.Synchronize. This solution didn't work before because the time I setup was too low and because I paused after opening Prime and not when it was calculating.

    Terms & ConditionsAccessibility statement