Stop concurrent access to a VBA function

Question

In Excel, I've defined a VBA function that searches for a file. I'd like to prevent concurrent access to this function because otherwise the hard-drive gets unnecessarily thrashed.

This is the function I've defined:

Public Function findDocument(docName As String)
    findDocument = findDocumentInFolder(docName, "C:\Correspondence")
End Function

I'm then calling it for thousands of cells in excel using the formula:

=findDocument(<cell reference>)

In java, we can control concurrent access by using the synchronized keyword. Is there any equivalent in VBA?

P.s. I assume it's running concurrently because at the bottom right of the excel window it says: Calculating: (4 Processors(s)): 9%

just like @cyboashu said no concurency in vba. [source](http://stackoverflow.com/questions/5721564/multi-threading-in-vba) — litelite, Aug 15 '16 at 18:23
See here for "caching" UDF results to avoid excessive work re-calculating the result: https://stackoverflow.com/questions/38079543/prevent-recalculating-of-functions-when-applying-autofilter-in-excel/38082713#38082713 — Tim Williams, Aug 15 '16 at 18:50
I think, in theory, you can run VBA in multiple threads -- only if you run the same worksheet macro in completely separate Excel instances in parallel (i.e. not copy / rename workbook and open both in the same Excel instance, but run Excel - open workbook, open _new_ separate Excel (it will have different process ID) and open the same workbook and run it)). — Logan Reed, Aug 15 '16 at 18:53
@LoganReed sure... but the two instances don't see each other, and have their own "thread-local" copy of the data... so it defeats the purpose ;-) — Mathieu Guindon, Aug 15 '16 at 18:55
@Mat'sMug In this particular case, though, they'd be "trashing" the same hard drive :-). — Logan Reed, Aug 15 '16 at 18:56

score 2 · Accepted Answer · answered Aug 15 '16 at 18:41

VBA and its host application runs on a STA COM thread: concurrency can't be a concern in VBA code. Even .NET code running on a MTA managed thread needs to marshal itself onto STA through COM interop - and MT issues involving COM are all but fun to fix.

That said, a UDF that accesses the file system, used in thousands of cells, will likely take Excel to its knees anyway.

I'd suggest trying another approach - perhaps cache the results in a Dictionary, to avoid unnecessarily hitting the file system given duplicate input (if that's a thing in your data), and/or making a button-triggered macro procedure that scans a given table column to run findDocumentInFolder and writing the result to these cells directly, instead of making them a UDF that needlessly recalculates every time the workbook is recalculated.

Thanks Mat, I added some logging and confirmed that the searches are run one at a time. Thanks also for the caching / button-press suggestion, I'll give those a shot — Fidel, Aug 15 '16 at 21:24

Stop concurrent access to a VBA function

1 Answers1