Return Matrix as Range From Function

mechanik · May 17th 2007

Hi

I'm trying to write some code to return a matrix as a range from a function. (The reason is that I already have functions that take in ranges as input, and i want to use this function there)

Code

Function Correlcalc(Stock1 As Range, Stock2 As Range, Stock3 As Range) As Range


Dim RangeArray(3) As Range
Dim Correls(3, 3) As Double
Dim correlrange As Range


Set RangeArray(1) = Stock1
Set RangeArray(2) = Stock2
Set RangeArray(3) = Stock3




For i = 1 To 3
    For j = 1 To 3
    Correls(i - 1, j - 1) = Application.WorksheetFunction.Correl(RangeArray(i), RangeArray(j))
    Next j
Next i


'/where it's not working
correlrange.Resize(3, 3) = Correls


Set Correlcalc = correlrange


End Function

Display More

I think the problem lies in the line

Code

correlrange.Resize(3, 3) = Correls

and i don't know how to transfer the array to a range. The tips i tried to modify from the forum don't seem to work

filo65 · May 18th 2007

Re: Array From A Range And Then Returning It From Function

mechanik,

welcome to the forum.

you probably miss the array definition "()" for correlrange

Code

Dim correlrange [B][COLOR="Red"]()[/COLOR][/B]As Range

filippo

**Andy Pope** · May 18th 2007

Re: Array From A Range And Then Returning It From Function

When using objects, such as ranges, you need to use the SET command.

Code

Set correlrange.Resize(3, 3) = Correls

filo65 · May 18th 2007

Re: Array From A Range And Then Returning It From Function

HI Andy,

wrong tags. this time I cannot edit for you!!: D

filippo

**Andy Pope** · May 18th 2007

Re: Array From A Range And Then Returning It From Function

Yes I just noticed that, thanks.

shg · May 18th 2007

Re: Return Matrix as Range From Function

This line of code would write the contents of Correls to correlrange on the worksheet, except that is Nothing when you execute it; it has not been initialized to point to any range:

Code

correlrange.Resize(3, 3) = Correls

If your intent is to set it to point to the array, as in

Code

Set correlrange.Resize(3, 3) = Correls

then I'd expect it to fail on the Resize (because it isn't defined, so there's nothing to resize), and if you got past that, it would fail on the assignment (because it's a type mismatch).

**Andy Pope** · May 18th 2007

Re: Return Matrix as Range From Function

I just noticed Correls is an array not a range so the set will not work at that point.

Code

Set correlrange = Range("A1").Resize(3, 3)
correlrange = Correls

Return Matrix as Range From Function

Participate now!

Share