limited # calls to 3rd party DLL allowed

[gte647i]

I am using a 3rd party hydraulic modeling program that provides its computational engine in a DLL and associated VBA module that provides the desired functions from the DLL to VBA.

I have a macro that I wrote that iterates on rows of a spreadsheet. Each row contains some inputs. At each row, the macro will load the model engine using the provided VBA commands, make some adjustments to the inputs, run the model engine using the VBA commands, and then write the outputs to Excel on the given row.

This works perfectly for a limited number of times. But inevitably a point is reached where the macro can no longer successfully load the model engine. I get no error messages, but all calls to the engine return zero values.

This happens two different ways. The first is in the middle of an attempt to run many iterations, but it also happens if I run small chunks. For example, it may happen in the middle of a run on 200 rows, but it will also happen eventually even if I run the macro on 10 rows at a time. In other words, the small 10 row runs will be successful for awhile, and I will save the workbook between each run. But eventually it will still reach a point where a small 10 row run will start generating all zero outputs. The only way I have found to fix this is to close the workbook and open it again; then I can start from where I left off and continue until the next point that I start getting zeros. (Upon reopening, I can successfully complete the row that I got stuck on the last time, so it's not a matter of bad inputs or bad code, in that sense.) But the rows are many and the workbook is large (takes long to open), so this is not feasible.

Could this be some kind of memory leak? I'm baffled, as it persists even across multiple macro runs. I would have thought that once one macro run completed successfully, all memory and everything would be cleared, and running the macro again on the next set of rows would be a fresh start. Apparently not... Something keeps piling up and closing the workbook is the only way to refresh it (that I know of at this point).

I have scoured the web for a solution and have found none. It's possible I don't know the right terms, since I'm not very familiar with DLLs in VBA.

Best guesses are very welcome; I'll try anything at this point.

 

[Richard Schollar]

Can you ask for technical support from the dll/VBA provider? Maybe it's something they are aware of...

 

[gte647i]

Unfortunately, I cannot. The software was created by the EPA, but no support is provided.

However, I think I have solved the problem, although I'm still a bit confused. There are two main commands used to open the software: one opens the "toolkit" and loads a given file; the other opens the simulation engine.

I thought that originally I had all of these open/close commands in my loop, i.e. each loop opens the toolkit, opens the simulation engine, runs simulations, accesses outputs, closes the engine, and finally closes the toolkit. Then it all starts again.

This would work running a single iteration and all the way up to 200-300 iterations (not the exact same number each time, and I could either do multiple macro runs of, say, 30 iterations or one macro run attempting several hundred). But then it would inevitably stop working, i.e. calls to the DLL simply were being ignored, returning all zero values but no error messages.

So I tried pulling some of the open/close commands out of the loop in an attempt to only open the toolkit and engine once. And it still crashed. Long story short, I went back to the original method of doing all the opening/closing of the DLL every time I go through the loop, and it worked!

So I think I must have had one or more of the open/close commands for the toolkit and engine outside the loop or something and I didn't realize it.

The confusion that remains, however, is that I could run the macro successfully a limited number of times. So if I had some command out of place, it wasn't crashing Excel every time or giving me zeros every time. I could run up to a few hundred iterations 100% successfully. It only happened when I had run enough iterations (even with several different calls to the macro) that it reached some strange breaking point.

In any case, I'm thrilled to have this solved if not understood. This problem goes all the way back to last year when it first started stumping me on another project I was doing.

Thanks for your concern. If anyone has further questions I can be reached at my username + gmail.