Link to home
Start Free TrialLog in
Avatar of Flora Edwards
Flora EdwardsFlag for Sweden

asked on

the UDF returns #Value when i open workbook.

I had this question after viewing how can i return the name of color from the cell background?.

i have this UDF helped by EE expert.


if i put the UDF and copy down it works, but then if i close the file and open again. the UDF returned values show #VALUE error.

the macro is not disabled either.

why this UDF work but then once i close the file it does not work until go to edit mode and then enter again?

any option to fix this?
EE.xlsb
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Try below UDF:
Function ColorName(MyCell As Range)

Dim x As Long

x = MyCell.Interior.ColorIndex

    Select Case x
    
    Case 1
        ColorName = "Black"
    Case 3
        ColorName = "Red"
    Case 6
        ColorName = "Yellow"
    Case 14
        ColorName = "Green"
    Case 18
        ColorName = "Maroon"
    Case 33
        ColorName = "Sky Blue"
    Case 40
        ColorName = "Tan"
    Case 43
        ColorName = "Lime"
    Case 44
        ColorName = "Gold"
    Case Else
        ColorName = "COLORNAME UNKNOWN"
    End Select
    
End Function

Open in new window

and in Formula :
=ColorName(A2)

Open in new window

Avatar of Flora Edwards

ASKER

thanks Shams.

but your UDF has the same problem as the one my file.  if you save your file and reopen it, all the values show #VALUE error.
Avatar of Norie
Norie

Try adding this at the start of the UDF.
Application.Volatile

Open in new window

thanks Norie, i did that i now put Application.Volatile in start of UDF, but problem still remain the same.
Using Excel 2010, when I open the file it works perfectly.
i use excel 2016 64bit
Flora,

I don't know the reason, I also re-opened many times. Perhaps, you saved in xlb format, it would cause a problem.

Try attached in xlsm format...I have added all the 56 Color Palettes to below UDF.
Function NameTheColor(MyCell As Range)

Dim x As Long

x = MyCell.Interior.ColorIndex

    Select Case x
    
    Case 1
        NameTheColor = "Black"
    Case 2
        NameTheColor = "White"
    Case 3
        NameTheColor = "Red"
    Case 4
        NameTheColor = "Bright Green"
    Case 5
        NameTheColor = "Blue"
    Case 6
        NameTheColor = "Yellow"
    Case 7
        NameTheColor = "Magenta"
    Case 8
        NameTheColor = "Turquoise"
    Case 9
        NameTheColor = "Dark Red"
    Case 10
        NameTheColor = "Green"
    Case 11
        NameTheColor = "Dark Blue"
    Case 12
        NameTheColor = "Dark Yellow"
    Case 13
        NameTheColor = "Violet"
    Case 14
        NameTheColor = "Teal"
    Case 15
        NameTheColor = "Silver"
    Case 16
        NameTheColor = "Medium Gray"
    Case 17
        NameTheColor = "Peri Winkle"
    Case 18
        NameTheColor = "Plum"
    Case 19
        NameTheColor = "Ivory"
    Case 20
        NameTheColor = "Lite Turquoise"
    Case 21
        NameTheColor = "Dark Purple"
    Case 22
        NameTheColor = "Coral"
    Case 23
        NameTheColor = "Ocean Blue"
    Case 24
        NameTheColor = "Ice Blue"
    Case 25
        NameTheColor = "Dark Blue"
    Case 26
        NameTheColor = "Magenta"
    Case 27
        NameTheColor = "Yello"
    Case 28
        NameTheColor = "Turquoise"
    Case 29
        NameTheColor = "Violet"
    Case 30
        NameTheColor = "Dark Red"
    Case 31
        NameTheColor = "Teal"
    Case 32
        NameTheColor = "Blue"
    Case 33
        NameTheColor = "Sky Blue"
    Case 34
        NameTheColor = "Light Turquoise"
    Case 35
        NameTheColor = "Light Green"
    Case 36
        NameTheColor = "Light Yellow"
    Case 37
        NameTheColor = "Pale Blue"
    Case 38
        NameTheColor = "Rose"
    Case 39
        NameTheColor = "Lavender"
    Case 40
        NameTheColor = "Tan"
    Case 41
        NameTheColor = "Light Blue"
    Case 42
        NameTheColor = "Aqua"
    Case 43
        NameTheColor = "Lime"
    Case 44
        NameTheColor = "Gold"
    Case 45
        NameTheColor = "Light Orange"
    Case 46
        NameTheColor = "Orange"
    Case 47
        NameTheColor = "Blue Gray"
    Case 48
        NameTheColor = "Gray"
    Case 49
        NameTheColor = "Dark Teal"
    Case 50
        NameTheColor = "Sea Green"
    Case 51
        NameTheColor = "Dark Green"
    Case 52
        NameTheColor = "Olive Green"
    Case 53
        NameTheColor = "Brown"
    Case 54
        NameTheColor = "Plum"
    Case 55
        NameTheColor = "Indigo"
    Case 56
        NameTheColor = "Dark Gray"
    Case Else
        NameTheColor = "NameTheColor Unknown"
    End Select
    
