Data Mining in Excel - Automated Data Analysis Using Excel - page 167

ColorNames = ",
vbBlack,vbRed,vbGreen,vbYellow,vbBlue,vbMagenta,vbCyan,vbWhite"
Colors() = Split (ColorNames, ",")
ReDim ColorMatrix(8, 2)
For vbColor = 1 To 8
Select Case vbColor
Case 1
vbColorValue(vbColor) = 0
Case 2
vbColorValue(vbColor) = 255
Case Else
If IsOdd(vbColor) = True Then
'Odd Reliant Upon Index
vbColorValue(vbColor) = vbColorValue(vbColor - 2 ^
oddmult) * (255 + ((2 ^ oddmult) - oddmult))
oddmult = oddmult + 1
Else
'Even Always Same Formula
vbColorValue(vbColor) = vbColorValue(vbColor - 1) + 255
End If
End Select
'Set Color Name
ColorMatrix(vbColor, 1) = Colors(vbColor)
'Set Color Value
ColorMatrix(vbColor, 2) = vbColorValue(vbColor)
Next vbColor
End Sub
The second dimension of the passed array is what determines which parameter is stored. If the
second dimension is a “1,” then a text item storing a color name is held in that position. If the second
dimension is a “2,” then a number that is the color constant is held in that position. The foregoing
subroutine can be tested rather easily by running the following subroutine:

Sub TestCreateColorArray()
Dim ColorMatrix()
Call CreateColorArray(ColorMatrix())
For ii = 1 To UBound(ColorMatrix(), 1)
Debug.Print ii, ColorMatrix(ii, 1), ColorMatrix(ii, 2)
Next ii
End Sub
This will produce the following output in the immediate window:

1 vbBlack 0

2 vbRed 255

3 vbGreen 65280

4 vbYellow 65535