Setting font properties using function

[Rod]

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

 

[joel]

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 Su

 

[Rod]

OK, that works fine. Thanks

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

 

[Rick Rothstein]

'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)

OK, that works fine. Thanks

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

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/showthread.php?t=166121

Microsoft Office Help

 

[Rod]

Thanks
I understand the error message now.

'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)

OK, that works fine. Thanks

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

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/showthread.php?t=166121

Microsoft Office Help

 

[joel]

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 Functio

 

[Rod]

OK thanks, I see now.