function

[SOLVED] Excel workbook causes Excel to crash when opening another workbook – 4 contributing factors identified but actual cause unknown

Issue

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.

Solution

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:

enter image description here

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:

  1. 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.
  2. 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".
  3. 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.

Answered By – Sgdva

Answer Checked By – Senaida (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *