A Problem in an User Definied Function About Color Info

[Erdin? E. Ka]

Hi everyone,

I wrote a U.D.F. about color info (interior or font) of a cell.

But if a range has no color then my U.D.F. returning 0 ( zero ). But i want that; if user want to get the name of the color information in English: U.D.F. must returns "No Color", in Turksih selection U.D.F. must return: "Renk Yok".

But i can't solved this problem. :think:

The codes like below:



Function COLOR(CeLL As Range, Language As Byte, Return_Only_The_Index_Number As Byte, _
InteriorColor_Or_FontColor As Byte) As Variant
'HELP AND DESCRIPTIONS:
'CeLL : Which Cell You want to Analysis: A6
'Language : for Turkish Language use 1 , for English Language 2
'Return_Only_The_Index_Number : If This argument = 0 thu UDF Returns the ColorIndex Number.
'InteriorColor_Or_FontColor : If you input 1 for this argument: UDF retuns you the INTERIOR
'color, If you input 2 for this argument: UDF retuns you the FONT color
'Not: Her bir arg?mana mutlaka bir değer girilmelidir. Yani =COLOR(A6;1) Gibi sadece iki
'değeri vererek form?l yazmayınız.
'
'Example1: Write This Formula For Learn INTERIOR Color of A6 Cell in Turkish :
'=COLOR(A6;1;1;1)
'Example2: Write This Formula For Learn INTERIOR Color of A6 Cell in English :
'=COLOR(A6;2;1;1)
'Example3: Write This Formula For Learn FONT Color of A6 Cell in English: =COLOR(A6;2;1;2)
'Example4: Write This Formula For Learn The ColorIndex Number of the FONT Color of A6 Cell:
'=COLOR(A6;2;0;2)
'Example5: Write This Formula For Learn The ColorIndex Number of the INTERIOR Color of A6 Cell:
'=COLOR(A6;2;0;1)
'You must write some data for all arguments. For example: If you write like =COLOR(A6;1)
'then UDF returns #VALUE!. So, please don't write absent UDF arguments.
'
Application.Volatile
On Error GoTo End_Of_The_Function
Dim No As Byte
Dim Result As String
If InteriorColor_Or_FontColor = 1 Then
No = CeLL.Interior.ColorIndex
ElseIf InteriorColor_Or_FontColor = 2 Then
No = CeLL.Font.ColorIndex
End If
If Return_Only_The_Index_Number <> 0 Then
If Language = 1 Then
Select Case No
Case Is = 1: Result = "Siyah"
Case Is = 2: Result = "Beyaz"
Case Is = 3: Result = "Kırmızı"
Case Is = 4: Result = "Parlak Yeşil"
Case Is = 5: Result = "Mavi"
Case Is = 6: Result = "Sarı"
Case Is = 7: Result = "Pembe"
Case Is = 8: Result = "Turkuaz"
Case Is = 9: Result = "Koyu Kırmızı"
Case Is = 10: Result = "Yeşil"
Case Is = 11: Result = "Koyu Mavi"
Case Is = 12: Result = "Koyu Sarı"
Case Is = 13: Result = "Mor"
Case Is = 14: Result = "Deniz Mavisi"
Case Is = 15: Result = "Gri %25"
Case Is = 16: Result = "Gri %50"
Case Is = 17: Result = "Koyu Soluk Mavi"
Case Is = 18: Result = "Erik Rengi 2"
Case Is = 19: Result = "?ok A?ık Sarı"
Case Is = 20: Result = "?ok A?ık G?k Mavisi"
Case Is = 21: Result = "Koyu Mor"
Case Is = 22: Result = "Somon"
Case Is = 23: Result = "Mavi 2"
Case Is = 24: Result = "A?ık İndigo"
Case Is = 25: Result = "Lacivert"
Case Is = 26: Result = "Pembe 2"
Case Is = 27: Result = "Sarı 2"
Case Is = 28: Result = "Turkuaz 2"
Case Is = 29: Result = "?ok Koyu Mor"
Case Is = 30: Result = "Kiremit"
Case Is = 31: Result = "A?ık Petrol Mavisi"
Case Is = 32: Result = "Mavi 3"
Case Is = 33: Result = "G?k Mavisi"
Case Is = 34: Result = "A?ık Turkuaz"
Case Is = 35: Result = "A?ık Yeşil"
Case Is = 36: Result = "A?ık Sarı"
Case Is = 37: Result = "Soluk Mavi"
Case Is = 38: Result = "G?l"
Case Is = 39: Result = "A?ık Eflatun"
Case Is = 40: Result = "Tan"
Case Is = 41: Result = "A?ık Mavi"
Case Is = 42: Result = "A?ık Deniz Mavisi"
Case Is = 43: Result = "Limon Rengi"
Case Is = 44: Result = "Altın"
Case Is = 45: Result = "A?ık Turuncu"
Case Is = 46: Result = "Turuncu"
Case Is = 47: Result = "Gri-Mavi"
Case Is = 48: Result = "Gri %40"
Case Is = 49: Result = "Koyu Deniz Mavisi"
Case Is = 50: Result = "Deniz Yeşili"
Case Is = 51: Result = "Koyu Yeşil"
Case Is = 52: Result = "Zeytin Yeşili"
Case Is = 53: Result = "Kahverengi"
Case Is = 54: Result = "Erik Rengi"
Case Is = 55: Result = "İndigo"
Case Is = 56: Result = "Gri %80"
End Select
ElseIf Language = 2 Then
Select Case No
Case Is = 1: Result = "Black"
Case Is = 2: Result = "White"
Case Is = 3: Result = "Red"
Case Is = 4: Result = "Bright Green"
Case Is = 5: Result = "Blue"
Case Is = 6: Result = "Yellow"
Case Is = 7: Result = "Pink"
Case Is = 8: Result = "Turquoise"
Case Is = 9: Result = "Dark Red"
Case Is = 10: Result = "Green"
Case Is = 11: Result = "Dark Blue"
Case Is = 12: Result = "Dark Yellow"
Case Is = 13: Result = "Violet"
Case Is = 14: Result = "Teal"
Case Is = 15: Result = "Gray-25%"
Case Is = 16: Result = "Gray-50%"
Case Is = 17: Result = "Dark Pale Blue"
Case Is = 18: Result = "Plum 2"
Case Is = 19: Result = "Very Light Yellow"
Case Is = 20: Result = "Very Light Sky Blue"
Case Is = 21: Result = "Dark Violet"
Case Is = 22: Result = "Somon"
Case Is = 23: Result = "Blue 2"
Case Is = 24: Result = "Light Indigo"
Case Is = 25: Result = "Dark Blue 2"
Case Is = 26: Result = "Pink 2"
Case Is = 27: Result = "Yellow 2"
Case Is = 28: Result = "Turquoise 2"
Case Is = 29: Result = "Very Dark Violet"
Case Is = 30: Result = "Clay Roofing Tile"
Case Is = 31: Result = "Light Petroil Blue"
Case Is = 32: Result = "Blue 3"
Case Is = 33: Result = "Sky Blue"
Case Is = 34: Result = "Light Turquoise"
Case Is = 35: Result = "Light Green"
Case Is = 36: Result = "Light Yellow"
Case Is = 37: Result = "Pale Blue"
Case Is = 38: Result = "Rose"
Case Is = 39: Result = "Lavender"
Case Is = 40: Result = "Tan"
Case Is = 41: Result = "Light Blue"
Case Is = 42: Result = "Aqua"
Case Is = 43: Result = "Lime"
Case Is = 44: Result = "Gold"
Case Is = 45: Result = "Light Orange"
Case Is = 46: Result = "Orange"
Case Is = 47: Result = "Blue-Gray"
Case Is = 48: Result = "Gray-40%"
Case Is = 49: Result = "Dark Teal"
Case Is = 50: Result = "Sea Green"
Case Is = 51: Result = "Dark Green"
Case Is = 52: Result = "Olive Green"
Case Is = 53: Result = "Brown"
Case Is = 54: Result = "Plum"
Case Is = 55: Result = "Indigo"
Case Is = 56: Result = "Gray-80%"
End Select
End If
COLOR = Result
Else
COLOR = No
End If
End_Of_The_Function:
End Function


