Collection of Ranges and Arrays

[Terry Detrie]

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 Su

 

[Dick Kusleika]

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

 

[Terry Detrie]

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

 

[Dick Kusleika]

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/library/aa164778(office.10).aspx
http://www.cpearson.com/excel/VBAArrays.htm
http://www.tushar-mehta.com/publish_train/book_vba/08_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,

 

[Terry Detrie]

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 are:
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 are:
5.170000076 5.239999771 5.289999962 5.320000172

Can someone enlighten me on what's going on and how to make Excel behave?

Terry

 

[Dick Kusleika]

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 are:
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 are:
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/archives/2007/06/06/subtracting-binary-fractions/

 

[Jerry W. Lewis]

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