Excel VBA中CHOOSECOLS公式自动计算失效问题求助

开发者阿泽

2026-6-21

问题描述

我在用Excel VBA编写程序，需要执行CHOOSECOLS公式（格式为CHOOSECOLS(matrix, col_num1, col_num2)）。但公式写入单元格后，VBA程序结束时单元格显示#NAME?错误，没有自动计算。公式语法是对的，因为手动按F2+Enter后就能正常计算出预期结果，其他方式都触发不了计算。请问怎么修改才能让公式自动计算？

现有代码

Function DeterminarRangeTabela() As Range
    Dim wsBase As Worksheet
    Set wsBase = ThisWorkbook.Sheets("1_BASE CARTEIRA")
    
    ' 确定最后一行和最后一列
    Dim lastRow As Long
    lastRow = wsBase.Cells(wsBase.Rows.Count, "A").End(xlUp).Row
    
    Dim lastCol As Long
    lastCol = wsBase.Cells(3, wsBase.Columns.Count).End(xlToLeft).Column
    
    ' 确定表格范围：从第4行到最后一行、最后一列的有数据区域
    Dim tblRange As Range
    Set tblRange = wsBase.Range(wsBase.Cells(4, 1), wsBase.Cells(lastRow, lastCol))
    
    ' 返回表格范围
    Set DeterminarRangeTabela = tblRange
End Function

' 调用部分代码
Dim tblRange As Range
Set tblRange = DeterminarRangeTabela()
Dim wsCarteira As Worksheet
Set wsCarteira = ThisWorkbook.Sheets("2_CARTEIRA")
Dim formula As String
    
formula = "=CHOOSECOLS('1_BASE CARTEIRA'!" & tblRange.Address(False, False) & ",4,1,5,6,7,8,9,2,3,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,47,45,46,48,49,50,51,52,53,54,55,56,57)"
wsCarteira.Range("A5").Formula2 = formula 
wsCarteira.Range("A5").Calculate

解决方案

出现这个问题的核心原因是CHOOSECOLS属于Excel 365/2021支持的动态数组函数，VBA赋值后可能因计算引擎未正确识别触发条件、或区域分隔符不匹配导致解析异常。以下是几种有效修复方法：

方法1：强制重建计算链
替换单独单元格的Calculate，改为刷新整个工作表的计算链，确保动态数组公式被正确识别：
```
wsCarteira.Range("A5").Formula2 = formula
wsCarteira.CalculateFullRebuild ' 强制重建计算逻辑并刷新
```
方法2：模拟手动编辑操作
既然F2+Enter能解决问题，直接在VBA中模拟该操作：
```
wsCarteira.Range("A5").Formula2 = formula
' 激活单元格、进入编辑模式、确认输入
wsCarteira.Range("A5").Activate
SendKeys "{F2}"
SendKeys "{ENTER}"
```
注意：使用SendKeys时需确保Excel窗口处于前台，否则可能失效。
方法3：适配区域分隔符
如果系统区域设置的列表分隔符是分号（;）而非逗号（,），需替换公式中的分隔符：
```
formula = Replace(formula, ",", Application.International(xlListSeparator))
wsCarteira.Range("A5").Formula2 = formula
wsCarteira.Calculate
```
方法4：使用本地化公式属性
用.Formula2Local替代.Formula2，它会自动适配系统区域的公式分隔符：
```
wsCarteira.Range("A5").Formula2Local = formula
wsCarteira.Calculate
```

内容的提问来源于stack exchange，提问作者Matheus Neri