Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection of Ranges and Arrays
I have an application where the user selects up to 5 data sets (each containing exactly 226 values) and Excel calculates a weighted average. I use VLOOKUP to get all 5 datasets in consecutive rows and then use SUMPRODUCT 226 times (keying off another range containing the weight%). Functionally this approach works just fine, but the calculation times are starting to become significant. I have turned Automatic calculation off, but this requires me to set up triggers off certain events to make sure I don't miss some key calculations. I've decided to try to do all of the curve selection and calculation in a UserForm. In the code below, ColorantVals() are the weight% of each colorant, and the five datasets are populated in a Range array KSRng(). I copy the values into 5 seperate Variant arrays and do my SUMPRODUCT using a For/Next loop. The code below yields the desired result, but I have to wonder: Is there a way to combine the 5 variant arrays into one super vArr() array? Terry Option Base 1 Option Explicit Dim Rw As Integer, X As Integer Public CalcCurve(1, 226) As Variant Sub PredCurve() Dim KSRng(5) As Range, ColorantVals(5) As Single Dim vArr1 As Variant, vArr2 As Variant, vArr3 As Variant, vArr4 As Variant, vArr5 As Variant Dim CurrSht As Worksheet, DestRng as Range Set CurrSht = Worksheets("KS-Data") For X = 1 To 5 [snipped code that determines what Row dataset is in] Set KSRng(X) = CurrSht.Range(Cells(Rw, 6), Cells(Rw, 231)) Next X vArr1 = KSRng(1).Value vArr2 = KSRng(2).Value vArr3 = KSRng(3).Value vArr4 = KSRng(4).Value vArr5 = KSRng(5).Value For X = 1 To 226 CalcCurve(1, X) = vArr1(1, X) * ColorantVals(1) + vArr2(1, X) * ColorantVals(2) + vArr3(1, X) * ColorantVals(3) + vArr4(1, X) * ColorantVals(4) + vArr5(1, X) * ColorantVals(5) Next X Set DestRng = Range(Cells(2, 6), Cells(2, 231)) DestRng.Value = CalcCurve End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection of Ranges and Arrays
On Sun, 3 Aug 2008 18:40:59 +0000 (UTC), Terry Detrie
wrote: I've decided to try to do all of the curve selection and calculation in a UserForm. In the code below, ColorantVals() are the weight% of each colorant, and the five datasets are populated in a Range array KSRng(). I copy the values into 5 seperate Variant arrays and do my SUMPRODUCT using a For/Next loop. The code below yields the desired result, but I have to wonder: Is there a way to combine the 5 variant arrays into one super vArr() array? Sub PredCurve() Dim KSRng(5) As Range, ColorantVals(5) As Single Dim vSuper(5) As Variant Dim CurrSht As Worksheet, DestRng As Range Dim i As Long, j As Long Set CurrSht = Worksheets("KS-Data") For X = 1 To 5 Rw = X + 10 'added for testing vSuper(X) = CurrSht.Cells(Rw, 6).Resize(, 226).Value ColorantVals(X) = 1 'added for testing Next X For i = 1 To 226 CalcCurve(1, i) = 0 For j = 1 To 5 CalcCurve(1, i) = CalcCurve(1, i) _ + vSuper(j)(1, i) * ColorantVals(j) Next j Next i Set DestRng = CurrSht.Cells(2, 6).Resize(, 226) DestRng.Value = CalcCurve End Sub -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection of Ranges and Arrays
Hello Dick,
I'd never seen anything like "vSuper(j)(1, i)" before, but all the data looked to be in the right places when I put a watch on vSuper (and of course I got the same result as with my initial code). That code is beautiful, thank you. One follow-up question: is there a good web reference to help me better understand this next level of working with arrays? Terry On Sun, 3 Aug 2008 18:40:59 +0000 (UTC), Terry Detrie wrote: I've decided to try to do all of the curve selection and calculation in a UserForm. In the code below, ColorantVals() are the weight% of each colorant, and the five datasets are populated in a Range array KSRng(). I copy the values into 5 seperate Variant arrays and do my SUMPRODUCT using a For/Next loop. The code below yields the desired result, but I have to wonder: Is there a way to combine the 5 variant arrays into one super vArr() array? Sub PredCurve() Dim KSRng(5) As Range, ColorantVals(5) As Single Dim vSuper(5) As Variant Dim CurrSht As Worksheet, DestRng As Range Dim i As Long, j As Long Set CurrSht = Worksheets("KS-Data") For X = 1 To 5 Rw = X + 10 'added for testing vSuper(X) = CurrSht.Cells(Rw, 6).Resize(, 226).Value ColorantVals(X) = 1 'added for testing Next X For i = 1 To 226 CalcCurve(1, i) = 0 For j = 1 To 5 CalcCurve(1, i) = CalcCurve(1, i) _ + vSuper(j)(1, i) * ColorantVals(j) Next j Next i Set DestRng = CurrSht.Cells(2, 6).Resize(, 226) DestRng.Value = CalcCurve End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection of Ranges and Arrays
On Mon, 4 Aug 2008 02:33:22 +0000 (UTC), Terry Detrie
wrote: One follow-up question: is there a good web reference to help me better understand this next level of working with arrays? http://msdn.microsoft.com/en-us/libr...ffice.10).aspx http://www.cpearson.com/excel/VBAArrays.htm http://www.tushar-mehta.com/publish_...8_variants.htm There's a few. It's the power of Variants. They can hold almost anything. You could have an Array whose elements are arrays whose elements are arrays. It gets a little hard to follow after a while. Good luck, -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection of Ranges and Arrays
OK, new question
In the example below, I apply a custom function RR to each CalcCurve array element. The RR function has a rounding statement at the end RR = Round(RR * 100, 2). When I do a Watch on CalcCurve I see that each element is indeed rounded to two decimal places. The first four numbers in array a 5.17 5.24 5.29 5.32 When I populate the DestRng with CalcCurve, I get random rounding errors. In the worksheet those same four numbers a 5.170000076 5.239999771 5.289999962 5.320000172 Can someone enlighten me on what's going on and how to make Excel behave? Terry On Sun, 3 Aug 2008 18:40:59 +0000 (UTC), Terry Detrie wrote: I've decided to try to do all of the curve selection and calculation in a UserForm. In the code below, ColorantVals() are the weight% of each colorant, and the five datasets are populated in a Range array KSRng(). I copy the values into 5 seperate Variant arrays and do my SUMPRODUCT using a For/Next loop. The code below yields the desired result, but I have to wonder: Is there a way to combine the 5 variant arrays into one super vArr() array? Sub PredCurve() Dim KSRng(5) As Range, ColorantVals(5) As Single Dim vSuper(5) As Variant Dim CurrSht As Worksheet, DestRng As Range Dim i As Long, j As Long Set CurrSht = Worksheets("KS-Data") For X = 1 To 5 Rw = X + 10 'added for testing vSuper(X) = CurrSht.Cells(Rw, 6).Resize(, 226).Value ColorantVals(X) = 1 'added for testing Next X For i = 1 To 226 CalcCurve(1, i) = 0 For j = 1 To 5 CalcCurve(1, i) = CalcCurve(1, i) _ + vSuper(j)(1, i) * ColorantVals(j) Next j CalcCurve(1, i) = RR(CalcCurve(1, i)) Next i Set DestRng = CurrSht.Cells(2, 6).Resize(, 226) DestRng.Value = CalcCurve End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection of Ranges and Arrays
On Sun, 10 Aug 2008 05:09:02 +0000 (UTC), Terry Detrie
wrote: OK, new question In the example below, I apply a custom function RR to each CalcCurve array element. The RR function has a rounding statement at the end RR = Round(RR * 100, 2). When I do a Watch on CalcCurve I see that each element is indeed rounded to two decimal places. The first four numbers in array a 5.17 5.24 5.29 5.32 When I populate the DestRng with CalcCurve, I get random rounding errors. In the worksheet those same four numbers a 5.170000076 5.239999771 5.289999962 5.320000172 Can someone enlighten me on what's going on and how to make Excel behave? The problem is in the conversion from decimal to binary and back. Take 5.17, for instance. The .17 converted to binary is 0010101110000101000111 When you convert that back to decimal, it becomes ..169999983787537 Close enough, but irritatingly innacurate. I'm not sure how to avoid it other than writing each cell one at a time. That's probably not worth it, so just format the cells and forget about it. See also http://www.dailydoseofexcel.com/arch...ary-fractions/ -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection of Ranges and Arrays
Change the "As Single" declarations to "As Double"
Single precision only gives 7 decimal digit accuracy, where double precision gives 15 decimal digit accuracy. Since both use binary representations, figues beyond that point will usually be different than you expect, although Excel will not directly display them. In this case you got to see them because you stored a single precision value into a double precision variable where Excel would display figures 8-15. Jerry "Terry Detrie" wrote: OK, new question In the example below, I apply a custom function RR to each CalcCurve array element. The RR function has a rounding statement at the end RR = Round(RR * 100, 2). When I do a Watch on CalcCurve I see that each element is indeed rounded to two decimal places. The first four numbers in array a 5.17 5.24 5.29 5.32 When I populate the DestRng with CalcCurve, I get random rounding errors. In the worksheet those same four numbers a 5.170000076 5.239999771 5.289999962 5.320000172 Can someone enlighten me on what's going on and how to make Excel behave? Terry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a collection of ranges | Excel Programming | |||
Working with ranges in arrays... or an introduction to arrays | Excel Programming | |||
Alternitive to Collection of Ranges | Excel Programming | |||
Collection vs. Array of Arrays, nszim | Excel Programming | |||
update collection of arrays | Excel Programming |