Also i posted the sample worksheet of U.D.F.

Any ideas?:think:

Thanks in advance.:friends:

Edited 30-Mar-07 by geekgirlau. Reason: insert line breaks

[geekgirlau]

When there is no colour, this section will fail as the value (-4142) cannot be stored as a byte. However if you add the "On Error Resume Next", then you can add "Case Is = 0" to your Select Case statement.



On Error Resume Next
If InteriorColor_Or_FontColor = 1 Then
No = CeLL.Interior.ColorIndex
ElseIf InteriorColor_Or_FontColor = 2 Then
No = CeLL.Font.ColorIndex
End If

[Erdin? E. Ka]

Hey!!

I solved it a few seconds ago!!! :mbounce2:




Function COLOR(CeLL As Range, Language As Byte, Return_Only_The_Index_Number As Byte, InteriorColor_Or_FontColor As Byte) As Variant
'HELP AND DESCRIPTIONS:
'CeLL : Which Cell You want to Analysis: A6
'Language : for Turkish Language use 1 , for English Language 2
'Return_Only_The_Index_Number : If This argument = 0 thu UDF Returns the ColorIndex Number.
'InteriorColor_Or_FontColor : If you input 1 for this argument: UDF retuns you the INTERIOR color, If you input 2 for this argument: UDF retuns you the FONT color
'
'Example1: Write This Formula For Learn INTERIOR Color of A6 Cell in Turkish : =COLOR(A6;1;1;1)
'Example2: Write This Formula For Learn INTERIOR Color of A6 Cell in English : =COLOR(A6;2;1;1)
'Example3: Write This Formula For Learn FONT Color of A6 Cell in English: =COLOR(A6;2;1;2)
'Example4: Write This Formula For Learn The ColorIndex Number of the FONT Color of A6 Cell: =COLOR(A6;2;0;2)
'Example5: Write This Formula For Learn The ColorIndex Number of the INTERIOR Color of A6 Cell: =COLOR(A6;2;0;1)
'You must write some data for all arguments. For example: If you write like =COLOR(A6;1) then UDF returns #VALUE!. So, please don't write absent UDF arguments.
'
Application.Volatile
On Error GoTo End_Of_The_Function
Dim No As Byte
Dim Result As String
If InteriorColor_Or_FontColor = 1 Then
No = CeLL.Interior.ColorIndex
ElseIf InteriorColor_Or_FontColor = 2 Then
No = CeLL.Font.ColorIndex
End If
If Return_Only_The_Index_Number <> 0 Then
If Language = 1 Then
Select Case No
Case Is = 1: Result = "Siyah"
Case Is = 2: Result = "Beyaz"
Case Is = 3: Result = "Kırmızı"
Case Is = 4: Result = "Parlak Yeşil"
Case Is = 5: Result = "Mavi"
Case Is = 6: Result = "Sarı"
Case Is = 7: Result = "Pembe"
Case Is = 8: Result = "Turkuaz"
Case Is = 9: Result = "Koyu Kırmızı"
Case Is = 10: Result = "Yeşil"
Case Is = 11: Result = "Koyu Mavi"
Case Is = 12: Result = "Koyu Sarı"
Case Is = 13: Result = "Mor"
Case Is = 14: Result = "Deniz Mavisi"
Case Is = 15: Result = "Gri %25"
Case Is = 16: Result = "Gri %50"
Case Is = 17: Result = "Koyu Soluk Mavi"
Case Is = 18: Result = "Erik Rengi 2"
Case Is = 19: Result = "?ok A?ık Sarı"
Case Is = 20: Result = "?ok A?ık G?k Mavisi"
Case Is = 21: Result = "Koyu Mor"
Case Is = 22: Result = "Somon"
Case Is = 23: Result = "Mavi 2"
Case Is = 24: Result = "A?ık İndigo"
Case Is = 25: Result = "Lacivert"
Case Is = 26: Result = "Pembe 2"
Case Is = 27: Result = "Sarı 2"
Case Is = 28: Result = "Turkuaz 2"
Case Is = 29: Result = "?ok Koyu Mor"
Case Is = 30: Result = "Kiremit"
Case Is = 31: Result = "A?ık Petrol Mavisi"
Case Is = 32: Result = "Mavi 3"
Case Is = 33: Result = "G?k Mavisi"
Case Is = 34: Result = "A?ık Turkuaz"
Case Is = 35: Result = "A?ık Yeşil"
Case Is = 36: Result = "A?ık Sarı"
Case Is = 37: Result = "Soluk Mavi"
Case Is = 38: Result = "G?l"
Case Is = 39: Result = "A?ık Eflatun"
Case Is = 40: Result = "Tan"
Case Is = 41: Result = "A?ık Mavi"
Case Is = 42: Result = "A?ık Deniz Mavisi"
Case Is = 43: Result = "Limon Rengi"
Case Is = 44: Result = "Altın"
Case Is = 45: Result = "A?ık Turuncu"
Case Is = 46: Result = "Turuncu"
Case Is = 47: Result = "Gri-Mavi"
Case Is = 48: Result = "Gri %40"
Case Is = 49: Result = "Koyu Deniz Mavisi"
Case Is = 50: Result = "Deniz Yeşili"
Case Is = 51: Result = "Koyu Yeşil"
Case Is = 52: Result = "Zeytin Yeşili"
Case Is = 53: Result = "Kahverengi"
Case Is = 54: Result = "Erik Rengi"
Case Is = 55: Result = "İndigo"
Case Is = 56: Result = "Gri %80"
Case Else: Result = "Renk Yok"
End Select
ElseIf Language = 2 Then
Select Case No
Case Is = 1: Result = "Black"
Case Is = 2: Result = "White"
Case Is = 3: Result = "Red"
Case Is = 4: Result = "Bright Green"
Case Is = 5: Result = "Blue"
Case Is = 6: Result = "Yellow"
Case Is = 7: Result = "Pink"
Case Is = 8: Result = "Turquoise"
Case Is = 9: Result = "Dark Red"
Case Is = 10: Result = "Green"
Case Is = 11: Result = "Dark Blue"
Case Is = 12: Result = "Dark Yellow"
Case Is = 13: Result = "Violet"
Case Is = 14: Result = "Teal"
Case Is = 15: Result = "Gray-25%"
Case Is = 16: Result = "Gray-50%"
Case Is = 17: Result = "Dark Pale Blue"
Case Is = 18: Result = "Plum 2"
Case Is = 19: Result = "Very Light Yellow"
Case Is = 20: Result = "Very Light Sky Blue"
Case Is = 21: Result = "Dark Violet"
Case Is = 22: Result = "Somon"
Case Is = 23: Result = "Blue 2"
Case Is = 24: Result = "Light Indigo"
Case Is = 25: Result = "Dark Blue 2"
Case Is = 26: Result = "Pink 2"
Case Is = 27: Result = "Yellow 2"
Case Is = 28: Result = "Turquoise 2"
Case Is = 29: Result = "Very Dark Violet"
Case Is = 30: Result = "Clay Roofing Tile"
Case Is = 31: Result = "Light Petroil Blue"
Case Is = 32: Result = "Blue 3"
Case Is = 33: Result = "Sky Blue"
Case Is = 34: Result = "Light Turquoise"
Case Is = 35: Result = "Light Green"
Case Is = 36: Result = "Light Yellow"
Case Is = 37: Result = "Pale Blue"
Case Is = 38: Result = "Rose"
Case Is = 39: Result = "Lavender"
Case Is = 40: Result = "Tan"
Case Is = 41: Result = "Light Blue"
Case Is = 42: Result = "Aqua"
Case Is = 43: Result = "Lime"
Case Is = 44: Result = "Gold"
Case Is = 45: Result = "Light Orange"
Case Is = 46: Result = "Orange"
Case Is = 47: Result = "Blue-Gray"
Case Is = 48: Result = "Gray-40%"
Case Is = 49: Result = "Dark Teal"
Case Is = 50: Result = "Sea Green"
Case Is = 51: Result = "Dark Green"
Case Is = 52: Result = "Olive Green"
Case Is = 53: Result = "Brown"
Case Is = 54: Result = "Plum"
Case Is = 55: Result = "Indigo"
Case Is = 56: Result = "Gray-80%"
Case Else: Result = "No Color"
End Select
End If
COLOR = Result
Else
COLOR = No
End If
Exit Function
End_Of_The_Function:
If Return_Only_The_Index_Number <> 0 Then
If Language = 1 Then
Select Case InteriorColor_Or_FontColor
Case Is = 1: COLOR = "Dolgu Rengi Yok"
Case Is = 2: COLOR = "Yazı Tipi Rengi Yok"
End Select
ElseIf Language = 2 Then
Select Case InteriorColor_Or_FontColor
Case Is = 1: COLOR = "No Interior Color"
Case Is = 2: COLOR = "No Font Color"
End Select
End If
ElseIf Return_Only_The_Index_Number = 0 Then
COLOR = "xlNone"
End If
End Function

