UDF returns #VAULE! only when changing filter setting via own button function

[phunsoft]

Hi,
I did some research, but so far I haven't found an answer to my problem. Hopefully I can get some hints here.

I wrote the IsChangeopen() function (UDF, show futhrer down) to display "Closed" if out of 5 cells in the same row, all of them are colored (marking a complete status). If one or more cells are not colored, then "Open" shall be displayed.

I enter the functions in, say, cell K5 as =IsChangeOpen(F5;G5;H5;I5;J5). This initlally works as desired: If any of cells F5 to J5 are non-colored, then "Open" is shown in cell K5; otherwise "Closed" is shown.

I have activated auto filters, so that I can easily choose only "Open" rows to be shown. Changing the filter manually between "Open" and "all" rows works perfectly. The K cells show the desired values and the filter selects only desired rows.

I then have added a button on the sheet that shall set the filter to only rows showing "Open" in cells Kn on a click.

The strange thing is that all K cells immediately show #VALUE! as soon as I click no the button??? I can reset the K cells by a simple F2 followed by return.

I read that UDF may not enter data in cells, which is what my function does. I change the K cells to contain
=IF(=IsChangeOpen(F5;G5;H5;I5;J5)="Open";"Open";"Closed")
I thought this way the UDF does not change the cell but only help the IF to change it.
The problem still persists.
I'm out of clue what causes the #VALUE! error. Any ideas?

Thx
Peter


--------------------------------------------------------------
Function IsChangeOpen(c1, c2, c3, c4, c5) As String
Dim OpenCount
OpenCount = 0

If c1.Interior.ColorIndex = xlNone Then
OpenCount = OpenCount + 1
End If
If c2.Interior.ColorIndex = xlNone Then
OpenCount = OpenCount + 1
End If
If c3.Interior.ColorIndex = xlNone Then
OpenCount = OpenCount + 1
End If
If c4.Interior.ColorIndex = xlNone Then
OpenCount = OpenCount + 1
End If
If c5.Interior.ColorIndex = xlNone Then
OpenCount = OpenCount + 1
End If

If OpenCount = 0 Then
IsChangeOpen = "Closed"
Else
IsChangeOpen = "Open"
End If

End Function
-----------------------------------------------------------------------

 

[Jerry Sullivan]

Hi Peter and Welcome to the Board,

Could you post the code that you are using to apply autofilters?
The problem might be there.

I read that UDF may not enter data in cells, which is what my function does.

