#VALUE! errors after setting displayed outline level from VBA

Question

I have reduced my problem to the following test case:

1. create a new workbook;

2. enter a constant value, e.g. 123 into Sheet1!A1;

3. define a name, e.g. foo, that refers to the formula =CHOOSE(!$A$1, Sheet1!$A$1);

4. enter the constant value 1 into Sheet2!A1;

5. enter the formula =foo into some other cell on Sheet2, e.g. Sheet2!B1: observe that, as expected, the result is the value that was entered into Sheet1!A1 in step 2 above;

6. create then run a VBA procedure containing the following code:

   Sheets("Sheet1").Outline.ShowLevels 1
   

You will notice that the cell from step 5 now contains a #VALUE! error.

Moreover, a simple sheet recalculation (whether using the F9 key or the Application.Calculate method) does not resolve the problem: one must instead perform either a full recalculation from VBA (using the Application.CalculateFull method) or else a full rebuild from the interactive UI (using the CTRL+ALT+SHIFT+F9 key combination).

Through trial-and-error, I have ascertained that for this condition to arise:

• the CHOOSE() index argument must involve a relative-sheet cell reference (not constants or absolute-sheet references);

• the CHOOSE() value argument that is being indexed must involve a reference to another sheet;

• the change of displayed outline level must arise from within a VBA procedure (not from the outline controls in the interactive UI or the VBA Immediate Window); and

• the ShowLevels method call (whose arguments are irrelevant) must be applied to a sheet that is referenced amongst any of the value (though not index) arguments to CHOOSE().

What's going on?

I would very much like to collapse a worksheet that I am referencing amongst the value arguments to CHOOSE() to its highest outline level without triggering this error, as a full recalculation of my actual workbook (still only a few seconds) is undesirable from a UX standpoint.

Suggestions for a workaround (whilst still using a defined name containing the CHOOSE() function together with a relative-sheet index argument) would be most welcome!

My platform: Excel 2010 (14.0.6123.5001, 32-bit) on Windows 7 Home Premium (SP1, 64-bit).

Answer 1

The problem is with your named formula: =CHOOSE(!$A$1, Sheet1!$A$1), specifically the !A1

The leading ! is invalid (without a preceeding sheet name, eg Sheet1!$A$1 is valid). Just specify a sheet and your problem goes away.

I suspect this may not satisfy you, depending on why you used !A1 in the first place. If it's that you want =foo to use an index value from A1 on the sheet the formula =foo is placed on use INDIRECT("A1") instead of !A1

---

BTW I think your may have found a bug, or at least undefned behaviour, in that the formula =CHOOSE(!$A$1, Sheet1!$A$1) is invalid and should always return a #Value error.