1

This is a question that I have always had but never really gave it much though.

What I have at the moment is a worksheet that displays data, and the user refreshes whenever needed. So:

  1. User triggers a VBA Function
  2. VBA Function gathers data and analyses WHILE USER WAITS
  3. VBA Function dumps the result on the spreadsheet
  4. User continues viewing data

Since the data analysis is all done internally in VBA (No use of workbook, only recordsets, arrays, library etc.) I wanted to somehow be able to allow the user to continue viewing the original data, while VBA works on getting and analyzing new data.

I know you cant use the workbook AND run VBA at the same time, but you can however, have two excel instances and work on one workbook while the other runs VBA.

So could I somehow have my original excel instance call another excel instance and have it run the VBA while I work on my first instance?

Any Ideas?

(Also, not sure if the tag "Multithreading" is technically correct)

Ernesto
  • 605
  • 1
  • 13
  • 30

2 Answers2

1

First thing - there is no multithreading for VBA in Excel. Second thing - since Excel 2007, Excel supports multithreaded recalculation of formulas.

Therefore to approach multithreading calculations in Excel you can do at least 2 things:

  1. Create a second instance of Excel (new Application instance! Not a new workbook within the same Application!) and execute the macro remotely.
  2. Create UDF functions (User Defined Functions) in VBA. Unfortunately you cannot edit other cells using UDF but you can save the results of your computations in a Global variable and then print the results.

My recommendation - go with option 2.

AnalystCave.com
  • 4,884
  • 2
  • 22
  • 30
  • I just tried your option 1 and I can access the workbook on a second instance (Dim newExcel As New Excel.Application) but when running the macro on the second instance, the first instance waits until its complete. No way of running in background? (Similar to background query refresh) – Ernesto Sep 11 '14 at 16:23
  • Run the second Excel then using Shell. Check out these links: http://stackoverflow.com/questions/17956651/execute-a-command-in-command-prompt-using-excel-vba http://stackoverflow.com/questions/2050505/way-to-run-excel-macros-from-command-line-or-batch-file – AnalystCave.com Sep 11 '14 at 16:31
  • I am running it shell, just like on the second link ([link](http://stackoverflow.com/questions/2050505/way-to-run-excel-macros-from-command-line-or-batch-file)) but the excel instance that I am using to shell the second one still waits at the xlApp.Run "Macro" line untill the xlApp finishes runing the macro. Looking at the first link, it seems there is a way using WScript to continue running instead of waiting for return, but its for executable files so not sure how to plug the Excel File in – Ernesto Sep 11 '14 at 18:10
  • Check this out: http://social.msdn.microsoft.com/Forums/en-US/0546f8eb-d786-4037-906e-1ee5d42e7484/asynchronous-applicationrun-call?forum=isvvba – AnalystCave.com Sep 12 '14 at 09:18
0

I haven't been able to try this, but it seems like you can launch Excel from VBA using Application.FollowHyperLink. The hyperlink would have to be the local path to the sheet. You might have to use VBA to also make a copy of the sheet first.

Have you thought through the concurrency issues with having two copies?

scrimshander
  • 11
  • 2