Excel Crashes when VBA macro cites a workbook that is being opened by another macro

[HackSlash]

BACKGROUND:
This is a bit of a complicated situation so bear with me here. I'm using a fully patched version of Excel 2013. I have an import macro that imports data in a reliable way. This has worked fine across all versions of Excel for years. Due to limitations in VLOOKUP or INDEX\MATCH I made a custom lookup formula in order to pull in data from a dynamic source in to my generic report. This function works great.

THE PROBLEM:
When I run my import macro the page attempts to recalculate. The cell formulas call my custom function which attempts to look at cells in the newly opened workbook. This occurs while my import macro is still running which leads Excel to crash HARD.

TESTS:
I have proved this to be the cause by turning calculation to "Manual". If I run the import macro and wait for it to complete I can click "Calculate Now" and my custom function runs perfectly.

HELP?:
I don't really know how to move forwards from here. I need it to auto-calculate when the new data is imported, like a VLOOKUP would. Maybe I could delay the calculation until after import is done but I don't know how to detect if another macro is still running. When I looked that up I learned that Excel isn't supposed to ever run two macros at once. Maybe that is the source of the crash.

THE CODE:

Public Function ARLOOKUP(ByVal area As Range, ByVal searchText As String, Optional ByVal rowOffset As Long = 0, Optional ByVal colmOffset As Long = 0) As Variant
    Dim foundCell As Range
    Set foundCell = area.Find(searchText, lookAt:=xlPart)
    If foundCell Is Nothing Then
        ARLOOKUP = "NOT FOUND"
    Else
        ARLOOKUP = foundCell.Offset(rowOffset, colmOffset)
    End If
End Function

NOTE:
If I set a breakpoint in here I can see that a window from my import macro is still open while this is running. The crash occurs on line 3 where the ".Find" is called. The new workbook is kinda open because it has a window yet the contents is all gray. It's like I'm in some terrible alternate dimension where workbooks are both open and closed while two macros are running. What have I done?

 

[mole999]

couple of things

Application.Calculate = xlManual before the function is called

DoEvents once the data is imported to let it finalise

Application.Calculate = xlAutomatic once the data is in

and consider an error trap

i might have misspelt something there

 

[HackSlash]

Thanks Mole999! I had tried trapping the error but it's Excel who crashes and not VBA. I was hoping to handle this from my 2nd macro but turning off calculation from the import side did stop the double macro jeopardy. I will have to use this solution as I've been unable to detect if another macro is already running from my lookup.

 

[mole999]

only one macro will run at a time, excel dosen't time slice. even if you create and run a timer, then you work between the procedures, not at the same time

 

[HackSlash]

What I mean is that the function to update cell contents is running before the import macro is finished. Maybe it's not time slicing or actually running simultaneously but they aren't running sequentially, as expected. The ARLOOKUP is interrupting the Import which is why turning off calculation fixes the problem. Maybe a better understanding of Workbook.Open would explain why but the fact that Excel is crashing suggests that this is a bug and Excel is actually doing something neither one of us would expect.

 

[mole999]

manual calculation and application.enableevents = false will prevent worksheet events (should do) running when you don't want them. then when imported DoEvents the automatic calculation and enableevents = true should make it stable again. Probably a best use order though I'm not sure which