There might be a misinterpretation here - it depends on how one defines "enter data in cells"
A UDF can display its return value in the cell in which it is called. Your UDF proves this.
A UDF can't directly change the values or formulas in other cells.
A UDF also can't replace it's own formula with different data (the UDF can't paste as values "Open").

You might consider modifying your code to exit the function as soon as the first referenced cell with NoFill is found.

Something like:

Function IsChangeOpen2(rRange As Range) As String
    Dim c As Range
    Application.Volatile
    For Each c In rRange
        If c.Interior.ColorIndex = xlNone Then
            IsChangeOpen2 = "Open"
            Exit Function
        End If
    Next c
    IsChangeOpen2 = "Closed"
End Function

One problem in the UDF approach is that by itself, the act of changing a Cell or Range's fill color will not trigger execution of the UDF. There needs to be a change in value to one of the dependent cells or some other event that causes a recalculation of that cell.

 

[mikerickson]

Your UDF is dependent on the colorIndex of a cell. Changing a cell's interior color does not trigger calculation, even of a volatile function. Might this be causing the problem?

 

[Jerry Sullivan]

Your UDF is dependent on the colorIndex of a cell. Changing a cell's interior color does not trigger calculation, even of a volatile function. Might this be causing the problem?

Hi Mike, A coincidence that we both hit the reply button roughly 12 hours after the OP.

I included Application.Volatile in my suggestion only because it will cause the UDF to trigger earlier in some cases than it would without it.

I'll be interested to see the AutoFilter code, because it would appear that somehow the act of clicking the button and running that code is triggering the UDF, but it's triggering it at a point that the UDF evaluates to an error.

 

[phunsoft]

Hi Peter and Welcome to the Board,

Could you post the code that you are using to apply autofilters?
The problem might be there.

Sure. Here is the code I'm calling from two buttons' "On-Click" code:

Sub ShowOnlyOpenItems()
ActiveSheet.Range("$A$5:$K$9999").AutoFilter Field:=11, Criteria1:="Open"
End Sub

Sub ShowAllItems()
ActiveSheet.Range("$A$5:$K$9999").AutoFilter Field:=11
End Sub

There might be a misinterpretation here - it depends on how one defines "enter data in cells"
A UDF can display its return value in the cell in which it is called. Your UDF proves this.
A UDF can't directly change the values or formulas in other cells.
A UDF also can't replace it's own formula with different data (the UDF can't paste as values "Open").

While being a programmer by profession since mid 80ies, I haven't done much MS Office VBS programming. There is still much to learn. I understandd my function does not change the cell content but merely modifies what is displayed. It seems that this is causing problems with autofilter.
If I correctly understand what you wrote, my UDF cannot use CELL.VALUE="OPEN" to make this the new cell content neither of itself (wouldn't want this) not for any other cell, right?

You might consider modifying your code to exit the function as soon as the first referenced cell with NoFill is found.

Something like:

Thanks for this sample. Much appreciated.

One problem in the UDF approach is that by itself, the act of changing a Cell or Range's fill color will not trigger execution of the UDF. There needs to be a change in value to one of the dependent cells or some other event that causes a recalculation of that cell.

Yes, that is why I have the following code in the subroutines used to change the color of the cells:

    tmpCell = Selection.Value
    Selection.Value = tmpCell

There probably is a more elegant way of doing this.

I got the job of trying to automate this spreadsheet without having good knowledge in Excel VBA. I'm usually recording a macro first, then start from this code.
I really need to get a good book on Excel VBA programming (Not a VBA introduction). Any suggestion?

I have tried various other things last night. I don't wont to make this post too lengthy, so I'll post some more questions separately.

Thanks for your help

--
Peter

 

[Jerry Sullivan]

Yes, I get that same result of #VALUE! when running that macro.

It appears to be a problem in Excel's sequencing of calculations.

A quick fix would be to recalculate the cells in the autofilter range after the autofilter.
(I've added a .Calculate before the AutoFilter in case you have any existing #VALUE! cells prior to clicking the button).

Sub ShowOnlyOpenItems()
    With ActiveSheet
        With .Range("A5:K" & .Cells(.Rows.Count, "K").End(xlUp).Row)
            .Calculate  
            .AutoFilter Field:=11, Criteria1:="Open"
            .Calculate
        End With
    End With
End Sub

Sub ShowAllItems()
    With ActiveSheet
        With .Range("A5:K" & .Cells(.Rows.Count, "K").End(xlUp).Row)
            .Calculate
            .AutoFilter Field:=11
            .Calculate
        End With
    End With
End Sub

If I correctly understand what you wrote, my UDF cannot use CELL.VALUE="OPEN" to make this the new cell content neither of itself (wouldn't want this) not for any other cell, right?

Yes.

Yes, that is why I have the following code in the subroutines used to change the color of the cells:

    tmpCell = Selection.Value
    Selection.Value = tmpCell

There probably is a more elegant way of doing this.

You could use Selection.Calculate.
If you use macros exclusively to change the colors of cells, you could forego the UDF and have your color change macro update the Open/Closed status in Column K.
Another approach would be to use Conditional Formatting to make Column K cells let's say Red or Green, then filter by Color instead of Text.

I really need to get a good book on Excel VBA programming (Not a VBA introduction). Any suggestion?

hiker95 maintains a list of Excel resources and posts it occasionally. You could search the site for a recent update.

 

[phunsoft]

Thanks to your help, I got it working. Great. In fact, this is a multi workseet workbook and the ShowOnlyOpenItems and ShowAllItems functions cycling over all workseets and are applying the autofilter to each.

Would it be usefull to post the complete code?

If you use macros exclusively to change the colors of cells, you could forego the UDF and have your color change macro update the Open/Closed status in Column K.

No, often rows or single cells are copied. This rules the "mark open/close" button out.

Anyway, if I could rely on the buttons being used exclusively to change color, how would this macro change the content of the related K cell? I thought "cell.value" was not supported.

Another approach would be to use Conditional Formatting to make Column K cells let's say Red or Green, then filter by Color instead of Text.

I didn't know conditional formatting can be based on a cell's appearance instead of its content.
So the idea was to apply conditional formatting to the K cells based on the color of the associates F to J cells, and then filter (not autofilter) based on K cells color. Right?

Sounds as if it could be an alternative that is not dependent on so many recalculations (which make the screen flicker). I'll have to find out how to do the contitional formatting and will give it a try.


--
Peter

 

[Jerry Sullivan]

Would it be usefull to post the complete code?

Sure. So many people visit MrExcel for code snippets that it's bound to help someone.

No, often rows or single cells are copied. This rules the "mark open/close" button out.

Anyway, if I could rely on the buttons being used exclusively to change color, how would this macro change the content of the related K cell? I thought "cell.value" was not supported.

A UDF isn't able to change another cell's Value Property, but a macro can using a statement like:
Cells(lRow, "K").Value = "Open"

Here's a code example that applies color to all the selected cells, then updates Column K for these rows.

Sub MarkSelectedTasksComplete()
    Dim lRow As Long
    Dim collRowsChecked As New Collection
    Dim bOpenTask As Boolean
    Dim cCellInSelection As Range, cTasksInRow As Range, rTasks As Range
    Set rTasks = Columns("B:F") '5 task columns to check status

        
    On Error GoTo CleanUp
    Application.ScreenUpdating = False
    '--Mark all selected tasks as complete
    Selection.Interior.Color = vbYellow

    
    '--Update status of Col K for Selection's Rows
    For Each cCellInSelection In Selection
        lRow = cCellInSelection.Row
        If Not KeyExists(collRowsChecked, CStr(lRow)) Then
            collRowsChecked.Add lRow, CStr(lRow)
            For Each cTasksInRow In Intersect(cCellInSelection.EntireRow, rTasks)
                If cTasksInRow.Interior.ColorIndex = xlNone Then
                    bOpenTask = True
                    Exit For
                End If
            Next cTasksInRow
            Cells(lRow, "K").Value = IIf(bOpenTask, "Open", "Closed")
            bOpenTask = False 'reset
        End If
    Next cCellInSelection
CleanUp:
    Application.ScreenUpdating = True
End Sub

Public Function KeyExists(Coll As Collection, sKey As String) As Boolean
'---Returns True if sKey exists the Coll Collection,
    Dim v As Variant
    On Error Resume Next
    Err.Clear
    v = Coll(sKey)
    KeyExists = Err.Number = 0
End Function

I didn't know conditional formatting can be based on a cell's appearance instead of its content.

You're right- AFAIK it can't. I wasn't thinking clearly when I suggested that!

 

[mikerickson]

...
A UDF isn't able to change another cell's Value Property...

Strictly that is true, but there is a workaround.

A UDF can put range objects and values in collections.
The Calculate event runs after all UDF's have been calculated.
The Calculate event can change the values in cells.

I don't know how it would apply to this problem, but here's an example.

Put this in a normal module

' in a normal module

Public CellsToGetValues As New Collection
Public ValuesToGiveCells As New Collection

Function SetOtherCellsValue(ValueForThisCell As Variant, otherCell As Range, otherValue As Variant) As Variant
    Dim keyString As String
    keyString = otherCell.Address(, , , True)
    On Error Resume Next
    CellsToGetValues.Add Item:=otherCell, Key:=keyString
    ValuesToGiveCells.Add Item:=otherValue, Key:=keyString
    On Error GoTo 0
    SetOtherCellsValue = ValueForThisCell
End Function

and this in ThisWorkbook code module

' in ThisWorkbook

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Dim oneCell As Range
    For Each oneCell In CellsToGetValues
        oneCell.Value = ValuesToGiveCells(oneCell.Address(, , , True))
    Next oneCell
    Set CellsToGetValues = Nothing
    Set ValuesToGiveCells = Nothing
End Sub

The formula =SetOtherCellsValue(A1+1, E1, B1+2) will result in A1+1 being shown in the cell holding the formula and the value B1+2 in E1.
The arguments in SetOtherCellsValue can be more complicated (an IF to determine the otherCell?)

But, my point is that this approach (UDF adds to collection, Calculate event processes those collections) can be used to make UDF's that can control other cell's values, formatting,.... a whole host of "impossible to do with UDF" things.

Perhaps that technique would work for this problem.

 

[Jerry Sullivan]

Mike, That's a really clever workaround to extend the functionality of a UDF.

I'm not seeing how that might solve this problem - but I can imagine applying that "proxy through the calculation event" technique to other problems.

Placing .Calculate in the Autofilter macro addressed the problem initially described in the OP, so the unsolved "problem" in this thread comes from our observation that there doesn't seem to be a satisfactory way to update the "Open/Closed" status immediately upon the act of the user changing the Interior.Color property of a cell through the User Interface.

In practical terms, that's a pretty small drawback, but it's surprising there isn't some way to hook this change.
The only other workaround I can think of to get an "immediate" update, would be to use a combination of Selection_Change and Application.OnTime to monitor selected cells at some high frequency (once per second). For most applications, that extra overhead wouldn't be worth addressing this minor problem.

Thanks again for sharing that trick!