Excel: getting values out of a variant array, VBA into adjoining cells on worksheet

[scubak1w1]

Hello,


For a project I am working on, I wrote a piece of code in VBA backend of Excel to convert RGB colour values to HSV...

The VBA is working fine, and as you can see at the bottom I am stuffing the three results generated into an array expressed (sic) as a variant...

The first value in the returned array, Hue, is dropped into the cell where the function is called (E2 in the attached screen shot..) - i.e., "=fntHSVtoRGB(E2 ,F2,G2)"

In Excel do I get access to the 2nd and 3rd values in the array (i.e., saturation and value) and put in the correct adjacent cells? (i.e., cells F2 and G2, respectively, in the screen shot attached)

As you can see towards the bottom of the code, I have tested using a message box and so know that the array has them in there! Frustrating as you can imagine...

I am thinking there is some CTRL+SHIFT+ENTE R approach – but I don’t use Excel for arrays much, that is what databases are for! ;-)

I am guessing I could call three variations of the function, one for hue, saturation and value - but that does not seem very elegant!

[aside: Python has RGB --> HSV and back as a built in functions, guess it could be a good time to start using Python... <wink>]

Thanks in advance for any input!

Cheers:
GREG COCKS

----------------------------------------

 
Function fntRGBtoHSV(intRed As Integer, intGreen As Integer, intBlue As Integer) As Variant
 
    'algorithm derived from http://www.easyrgb.com/index.php?X=MATH#text2
 
    Dim aryHSLResults As Variant
 
    Dim intMax As Integer
    Dim intMin As Integer
 
    Dim dblWorkingR As Double
    Dim dblWorkingG As Double
    Dim dblWorkingB As Double
 
    Dim dblHue As Double
    Dim dblSaturation As Double
    Dim dblValue As Double
 
    Dim dblDelta As Integer
 
    intMax = fntMaximum(intRed, intGreen, intBlue)
 
    intMin = fntMinimum(intRed, intGreen, intBlue)
 
    dblDelta = intMax - intMin
 
    dblValue = intMax
 
    If (intMax = 0) Then
 
        'i.e., this is a gray, no chroma
        dblHue = 0
        dblSaturation = 0
        dblValue = 0
 
    Else
 
        'i.e., a chromatic value
        dblSaturation = dblDelta / intMax
        dblWorkingR = (((intMax - intRed) / 6) + (dblDelta / 2)) / dblDelta
        dblWorkingG = (((intMax - intGreen) / 6) + (dblDelta / 2)) / dblDelta
        dblWorkingB = (((intMax - intBlue) / 6) + (dblDelta / 2)) / dblDelta
 
        If (intRed = intMax) Then
            'red is max
            dblHue = dblWorkingB - dblWorkingG
        Else
            If (intGreen = intMax) Then
                'green is max
                dblHue = (1 / 3) + dblWorkingR - dblWorkingB
            Else
                If (intBlue = intMax) Then
                    'blue is max
                    dblHue = (2 / 3) + dblWorkingG - dblWorkingR
                End If
            End If
        End If
 
    End If
 
    If (dblHue < 0) Then
        dblHue = dblHue + 1
    Else
        If (dblHue > 1) Then
            dblHue = dblHue - 1
        End If
    End If
 
    'convert hue to degrees
    dblHue = dblHue * 360
 
    'convert (sic) saturation to a percentage
    dblSaturation = dblSaturation * 100
 
    'convert 'value' with a distinct multipler
    dblValue = dblValue / cstValueMultipler
 
    'stuff the three results into an array
    aryHSLResults = Array(dblHue, dblSaturation, dblValue)
 
    'DRAFT @ 12/15/10 - delete this message box call
    MsgBox ("HSV: " & aryHSLResults(0) & ", " & aryHSLResults(1) & ", " & aryHSLResults(2))
 
    'send the values back
    fntRGBtoHSV = aryHSLResults
 
End Function