Difference between Sub and Function

[Danny54]

Running into a situation that has me confused. I have a subroutine called DisplayFormatCount1() and converted it to a Function as I want to insert
=DisplayFormatCount2(M5:M9,65535) into various cells.

the value being returned is 0

Do i need to pass the worksheet to the function?


Orginal VBA Subroutine Here

Sub DisplayFormatCount1()
Dim Rng As Range
Dim CountRange As Range
Dim ColorRange As Range
Dim xBackColor As Long
Dim xFontColor As Long
On Error Resume Next

Set CountRange = Range("$M$5:$M$9")
xxx = 65535

For Each Rng In CountRange
qqq = Rng.Value
xxx = Rng.DisplayFormat.Interior.Color
If Rng.DisplayFormat.Interior.Color = 65535 Then
xBackColor = xBackColor + 1
End If
If Rng.DisplayFormat.Font.Color = ColorRange.DisplayFormat.Font.Color Then
xFontColor = xFontColor + 1
End If
Next
MsgBox "BackColor is " & xBackColor & Chr(10) & "FontColor is " & xFontColor


My attempt to create a UDF Function

Function DisplayFormatCount3(CountRange As Range, inXXX) As Long
Dim CelVal As Range
Dim ColorRange As Range
Dim xBackColor As Long
Dim xFontColor As Long
On Error Resume Next

Set CR = CountRange

For Each CelVal In CR
qqq = CelVal.Value
xxx = CelVal.DisplayFormat.Interior.Color
If CelVal.DisplayFormat.Interior.Color = inXXX Then
xBackColor = xBackColor + 1
End If
If CelVal.DisplayFormat.Font.Color = ColorRange.DisplayFormat.Font.Color Then
xFontColor = xFontColor + 1
End If
Next
MsgBox "BackColor is " & xBackColor & Chr(10) & "FontColor is " & xFontColor
End Function

 

[Fluff]

With the CFColour function in the macro as below I get the "Ambiguous name detected" message

That means you have two functions with the same name. Delete one of them, but ensure that you keep the one from post#5 & not the earlier one.

 

[Danny54]

So,

this is the posting from #5

Function DisplayFormatCount3(CountRange As Range, inXXX As Long) As Long
Dim CelVal As Range

For Each CelVal In CountRange
If Evaluate("CFColour(" & CelVal.Address & ")") = inXXX Then
DisplayFormatCount3 = DisplayFormatCount3 + 1
End If
Next
End Function
Function CFColour(Cl As Range) As Double
CFColour = Cl.DisplayFormat.Interior.Color
End Function


I removed the last 3 lines and saved the macro. So it now looks like this

Function DisplayFormatCount3(CountRange As Range, inXXX As Long) As Long
Dim CelVal As Range

For Each CelVal In CountRange
If Evaluate("CFColour(" & CelVal.Address & ")") = inXXX Then
DisplayFormatCount3 = DisplayFormatCount3 + 1
End If
Next
End Function


When i make the call i get the #VALUE! inserted into cell L4.

Sorry to be so confused and Thanks for the help

 

[Fluff]

Do not remove those last three lines, but check all modules for any procedure called CFColour & remove it.

 

[Danny54]

Sweet.

Sometimes I get lost.

That worked perfectly.

Thank you

 

[Fluff]

Glad it's sorted & thanks for the feedback.