Casing of built-in property Font.Bold

[RB Smissaert]

Excel 2003.
Noticed something strange and that is that in my code the casing of
Font.Bold is not like that anymore but instead: Font.bOld
Making it Font.Bold doesn't alter anything, it will revert back to bOld.
I had a look if I maybe had a class or UDT with this property, but I
haven't.
I had a local variable bOld, but changing that to something else made no
difference.
Not sure there is any harm in it, but maybe somebody could explain to me
what is
going on here.

RBS

 

[Bob Phillips]

Hi Bart,

I have noticed this many times. Somehow, VBA seems to keep some record of
it, and maintains it looking like a reference to the variable even when you
change/delete that variable. I have never had a problem with it, even when I
have had a variable with the same name as the property.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[RB Smissaert]

Thanks Bob, that is reassuring.
This looks like a bug in VBA to me.
Any idea where this could be stored?
The object browser shows (as expected) the normal casing, Bold.

RBS

 

[Bob Phillips]

Bart,

This is just conjecture upon my part, and I have no idea if it is actually
so, and if so where. Like you, I think it is a bug, no proper clean-up when
variables are deleted.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[RB Smissaert]

It was easy though to make it go back to the normal Font.Bold by adding
this to a normal module, and then deleting it:

Public Type dummy
Bold As Boolean
End Type

Pleased with this as it looked bad.

RBS

 

[Tim Zych]

Dim bOld As Boolean
Dim rng As Range
Set rng = ActiveCell
rng.Font.bOld = True

To get back the right casing:

Dim Bold as Boolean

 

[Bob Phillips]

LOL! never thought of that. I have experienced it most with Mid, where I
have declared a variable of mID. Even declaring it as a simple variable
corrects it back. I will remember this.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ralph]

Thanks Bob, that is reassuring.
This looks like a bug in VBA to me.
Any idea where this could be stored?
The object browser shows (as expected) the normal casing, Bold.

RBS

As Mr. Phillips noted it is common enough - but not so common that it isn't
surprising when it happens.

It comes about because of the "interpreted" nature of VBA, and the complex
symbol tables VBA creates as it flows along (during development). This is
'stored' as pcode or sometimes called OpCode in the VBA world. Just as in VB
the 'text' portion of the code that you see in the editor is actually a View
to the underlying pcode. When you type something VBA immediately parses it
(syntax checking, symbol table comparisons, etc.) and converts it to pcode,
this pcode is immediately converted back to text and presented back to the
User. That's where the spelling change happens. (Happens quick doesn't it.
<g>)

[The same thing is happening in VB.]

When doing your own research note that there two subtle flavors of 'OpCode'
in VBA - OpCode and ExCode. OpCode refers to pcode within an Editor. ExCode
refers to pcode that is packaged up and ready to 'run' - precompiled pcode.
For example while you have a module open in the editor the code behind is
OpCode. Once you save the module it gets wraps with additional material, is
rearranged a bit and becomes ExCode. Or to some - ExCode plus OpCode. That
is best left up to the geeks as this is a rather fine distinction since to
us it is just pcode. <g>

While developing a lot of crap builds up. (technical term for additional
information no longer needed). You can get rid of most of this stuff, by
periodically running the module - convert the 'OpCode' to 'ExCode'. You
don't have to excise the whole module just call some subroutine in it.

[That's why in VB a periodic "Start with Full Compile" helps to keep it
'clean', and removes strange errors.]

There used to be a whole list of "things to do" to clean up code (reset it),
like saving code to a file and reloading it. I don't remember them all -
most were probably wives' tales anyway. <g> Supposedly Office VBA cleaned up
its act with Office 2000 and beyond (more cleanup when converted to ExCode).
There must still be some need because I see the occasional "VBA Decompiler
and Cleaner" being sold. These tools cut down module size and appear to
improve performance so apparently things haven't changed that much. Most
full-blown Office developers own one.

This is really the kind of question you should be asking in an Excel
Developer's group. They fool with this on a daily basis.

hth
-ralph

 

[RB Smissaert]

Yes, found out, as you say even a simple local variable will make it go back
to normal.
Somehow it must be stored in the particular workbook as other workbooks are
not
affected and closing and re-opening Excel doesn't make it go away.
Had a look in VB6 and it does the same.

RBS

 

[RB Smissaert]

This is really the kind of question you should be asking in

an Excel Developer's group

Did some quick testing and it looks to me that VB6 behaves exactly the same.
So, not sure now if your explanation to do with OpCodea and ExOpCode
explains it all.

RBS

Thanks Bob, that is reassuring.
This looks like a bug in VBA to me.
Any idea where this could be stored?
The object browser shows (as expected) the normal casing, Bold.

RBS

As Mr. Phillips noted it is common enough - but not so common that it
isn't
surprising when it happens.

It comes about because of the "interpreted" nature of VBA, and the complex
symbol tables VBA creates as it flows along (during development). This is
'stored' as pcode or sometimes called OpCode in the VBA world. Just as in
VB
the 'text' portion of the code that you see in the editor is actually a
View
to the underlying pcode. When you type something VBA immediately parses it
(syntax checking, symbol table comparisons, etc.) and converts it to
pcode,
this pcode is immediately converted back to text and presented back to the
User. That's where the spelling change happens. (Happens quick doesn't it.
<g>)

[The same thing is happening in VB.]