[mdmackillop]

I was going to suggest

Case Is = xlNone

[Erdin? E. Ka]

I was going to suggest

Case Is = xlNone


Hi Malcolm,

At the first time, i was tried same like your suggestion. But it wasn't worked well. I think that problem was depened on types of the variables. But no matter for now because, it's ok for me.

Thank you very very much to kindly suggestion. Take care. :friends:

[Erdin? E. Ka]

When there is no colour, this section will fail as the value (-4142) cannot be stored as a byte. However if you add the "On Error Resume Next", then you can add "Case Is = 0" to your Select Case statement.




On Error Resume Next
If InteriorColor_Or_FontColor = 1 Then
No = CeLL.Interior.ColorIndex
ElseIf InteriorColor_Or_FontColor = 2 Then
No = CeLL.Font.ColorIndex
End If


I editied the code row "ON ERROR GOTO ... " as On Error Resume Next
And
I chanced Dim No As Byte
as
Dim No As Integer

and

I added the code row as : Case Is = 0: Netice = "Renk Yok"

But, UDF returned NullString value,

Anyway,

Not necassary to hurt :yes we already solved it.

Thanks for all helps my friends.

[Norie]

What about Case Else?

And also what about using an array for all those values?

[Bob Phillips]

I create an Enum of colorindexes so that I can use colour names, like so




