Excel hard crash when returning from VBA UDF

[KBARAD]

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.

 

[KBARAD]

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]

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]

I think we'd probably need to see the code for the UDF to make sure there aren't any obvious problems there.

 

[KBARAD]

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:

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)

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]

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).