Excel hard crash when returning from VBA UDF

KBARAD

New Member
Joined
Mar 31, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
I have a large workbook (6kb) with a large number of UDFs and working on quite a large amount of data. I developed it on a smaller copy with only 50 rows and have done a scale up (4000 rows) after each major feature to check stability. The problem is, right now it seems to be crashing where it worked on the small version.

The crash is a hard crash - Excel immediately closes without any dialogue or prompt: it just vanishes. There IS a big delay, lots of processing, a burning CPU and not responding period. I have done sheet by sheet recalculation and have isolated it down to one sheet - the one I most recently changed. the sheet has 3-4 array formulae (mainly just column vectors) and one more complex VBA UDF. The UDF does complete - it gets to the last line where I assign the output. The crash is after/on return from the function.

The UDF is called as an array formula returning a 3200x1 array of results. I've done this all over this workbook without issues and it has good performance normally. I've also disabled automatic calculation (necessary for this size), disabled recalculate on save so I can save THEN calculate (to resolve some issues with poor dependency tracing of UDFs by excel). It is tricky at this point because it seems the point of crash is after the end of the code I developed.

Where do I start on debugging this? Has anyone seen similar issues before?
If I can get it working I'm tempted to try a control-alt-shift-F9 to force a full recalculation - although that will be my PC locked up for half a day or so.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

KBARAD

New Member
Joined
Mar 31, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
Minor clarifications: the UDF is a variant. I am assigning String(1 to 3197, 1 to 1) to it, and it does correctly assign as a Variant/String(1 to 3197, 1 to 1). It is at/after the end function that excel crashes.

This is not the biggest function I have (I have some returning 7000x200 successfully which are far more complex)
 

KBARAD

New Member
Joined
Mar 31, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
Update: Running another test - if I forcefully recalculate just that array formula (opening the formulae bar and doing control+shift+enter) the function DOES correctly complete. For some reason it only crashes when trying to recalculate the sheet.
As a temporary measure I have copy+pasted values to remove the formula (I do have it saved) and now recalculating the sheet works. So the question is: why would refreshing the sheet crash with the UDF, not crash without the UDF, and the UDF work when directly refreshed in isolation
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,633
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I think we'd probably need to see the code for the UDF to make sure there aren't any obvious problems there.
 

KBARAD

New Member
Joined
Mar 31, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
unfortunately the code is somewhat sensitive: I can at best give a partial function signature and the final lines.

The main puzzle for me is why it works when executed directly, but crashes if I refresh the sheet. The function does execute correctly (takes ~20 minutes - but it's a complex function so no surprises).

What I can probably share is:

VBA Code:
Function group_ResolveGroups( _
' <quite a few inputs>
) As Variant
    Dim OutArray() As String
    ' ...
    ' ... other variables, code and lots of looping
    ' ...

    ' final output
    group_ResolveGroups = OutArray
    dummy = 1 ' debug breakpoint
End Function

The sheet is being refreshed with a simple macrobutton to make it easy for the users to refresh the single sheet (for testing)

VBA Code:
Sub RefreshPage()
    Application.ActiveSheet.Calculate
End Sub

the value of group_ResolveGroups at the dummy breakpoint is a Variant/String(1 to 3197,1 to 1) which is as expected and the content is correct too. if I hit F5 excel (and the VB editor) then disappears completely.

I have thought of erasing my larger arrays in the function when done with them, but that I assume should be managed already as they are all internal to the function (this one doesn't use any module variables)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,633
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I'm afraid I can't help you if that's all you can share (and I do appreciate that it's sensitive to you).
 

Watch MrExcel Video

Forum statistics

Threads
1,112,787
Messages
5,542,511
Members
410,559
Latest member
jordansmith6532
Top