VBA中调用IRR函数传递数组报类型不匹配编译错误

问题复现 #

在VBA中编写计算内部收益率的逻辑，同组现金流在Excel工作表中直接调用IRR函数可正常计算，但VBA宏运行到IRR调用行时直接抛出编译错误：

Compile error: Type mismatch: array or user-defined type expected.（编译错误：类型不匹配：需要数组或用户定义类型）

已提前确认传入参数为数组，且数组内现金流至少包含1次正负符号变化，以下排查操作均未解决问题：

• 尝试调整iRRArray的Dim声明、ReDim重定义的多种组合写法
• 分别测试数组定义、传入IRR参数时加括号/不加括号的写法
• 替换其他测试数组传入IRR函数

问题代码如下：

Dim iRRArray() As Variant
Erase iRRArray()
ReDim iRRArray(garageLife)

Dim i As Integer
i = 0

Do While i <= garageLife
    Dim difference As Double
    difference = (Worksheets("IRR").Cells(k + 1, i).Value - Worksheets("IRR").Cells(defender, i).Value) * -1
    
    iRRArray(i) = difference

    i = i + 1
Loop

Dim iRRValueTemp As Variant
iRRValueTemp = IRR(iRRArray(), 0.1)

代码逻辑：读取IRR工作表对应单元格的数值计算差值，存入iRRArray数组后传入IRR计算结果。

错误根因 #

VBA环境下存在两个同名的IRR计算函数，参数要求不同：

• VBA标准库VBA.Financial内置的IRR函数，要求第一个传入参数必须是Double类型的一维数组
• Excel工作表对象的WorksheetFunction.IRR函数，支持Variant类型数组、单元格区域等多种入参类型

当前代码直接调用IRR时，默认匹配到VBA标准库的版本，但代码中声明的iRRArray是Variant类型数组，和函数要求的Double数组类型不匹配，因此在编译阶段直接抛出类型不匹配错误。

修复方案 #

二选一即可：

方案1：调用工作表版IRR，无需修改数组定义 #

直接显式调用工作表函数下的IRR，兼容Variant类型数组，仅需修改IRR调用行：

' 替换原有iRRValueTemp = IRR(iRRArray(), 0.1) 行
iRRValueTemp = WorksheetFunction.IRR(iRRArray, 0.1)

方案2：修改数组类型，匹配VBA内置IRR参数要求 #

将数组声明从Variant类型改为Double类型，即可正常调用VBA内置IRR：

' 替换原有Dim iRRArray() As Variant 行
Dim iRRArray() As Double
Erase iRRArray()
ReDim iRRArray(garageLife)
' 其余循环赋值、IRR调用逻辑无需改动
iRRValueTemp = IRR(iRRArray, 0.1)

额外提醒 #

当前代码中Cells的列索引使用从0开始计数的循环变量i，VBA中工作表单元格的列索引最小值为1，编译错误修复后如果触发运行时错误，需要调整循环索引起始值，或给列索引加1偏移，避免引用不存在的0列单元格。

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