Setting font properties using function
 

I want to be able to set the properties of a cell font using a function.
I have tried the below but it errors in the function with

Error Number: 91
Error Description: Object variable or With block variable not set

Anyone not on holiday out there who can help me please!

happy new year

Rod


------------------------
'Calling code

xlSht.Cells(1, 1).Font = NameFormatType1(255)


Private Function NameFormatType1(NmCol As Long) As Excel.Font

NameFormatType1.Color = RGB(NmCol, 0, 0)
NameFormatType1.Bold = True

End Function

Setting font properties using function
 

Try these changes


Sub test()
Set xlSht = ActiveSheet
Call NameFormatType1(xlSht.Cells(1, 1), 255)

End Sub


Private Sub NameFormatType1(ByRef cell As Range, _
ByVal NmCol As Long)

With cell.Font
.Color = RGB(0, 0, NmCol)
.Bold = True
End With

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=166121

Microsoft Office Help

Setting font properties using function
 

OK, that works fine. Thanks

Out of curiosity can my way be made to work.
It compiled OK so I was hopeful.



"joel" wrote in message
...

Try these changes


Sub test()
Set xlSht = ActiveSheet
Call NameFormatType1(xlSht.Cells(1, 1), 255)

End Sub


Private Sub NameFormatType1(ByRef cell As Range, _
ByVal NmCol As Long)

With cell.Font
Color = RGB(0, 0, NmCol)
Bold = True
End With

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=166121

Microsoft Office Help

Setting font properties using function
 

'Calling code

xlSht.Cells(1, 1).Font = NameFormatType1(255)

Private Function NameFormatType1(NmCol As Long) As Excel.Font
NameFormatType1.Color = RGB(NmCol, 0, 0)
NameFormatType1.Bold = True
End Function

There are two problems with your originally posted code... one easily
solved, the second not. First, in your calling line of code, Font is an
object, so you must use the Set keyword to assign your function's returned
object to it. So that calling line should have looked like this...

Set xlSht.Cells(1, 1).Font = NameFormatType1(255)

Doing that, however, reveals the second problem... your function is declared
as an object, but it has no reference. That is, it's a font object, but a
font object to what? Obviously, you want it to reference A1 on xlSht, but
how is it to know that? Before you can change properties of NameFormatType1,
you first have to Set it to some object. In this case, you would need the
equivalent of this...

Set NameFormatType1 = xlSht.Cells(1, 1)

so it would know what object is having its properties modified. Of course,
having to do this would interfere with the approach you are trying to use.

--
Rick (MVP - Excel)


"Rod" wrote in message
...
OK, that works fine. Thanks

Out of curiosity can my way be made to work.
It compiled OK so I was hopeful.



"joel" wrote in message
...

Try these changes


Sub test()
Set xlSht = ActiveSheet
Call NameFormatType1(xlSht.Cells(1, 1), 255)

End Sub


Private Sub NameFormatType1(ByRef cell As Range, _
ByVal NmCol As Long)

With cell.Font
Color = RGB(0, 0, NmCol)
Bold = True
End With

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=166121

Microsoft Office Help

Setting font properties using function
 

Thanks
I understand the error message now.


"Rick Rothstein" wrote in message
...
'Calling code

xlSht.Cells(1, 1).Font = NameFormatType1(255)

Private Function NameFormatType1(NmCol As Long) As Excel.Font
NameFormatType1.Color = RGB(NmCol, 0, 0)
NameFormatType1.Bold = True
End Function

There are two problems with your originally posted code... one easily
solved, the second not. First, in your calling line of code, Font is an
object, so you must use the Set keyword to assign your function's returned
object to it. So that calling line should have looked like this...

Set xlSht.Cells(1, 1).Font = NameFormatType1(255)

Doing that, however, reveals the second problem... your function is
declared as an object, but it has no reference. That is, it's a font
object, but a font object to what? Obviously, you want it to reference A1
on xlSht, but how is it to know that? Before you can change properties of
NameFormatType1, you first have to Set it to some object. In this case,
you would need the equivalent of this...

Set NameFormatType1 = xlSht.Cells(1, 1)

so it would know what object is having its properties modified. Of course,
having to do this would interfere with the approach you are trying to use.

--
Rick (MVP - Excel)


"Rod" wrote in message
...
OK, that works fine. Thanks

Out of curiosity can my way be made to work.
It compiled OK so I was hopeful.



"joel" wrote in message
...

Try these changes


Sub test()
Set xlSht = ActiveSheet
Call NameFormatType1(xlSht.Cells(1, 1), 255)

End Sub


Private Sub NameFormatType1(ByRef cell As Range, _
ByVal NmCol As Long)

With cell.Font
Color = RGB(0, 0, NmCol)
Bold = True
End With

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=166121

Microsoft Office Help

Setting font properties using function
 

Here is another method closer to what you did.


Type MyFont
Color As Long
Bold As Boolean
End Type
Sub test()
Dim Mycell As MyFont

Set xlSht = ActiveSheet
Mycell = NameFormatType1(255)
xlSht.Cells(1, 1).Font.Color = Mycell.Color
xlSht.Cells(1, 1).Font.bold = Mycell.bold

End Sub

Private Function NameFormatType1(NmCol As Long) As MyFont

NameFormatType1.Color = RGB(NmCol, 0, 0)
NameFormatType1.bold = True

End Function


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=166121

Microsoft Office Help

Setting font properties using function
 

OK thanks, I see now.

"joel" wrote in message
...

Here is another method closer to what you did.


Type MyFont
Color As Long
Bold As Boolean
End Type
Sub test()
Dim Mycell As MyFont

Set xlSht = ActiveSheet
Mycell = NameFormatType1(255)
xlSht.Cells(1, 1).Font.Color = Mycell.Color
xlSht.Cells(1, 1).Font.bold = Mycell.bold

End Sub

Private Function NameFormatType1(NmCol As Long) As MyFont

NameFormatType1.Color = RGB(NmCol, 0, 0)
NameFormatType1.bold = True

End Function


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=166121

Microsoft Office Help