SOLVED

Excel Macros #Value! Error *Help Needed*

Copper Contributor

My job uses Excel macros for financial reports. I converted to a new laptop, had some issues with transferring all my files from OneDrive backup (not sure if some important backend file with Excel software was deleted), and then none of my Excel macros would work. To give some context, here is what happened:

 

  1. At first, all formulas are disabled. I was instructed to enable them and place the file in a "Trusted Location" folder. If I open it directly from an email, it is in 'protected view'. I can see the values in protected view, but the formulas are not enabled yet.
  2. Once I enabled formulas, #Value! errors appeared.
  3. IT help desk ran the Office 365 fixer app. Nothing changed.
  4. We tried enabling 'add ins', enabling more trust center settings, etc. Nothing changed.
  5. They then deleted the 365 apps and reinstalled them. After that, some of the formulas do run and output a valid result, but others still have the #Value! errors. See Screenshot.
  6. I tried the help article for DateValue errors, with no success. Nothing was wrong with the cells formatting. After all, they are all formatted the same and some work while others don't. Also, my coworkers are all able to open these files with no issues. It has something to do with my computer either missing files or having an Excel setting wrong. 
  7. After the reinstall, I have the same trusted location folders in the trust center settings. Apparently, these did not reset. I attempted to return everything to default with no success in fixing the #Vaue! errors.

*This is public data that can be obtained by anyone at fpr.ncua.gov. If you want to download the same file, enter CU# "1750", "effective date of 3/31/2023", and choose "Quarterly".

 

If anyone can tell me what the problem is and what to check, I would appreciate it. 

 

 

FPR errors.png

Jrojas5_4-1690991222694.pngJrojas5_5-1690991236808.pngJrojas5_6-1690991254538.pngJrojas5_7-1690991275968.pngJrojas5_8-1690991298002.pngJrojas5_9-1690991367376.pngJrojas5_10-1690991392980.pngJrojas5_11-1690991456214.pngJrojas5_12-1690992138554.png

Jrojas5_0-1690990916895.png

Jrojas5_1-1690990935621.pngJrojas5_2-1690990984493.png

Jrojas5_3-1690991098088.png

 

 

 

 

 

 

1 Reply
best response confirmed by Jrojas5 (Copper Contributor)
Solution

@Jrojas5 

It seems like there might be a compatibility issue or some settings that need to be adjusted on your new laptop for the Excel macros to work correctly. Here are some steps you can try to troubleshoot and fix the #VALUE! errors in your Excel macros:

  1. Check Macro Security Settings:
    • Open Excel, go to the "File" tab, and select "Options."
    • In the Excel Options dialog, go to "Trust Center" and click on "Trust Center Settings."
    • Go to the "Macro Settings" tab and ensure that "Enable all macros" or "Enable all macros (not recommended; potentially dangerous code can run)" is selected. If not, choose one of these options and click "OK" to save the settings.
    • Restart Excel and try running the macros again.
  2. Check Calculation Options:
    • Sometimes, Excel may not recalculate formulas automatically, leading to #VALUE! errors. Go to the "Formulas" tab and click on "Calculation Options." Make sure "Automatic" is selected.
    • Alternatively, you can press "F9" to force a manual recalculation of all formulas.
  3. Check Data Connections and References:
    • If your macros rely on external data connections or references to other workbooks, make sure these connections are set up correctly on your new laptop.
    • Verify that the data sources and references are accessible and have not changed.
  4. Check Excel Add-Ins:
    • Go to the "File" tab and select "Options."
    • In the Excel Options dialog, go to "Add-Ins" and check if any add-ins are disabled. If there are any disabled add-ins related to your macros, try enabling them and see if it resolves the issue.
  5. Verify File Locations:
    • Ensure that the file paths used in your macros are correct and accessible on your new laptop. If the file paths have changed due to transferring files, update the macros with the correct file paths.
  6. Test Macros on Another Computer:
    • To rule out any issues with your specific laptop, try running the macros on another computer within your organization where they are known to work correctly. This will help determine if the issue is related to your laptop or the Excel settings.
  7. Check Regional Settings:
    • In some cases, the #VALUE! error may occur due to differences in regional settings between computers. Make sure the regional settings (date, time, decimal separator, etc.) on your new laptop match the original computer.

If none of the above steps resolve the #VALUE! errors, it might be helpful to insert a file (without sensitive data), more info’s about the Excel version, Operational system, storage medium. The text and the steps was created with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

1 best response

Accepted Solutions
best response confirmed by Jrojas5 (Copper Contributor)
Solution

@Jrojas5 

It seems like there might be a compatibility issue or some settings that need to be adjusted on your new laptop for the Excel macros to work correctly. Here are some steps you can try to troubleshoot and fix the #VALUE! errors in your Excel macros:

  1. Check Macro Security Settings:
    • Open Excel, go to the "File" tab, and select "Options."
    • In the Excel Options dialog, go to "Trust Center" and click on "Trust Center Settings."
    • Go to the "Macro Settings" tab and ensure that "Enable all macros" or "Enable all macros (not recommended; potentially dangerous code can run)" is selected. If not, choose one of these options and click "OK" to save the settings.
    • Restart Excel and try running the macros again.
  2. Check Calculation Options:
    • Sometimes, Excel may not recalculate formulas automatically, leading to #VALUE! errors. Go to the "Formulas" tab and click on "Calculation Options." Make sure "Automatic" is selected.
    • Alternatively, you can press "F9" to force a manual recalculation of all formulas.
  3. Check Data Connections and References:
    • If your macros rely on external data connections or references to other workbooks, make sure these connections are set up correctly on your new laptop.
    • Verify that the data sources and references are accessible and have not changed.
  4. Check Excel Add-Ins:
    • Go to the "File" tab and select "Options."
    • In the Excel Options dialog, go to "Add-Ins" and check if any add-ins are disabled. If there are any disabled add-ins related to your macros, try enabling them and see if it resolves the issue.
  5. Verify File Locations:
    • Ensure that the file paths used in your macros are correct and accessible on your new laptop. If the file paths have changed due to transferring files, update the macros with the correct file paths.
  6. Test Macros on Another Computer:
    • To rule out any issues with your specific laptop, try running the macros on another computer within your organization where they are known to work correctly. This will help determine if the issue is related to your laptop or the Excel settings.
  7. Check Regional Settings:
    • In some cases, the #VALUE! error may occur due to differences in regional settings between computers. Make sure the regional settings (date, time, decimal separator, etc.) on your new laptop match the original computer.

If none of the above steps resolve the #VALUE! errors, it might be helpful to insert a file (without sensitive data), more info’s about the Excel version, Operational system, storage medium. The text and the steps was created with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

View solution in original post