# Passing a range into a VBA function

#### scottcolbury

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

Code:
``````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:

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]

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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:

Code:
``````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.

Does this do what you want?

Code:
``````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``````

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.

I'm all set.

Thanx again and have a great week!
s_c

Replies
22
Views
972
Replies
2
Views
442
Replies
11
Views
245
Replies
24
Views
921
Replies
7
Views
386

1,219,913
Messages
6,150,932
Members
450,994
Latest member
MacOrch

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back