Volatile named ranges? Prompts for save changes on exit

[Mandie]

I have a problem that I've spent way too much time trying to figure out! I need to SUM a series of named ranges. When using the range names in the SUM formula, instead of A1 cell references, Excel prompts to save changes when exiting even though no changes have been made to the workbook.

There are absolutely no dynamic named ranges or volatile functions in my workbook. In fact, I created a new workbook and replicated the problem on a much smaller scale. Here's the entire contents of my "test" workbook. No personal macros, no regular macros, no controls, events, dynamic ranges or anything else (btw, using XL 2002):

There are three named ranges: "pi", "it", "ta", with respective values of 1, 2, 3 in cells $B$1:$B$3.

In a scenario where I open this workbook and make NO changes:

The following function allows exiting Excel without prompting for saving changes: =SUM($B$1:$B$3)

HOWEVER, this function always prompts for changes, regardless of if any have been made: =SUM(pi:ta)

Both functions return the correct value; i.e. 6.

I can't find any information on regular named ranges being volatile. I've been using Excel/VBA for many years and this is the first time I've resorted to posting! Thanks in advance for any help.

 

[Mr_Roscoe]

Mandie - welcome. You are correct that named ranges will create this prompt before saving. As well as named ranges, other objects in the workbook have the potential to create this prompt.

The solution is to create a before close macro that will negate the prompt:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

So this will run automatically and the prompt will not appear.

HTH, Mr R.

 

[Mandie]

Thanks, Mr. R. I did notice that solution in other threads, and I should have mentioned that in my post. I would like to avoid this solution, if possible, as it eliminates the possibility of being notified when desired changes need to be saved. I have never noticed this behavior before...that is, not being able to sum named ranges. In fact, this particular workbook, which I developed several months ago, seems to just have developed this problem. Ah well, thanks again for your response.

 

[Mr_Roscoe]

Mandie - I tested this option and thsi may solve your problem.

In summing the named ranges as you have =SUM(pi:ta), which assumes pi, it and ta are adjacent, this creates the save changes prompt.

Instead you could simply use =pi+it+ta which does not create the prompt.

HTH, Mr R.