vba - ＃值(value)!从VBA设置显示的大纲级别后出现错误

我已将问题简化为以下测试用例:

创建一个新的工作簿；

输入一个常量值，例如123变成Sheet1!A1;

定义一个名称，例如foo，指公式=CHOOSE(!$A$1, Sheet1!$A$1);

将常量值1输入到Sheet2!A1中；

将公式=foo输入Sheet2上的其他某个单元格中，例如Sheet2!B1:观察到，正如预期的那样，结果是在上面的步骤2中输入到Sheet1!A1中的值；

create然后运行包含以下代码的VBA过程:

Sheets("Sheet1").Outline.ShowLevels 1

您会注意到，第5步中的单元格现在包含#VALUE!错误。

此外，简单的工作表重新计算(无论使用F9键还是Application.Calculate方法)都不能解决问题:必须改为从VBA(使用Application.CalculateFull方法)执行完全重新计算，或者从交互式UI进行完全重新生成(使用CTRL + ALT + SHIFT + F9组合键)。

通过反复试验，我确定了这种情况的产生:

CHOOSE()索引参数必须包含相对工作表单元格引用(而不是常量或绝对工作表引用)；

被索引的CHOOSE()值参数必须包含对另一个工作表的引用；

显示轮廓级别的更改必须来自VBA过程(而不是交互式UI或VBA立即窗口中的轮廓控件)；和

必须将ShowLevels方法调用(其参数无关)应用到在CHOOSE()的任何值(尽管不是索引)参数中引用的工作表。

这是怎么回事？

我非常想折叠我在CHOOSE()的值参数中引用的工作表到其最高大纲级别，而不会触发此错误，因为从UX角度来看，我不希望完全重新计算我的实际工作簿(仍然只有几秒钟) 。

建议提供解决方法(尽管仍使用包含CHOOSE()函数以及相对工作表索引参数的已定义名称)!

我的平台:Windows 7 Home Premium(SP1，64位)上的Excel 2010(14.0.6123.5001，32位)。

最佳答案

问题出在您的命名公式:=CHOOSE(!$A$1, Sheet1!$A$1)，特别是!A1
前导!无效(没有前面的工作表名称，例如Sheet1!$A$1有效)。只要指定一张纸，您的问题就消失了。

我怀疑这可能无法满足您的要求，具体取决于您为什么首先使用!A1。如果是您希望=foo使用工作表上A1的索引值，则将公式=foo放在使用INDIRECT("A1")而不是!A1的位置上

顺便说一句，我认为您可能已经发现错误，或者至少是未定义的行为，因为公式=CHOOSE(!$A$1, Sheet1!$A$1)无效，应该始终返回#Value错误。