I have a series of automated calls to a Mathcad sheet from Excel (via a VBA module). The Mathcad sheet is called several times in a loop, each call referring to the values in successive cells on the Excel sheet. The Mathcad sheet takes a number of seconds to recalculate, and I occasionally get the previous calculation result returned to Excel rather than the current one (that is, Excel "runs ahead" of Mathcad in the loop). Can Excel be forced to wait until Mathcad has fully recalculated, without using the (next to useless) VBA Wait function? Thanks
On 7/17/2006 12:31:33 PM, manderson wrote: >I have a series of automated calls to a Mathcad sheet from Excel (via a VBA module). The Mathcad sheet is called several times in a loop, each call referring to the values in successive cells on the Excel sheet. The Mathcad sheet takes a number of seconds to recalculate, and I occasionally get the previous calculation result returned to Excel rather than the current one (that is, Excel "runs ahead" of Mathcad in the loop). Can Excel be forced to wait until Mathcad has fully recalculated, without using the (next to useless) VBA Wait function
One possibility might be to add an extra input and output parameter to the Mathcad sheet (representing the cell row 'number', for example); get Mathcad to return the number without change, and check to make sure you're getting the 'call' number that you expect - recall if necessary.
I am using Mathcad 12.1 and Excel 2002 service pack 3.
I could re-write the worksheet to input an array. However, I am calling a quality-assured worksheet repeatedly to generate sensitivity plots with given independent variables. It is much easier to do this from Excel than it would be to re-configure the worksheet for a bespoke analysis, and get it fully checked and quality-assured.
Stuart's suggestion works well. I generate a random number in VBA, assign it to an input variable then read it back as the last statement in the worksheet, without modifying any of the technical coding. The sheet is then recalculated within a VBA loop until the value read back agrees with the value input (not efficient, but efficient enough for current purposes). Excel will call MathCAD 8 to 12 times before the recalcuation is completed fully. A more elegant solution would be welcome.
I can't post the sheets I am working with, but I can try and generate something (tomorrow, I'm afraid) that exhibits the same behaviour.
IOt has never been clear on when the automation interface considers a function complete and returns control. But .... Is the sheet configured for auto-calc? Are you setting multiple variables for a calculation, or just one? If you are setting multiple variables try with auto-calc off and just the recalculate method. If only a single variable, try with autocalc on, and without the recalculate method (with auto-calc on setting a variable is supposed to trigger a recalculation).
See the attached excel sheet illustrating my point. If you have a lot of background processes running at the same time, it takes several calls in the VBA loop to ensure the MathCAD sheet is fully calculated.
As you can see, I am recalculating from VBA rather than automatically (naively assuming it didn't make any difference).
I'm wondering about the "recalculated" event associated with the Mathcad worksheet, and whether that might be part of a solution, but if I have to start defining VBA event classes I'll lose all the benefits of simplicity.
Sorry, I forgot to activate the Mathcad sheet in VBA in my example. Please double click the embedded sheet before going back into Excel and running the macro (otherwise you'll get an error). You should see that it takes a number of calls to Mathcad to get the "check value" written into Mathcad to agree with the value read back out.
I can't get your sheet to run on my system. I keep getting a variety of errors. But I did notice that the Mathcad sheet has automatic calculations turned on. Since you are using the recalculate method explicitly, that should be off. How does the sheet work when automatic recalculation is turned off?
If you can read a file from Excel, then why not WRITE... the Mathcad result in file and have your VBA update on Mathcad returning file ?
Or without any VBA at all: if you are in Excel and click on the Mathcad component, that will update the file that Mathcad is writing and that Excel is reading and on a cell change it should update all depending cells in Excel . And not knowing the complexity of the calculations passed to Mathcad , who can guess wether or not Excel could do it on its own ?
Automatic calculation should be turned off (I may have left it on by mistake when playing around with it). If automatic calculation is on, and I comment out the explicit recalucation, I get very similar behaviour to the opposite condition. If the sheet is both explicitly recalcuated and automatic calculation is on, the whole process slows down, and requires fewer "calls" to fully recalculate, but (at leaset with my original sheet) still requires more than one "call" to reliably fully calculate.