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]

You cannot use displayformat directly in a function, when it's called from a sheet.
However you can use a workround like

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


For Each CelVal In CountRange
   If Evaluate("CFColour(" & CelVal.Address & ",""interior"")") = inXXX Then
      xBackColor = xBackColor + 1
   End If
   If Evaluate("cfcolour(" & CelVal.Address & ",""font"")") = inXXX Then
      xFontColor = xFontColor + 1
   End If
Next
MsgBox "BackColor is " & xBackColor & Chr(10) & "FontColor is " & xFontColor
End Function
Function CFColour(Cl As Range, Itm As String) As Double
   If Itm = "interior" Then
      CFColour = Cl.DisplayFormat.Interior.Color
   ElseIf Itm = "font" Then
      CFColour = Cl.DisplayFormat.Font.Color
   End If
End Function

 

[Danny54]

Thank you.

ok, somewhere i'm still lost. The function call gets inserted into the spreadsheet at L4 "=DisplayFormatCount3(L5:L9,65535)" thru the vbs macro

Now, isn't the execution of the "=DisplayFormatCount4(L5:L9,65535)" supposed to return the value that found in xBackColor?

 

[Danny54]

in a nutshell, what im doing is

create a xls sheet that contains a gantt chart using conditional formatting to make the bars. the whole thing is done by date/hour for over 90 days.
now i need to count the number of times yellow was found in the column by row and insert that count in L4, for column L, count in M4 for column M
and so on.

Since the number of columns and rows are always dynamic i figured i could use a function to do the cound and insert it into L4. Then drag it over to the end of row 4
counting all the yellow filled rows.


Again, thanks for your help

 

[Fluff]

In that case you can use

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

 

[Danny54]

Thank you

Im getting a popup box "Ambiguous name detected: CFColour" when resolving the "=DisplayFormatCount3(L5:L9,65535)" inserted into L4

 

[Fluff]

In that case remove the original CFColour function I supplied.

 

[Danny54]

When I remove the CFColour function and resolve the "=DisplayFormatCount3(L5:L9,65535)" inserted into L4 it shows #VALUE!

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

 

[Fluff]

Do you still have the CFColour function from post#5 in there as well?

 

[Danny54]

Thanks for the response

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

Without the Function I get a "#VALUE!" inserted in L4


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