Microsoft Visual Basic for Applications System Error &H8000FFFF (-2147418133). Catastrophic failure.

[Karinh395]

Hi All:

I created a spreadsheet with the following VBA:

Function SumColor(MatchColor As Range, sumRange As Range) As Double
Dim cell As Range
Dim myColor As Long
myColor = MatchColor.Cells(1, 1).Interior.Color
For Each cell In sumRange
If cell.Interior.Color = myColor Then
SumColor = SumColor + cell.Value
End If
Next cell
End Function

It worked great until I closed it and opened another spreadsheet that does not use this code. Then when I reopened the spreadsheet with the VB micro above I get the Catastrophic failure message. Any ideas?

[#2]

@Karinh395 

The error you are encountering, "Microsoft Visual Basic for Applications System Error &H8000FFFF (-2147418133). Catastrophic failure," can sometimes occur due to issues with the VBA environment or conflicts with other applications. Here are some suggestions to troubleshoot and resolve the issue:

1. Close and Reopen Excel:
• Close Excel completely.
• Reopen Excel and open the spreadsheet with the VBA code.
2. Check for ActiveX Controls:
• Ensure that there are no ActiveX controls or form controls causing conflicts.
• Remove any unnecessary ActiveX controls or form controls.
3. Macro Security Settings:
• Check your macro security settings to make sure that macros are enabled.
• Go to the "Developer" tab, click "Macro Security" in the "Code" group, and ensure that it's set to a level that allows macros to run.
4. Compile VBA Code:
• Open the VBA editor (Alt + F11).
• In the VBA editor, go to "Debug" and choose "Compile VBAProject."
• Check for any compilation errors.
5. Remove Recently Installed Add-ins:
• If you've recently installed any Excel add-ins, try disabling or removing them to see if the issue persists.
6. Update Excel:
• Ensure that your Excel application is up to date. Check for and install any available updates.
7. Reset Excel Settings:
• As a last resort, you can try resetting Excel settings. Note that this will reset all customizations.
• Close Excel.
• Press Windows key + R to open the Run dialog.
• Enter %appdata%\Microsoft\Excel and press Enter.
• Rename the "XLSTART" folder to something else (e.g., "XLSTART_backup").
• Restart Excel.

If the issue persists after trying these steps, it may be necessary to investigate further or seek assistance from Microsoft Support. Additionally, if the Catastrophic Failure error persists, there might be deeper issues with your Excel installation, and you may need to repair or reinstall Microsoft Office. The text and steps were edited with the help of AI.

The steps provided are generally applicable to various versions of Microsoft Excel, including Excel 2016, 2019, and Excel for Microsoft 365. However, some specific features or menu locations might vary slightly between versions. Remember, these instructions are general, and some details may vary based on your specific Excel version.

If none of these steps help you, I recommend adding more information to your topic. Information such as Excel version, operating system, storage medium, file extension, etc.

In this link you will find some more information about it:

Welcome to your Excel discussion space!

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

[#3]

@Karinh395 

The "Microsoft Visual Basic for Applications System Error &H8000FFFF (-2147418133). Catastrophic failure." error you're encountering can be frustrating, but there are a few reasons it might be happening in your specific case. Here are some things to try:

1. Check for "Workbook_Open" event code:

• Open the VBA editor (Alt + F11).
• In the Project Explorer, expand your workbook and look for a module named "ThisWorkbook."
• Double-click "ThisWorkbook."
• Check if there's any code in the "Workbook_Open" event procedure.
• If there is, try commenting it out (adding ' in front of each line) or deleting it. Sometimes rogue code in this event can trigger errors when opening the workbook.

2. Verify references:

• Go to Tools > References in the VBA editor.
• Look for any unfamiliar or unnecessary references listed.
• Try unchecking those references and see if the error persists. Sometimes unexpected references can cause crashes.

3. Clear compiled code:

• Go to Tools > VBAProject Properties.
• Select the "Compile" tab.
• Click the "Clean" button under "Compile options."
• This will clear any cached compiled code and force it to be rebuilt next time you run the macro.

4. Debug the code:

• Set breakpoints within your "SumColor" function at key points like the loop or conditional statement.
• Run the macro in step-by-step mode (F8 key) and see if you can identify any suspicious behavior or errors within the code.

5. Consider alternative approaches:

• If the function only needs to check the first cell in "MatchColor" for color, consider simplifying the code by directly comparing the color instead of looping through cells.
• You can also explore using built-in Excel functions like SUMIF or SUMPRODUCT that might achieve the same result without needing custom VBA.

Additional Tips:

• Make sure you have the latest updates installed for both Excel and VBA.
• Try opening the workbook in Safe Mode (holding Ctrl while opening Excel) to see if any add-ins are causing the issue.
• If none of these suggestions work, consider sharing the full code of your "SumColor" function and any other relevant VBA code for further analysis.

Remember, troubleshooting VBA errors can be tricky, but by testing and analyzing different possibilities, you should be able to pinpoint the cause and fix the problem.

I hope this helps!

[#4]

Hi All:
Thanks for the information above. Maybe there is an easier way to get the color situation done without using VBA? My document can be sorted for individual managers and they want the information color coded. Each item is color coded for the year in which the event happened. Which means that I have 8 colors (deposits 4 and EF Reminder 4) . Please let me know if you have any ideas.

Deposit Date Deposit EF Date EF Remainder
03/23/2022 $8,928.00 04/01/2022 $80,352.00
01/24/2022 $10,340.00 03/24/2022 $93,059.00
06/14/2022 $12,876.00 09/08/2022 $176,396.00
04/01/2022 $10,340.00 07/12/2022 $93,059.00
03/24/2021 $32,124.00 07/29/2023 $96,632.00
08/17/2022 $10,340.00 09/09/2022 $93,059.00
08/24/2022 $10,340.00 11/21/2022 $103,399.00
12/07/2022 $17,700.00 12/20/2022 $150,446.00
11/07/2022 $15,200.00 12/14/2022 $136,796.00
01/31/2023 $12,321.00 04/17/2023 $126,418.00
04/14/2023 $9,821.00 04/24/2023 $88,387.00
05/02/2023 $8,790.00 05/17/2023 $79,114.00
04/18/2023 $10,681.00 06/02/2023 $96,132.00
07/03/2023 $11,374.00 07/27/2023 $102,365.00
09/18/2023 $11,374.00 10/31/2023 $102,365.00
07/28/2023 $16,720.00 12/01/2023 $150,476.00
12/21/2023 $19,779.00





2021 2021
2022 2022
2023 2023
2024 2024
2025 2025
2026 2026
2027 2027
2028 2028
2029 2029
2030 2030

[#5]

@Karinh395 

To color code the events based on the year in which they occurred in Excel, you can use conditional formatting. Here's how you can do it:

1. Select the range of cells containing the dates: Highlight the range of cells in the "Deposit Date" and "EF Date" columns where you want to apply conditional formatting.
2. Apply conditional formatting: Go to the "Home" tab in Excel, click on "Conditional Formatting" in the ribbon, and then select "New Rule..."
3. Create a new formatting rule: In the "New Formatting Rule" dialog, choose "Use a formula to determine which cells to format".
4. Enter the formula: You'll need to create a formula that checks the year of each date and compares it to the year you want to color code. For example, if you want to color code events for the year 2022:
• For "Deposit Date": Enter the formula =YEAR($A1)=2022 (assuming "Deposit Date" starts from cell A1).
• For "EF Date": Enter the formula =YEAR($C1)=2022 (assuming "EF Date" starts from cell C1).

Adjust the cell references in the formula as per your actual data range.

1. Set the formatting: Click on the "Format..." button to choose the formatting style you want to apply to the cells meeting the condition. You can choose a fill color under the "Fill" tab.
2. Repeat for other years: Repeat steps 2-5 for each year you want to color code.

By following these steps, you can color code the events based on the year in which they occurred in your Excel sheet. You can customize the formatting and the years as per your requirements. This method allows you to visually distinguish events from different years, making it easier for managers to analyze the data.