Excel workbook causes Excel to crash when opening another workbook - 4 contributing factors identified but actual cause unknown

Question

I've encountered an unusual bug in Excel that results in the whole programme crashing without warning, any form of error notification (including upon startup), or any acknowledgement that it crashed. This occurs when I have a particular workbook open and then I try to open another one; at this point Excel effectively just 'end task's itself.

I've played around with a copy of the workbook at fault and seem to have narrowed it down to a combination of four factors. If any of these factors are removed the bug doesn't appear to occur, and creating them in a fresh workbook seems to cause the issue to occur (i.e. it's replicable and not just that specific original workbook).

These four factors are as follows:

Factors 1 & 2 are on a cell (let's say B2; but see my additional notes) that contains a date:

Factor 1: A conditional formatting rule with the formula: =NOT(OR(CheckIfDate($B2),$B2="")) (the CheckIfDate function is covered in Factor 4)
Factor 2: A conditional formatting rule with the formula: =AND($B2-TODAY()>1,$B2-TODAY()<=10)
Factor 3: Another cell (let's say C2; but again, see my additional notes) that contains the formula =IF($B2="","",IF($B2-TODAY()<=1,5,IF($B2-TODAY()<=10,4,IF($B2-TODAY()<=30,3,IF($B2-TODAY()<=90,2,1)))))
Factor 4: A VBA function with the following code: Option Explicit Function CheckIfDate(Test_Cell as Range) as Boolean CheckIfDate = IsDate(Test_Cell) End Function

At this stage I could probably tweak 1+ of the factors to avoid the bug, but I'm keen to understand what's going on before deciding what the best course of action going forward is. Any help/support would be greatly appreciated, and I'm happy to provide additional information, etc. if needed.

Additional notes:

I can confirm that this is not machine specific (I was able to replicate the issue on a colleague's machine)
It appears that this bug either doesn't impact all versions of Excel, or could even be specific to the one in question. The one being used is a 32-bit Office Professional Plus 2016 version (build number is 16.0.5254.1000). I tried to replicate it on a 64-bit 365 version (build number 16.0.14729.20254) and that seemed to work without issue; thus making me think the issue might be tied to 32-bit limitations?
In terms of the two mentioned cell locations, there seems to be some weak link between their location and the issue occurring. I was able to determine that when they were next to each other like in my example, the issue occurs, but that when they get further apart the issue seems not to trigger. I've not yet determined how far the 'trip' point is so to speak; but it appears there can be a couple of columns and/or rows between them and the issue still occurs (thus it's not a case of "they have to be directly next to each other")
My initial thoughts when trying to nail down exactly what factors contributed the bug was that it would be related to the fact that TODAY() is a volatile function and it's used quite heavily here. But given Factor 1 doesn't include that function I'm not so sure anymore. However, the obvious link between the 4 factors is that they all relate to dates in some shape or form; but that may be coincidental.

UDF's in CF will be triggered a lot, when screen is refreshed, when the cell which contains the CF is on your screen. (probably your "weak link between location and issue). Perhaps putting =Today() in only one cell and refer to that Cell in your CF will save some calculation-cycles. — EvR, Jan 24 '22 at 23:43
I could not reproduce the issue in Office 365 both x32 and x64. I also tried in Office 2013 x64 but no issue. What happens if you change ```Test_Cell as Range``` to ```ByVal Test_Cell as Range``` and ```IsDate(Test_Cell)``` to ```IsDate(Test_Cell.Value)```? — Cristian Buse, Jan 25 '22 at 16:39
Two questions: 1. Do you have any other Add-Ins than default? Like Oracle, Jiver or related to company? 2. Is the personal Workbook clean? Or are other functions there? — Sgdva, Jan 26 '22 at 15:24
I could not reproduce with 32-bit Office Professional Plus 2016 version (build number is 16.0.5215.1000) - Could you perhaps share a workbook with the 4 factors implemented? — Automate This, Jan 26 '22 at 17:37
@EvR That was more or less my original line of thinking. I did try something like this earlier and it seemed to work on the test workbook but then failed when applied to the larger one; so it does feel like that could be the issue. However, given others are unable to replicate it, perhaps there's more to it :-/ — Steve, Jan 26 '22 at 23:48
@Sgdva So I'm unsure what the default ones are, so I could be missing some, but I *think* all my non-default add-ins are: SplashBI, SplashBI (Ribbon Helper), and Oracle Smart View for Office. My real workbook has other functions, but when carrying out my diagnostic tests I kept it to only what was essential for the crash to occur (so there's no other functions, etc. in that one). — Steve, Jan 26 '22 at 23:56
@AutomateThis in theory I'm happy to share a workbook, but I'm not sure how I do so on this site :s — Steve, Jan 26 '22 at 23:57

Sgdva · Accepted Answer · 2022-01-31T18:10:04.427

Scenario Detected
Giving the office 32 bit version and one add-in that you have listed I can get some pointers on at least one of the factors:

I have noticed that having Oracle Smart View enabled causes the error "Out of memory" to display when you have UDFs on the workbook (even if you are not using any functionality/API from Oracle) and UserForms coded (even if they don't get called at all either or are in another opened WBs and even after closing the stated WBs). I am not quite sure if this happens in 64 bits versions with Oracle, but at least I can confirm this behaviour happens oddly in 32 bits machines:

Sometimes this message crashes excel without notice when the app tries to display it.

On a side note: having too many conditional formatting can cause slow navigation and performance issues since they are volatile. I do not recommend to use conditional formatting due to this and some other problems along the line, like detecting its color by vba.

Some fixes/workarounds that I have found:

Clear temp files: Sometimes this helps in regard to oracle memory handling, check with your IT department, but it would be safe to do "%temp%" delete all files there and reboot.
Clear Java temp files: Go to Control Panel->Java 32 bits. In the General Tab, go to the section "Temporary Internet Files" then hit settings and click "delete files".
Drastic solution: Disable the Oracle Add-In from COM Add-Ins and test if this error still happens with this disabled.

As per your scenario, I am quite sure it's related to the UDF. Try some scenarios without the UDF in the file, there may be a chance that conditional formatting may be causing it too, I would do a few tests just to confirm that is or isn't the case. I am mostly sure by doing the previous you will dig into the root cause, if this is the case, your only way to go is calculate the formula as needed and do not leave UDFs within the Workbook, it may be a hassle for the user, but in general, it speeds up and this kind of approach leaves a smoother experience in terms on navigation and performance. Having said that, by quoting your Factor 1 and 4, I would change that portion to something like:

 Sub Exec_ColorFormatting()
 Const TxtRangeForConditional As String = "B2:C100"
 Const TxtSheetForConditional As String = "Sheet1"
    Dim RangeForConditional As Range: Set RangeForConditional = Sheets(TxtSheetForConditional).Range(TxtRangeForConditional)
    Dim CounterRowRange As Long
    Dim CounterColRange As Long
    For CounterColRange = 1 To RangeForConditional.Columns.Count
    For CounterRowRange = 1 To RangeForConditional.Rows.Count
    If CheckIfDate(RangeForConditional.Cells(CounterRowRange, CounterColRange)) = False Then ' 1. If CheckIfDate(RangeForConditional.Cells(CounterRowRange, CounterColRange)) = False
    RangeForConditional.Cells(CounterRowRange, CounterColRange).Interior.Color = vbRed
    'Or if you want a custom color use the RGB
    'RangeForConditional.Cells(CounterRowRange, CounterColRange).Interior.Color =  = RGB(255, 0, 0)
    End If ' 1. If CheckIfDate(RangeForConditional.Cells(CounterRowRange, CounterColRange)) = False
    Next CounterRowRange
    Next CounterColRange
 End Sub
Function CheckIfDate(Test_Cell As Range) As Boolean
    CheckIfDate = IsDate(Test_Cell)
End Function

I would set a shape to call the Exec_Formatting: it will be the balance of speeding up at the cost of the user pressing a button.

@sdgva firstly thank you very much for such a detailed answer; it's really interesting reading. I think that you've likely hit the nail on the head with this one, and whilst it's hard for me to confirm 100% that it's the correct one (due to time constraints & annual leave I'm unsure when I might have a chance to work through this [in the original workbook] in the required detail to get to 100%) I think the depth of the answer, etc. makes it highly likely to be the issue and so I'm going to accept it as such. Thank you very much for your support :) — Steve, Jan 31 '22 at 18:21
Feel free to comment after you've returned to the office if this worked and enjoy your leave ;) — Sgdva, Jan 31 '22 at 18:28

Excel workbook causes Excel to crash when opening another workbook - 4 contributing factors identified but actual cause unknown

1 Answers1