Use VBA to get values from named range that contains an array formulaexpression

[jhcoxx]

I've used an array formula (linest, as a test) to create an array
formula in an named range by typing the array formula into the Refers
to box on the Define Name pop-up window.

By entering the Index worksheet function in a worksheet cell, I can
access the values in the linest result 'array' but I can't figure out
how to access the values in VBA.

Surely someone has done this, but my best thinking and a lot of
internet searches haven't turned up an answer.

Thanks in advance, even if the answer turns out to be "You can't do
that!"

James

 

[Peter T]

Hi James,

It's not clear what you are doing, eg have you created a named range or a
named formula or both, how are you using Index, etc.

I did the following -

Created two dynamic named ranges KnownX & KnownY to refer to cells in
columns A & B

Created a named formula
"nmLinest" refersto: =LINEST(KnownY, KnownX^{1,2})

I entered some X's in col-A, FWIW 1, 2 & 3
For Y's, in B1: =A1^2*2+A1*3+5
and filled down

In VBA -

arr = Evaluate("nmLinest")

For i = 1 To UBound(arr) ' to 3
Debug.Print Application.Index(arr, i)
Next

1.99999999999999
3.00000000000004
4.99999999999997

A tad of rounding would return correct results of 2, 3 & 5 in this contrived
example.

Regards,
Peter T

 

[jhcoxx]

Thanks, Peter!

(Memo to self - when posting between 1 AM and 8 AM, give lots of
explanation about what I'm asking!)

Using your method, I generated the following cells in a worksheet


A B
1 1 10
2 2 19
3 3 32
4 4 49
5 5 70
6 6 95
7 7 124
8 8 157
9 9 194
10 10 235

To define what I should be expecting from the Linest formula, I
selected the range C1:E10 and in that range entered the array formula
=Linest(KnownY,KnownX,True,True). This gave the following values


A B C D E
1 1 10 25 -39 #N/A
2 2 19 1.788854382 11.09954954 #N/A
3 3 32 0.960651706 16.24807681 #N/A
4 4 49 195.3125 8 #N/A
5 5 70 51562.5 2112 #N/A
6 6 95 #N/A #N/A #N/A
7 7 124 #N/A #N/A #N/A
8 8 157 #N/A #N/A #N/A
9 9 194 #N/A #N/A #N/A
10 10 235 #N/A #N/A #N/A

(Obviously, I made the array formula range too large, but that
shouldn't affect anything below)

Next, I used Insert | Names | Define to define a named range PNR
(Peter's Named Range) and in the Refers to box put the formula used
above =Linest(KnownY,KnownX,True,True)

(I freely admit that your formula puzzled me - too early again,
perhaps - but the KnownX^{1,2} part blew right by me, and it wasn't
accepted when I tried to enter it as part of the array formula for a
worksheet range. Howerver, for my true application, I need the
equivalent of the statistics generated by Linest, so I went ahead.)

In the VBA code, your

Arr =Evaluate("PNR")

was the piece that was I needed - after that, even a plain

Debug.Print Arr(i,n)

would return the same values as seen in the worksheet (for appropriate
values of i and n, of course) !

BTW, I did define a second named range/array formula via Insert |
Names | Define using your expression

refersto: =LINEST(KnownY, KnownX^{1,2})

and it works just like you said - I just don't understand what it's
calculating for me.


Again - thanks, Peter!

James

 

[Peter T]

Glad it's all working. Indeed no need to use the Index function at all (but
you had asked about it!).

FWIW, just for fun you could do -

arr = Evaluate("PNR")
Range("F2").Resize(UBound(arr), UBound(arr, 2)).Value = arr

or with the 1D array that's returned with the example I posted

arr = Evaluate("nmLinest")
Range("d16").Resize(, UBound(arr)).Value = arr

refersto: =LINEST(KnownY, KnownX^{1,2})
I just don't understand what it's
calculating for me.

The coefficients of a 2-order polynomial, a, b & c
y = ax^2 + bx + c

Now you've got it working don't want to be a killjoy but the formula will
probably calculate faster in cells than with Evaluate !

Regards,
Peter T


Thanks, Peter!

(Memo to self - when posting between 1 AM and 8 AM, give lots of
explanation about what I'm asking!)

Using your method, I generated the following cells in a worksheet


A B
1 1 10
2 2 19
3 3 32
4 4 49
5 5 70
6 6 95
7 7 124
8 8 157
9 9 194
10 10 235

To define what I should be expecting from the Linest formula, I
selected the range C1:E10 and in that range entered the array formula
=Linest(KnownY,KnownX,True,True). This gave the following values


A B C D E
1 1 10 25 -39 #N/A
2 2 19 1.788854382 11.09954954 #N/A
3 3 32 0.960651706 16.24807681 #N/A
4 4 49 195.3125 8 #N/A
5 5 70 51562.5 2112 #N/A
6 6 95 #N/A #N/A #N/A
7 7 124 #N/A #N/A #N/A
8 8 157 #N/A #N/A #N/A
9 9 194 #N/A #N/A #N/A
10 10 235 #N/A #N/A #N/A

(Obviously, I made the array formula range too large, but that
shouldn't affect anything below)

Next, I used Insert | Names | Define to define a named range PNR
(Peter's Named Range) and in the Refers to box put the formula used
above =Linest(KnownY,KnownX,True,True)

(I freely admit that your formula puzzled me - too early again,
perhaps - but the KnownX^{1,2} part blew right by me, and it wasn't
accepted when I tried to enter it as part of the array formula for a
worksheet range. Howerver, for my true application, I need the
equivalent of the statistics generated by Linest, so I went ahead.)

In the VBA code, your

Arr =Evaluate("PNR")

was the piece that was I needed - after that, even a plain

Debug.Print Arr(i,n)

would return the same values as seen in the worksheet (for appropriate
values of i and n, of course) !

BTW, I did define a second named range/array formula via Insert |
Names | Define using your expression

refersto: =LINEST(KnownY, KnownX^{1,2})

and it works just like you said - I just don't understand what it's
calculating for me.


Again - thanks, Peter!

James