User-Defined Function to Determine Cell Formatting
dnsw
bold or not bold). My novice attempt to write such a function (called
IsBold) was unsuccessful. Given below is the attempt.
Function IsBold(TestCell As Range)
IsBold = Worksheet("Sheet1").Range(TestCell).Font.Bold
End Function
I had expected the function to return true or false depending on whether
the cell in question was bold or not. Using Excel 97 the function
appeared under the user-defined category as expected. However when
inserted the message box said 'function takes no arguments', so
evidently I am not passing the correct cell address. If I insert a cell
address anyway, the NAME# error appeared. Any help would be
appreciated.
D.Shaw
dn...@worldnet.att.net
Rkrats4
Function IsBold(TestCell As Range) As Boolean
'Works only for 1 cell selection
If TypeName(Selection) = "Range" And _
TestCell.Font.Bold = True Then
IsBold = True
Else: IsBold = False
End If
End Function
Enjoy,
Ron S|%)
Thomas Ogilvy
Just to explain why you are having a problem:
when you do
=isBold(a1)
then TestCell holds a reference to something like [book5.xls]Sheet1!A1
but you are treating it as if it were a string variable containing "A1"
This will work:
Function IsBold(TestCell As Range)
Application.Volatile
IsBold = TestCell.Font.Bold
End Function
However, changing the font to bold does not trigger a recalc - so it will
not always be accurate. Adding application volatile will cause it to be
recalced every time the sheet is recalced, but there will still be periods
when it is incorrect.
Regards,
Tom Ogilvy
dnsw wrote in message <373E0F57...@worldnet.att.net>...
>I need a function that will return format properties of a cell (Is it
dnsw
try either suggested code I still get the error message "#NAME?". The
function is being recognized as it appears in the User Defined group when
Insert Function is selected. The test I made was to enter some text in cell
A1, then I entered in B1 the function "=IsBold(A1)". The result was the
"#NAME?" error in B1. It seems as if it should work, but it doesn't. Any
suggestions would be appreciated.
D. Shaw
Myrna Larson
>Many thanks to Thomas Ogilvy and Rkrats4 for your help. Unfortunately when I
>try either suggested code I still get the error message "#NAME?". The
>function is being recognized as it appears in the User Defined group when
>Insert Function is selected. The test I made was to enter some text in cell
>A1, then I entered in B1 the function "=IsBold(A1)". The result was the
>"#NAME?" error in B1. It seems as if it should work, but it doesn't. Any
>suggestions would be appreciated.
The following works without problems for me:
Function IsBold(X As Range)
IsBold = X.Font.Bold
End Function
BTW, it returns TRUE if the cell is formatted as Bold, even though it contains
no data.
Håkan Müller
It's not as simple as Excel has the option R1C1 type of reference ticked,
and you're entering your A1 reference from the keyboard? No? Well, I didn't
really think so.
/Håkan
Thomas Ogilvy
Do you have the function in a general module rather than the module
associated with the worksheet?
Regards,
Tom Ogilvy
dnsw wrote in message <373EEE1E...@worldnet.att.net>...
>Many thanks to Thomas Ogilvy and Rkrats4 for your help. Unfortunately when
I
>try either suggested code I still get the error message "#NAME?". The
>function is being recognized as it appears in the User Defined group when
>Insert Function is selected. The test I made was to enter some text in
cell
>A1, then I entered in B1 the function "=IsBold(A1)". The result was the
>"#NAME?" error in B1. It seems as if it should work, but it doesn't. Any
>suggestions would be appreciated.
dnsw
editor, I selected View/Code. When the code window appeared, I entered the
function. Just as you suspected, the function was entered for Book 1, Sheet
1(code) and the #NAME? error resulted whenever I attempted to use the function.
On the other hand, if I entered the function code by selecting Insert/Module,
the code was entered into Book 1, Module 1 (code) and the function performs
correctly, returning true or false depending on the format of the test cell.
I'm uncertain why it didn't work when entered as Sheet 1 (code) at least when
the function was used with Sheet 1. A brief explanation would be welcomed. In
any case all is well now. Thanks also to the others who responded to my pleas
for help.
Myrna Larson
>I'm uncertain why it didn't work when entered as Sheet 1 (code) at least when
>the function was used with Sheet 1. A brief explanation would be welcomed. In
>any case all is well now. Thanks also to the others who responded to my pleas
>for help.
It's probably a Private procedure.
Paul S. Cilwa
about it, but misunderstands the number of arguments and doesn't recognize
the name after it's inserted.
Why don't functions in Sheets work the same as in Modules? That's the
question.
--Paul
Myrna Larson <myrna_...@email.msn.com> wrote in message
news:374284c9...@msnews.microsoft.com...