Prev: VBA Macro SaveAs Newbe
Next: "grouping" a table to prevent change from users
From: Peter Jamieson on 24 Jan 2010 11:10
> 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

On 24/01/2010 07:56, andreas wrote:
> 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
 | 
Pages: 1
Prev: VBA Macro SaveAs Newbe
Next: "grouping" a table to prevent change from users