Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel user defined function error

Posted on 2013-01-29
4
Medium Priority
?
280 Views
Last Modified: 2013-02-01
Experts:

I need to determine if a certain cell is "BOLD"....  I've inserted the below code in an attempt; however, when I execute the call I get a compile error:  "User-defined type not defined"

Here is code...  was placed in a module

Public Function isBold(xlCell As cell) As Integer
  Dim isBold As Integer
  Dim bolTest As Integer
  bolTest = 0
  If xlCell.Font.Bold Then bolTest = 1
  isBold = bolTest
End Function

I also placed the code on the worksheet tab itself...  When placed there, I get nothing!

Thanks in advance for your assistance.

Rick Norris
0
Comment
Question by:Rick Norris
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 38831055
Replace as cell with As Range.
Public Function isBold(xlCell As Range) As Integer
  Dim isBold As Integer
  Dim bolTest As Integer
  bolTest = 0
  If xlCell.Font.Bold Then bolTest = 1
  isBold = bolTest
End Function

Open in new window

0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38831236
Note that that function only evaluates whether the entire cell is set to bold.

It is also possible to set bold formatting on a character-by-character setting (thus allowing things like half of the text to be bold and the rest to be plain).

Do you need to test for that as well?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38831287
Two ways to handle testing not just entire cell, but also on a character basis:

Public Function isBold(xlCell As Range) As Boolean

    ' returns True if entire cell or any characters are set to bold,
    ' False otherwise
    
    Dim Counter As Long
    
    isBold = False
    
    With xlCell.Cells(1, 1)
        If .Font.Bold Then
            isBold = True
        Else
            For Counter = 1 To Len(.Value)
                If .Characters(Counter, 1).Font.Bold Then
                    isBold = True
                    Exit For
                End If
            Next
        End If
    End With
    
End Function

Public Function isBold2(xlCell As Range) As Long
    
    ' Returns:
    ' 0 = nothing at all in cell is bold
    ' 1 = entire cell set to bold
    ' 2 = at least one character set to bold
    
    Dim Counter As Long
    
    isBold2 = 0
    
    With xlCell.Cells(1, 1)
        If .Font.Bold Then
            isBold2 = 1
        Else
            For Counter = 1 To Len(.Value)
                If .Characters(Counter, 1).Font.Bold Then
                    isBold2 = 2
                    Exit For
                End If
            Next
        End If
    End With
    
End Function

Open in new window

0
 

Author Closing Comment

by:Rick Norris
ID: 38845033
Sorry for the delay,I got sidetracked the last few days.  Excellent catch in my code!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question