Unstable macro when continuously calling Application.Run

**Donitz** · 09-24-2013, 04:57 AM

Hello!

I just joined this forum in hopes that one of you may have a solution to this rather bizarre problem.

What I'm basically trying to do is continuously update an Excel spreadsheet by calling VBA from the scripting language (VBS) in a rather locked down application. I'm using a loop in VBS to use xlApp.Run MyVBAMacro to pass information to Excel in real time. This works all fine and good, but the problem is that if you were to for example change the cell or spreadsheet in Excel while the loop is running, it will crash. This is really inconvenient as you cannot browse the workbook at all while the VBS script is running.

I've found that setting ScreenUpdating to False prevents it from crashing, but then you still cannot browse the workbook (and if the script is interrupted you're stuck with ScreenUpdating False). Does anyone have an idea why calling xlApp.Run would crash when you're manipulating the called workbook?

**OllieB** · 09-24-2013, 05:08 AM

From what I gather you are using a VBS script that opens a workbook in Excel and runs a macro in the Excel host application to perform updates.

I have similar solutions that work and are very stable.

The only thing to remember though is that Excel is single threaded and making changes to the opened workbook at the same time as the macro is running is not good practice.

In my own solutions the Excel application and workbook are opened in memory (visible = false) to prevent conflicts, and I use specific cells to communicate the status of the workbook to the VBS script and vice versa.

**Donitz** · 09-24-2013, 05:18 AM

I do something similar. I'm using Get/Let properties linked to Excel cells to save and load the status between each VBA call. Excel optimizes various parameters which are returned to VBS.

I need some way to tell what the current status is when it's running. Just leaving the correct spreadsheet open works since it allows you to watch the cells update between each call, but I can't fit all the relevant information on a single screen so I was hoping it would be possible to browse between spreadsheets while it's running. I think it's a bit odd that even that causes the script to crash.

**OllieB** · 09-24-2013, 05:29 AM

Like I said; in essence Excel is a single threaded application and can only do one thing at a time. I use an Excel control/console panel to report on progress; so

1. start VBS script
2. open Excel console
3. use VBS scripts to open multiple children passing a reference row of the Excel console interaction/status row
4. each child opened retrieves a pointer to the Excel console (was the first Excel instance opened thus also always returned via getObject)
5. each child opens their own Excel workbook
6. each child communicates with the Excel console using the reference row passed, including progress made
7. console uses reference row per child to pass instructions to child, child uses event or timer to read instructions and execute