End Function

Open in new window

Flora_Name-The-Colors_v1.xlsm
See if this helps.

Function CellColor(rCell As Range, Optional ColorName As Boolean)
this is completely weird.

i changed the rCell As Range and added application.volatile  nothing works.

when i close the file and then reopen all those UDF returned values are showing #VALUE and when i press the F9 key to recalculate then they change back to correct values.

anyone know why this is happening, when you close the file and reopen does it happen with you or it is just my damn machine.

i even tried with another file, downloaded from http://www.myonlinetraininghub.com/count-sum-and-average-colored-cells 

when i open the attached file, the UDFs show #value error unless i press F9.

i also changed the format to xlsm as suggested by Shams, it did not work.
Count-Sum-Average-Colored-Cells.xlsm
Try adding this code.
Private Sub Workbook_Open()
Application.Calculate
End Sub

Open in new window

Try to change the calculation to automatic by referring this link:
Change formula recalculation, iteration, or precision
Martin, that application.calculate did not work either.

Shams,  that links does not help me.
i am not sure, but this has to do with this specific UDF, maybe becuase of its use of interior background color.

i am using this UDF and it does not even have the application.volatile and it works just fine.
Function Lookup_concat(Search_string As String, _
  Search_in_col As Range, Return_val_col As Range)
Dim i As Long, result As String
Dim Search_strings, Value As Variant
Search_strings = Split(Search_string, ";")
For Each Value In Search_strings
    For i = 1 To Search_in_col.Count
      If Search_in_col.Cells(i, 1) = Value Then
        result = result & " " & Return_val_col.Cells(i, 1).Value
      End If
    Next i
Next Value
Lookup_concat = Trim(result)
End Function

Open in new window


but my color UDF does not work. it returns VALUE Error unless F9 is pressed
Lookup-multiple-values-in-one-cell.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Exit excel and re-open the file I sent you, then Navigate to File / Options / Formulas, check if Automatic is ticked under Workbook Calculation, if not, then select Automatic. Press OK. Save the file, Exit Excel and re-open the file.

Hope this helps.
Open attached, I have added Martin's suggestion while opening the file:
Private Sub Workbook_Open()
Application.Calculation = xlAutomatic
End Sub

Open in new window


Let us the know, if problem still persist.
Flora_Name-The-Colors_v1.xlsm
thanks everyone.  

nothing worked. except that Martin's advise led me to use below in workbook open event which now works.



Private Sub Workbook_Open()
Application.SendKeys "{F9}"
End Sub
this was one of the strangest problem i have seen in Excel.

in workbook open event the Application.Calculate  did not work but Application.SendKeys "{F9}" works.

i am not sure why
thanks Shams,

but the application calculation method was already automatic, so the solution you advised, i tried it and it did not work.