Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use VBA to get values from named range that contains an array formulaexpression
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use VBA to get values from named range that contains an array formula expression
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 wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use VBA to get values from named range that contains an arrayformula expression
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 On Apr 27, 5:44*am, "Peter T" <peter_t@discussions wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use VBA to get values from named range that contains an array formula expression
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 wrote in message ... 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 On Apr 27, 5:44 am, "Peter T" <peter_t@discussions wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
Named range in an array | Excel Programming | |||
Named range into an array | Excel Worksheet Functions | |||
Named Range to Array Oddity | Excel Programming | |||
Defined named range to array | Excel Programming |