By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,321 Members | 1,194 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,321 IT Pros & Developers. It's quick & easy.

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

scubak1w1
P: 53
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+ENTER 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

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function fntRGBtoHSV(intRed As Integer, intGreen As Integer, intBlue As Integer) As Variant
  3.  
  4.     'algorithm derived from http://www.easyrgb.com/index.php?X=MATH#text2
  5.  
  6.     Dim aryHSLResults As Variant
  7.  
  8.     Dim intMax As Integer
  9.     Dim intMin As Integer
  10.  
  11.     Dim dblWorkingR As Double
  12.     Dim dblWorkingG As Double
  13.     Dim dblWorkingB As Double
  14.  
  15.     Dim dblHue As Double
  16.     Dim dblSaturation As Double
  17.     Dim dblValue As Double
  18.  
  19.     Dim dblDelta As Integer
  20.  
  21.     intMax = fntMaximum(intRed, intGreen, intBlue)
  22.  
  23.     intMin = fntMinimum(intRed, intGreen, intBlue)
  24.  
  25.     dblDelta = intMax - intMin
  26.  
  27.     dblValue = intMax
  28.  
  29.     If (intMax = 0) Then
  30.  
  31.         'i.e., this is a gray, no chroma
  32.         dblHue = 0
  33.         dblSaturation = 0
  34.         dblValue = 0
  35.  
  36.     Else
  37.  
  38.         'i.e., a chromatic value
  39.         dblSaturation = dblDelta / intMax
  40.         dblWorkingR = (((intMax - intRed) / 6) + (dblDelta / 2)) / dblDelta
  41.         dblWorkingG = (((intMax - intGreen) / 6) + (dblDelta / 2)) / dblDelta
  42.         dblWorkingB = (((intMax - intBlue) / 6) + (dblDelta / 2)) / dblDelta
  43.  
  44.         If (intRed = intMax) Then
  45.             'red is max
  46.             dblHue = dblWorkingB - dblWorkingG
  47.         Else
  48.             If (intGreen = intMax) Then
  49.                 'green is max
  50.                 dblHue = (1 / 3) + dblWorkingR - dblWorkingB
  51.             Else
  52.                 If (intBlue = intMax) Then
  53.                     'blue is max
  54.                     dblHue = (2 / 3) + dblWorkingG - dblWorkingR
  55.                 End If
  56.             End If
  57.         End If
  58.  
  59.     End If
  60.  
  61.     If (dblHue < 0) Then
  62.         dblHue = dblHue + 1
  63.     Else
  64.         If (dblHue > 1) Then
  65.             dblHue = dblHue - 1
  66.         End If
  67.     End If
  68.  
  69.     'convert hue to degrees
  70.     dblHue = dblHue * 360
  71.  
  72.     'convert (sic) saturation to a percentage
  73.     dblSaturation = dblSaturation * 100
  74.  
  75.     'convert 'value' with a distinct multipler
  76.     dblValue = dblValue / cstValueMultipler
  77.  
  78.     'stuff the three results into an array
  79.     aryHSLResults = Array(dblHue, dblSaturation, dblValue)
  80.  
  81.     'DRAFT @ 12/15/10 - delete this message box call
  82.     MsgBox ("HSV: " & aryHSLResults(0) & ", " & aryHSLResults(1) & ", " & aryHSLResults(2))
  83.  
  84.     'send the values back
  85.     fntRGBtoHSV = aryHSLResults
  86.  
  87. End Function
  88.  
  89.  
Attached Images
File Type: jpg screen_shot.jpg (30.4 KB, 89 views)
Dec 16 '10 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.