Public Enum xlColorIndex
xlCIBlack = 1
xlCIWhite = 2
xlCIRed = 3
xlCIBrightGreen = 4
xlCIBlue = 5
xlCIYellow = 6
xlCIPink = 7
xlCITurquoise = 8
xlCIDarkRed = 9
xlCIGreen = 10
xlCIDarkBlue = 11
xlCIDarkYellow = 12
xlCIViolet = 13
xlCITeal = 14
xlCIGray25 = 15
xlCIGray50 = 16
xlCIPeriwinkle = 17
xlCIPlum = 18
xlCIIvory = 19
xlCILightTurquoise = 20
xlCIDarkPurple = 21
xlCIcoral = 22
xlCIOceanBlue = 23
xlCIIceBlue = 24
'xlCIDarkBlue = 25
'xlCIPink = 26
'xlCIYellow = 27
'xlCITurquoise = 28
'xlCIViolet = 29
'xlCIDarkRed = 30
'xlCITeal = 31
'xlCIBlue = 32
xlCISkyBlue = 33
xlCILightGreen = 35
xlCILightYellow = 36
xlCIPaleBlue = 37
xlCIrose = 38
xlCILavender = 39
xlCITan = 40
xlCILightBlue = 41
xlCIAqua = 42
xlCIlime = 43
xlCIGold = 44
xlCILightOrange = 45
xlCIOrange = 46
xlCIBlueGray = 47
xlCIGray40 = 48
xlCIDarkTeal = 49
xlCISeaGreen = 50
xlCIDarkGreen = 51
xlCIBrown = 53
xlCIIndigo = 55
xlCIGray80 = 56
End Enum


I wouldn't use a byte variable, and the best way to test a cell for no colour is to use xlColorindexNone.