Passing a range into a VBA function

[scottcolbury]

Hi all,
I'm trying to use this function from VB Numerical Methods:

http://www.vbnumericalmethods.com/download.asp?codeType=m&cTitle=Cholesky Factorization

Function Cholesky(Mat As Range)
Dim A, L() As Double, s As Double
A = Mat
n = Mat.Rows.Count
M = Mat.Columns.Count
If n <> M Then
    Cholesky = "?"
    Exit Function
End If

ReDim L(1 To n, 1 To n)
For j = 1 To n
    s = 0
    For k = 1 To j - 1
        s = s + L(j, k) ^ 2
    Next k
    L(j, j) = A(j, j) - s
    If L(j, j) <= 0 Then Exit For
    L(j, j) = Sqr(L(j, j))
    
    For i = j + 1 To n
        s = 0
        For k = 1 To j - 1
            s = s + L(i, k) * L(j, k)
        Next k
        L(i, j) = (A(i, j) - s) / L(j, j)
    Next i
Next j
Cholesky = L
End Function

... and here is my code:

Sub testcholesky()
Dim x As Range

   Set x = Sheet6.Range(Cells(12, 12), Cells(12 + 53 - 1, 12 + 53 - 1))
   Cholesky (x)

End Sub

However, I keep getting a runtime '424' error for object required.
I've been told that sometimes VBA doesn't allow you to pass ranges into a function. Any help on this one?

Also, the code from VB Numerical Methods creates an array.
I can't figure out the syntax to get that data back out of the function and into my spreadsheet. I'd like to do this within the sub rather than the function itself.

Any ideas?
thanx,
s_c[/code]

 

[Bob Rooney]

A few things:

1. Use Option Explicit in the top of the module and correct the variable declarations for the code for the Cholesky decomp matrix.

2. Fully qualify your Sheet6 references. The Cells(...) identifiers implicitly work with the active sheet, unless you fully qualify them. If the active sheet is not the one with Sheet6 as the code name, your x range will be incorrect.

3. The Cholesky function is a UDF that returns an array. However, the code does not assign it to an array variable. The following worked for me:

Sub testcholesky()
Dim x As Range, arrCholesky
    
    With Sheet6
        Set x = .Range(.Cells(12, 12), .Cells(12 + 53 - 1, 12 + 53 - 1))
    End With
   
   arrCholesky = Cholesky(x)

End Sub

arrCholesky is now a 53x53 matrix.

4. I presume that the x range parameters were just for illustration, right? If not, use constants/variables so that any changes can be easily handled.

Const StRow as Long = 12
Const StCol as Long = 12
Const SizeRows as Long = 53
Const SizeCols as Long = 53

Set x = .Range(.Cells(StRow,StCol),.Cells(StRow+SizeRows-1, StCol+SizeCols-1))

is one example.

 

[Andrew Poulsom]

Does this do what you want?

Sub testcholesky()
    Dim x As Range
    With Sheet6
        Set x = .Range(.Cells(12, 12), .Cells(12 + 53 - 1, 12 + 53 - 1))
        .Cells(70, 12).Resize(53, 53).Value = Cholesky(x)
    End With
End Sub

 

[scottcolbury]

Much thanx

Hi guys,

Much thanx to both of you.
To answer your questions... the ranges in the testcholesky sub were for testing/example purposes. I've defined them much better in my actual code.

Your code suggestions worked beautifully.
I'm all set.

Thanx again and have a great week!
s_c