COM Automation Loop Error

[jfgeli]

Hi there,

I am experiencing an issue when running a loop on VBA using the applications objects PutSeries or PutGroup. In a nut shell, the logic of the VBA code is the following:

1. Select input sheet
2. Select region
3. Select output sheet
4. Send data to Eviews (I tried putgroup and putseries)

I am looping over that structure becasuse there are many regions. The problem is that after 5 o 7 regions (it´s not always the same but rather a random number) Eviews COM crashes and reports and error. When I am using PutSeries I get the error "This data object is not recognized". If I check in the COM automation Log in Eviews I read PutSeries("CYL_YEN_S01", <data>, "2000 2046", SeriesTypesSeries). Clearly the error is in the second argument. But I don´t undertand why...the error appears randomly after some time. Now, if I used PutGroup I get something different, namely, "Method 'Lookup' of object 'IApplication' failed". I can´t see anything else in the COM automation Log.

In order to create my program I followed the structure presented in the COM Automation documentation in the Eviews website. I was wondering if this might be an issue of the Eviews Manager keeping something in memory. I would like to try switching off the Eviews connection to Excel but I am not pretty sure I know how to do that. I have tried with the command exit (i.e. app.Run "Exit") each time I was running the loop, right after sending the data to Eviews, but it did not work.

I have Eviews 9.5 and specifically Eviews Type Library 9. Excel 2013.

Any help would be really appreciated.

[EViews Steve]

First, I would make sure you are the latest patch installed. The latest build date for EViews 9 is November 14, 2016.
http://www.eviews.com/download/download.html

If EViews crashes in the middle of your loop, you'll get errors like "Method 'Lookup' of object 'IApplication' failed" afterwards. The question is why is EViews crashing in the first place...

Put, PutSeries, and PutGroup methods are all meant to support Excel regions objects as the data parameter - but you might try transferring your region's data into a simple array (e.g. double array) and then passing that in instead to see if that resolves your crashing issue.

I'll need to be able to recreate the error before I can figure out what's going on. Please send me a spreadsheet with your different regions and your VBA code that is causing the problem -- you can either upload them to this forum posting, or your can email them to me at steve@eviews.com and refer to this posting in the email. If I can recreate the error, I'll let you know.

Steve

[jfgeli]

Of course! I am sending you my workfile and edb files associated. I have installed the last patch, I still experience the problem

I just want to stress that this happens when I run several regions/scenarios in the loop. If I just loop over 2 or 3 it does not usually happen. I am not sure what you mean by "you might try transferring your region's data into a simple array (e.g. double array) and then passing that in instead to see if that resolves your crashing issue" Could you give me an example?

In order to recreate the problem just go the "Control" sheet and press the button "Actualizar INPDB". This will start a Sub called Freeze (located in Module Main), which in turn calls another sub, namely, Save_to_DB (located in module Functions). The last one is the sub where I am using PutGroup (commented it´s also a trial I made using the PutSeries method).

I am afraid it is not the most elegantly and efficiently programmed code, partly because I have been trying to figure out what is the problem and I have been changing things. Broadly speaking the sequence is the following:

1) Select sheet "Debt" and pick the region and scenario you are interested (there are 9 scenarios per region and 19 regions). Regions are currently set 1 to 18 and scenarios 1 to 9. (Regions 19 needs also a Matlab rutine, so no need to run it for the sake of my problem).
2) Select sheet "To_EViews", recalculate and copy&paste values. Headers are in range B1:M1 and data are in range B3:M49.

In theory, as long as you saves the Excel Worksheet and the EDB files in the same folder, the paths should automatically work. If not, you can check the settings in the "Settings" sheet.

I am attaching an xlsm file, the prg which should triggered in order to update the db (I know i could update directly the db but for debugging purposes I made it like that) and the edb files.

Thank you for your help.

[EViews Steve]

Ok, I took a quick look at your Save_To_DB method and I have some concerns...

First, the way you call GetApplication with (ExistingOrNew) means it will try to connect to an existing EViews application if there is one already running. Then you call .Show to display the main EViews window. Finally, at the end of the function, you call .Run("exit") to ask EViews to quit.

There are a several reasons you don't want to do this. When you ask EViews to "exit" from your VBA program, EViews has to hide itself but then stick around until your VBA code decides to let go of the Application object (which it still has after calling app.Run("exit")). Because of this, EViews cannot immediately terminate when you call "exit". And with the nature of VBA, your app object won't get deleted and cleaned up (by the VBA garbage collector) until some time later -- meaning EViews will be sticking around for a while.

But since you're calling Save_To_DB in a loop, the function will get called again immediately and when you call GetApplication again, you might be connecting to the instance of EViews that was in the middle of trying to shut down. Now technically this shouldn't happen as an instance of EViews shutting down should not be keeping itself in the Windows COM registry, but the timing of how and when EViews removes itself from the COM registry can be tricky.

In any case, I have a few suggestions:

1. For the best performance, only use a single instance of EViews for all of your calls to Save_To_DB -- don't create and recreate EViews every time as this is very time consuming and uses a lot of resources. Instead, try just calling WFCLOSE at the end of the Save_To_DB function to close your workfile and leave EViews ready to run again for the next loop.

2. If you can't use a single instance, get a new instance of EViews (don't connect to existing instance) every time you run the Save_To_DB by calling .GetApplication(NewInstance). And if you do this, do not make the application visible and also do not call "EXIT" at the end. Simply leaving EViews hidden and then abandoning the app object at the end of your function will tell VBA that you don't need this instance anymore and EViews will shutdown on its own (as long as the window is hidden).

3. If you need to see the EViews window during execution, simply hide it at the end of the function by calling app.Hide (instead of calling app.Run("exit")).

Steve