• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Recalculation of formulas opened in 97-2003 file format

P

PremSivakanthan

New Member
Hi all, I have been working on a dashboard that is continually being added to and refined. This report needs to be saved in a .xls format to ensure everybody in our company can open it without any problems. I have written some code to save it in a number of different formats, and that was working fine until last week. The code would execute and the file would save, but whenever anybody tried to open the file, excel would crash. After many hours of problem solving, I have tracked it down to the message that comes up when saving: Defined names of formulas in this workbook may display different values when they are recalculated...Do you want Excel to recalculate all formulas when this workbook is opened?". When I select yes, and try to open the file it crashes, but when I select no and try and open the file everything is fine.


Questions:

1) How do I control this via vba? Setting Application.Displayalerts = False does not solve the problem, as while the alert wont come up, it appears that the default selection when using this is yes.

2) What does excel mean by this message, the best explanation I have been able to find is in this link

http://office.microsoft.com/en-ca/excel-help/change-formula-recalculation-iteration-or-precision-HP010054149.aspx#BMlearn_about_calculating_workbooks_tha

I have spent many hours scouring the net with no avail…!
 
Hi Siva ,


Without the workbook to examine , I cannot say much.


One possibility is the limits in older versions of Excel are different from the limits in Excel 2007 and later e.g. the number of rows is 65536 against 1048576 , so also is the case for the columns.

If your defined range names are going beyond these limits , there may be a problem.


Narayan
 
Thanks, I can confirm I have not exceed the row/column limitations, nor am I using any formulas that arent supported in 2003.


My question is how do I control this alert in vba? It is this prompt that comes up when saving a .xlsx to .xls:


"Defined names of formulas in this workbook may display different values when they are recalculated...Do you want Excel to recalculate all formulas when this workbook is opened?".


Any ideas?


Prem
 
Hi Siva ,


I am not clear on your problem ; is it that you want to know why Excel crashes when you select YES to the prompt , or is it that you want the prompt to be cancelled with a default NO response via VBA ?


Narayan
 
Hi, yes, the later, I want to know how the prompt can be cancelled with a default NO in VBA. Using Application.DisplayAlerts = False, doesnt work (it defaults it to YES, as far as I can tell)...


I havent been able to find any documentation about this anywhere....
 
You must log in or register to reply here.
Back
Top