UKH

A question for this parish.

I have a excel macro which is solid and stable but takes around 30 mins to run.

If I try an open another excel document it causes a run time error and macro crashes.

Is there any way I can have vba running in background and use another Excel file without the crash?

Depending on how the code is written, you might get away with opening another copy of Excel before you run the VBA and be able to use that.

You may also be able to open a seperate instance of Excel, rather than trying to open another file into the same instance - try to do this by opening Excel once your code is running, then trying to open a new file in that second version of Excel - not by double clicking the spreadsheet you want to open in Windows explorer as by default that will try to open into the version of Excel that is already open and is running your VBA.

Other than that, if its feasible use a 2nd PC or set your 30min code to run overnight when you're not trying to work.

If none of Chris' excellent suggestions above work out, other weird workarounds might include:

• Running either the VBA task or another instance of Excel in a virtual machine
• Trying out whether the crash still happens in an older or newer version of Excel
• Installing an older or newer version of Excel alongside your existing version
• Using the browser version of Excel alongside the desktop version
• Using one of the free software alternatives to Office e.g. Libre Office

For some reason Microsoft changed excel around 10 years ago so that it doesn't open a second instance of Excel when opening a second file as it used to. Still annoys me to this day. Chris's suggestion should work i.e. open 2 instances of excel

You could possibly create another windows account and switch user.

Redevelop it in Access

Thanks all, I’ll try the suggestions.

Is there a way to code the Macro which locks down the session in which it’s running? 

probably. question 1 would be "should this really be taking 30 minutes to run". Unless you are doing something industrial scale then the amount of processing that can be done in half an hour is immense so it is more than likely that this can be improved.

In windows if you shift-click to open excel from the start menu you might get a separate instance of Excel that doesn't interfere with the first.

It’s scraping data from from one system into excel. Ideally would code directly to the other system but unsure if it possible 

If its crashing when you open a new document it indicates to me that you my be referencing the first workbook/worksheet with "ActiveWorkbook". Try instead to directly reference the file by declaring a workbook variable.

If you are running a recent version of Excel and the task is scraping data from an external source look at Power Query (Get & Transform) as an alternative. Much easier to learn and modify compared to VBA.

30 minutes seems excessive for a macro, is it running post Brexit scenarios?

The easy option is to make sure the code is using Excel efficiently. For example can you turn the automatic calculation off and is screen updating turned off?

30 minutes running time is extraordinary. The smart way to run Excel for complex tasks is to write all the computations in the underlying VBA and just use Excel worksheets for input and output. You will probably find the macro then runs about ten thousand times faster than before. 

If you're going to redevelop it, might as well do it in something that's actually good - say python.

Standard practice whilst it's running is to go for a fag, or a poo, and chat to a couple of colleagues on the way back.

Uninstall excel (or use it as a typing interface only). Install Rstudio. Program your macros in R instead. Or, if commercial, get a matlab licence.

Excel will only get you so far, and becomes unbearably slow above a certain table size. I have some files in excel (as I have too, not voluntarily...) that are around 20k lines by 100 columns, and finding a single unique entry can take up to 10sec depending on ow much other stuff I am running.

Starting Excel with the flag "/x" on the command line will open a new instance of Excel, separate from the one you already have running. This option works for Excel 2013 and 2016.

Just create a shortcut to the Excel executable on your desktop or taskbar, something like this for the 32 bit version of Excel 2016:

"C: | Program Files (x86) | Microsoft Office | Office16 | EXCEL.EXE" /x

Click / double-click the shortcut when you want to open a new Excel instance while your macro is running.

Edit: Apparently backslashes are not allowed here - replace the " | " with backslashes

I realise this diverges from your original question...

If its pulling data off of somewhere else then what's the latency between your spreadsheet and it? I've something I wrote that accesses a back-end with a rest api and the latency of that absolutely kills it.  Thats with Application.ScreenUpdating = False turned on and minimal dependent macros (its mostly vba)

I'd use something else but excel is handy for some bits and pieces that need no coding.