Public Const strConstantBlue As String = "RGB(79, 129, 189)" throwingerror messages

[andreas]

Dear Experts:


I declared a Public Constant whose value is valid for a COUPLE of
macros. The declaration statement to define the value of the constant
is as follows:

Public Const strConstantBlue As String = "RGB(79, 129, 189)"


The above defined constant will then be used in below macro as well as
several similar macros. I declared a Public Const so that I just have
to alter the RGB values in one place, should a change be required.

Regrettably the macro throws error messages, telling me that a runtime
error 13 has occurred. What's wrong with the declaration statement of
the Public Constant?

Sub BordersBlue()

Set myTable = Selection.Tables(1)


With myTable.rows(1)
..Borders(wdBorderTop).LineStyle = wdLineStyleSingle
..Borders(wdBorderTop).LineWidth = wdLineWidth050pt
..Borders(wdBorderTop).Color = strConstantBlue
End With

With myTable.rows(1)
..Borders(wdBorderBottom).LineStyle = wdLineStyleSingle
..Borders(wdBorderBottom).LineWidth = wdLineWidth050pt
..Borders(wdBorderBottom).Color = strConstantBlue
End With

End Sub

 

[Doug Robbins - Word MVP]

Use:

Public Const intRed As Integer = 79
Public Const intGreen As Integer = 129
Public Const inBlue As Integer = 189

Sub BordersBlue()
Dim myTable As Table

Set myTable = Selection.Tables(1)


With myTable.Rows(1)
..Borders(wdBorderTop).LineStyle = wdLineStyleSingle
..Borders(wdBorderTop).LineWidth = wdLineWidth05pt
..Borders(wdBorderTop).Color = RGB(intRed, intGreen, IntBlue)
End With

With myTable.Rows(1)
..Borders(wdBorderBottom).LineStyle = wdLineStyleSingle
..Borders(wdBorderBottom).LineWidth = wdLineWidth050pt
..Borders(wdBorderBottom).Color = RGB(intRed, intGreen, IntBlue)
End With


End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Peter Jamieson]

What's wrong with the declaration statement of
the Public Constant?

There's nothing wrong with the declaration, but
a. Word VBA expects a Color to be set to a Long (i.e. 32-bit integer) type
b. VBA will not automatically convert a string with the value
"RGB(79,129,189)" to be the Long value that you need.
c. i.e. you are defining a constant of the wrong type.

What you can do is discover the value of RGB(79,129,189), for example by
typing

?rgb(79,129,189)

in the Immediate Window, note the result (12419407) and use something like

Public Const myBlue as Long = 12419407&

Or, if you prefer to use a 24-bit Hex constant where each of the three
colours is represented as two hex digits (although in the reverse
sequence), you can use

?hex(rgb(79,129,189))

which displays BD814F

then plug that into the value as

Public Const myBlue as Long = &HBD814F


As background...

Don't be fooled by the fact that VBA will convert other types of value,
for example

Sub test()
Dim i as Integer
i = "123"
Debug.Print i*2 ' should display 246
End Sub

In this case VBA is converting the string "123", which would be
represented in memory as 3 16-bit Unicode values and some string length
information, into an Integer value, which would be represented as a
single 16-bit value.

However, these conversion facilities are fairly limited.

If you happened to be thinking that because VBA is used to define
"macros" and you are familiar with the notion that "macro languages" are
actually about text replacement, perhaps you hoped that by using

Public Const strConstantBlue As String = "RGB(79, 129, 189)"
then
..Borders(wdBorderTop).Color = strConstantBlue

that Word would substitute the text "strConstantBlue" by the text
"RGB(79, 129,189)" and execute the resulting statement as you hoped.

However, VBA is not a "macro processing language" in that sense of the word.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[andreas]

Use:

Public Const intRed As Integer = 79
Public Const intGreen As Integer = 129
Public Const inBlue As Integer = 189

Sub BordersBlue()
Dim myTable As Table

Set myTable = Selection.Tables(1)

With myTable.Rows(1)
.Borders(wdBorderTop).LineStyle = wdLineStyleSingle
.Borders(wdBorderTop).LineWidth = wdLineWidth05pt
.Borders(wdBorderTop).Color = RGB(intRed, intGreen, IntBlue)
End With

With myTable.Rows(1)
.Borders(wdBorderBottom).LineStyle = wdLineStyleSingle
.Borders(wdBorderBottom).LineWidth = wdLineWidth050pt
.Borders(wdBorderBottom).Color = RGB(intRed, intGreen, IntBlue)
End With

End Sub

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com















- Show quoted text -

Hi Dough,

Great. This did the trick!. Thank you very much for your professional
help. Regards, Andreas

 

[andreas]

 > What's wrong with the declaration statement of
 > the Public Constant?

There's nothing wrong with the declaration, but
  a. Word VBA expects a Color to be set to a Long (i.e. 32-bit integer)type
  b. VBA will not automatically convert a string with the value
"RGB(79,129,189)" to be the Long value that you need.
  c. i.e. you are defining a constant of the wrong type.

What you can do is discover the value of RGB(79,129,189), for example by
typing

?rgb(79,129,189)

in the Immediate Window, note the result (12419407) and use something like

Public Const myBlue as Long = 12419407&

Or, if you prefer to use a 24-bit Hex constant where each of the three
colours is represented as two hex digits (although in the reverse
sequence), you can use

?hex(rgb(79,129,189))

which displays BD814F

then plug that into the value as

Public Const myBlue as Long = &HBD814F

As background...

Don't be fooled by the fact that VBA will convert other types of value,
for example

Sub test()
Dim i as Integer
i = "123"
Debug.Print i*2 ' should display 246
End Sub

In this case VBA is converting the string "123", which would be
represented in memory as 3 16-bit Unicode values and some string length
information, into an Integer value, which would be represented as a
single 16-bit value.

However, these conversion facilities are fairly limited.

If you happened to be thinking that because VBA is used to define
"macros" and you are familiar with the notion that "macro languages" are
actually about text replacement, perhaps you hoped that by using

Public Const strConstantBlue As String = "RGB(79, 129, 189)"
then
.Borders(wdBorderTop).Color = strConstantBlue

that Word would substitute the text "strConstantBlue" by the text
"RGB(79, 129,189)" and execute the resulting statement as you hoped.

However, VBA is not a "macro processing language" in that sense of the word.

Peter Jamieson

http://tips.pjmsn.me.uk












- Zitierten Text anzeigen -

Hi Peter,

thank you so much for the in-depth look into this matter. I really
appreciate it. Great support!

Regards, Andreas