Cholesky Formula yields #Value in one .xls.file and works in another one

[Timi]

Hey All

I designed a cholesky composition function in VBA. Unfortunately it doesn't work in one of my .xls-files (result is #Value) whereas it works if I open a new .xls file and try it there.

Does anybody have an idea why a function doesn't work in one file but works in another one (excel options are the same since I opened both files on the same computer and the module in VBA is also the right one).

Function cholesky_m(Correlation_Matrix As Variant) As Variant
    
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
         
    Dim row_number As Integer
    Dim column_number As Integer
    Dim sum_count As Integer
    Dim total_rows As Integer
    
    total_rows = Correlation_Matrix.Rows.Count - 1
    ReDim input_value(total_rows, total_rows) As Double
    ReDim output_value(total_rows, total_rows) As Double
    ReDim sum_value(total_rows, total_rows) As Double
    
' record data array from correlation matrix
    
    For row_number = 0 To total_rows
        For column_number = 0 To total_rows
            input_value(row_number, column_number) = Correlation_Matrix.Cells(row_number + 1, column_number + 1).Value
        Next column_number
    Next row_number
' perform cholesky decomposition
    For row_number = 0 To total_rows
        For column_number = 0 To row_number
            If row_number = column_number Then
                For sum_count = 0 To row_number - 1
                    sum_value(row_number, column_number) = sum_value(row_number, column_number) + _
                        output_value(row_number, sum_count) ^ 2
                Next sum_count
                output_value(row_number, column_number) = (input_value(row_number, row_number) - _
                    sum_value(row_number, column_number)) ^ 0.5
            Else
                For sum_count = 0 To column_number - 1
                    sum_value(row_number, column_number) = output_value(row_number, sum_count) * _
                        output_value(column_number, sum_count) + sum_value(row_number, column_number)
                Next sum_count
                    output_value(row_number, column_number) = 1 / output_value(column_number, column_number) * _
                        (input_value(row_number, column_number) - sum_value(row_number, column_number))
            End If
        Next column_number
    Next row_number
' record results
    cholesky_m = output_value()
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Function

thx

 

[Ralajer]

Double check you inputs and references. It's my understanding that if a worksheet can't access a function for any reason it will return a "#NAME?" error not a "#VALUE!" error. Please anyone correct my understanding if I'm off base.

Also found another VBA implementation of Cholesky Decomposition on the Wilmott Forums (link). Can't vouch for its accuracy, only touched on linear algebra for few weeks in college. But it might be worth checking to see if you can reproduce the error with it.