Convert Subroutine to a UDF - Range Issue

[MartinL]

I have a Subroutine that works just fine, but I have had a request to make it a UDF
I thought it would be easy there are just two variables one is a range and the other a single cell, but the range doesn't appear to translate as expected - debugging produces an "Application-defined or Object-defined error<application-defined error="" Object-defined="" or="">"
in the sub a popup box requested the range and then another to request the cell which I have now commented out and replaced with input variables.


If anyone can shed some light on my ignorance here that would be great.<application-defined error="" Object-defined="" or="">

Dropbox - 4885953.xlsm

This is my translated code with the error line highlighted

Function COLOURCOUNT(CountRange As Range, ColorRange As Range)

    'Count the number of cells with a specific background 
    'Select range to count
    'Select cell to check colour against
    
    Dim rng As Range
    'Dim CountRange As Range - this line used to be in the sub
    'Dim ColorRange As Range - this line used to be in the sub
    Dim xBackColor As Long
    Dim xFontColor As Long
    On Error Resume Next
    xTitleId = "ColourCount"
    'Set CountRange = Application.Selection
    'Set CountRange = Application.InputBox("Count Range :", xTitleId, CountRange.Address, Type:=8)
    'Set ColorRange = Application.InputBox("Color Range(single cell):", xTitleId, Type:=8)
    'Set ColorRange = ColorRange.Range("A1")

        xReturn = 0
    For Each rng In CountRange
        qqq = rng.Value
        xxx = rng.DisplayFormat.Interior.Color
        If rng.DisplayFormat.Interior.Color = ColorRange.DisplayFormat.Interior.Color Then
            xBackColor = xBackColor + 1
        End If
    Next
   'MsgBox "Number of Coloured cells is " & xBackColor & Chr(10)
End Function

</application-defined></application-defined>

 

[FormR]

Hi,

See here:

https://msdn.microsoft.com/en-us/VB...tFrameworkMoniker-Office.Version=v15)&rd=true

[FONT=&quot]Note that the [/FONT][FONT=&quot]DisplayFormat[/FONT][FONT=&quot] property does not work in user defined functions. For example, in a worksheet function that returns the interior color of a cell, you use a line similar to: [/FONT]<code style="box-sizing: border-box; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; font-size: 13.5px; padding: 2px 4px; color: rgb(199, 37, 78); background-color: rgb(249, 242, 244); border-radius: 4px;">Range.DisplayFormat.Interior.ColorIndex</code>[FONT=&quot]. When the worksheet function executes, it returns a [/FONT][FONT=&quot]#VALUE![/FONT][FONT=&quot] error.

[/FONT]

 

[AlphaFrog]

1.) you used countcolour in the formula and the UDF is called colourcount
2.) the help file for Displayformat states it cannot be used in a UDF.
3.) add this last line COLOURCOUNT = xBackColor to return the count to the function.

[COLOR=darkblue]Function[/COLOR] COLOURCOUNT(CountRange [COLOR=darkblue]As[/COLOR] Range, ColorRange [COLOR=darkblue]As[/COLOR] Range) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
[COLOR=green]'Count the number of cells with a specific background (default)[/COLOR]
    [COLOR=green]'Select range to count[/COLOR]
    [COLOR=green]'Select cell to check colour against[/COLOR]
    
    [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] xBackColor [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] xFontColor As [COLOR=darkblue]Long[/COLOR]
    xTitleId = "ColourCount"
    [COLOR=green]'Set CountRange = Application.Selection[/COLOR]
    [COLOR=green]'Set CountRange = Application.InputBox("Count Range :", xTitleId, CountRange.Address, Type:=8)[/COLOR]
    [COLOR=green]'Set ColorRange = Application.InputBox("Color Range(single cell):", xTitleId, Type:=8)[/COLOR]
    [COLOR=green]'Set ColorRange = ColorRange.Range("A1")[/COLOR]
        xReturn = 0
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] rng [COLOR=darkblue]In[/COLOR] CountRange
        qqq = rng.Value
        xxx = rng.DisplayFormat.Interior.Color
[B]        [COLOR=darkblue]If[/COLOR] rng.Interior.Color = ColorRange.Interior.Color [COLOR=darkblue]Then[/COLOR][/B]
            xBackColor = xBackColor + 1
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR]
[B]    COLOURCOUNT = xBackColor[/B]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

If you do a search for something like vba count color UDF, you'll fine many examples for this UDF.