asked on

just curious to lean why these two UDFs behaviors are different?

I had this question after viewing the UDF returns #Value when i open workbook..

i have a solution, but still cannot get my head around the problem what is really causing this issue and i really want to learn why this is happening.

here is the scenario.

environment tested the scenario is , Excel Professional Plus 2016 64bit

in the attached file there are two UDFs.

in column B it has UDF of CellColor and it works fine, until you save then close the file and when you re-open the file. all values returned by UDF before closing that had correct values are now showing #VALUE error, clicking "CALCULATE NOW" undeer the formula TAB does not fix the issue biut the only key that fixes the error is when I press F9 key then #VALUE disappears and UDF output is returned.

in COlumn C it has UDF of Lookup_concat and it works just fine, even when workbook is closed and when reopened the output values shows exactly as it was before closing, there is NO #VALUE error for this UDF. this UDF do not even has the application.volatile it in and yet it works just fine.

the culpirt is the UDF CellColor, i tried many options, adding application.volatile, application.calculate, with any method, it just fails.
the only thing that works is to put this code below in workbook open event and then when the workbook opens it send F9 key and then values refresh and #VALUE disappears.

Private Sub Workbook_Open()
Application.SendKeys "{F9}"
End Sub

Open in new window

anyone has any idea or experience why these two UDFs have different behaviors? or whether there is any alternative solution to this, so that the CellColor UDF works without sending the F9 key.

thanks.
EE.xlsb

Martin Liss

Does this make any difference?

Function CellColor(rCell As Range, Optional ColorName As Boolean)


Dim strColor As String
ActiveSheet.Range("A2").Activate
Select Case rCell.Interior.ColorIndex

   Case 1

    strColor = "Black"

   Case 53

    strColor = "Brown"

   Case 52

    strColor = "Olive Green"

   Case 51

    strColor = "Dark Green"

   Case 49

    strColor = "Dark Teal"

   Case 11

    strColor = "Dark Blue"

   Case 55

    strColor = "Indigo"

   Case 56

    strColor = "Gray-80%"

   Case 9

    strColor = "Dark Red"

   Case 46

    strColor = "Orange"

   Case 12

    strColor = "Dark Yellow"

   Case 10

    strColor = "Green"

   Case 14

    strColor = "Teal"

   Case 5

    strColor = "Blue"

   Case 47

    strColor = "Blue-Gray"

   Case 16

    strColor = "Gray-50%"

   Case 3

    strColor = "Red"

   Case 45

    strColor = "Light Orange"

   Case 43

    strColor = "Lime"

   Case 50

    strColor = "Sea Green"

   Case 42

    strColor = "Aqua"

   Case 41

    strColor = "Light Blue"

    iIndexNum = 41

   Case 13

    strColor = "Violet"

    iIndexNum = 13

   Case 48

    strColor = "Gray-40%"

   Case 7

    strColor = "Pink"

   Case 44

    strColor = "Gold"

   Case 6

    strColor = "Yellow"

   Case 4

    strColor = "Bright Green"

   Case 8

    strColor = "Turqoise"

   Case 33

    strColor = "Sky Blue"

   Case 54

    strColor = "Plum"

   Case 15

    strColor = "Gray-25%"

   Case 38

    strColor = "Rose"

   Case 40

    strColor = "Tan"

   Case 36

    strColor = "Light Yellow"

   Case 35

    strColor = "Light Green"

   Case 34

    strColor = "Light Turqoise"

   Case 37

    strColor = "Pale Blue"

   Case 39

    strColor = "Lavendar"
    
   Case 18

    strColor = "Maroon"

   Case 2

    strColor = "White"

  Case Else

    strColor = "Custom color or no fill"

End Select



    If ColorName = True Or strColor = "Custom color or no fill" Then

        CellColor = strColor

    Else

        CellColor = rCell.Interior.ColorIndex

    End If



End Function

Open in new window

Martin Liss

Ah, this may be it.

Function CellColor(rCell As Range, Optional ColorName = True)

SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Flora Edwards

ASKER

thanks Martin, but unfortunately, it did not work.

i have attached the file with your latest code embedded in it. it still show value error on the column B UDF.
EE.xlsb

Jacques Geday

I downloaded your file it had the VALUE I ran macros it replaces it by color names. I saved the file and opened it no problem did this 3 times no problem.

My question to you:
Do you have Calculations in options set to Automatic ? If it is set to manual this could be the reason. Put it to automatic and try.

gowflow

Flora Edwards

ASKER

thanks gowflow

yes, i checked all possible scenarios of calculation method etc. the calculation method is automatic and what is really surprising for me is, that when i hit "CALCULATE NOW" button even that does not trigger the fix. only F9 triggers the #VALUE to be changed to output.

did you test this with Excel 2016 64bit? i do not have earlier version of Excel to test.

Jacques Geday

well I have 2010. no don't have 2016. Maybe an issue with 2016
gowflow

Flora Edwards

ASKER

i have recorded a video that demonstrates the problem.
Vid.mp4

Wayne Taylor (webtubbs)

The Optional variant argument "ColorName" isn't used, so remove that. There is also a couple instances of 'iIndexNum' being used which don't appear to do anything, so remove them as well. Then change the formula to simply =CellColor(A2).

It would be beneficial for you to ensure all variables are declared which is done by placing "Option Explicit" at the every top of every code module. This can be ensured by also going to Tools > Options and ticking "Require Variable Declaration".

