使用Selection.FormatConditions.Add时如何处理带括号文件名避免Excel报错

根因说明 #

Excel的公式语法要求：当引用的外部工作簿名称包含括号、空格、特殊符号时，必须将[工作簿名称]工作表名称这一整段用单引号'包裹，否则括号会被识别为公式运算符，导致语法错误，触发运行时错误5。

修正方案 #

只需要调整formulaAddMe的拼接逻辑，在对应位置补充单引号即可，修改后的代码如下：

' 仅修改了formulaAddMe拼接规则，新增两处单引号
formulaAddMe = "='[" & targetTestWorkbook & "]" & targetTestSheet & "'!" & targetPlacementPosition & formulaAddMe3

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
                    formulaAddMe
                Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
                With Selection.FormatConditions(1).Interior
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = 0
                    .Color = colourToInsert
                End With

效果验证 #

修改后，带括号的文件名生成的公式格式为：

='[PixelActivitySimpsons6(MeNewRandomPixels).xlsx]Sheet2'!$C$4=1

符合Excel公式语法规范，Selection.FormatConditions.Add方法可以正常解析执行，不会再触发参数错误。

补充说明 #

如果目标工作簿处于未打开状态，你需要在工作簿名称前拼接完整的文件绝对路径，路径+[工作簿名]工作表名整体包裹在单引号内即可。

内容的提问来源于stack exchange，提问作者Alec Armstrong