When doing your own research note that there two subtle flavors of
'OpCode'
in VBA - OpCode and ExCode. OpCode refers to pcode within an Editor.
ExCode
refers to pcode that is packaged up and ready to 'run' - precompiled
pcode.
For example while you have a module open in the editor the code behind is
OpCode. Once you save the module it gets wraps with additional material,
is
rearranged a bit and becomes ExCode. Or to some - ExCode plus OpCode. That
is best left up to the geeks as this is a rather fine distinction since to
us it is just pcode. <g>

While developing a lot of crap builds up. (technical term for additional
information no longer needed). You can get rid of most of this stuff, by
periodically running the module - convert the 'OpCode' to 'ExCode'. You
don't have to excise the whole module just call some subroutine in it.

[That's why in VB a periodic "Start with Full Compile" helps to keep it
'clean', and removes strange errors.]

There used to be a whole list of "things to do" to clean up code (reset
it),
like saving code to a file and reloading it. I don't remember them all -
most were probably wives' tales anyway. <g> Supposedly Office VBA cleaned
up
its act with Office 2000 and beyond (more cleanup when converted to
ExCode).
There must still be some need because I see the occasional "VBA Decompiler
and Cleaner" being sold. These tools cut down module size and appear to
improve performance so apparently things haven't changed that much. Most
full-blown Office developers own one.

This is really the kind of question you should be asking in an Excel
Developer's group. They fool with this on a daily basis.

hth
-ralph

 

[Ralph]

< snipped>

This is really the kind of question you should be asking in an Excel
Developer's group. They fool with this on a daily basis.

Please ignore. I didn't notice that you did post to an Excel group.

-ralph

 

[RB Smissaert]

The main thing is that it looks to be the same in VB6.

RBS

 

[Ralph]

LOL! never thought of that. I have experienced it most with Mid, where I
have declared a variable of mID. Even declaring it as a simple variable
corrects it back. I will remember this.

Perhaps the most annoying reflection of this in VB proper is with Enums,
where VB will change the spelling to whatever is last typed.
The fix is to do this ...
Public Enum eOPERATION_STATUS
eNoneSelected = 0
eNewEntry
eReports
eEnd
End Enum
#If 0 Then ' preserve case
Dim eNoneSelected, eNewEntry, eReports, eEnd
#End If

You can do that in VBA as well. But to straighten out 'normal' Dimensioned
Variables, such a construct would be more of a 'hack' than a 'cure'. <bg>

-ralph

 

[Ralph]

Did some quick testing and it looks to me that VB6 behaves exactly the same.
So, not sure now if your explanation to do with OpCodea and ExOpCode
explains it all.

RBS

But that is the point. It IS EXACTLY the same thing.
Open up VB.
Open up Project ->References.
What do you see at the top? <g>

During development in the editor, VB (or VBA) is doing exactly what I
explained. It tends to build on what its got. If you delete something it
isn't automatically removed from the internal tables/caches. You provide a
new spelling (counting case), etc. Wrong ideas tend to build up and can
appear mysterious until you appreciate that what you are typing is actually
going elsewhere.

Imagine if you will, you are standing in an Office dictating to a clerk
sitting at a desk. As you describe what you would like to do the clerk
dutifully takes what you say writes it out to sheets of fan fold paper as a
series of instructions in another language. You stop and decide to delete
something you said - the clerk goes back and crosses it off. But its still
there on the paper. Trash builds.

Let's say you provide a spelling for a term. The clerk writes it to a table.
Let's say you use it again with a different spelling, the clerk goes back
and corrects it on his paper. But let's say we are using a different
context, or for whatever reason - the clerk uses the new spelling. Now he
has two symbols with the only difference one of case. He picks one - likely
the first one he runs across.

It is interesting to note that "bOld" sorts before "Bold". <g>

Again the "code text" within an editor window is just a artifact of what
VB/VBA is actually using. It is never saved with an Office project - it is
converted to ExCode. When you open a module the ExCode is converted to Text.

I was only suggesting that you post to an Excel group (before I realized you
WERE posting to an Excel group) because they might have a better idea of how
to get it out of its system. But that is because I'm kind of stuck in a
pre-2000 world when it comes to Office VBA development. The last time I got
paid to create Office Apps trash accumulation was a real problem and I
vaguely remember odd things happening and strange cures.

I find it rather cool that the solution is so simple.

-ralph

 

[Ralph]

Yes, found out, as you say even a simple local variable will make it go back
to normal.
Somehow it must be stored in the particular workbook as other workbooks are
not
affected and closing and re-opening Excel doesn't make it go away.
Had a look in VB6 and it does the same.

RBS

It is stored in the workbook as pcode (ExCode).

The spelling change occurred because the pcode has a symbol table that
refereces that variable as "bOld". The "code text" in an editor window is
reconstructed from the pcode. When you type in "Bold", the parser performs a
spelling check - notes that you typed the wrong case - and helpfully
corrects it for you, making it hard to shake it.

-ralph

 

[RB Smissaert]

I just thought (not sure why now) that OpCode and ExOpCode were a VBA thing.
That combined with the suggestion to post to an Excel group made me think
you were
on the wrong track. I am sure you are right and thanks for clarifying.

BTW, VBA still needs a code cleaner and I run Andrew Baker's Workbook
rebuilder after
every compile and save of any .xla or .xls with any significant amount of
code. Not doing that
can still get you into real trouble.

RBS

 

[Bob Phillips]

I know. That one I really hate, it is just so darned annoying.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)