Invalid input syntax for type double precision

[Ravinder]

Issue:  22P02: invalid input syntax for type double precision: ""

 

Explanation of the Issue:
The issue stemmed from an Excel file where names of user constraints were created via formulas, but the workbook got saved without those formulas being calculated.

Note that VEDA does not refresh Excel files before reading. VEDA encountered numeric values(0) instead of strings because the formulas were not calculated before the workbook was saved.

Workaround:
To resolve/avoid this issue, ensure that the Excel files are saved with all formulas calculated. You can enable the automatic workbook calculation option in Excel by navigating to Excel Options -> Formulas.

[Antti-L]

> You can enable the automatic workbook calculation option in Excel by navigating to Excel Options -> Formulas.

However, according to Microsoft (Copilot):
By default, Excel does not recalculate formulas automatically when you save a workbook. However, you can enable this feature by going to File > Options > Formulas, selecting “Manual” under Calculation options, and checking the box next to “Recalculate workbook before saving.”

  and then a further remark by Microsoft (Office help pages):
Note: When you click Manual, Excel automatically selects the Recalculate workbook before saving check box.

Therefore, according to Microsoft, automatic recalculation of formulas when saving a workbook can actually be ensured by selecting “Manual” under Calculation options. Excel will then automatically select the option Recalculate workbook before saving.

So, I am a bit confused about this error... had the user actually specifically disabled Recalculate workbook before saving, when this error occurred?  Or are there some additional conditions for this error to occur, such as external references?

[Antti-L]

Small follow-up:

I stumbled into this problem myself now.  I am using some templates that calculate cells on the basis of the Workbook name. This worked well in earlier Excel versions, but with Excel 365 it no longer works reliably:

  • When calculation is set to Automatic, Excel does NOT update the workbook correctly when saving under a new name.
  • When calculation is set to Manual, Excel does update the workbook correctly when saving under a new name, unless the user has used Calculate Now just before saving!

So, under the new Excel 365, the calculation is no longer reliable with either setting, but it is still more reliable under Manual. The problem is that when Excel thinks that there are no pending calculations, it does not re-calculate when saving under a new name. In my view this is a bug in the latest Excel versions...