Excel VBA中Cholesky分解测试正常但作业模块报错求助

问题背景 #

在Excel VBA中实现乔列斯基分解，测试模块可成功计算分解矩阵，但作业模块执行时触发「无效的过程调用或参数」错误，代码逻辑与测试模块一致，需排查问题原因并修复。

核心排查方向 #

1. 协方差矩阵的有效性问题 #

乔列斯基分解要求输入矩阵为正定对称矩阵，作业模块中从工作表读取的矩阵mat可能存在以下问题：

• 单元格读取范围错误：代码中mat(i, j) = Cells(i + 10, 3 * j)的列索引计算逻辑错误，j=1时读取列3，j=2时读取列6，j=3时读取列9，但之前输出相关系数的位置是Cells(11,3)、Cells(12,3)等，实际应该读取连续的列（比如列3-5），当前读取方式可能拿到空值或非对称数据，导致矩阵非正定。
• 浮点精度与矩阵完整性问题：计算相关系数时的浮点误差可能导致矩阵出现微小非正定情况，且代码未手动填充相关系数矩阵的对角线（对角线应为1），进一步破坏矩阵的正定性，导致Sqr函数传入负数触发错误。

2. 变量类型溢出风险 #

days定义为Integer类型，若数据量超过32767会触发溢出错误，建议改为Long类型。

3. 函数参数与数据合法性 #

需确保Cells(6,1)读取的days为正整数，且IndexData工作表中对应位置的股价数据为有效数值（无空值、文本），否则Log函数会触发错误。

具体修复方案 #

1. 修正协方差矩阵读取逻辑 #

假设相关系数矩阵从Cells(11,3)开始，行范围为11-13，列范围为3-5，修改读取代码：

' CholeskyDecomposition
For i = 1 To 3
    For j = 1 To 3
        mat(i, j) = Cells(i + 10, j + 2)
    Next j
Next i
' 手动填充相关系数矩阵对角线（必须为1）
mat(1, 1) = 1
mat(2, 2) = 1
mat(3, 3) = 1

2. 增加分解函数的错误校验 #

在CholeskyDecompositionF函数中增加对非正定矩阵的校验，避免无效参数调用：

Function CholeskyDecompositionF(mat() As Double, size As Long) As Double()
    Dim a() As Double, L() As Double, i As Long, j As Long, k As Long, sum As Double
    a = mat
    ReDim L(1 To size, 1 To size)
    
    For i = 1 To size
        For j = 1 To size
                If i = j Then
                    sum = 0
                    For k = 1 To j - 1
                        sum = sum + L(j, k) ^ 2
                    Next
                    ' 校验根号内数值非负，允许微小浮点误差
                    If a(j, j) - sum < -1e-10 Then
                        Err.Raise vbObjectError + 1001, , "输入矩阵非正定，无法执行乔列斯基分解"
                    End If
                    L(j, j) = Sqr(a(j, j) - sum)
                ElseIf i > j Then
                    sum = 0
                    For k = 1 To j - 1
                        sum = sum + L(i, k) * L(j, k)
                    Next
                    ' 校验分母非零
                    If Abs(L(j, j)) < 1e-10 Then
                        Err.Raise vbObjectError + 1002, , "矩阵对角线元素接近0，无法执行分解"
                    End If
                    L(i, j) = (a(i, j) - sum) / L(j, j)
                ElseIf i < j Then
                    L(i, j) = 0
                End If
            Next
    Next
    
    CholeskyDecompositionF = L
End Function

3. 修正变量类型 #

将days改为Long类型，避免数据量过大时溢出：

Dim days As Long

4. 校验工作表数据 #

确保IndexData工作表中Cells(i+4,2)、Cells(i+4,5)等位置的股价数据为有效数值，无空值或文本内容。

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