Checking for Bold property in Excel VBA 3

[RealityCheque]

I'm trying to write a function that will count the number of cells within a range that are bold, despite having a function that counts cells of a particular colour I have not managed to adapt it for 'bold checking'.

The colour function that works fine:

Function CountByColor(sCountRange As String, Color As Integer) As Long
Dim ColorCount As Long
Dim CountRange As Range
Dim c As cell
Application.Volatile True

Set CountRange = Range(sCountRange)

For Each c In CountRange
If c.Interior.ColorIndex = Color Then
ColorCount = ColorCount + 1
End If
Next

CountByColor = ColorCount
End Function

The bold function that reports a #name? error:

Function CountByBold(sCountRange As String) As Long
Dim BoldCount As Long
Dim CountRange As Range
Dim c As cell
Application.Volatile True

Set CountRange = Range(sCountRange)

For Each c In CountRange
If c.Font.Bold = True Then
BoldCount = BoldCount + 1
End If
Next

CountByBold = BoldCount
End Function

Can anyone see where I'm going wrong? I must have stared at this for hours trying to spot my mistake

Simon

"I do not have to forgive my enemies. I have had them all shot."
- Ramon Maria Narvaez

 

[PHV]

And what about replacing this:
Dim c As cell
with this ?
Dim c As Range

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rofeu]

Hi,

try:

 Dim c As Object

Actually I wouldn't have expected your ColorCount to work with 'dim c as cell'

Cheers,

Roel

 

[RealityCheque]

Cheers for the ideas, but I'm still getting the same #name? error when I try to use the formula.

For some reason the CountByBold formula doesn't appear in the User Defined Fuctions list whereas the CountByColour does, could it be going wrong on this front? Both are pasted in the "ThisWorkbook" page of VBA.

@Rofea: <i>"Actually I wouldn't have expected your ColorCount to work with 'dim c as cell"</i>
I actually lifted that formula from this site, and it works perfectly

Simon

"I do not have to forgive my enemies. I have had them all shot."
- Ramon Maria Narvaez

 

[firefytr]

Not Object, as PHV states, a Range dimension. And trim to ..

Function CountByBold(CountRange As Range) As Long
    Dim BoldCount As Long, c As Range
    Application.Volatile True
    For Each c In CountRange
        If c.Font.Bold = True Then
            BoldCount = BoldCount + 1
        End If
    Next
    CountByBold = BoldCount
End Function

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[RealityCheque]

Cheers for the trimmed code firefytr, but I'm still getting the #name? error.

I've amended my CountByColour function to match the trimmed version, and that works fine but the bold one gives me the same error even if I deliberately break the code.

What would cause it to not show up in the User Defined Function list?

Simon

"I do not have to forgive my enemies. I have had them all shot."
- Ramon Maria Narvaez

 

[firefytr]

Take the True off the Volatile statement.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[RealityCheque]

Still no joy, exactly the same error.

It's just not finding the formula!

Simon

"I do not have to forgive my enemies. I have had them all shot."
- Ramon Maria Narvaez

 

[firefytr]

Hmm, works for me. Making sure you take the True part off the end of the Volatile line, take the code out, compile the project (VBE | Debug | Compile VBAProject), repaste function, recompile project, try again.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[RealityCheque]

It works

Thank you all for your help, you've just saved me another day of banging my head on my desk!

Simon

"I do not have to forgive my enemies. I have had them all shot."
- Ramon Maria Narvaez