2

Currently, I have a spreadsheet which uses an external program to do calculations (let's say it's an aerodynamic calculation tool). In Excel VBA, I update the inputs to the program via a UDF at the spreadsheet level ( =input("var1",1.234)).

Then, using VBA, I write the input file, run the external .exe, and read the output file. Outputs are again reported to the spreadsheet level via a UDF (=output("var2"))

I would like to use the Excel Solver function to make an optimization of the spreadsheet. Here, I would like it to change the input values ( via the =input() function) to get the optimum in the outputs (via the =output() function). The solver should be able to change the inputs, wait for the new calculation to be made (via the external .exe) and then resume when it gets the outputs.

So far I've found no reliable way to make it work. The Excel solver does not know that it needs to wait for the .exe to finish, even when I embed the shell(.exe) command in the input UDF....the solver still plows through as if nothing was happening.

Expectedly, it comes up with bunk since the output is out of sync with the input. I've pried open the SOLVER.XLAM (...ahemm..) to find a way to insert a wait statement, but that wasn't much help; the interface ends where the VBA interfaces with the SOLVER.dll, which places hooks into Excel to run the Excel calculation directly from the .dll ...so no help to me.

So far I'm stumped. Nobody seems to have encountered this before. I've tried looking into OpenSolver.org version, but they also end at some mysterious .dll or .exe interface. Any ideas?

(BTW: I have no access to the external program code, if you're wondering.... an .exe and some text files is all I get).

RexBarker
  • 1,456
  • 16
  • 14
  • With .vbs script: Pause(10) 'To sleep for 10 secondes Sub Pause(NSeconds) Wscript.Sleep(NSeconds*1000) End Sub – Hugues Paquet Blanchette Aug 18 '15 at 14:31
  • This may help [link](http://stackoverflow.com/questions/15951837/excel-vba-wait-for-shell-command-to-complete) --- it describes having the vba code wait for the shell process to complete – xidgel Aug 18 '15 at 15:46
  • Thanks for the ideas, but the problem is not to make the VBA wait on the .exe....this is already done and it functions fine. Currently, it uses a method which executes the shell command and then waits for the process handle to be released. The issue is that the SOLVER.dll manipulates the UDF inputs directly (the ***=input()*** and ***=output()*** functions). This causes a problem because UDFs cannot provide a correct response without external .exe execution; I believe it disrupts the normal VBA flow control which allows for other process to continue (such as Shell() commands) – RexBarker Aug 19 '15 at 07:13

1 Answers1

0

OpenSolver should work if you use the NOMAD solver, which does all calculations directly inside the spreadsheet. See http://opensolver.org/non-linear-nomad-integration/ for more information.

user364130
  • 1
  • 1
  • Is there an easy way to get this "OpenSolverNomad.dll" which it needs? I've just spent the past hour trying to get it to compile from the GitSource hub...but I got frustrated and gave up (I'm no Visual Studio wiz). Apparently due to some complicated licensing stuff with the ExcelXLLSDK, they don't put it directly...you have to build it yourself :-| – RexBarker Sep 04 '15 at 14:02