Flora Edwards
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
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
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.
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.
Try adding this at the start of the UDF.
Application.Volatile
ASKER
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.
ASKER
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.
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
Flora_Name-The-Colors_v1.xlsm
See if this helps.
Function CellColor(rCell As Range, Optional ColorName As Boolean)
Function CellColor(rCell As Range, Optional ColorName As Boolean)
ASKER
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
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
Try to change the calculation to automatic by referring this link:
Change formula recalculation, iteration, or precision
Change formula recalculation, iteration, or precision
ASKER
Martin, that application.calculate did not work either.
Shams, that links does not help me.
Shams, that links does not help me.
ASKER
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.
but my color UDF does not work. it returns VALUE Error unless F9 is pressed
Lookup-multiple-values-in-one-cell.xlsm
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Hope this helps.
Open attached, I have added Martin's suggestion while opening the file:
Let us the know, if problem still persist.
Flora_Name-The-Colors_v1.xlsm
Private Sub Workbook_Open()
Application.Calculation = xlAutomatic
End Sub
Let us the know, if problem still persist.
Flora_Name-The-Colors_v1.xlsm
ASKER
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
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
ASKER
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
in workbook open event the Application.Calculate did not work but Application.SendKeys "{F9}" works.
i am not sure why
ASKER
thanks Shams,
but the application calculation method was already automatic, so the solution you advised, i tried it and it did not work.
but the application calculation method was already automatic, so the solution you advised, i tried it and it did not work.
Open in new window
and in Formula :Open in new window