Jacques Geday

@Wayne Taylor (webtubbs)
Yes it is used look at the intial wb posted. If false is set then it return the colorindex.

gowflow

Wayne Taylor (webtubbs)

One last change is to remove this line completely as it serves no real function.

ActiveSheet.Range("A2").Activate

Open in new window

Jacques Geday

@Wayne Taylor (webtubbs)
Sorry All your notes don't improve in solving the issue !!! Just makeup
.gowflow

Martin Liss

If it makes you feel any better, I get #VALUE! now too in Excel 2010.

Jacques Geday

What did you do ? to get it !!! maybe its a setting of somekind in default excel. I don't get it no matter what I do.
gowflow

Wayne Taylor (webtubbs)

Sorry All your notes don't improve in solving the issue !!! Just makeup.

How would you know? Flora hasn't got back to us yet.

Jacques Geday

Well for the TRUE/FALSE ColorName it is in the code.
For activate it does not harm to have it. Anyway Martin can check I am not getting the error.

gowflow

Jacques Geday

I hate to do this, give an advice and not being able to test it .

@Flora
Please add this line in the beginning of the Function CellColor:
Application.Volatile

It should be the first statement.

Maybe this is the culprit.
gowflow

Wayne Taylor (webtubbs)

Flora, one other thing to ensure full calculation of your workbook is to set ForceFullCalculation. This is done by going into the Visual Basic Editor, selecting "ThisWorkbook" and pressing F4 of the keyboard. Set ForceFullCalculation to True, save your workbook, then reopen and see if that halped.

Flora Edwards

ASKER

thanks very much everyone for trying to help me. i am hopeless with this case, perhaps i will just give up and live with it.

i removed and simplified the code as below
still did not work. added application.volatile did not work.

added option explicit and declared all variables and it did not help either.

on this workbook set the workbook forcefullcalculation to true, by pressing F4 and also by putting this line ThisWorkbook.ForceFullCalculation = True in workbook open event. this did not help either. perhaps something is wrong with Excel 2016 64bit.

Function CellColor(rCell As Range) As String

Select Case rCell.Interior.ColorIndex

   Case 1

    CellColor = "Black"

   Case 53

    CellColor = "Brown"

   Case 52

    CellColor = "Olive Green"


   Case 51

    CellColor = "Dark Green"

   Case 49

    CellColor = "Dark Teal"

   Case 11

    CellColor = "Dark Blue"

   Case 55

    CellColor = "Indigo"

   Case 56

    CellColor = "Gray-80%"

   Case 9

    CellColor = "Dark Red"

   Case 46

    CellColor = "Orange"

   Case 12

    CellColor = "Dark Yellow"

   Case 10

    CellColor = "Green"
    
   Case 14

    CellColor = "Teal"

   Case 5

    CellColor = "Blue"

   Case 47

    CellColor = "Blue-Gray"

   Case 16

    CellColor = "Gray-50%"

   Case 3

    CellColor = "Red"

   Case 45

    CellColor = "Light Orange"

   Case 43

    CellColor = "Lime"

   Case 50

    CellColor = "Sea Green"

   Case 42

    CellColor = "Aqua"

   Case 41

    CellColor = "Light Blue"

   Case 13

    CellColor = "Violet"

   Case 48

    CellColor = "Gray-40%"

   Case 7

    CellColor = "Pink"

   Case 44

    CellColor = "Gold"

   Case 6

    CellColor = "Yellow"

   Case 4

    CellColor = "Bright Green"

   Case 8

    CellColor = "Turqoise"

   Case 33

    CellColor = "Sky Blue"

   Case 54

    CellColor = "Plum"

   Case 15

    CellColor = "Gray-25%"

   Case 38

    CellColor = "Rose"

   Case 40

    CellColor = "Tan"

   Case 36

    CellColor = "Light Yellow"

   Case 35

    CellColor = "Light Green"

   Case 34

    CellColor = "Light Turqoise"

   Case 37

    CellColor = "Pale Blue"

   Case 39

    CellColor = "Lavendar"
    
   Case 18

    CellColor = "Maroon"

   Case 2

    CellColor = "White"

  Case Else

    CellColor = "Custom color or no fill"

End Select


End Function

Open in new window

Martin Liss

Close and reopen Excel, create a new workbook and copy your code to it and see what happens.

Flora Edwards

ASKER

created a new workbook. pasted the code and then same problem. closed it and reopened it and the bloody #VALUE error do not die.

Flora Edwards

ASKER

saw a similar question at mrexcel https://www.mrexcel.com/forum/excel-questions/347940-user-defined-function-gives-value-error.html
but cannot understand the solution
Book1.xlsm

ASKER CERTIFIED SOLUTION

Wayne Taylor (webtubbs)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Subodh Tiwari (Neeraj)

I also got the #Value error with this but changing the first line like below resolved the issue for me.

Function CellColor(ByVal rCell As Range, Optional ColorName = True) As String

Open in new window

Flora Edwards

ASKER

thanks Wayne. yes, the problem persists even what that UDF you provided.

Neeraj, can you upload your complete code? i changed the first line as you suggested but it did not work for me.

SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Flora Edwards

ASKER

thanks Neeraj.

when i opened the attached file from your post, the issue is still there. i see #VALUE error.

i guess it has to do with Excel 2016 64bit as pointed by Wayne.

SOLUTION

Jacques Geday

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Flora Edwards

ASKER

Thanks so very much everyone for your help and support.