Defining Const in VBA

ACommandLineKindaGuy

Active Member
Joined
May 11, 2002
Messages
304
I'm trying to determine to set a constant that returns whether a commandbar control is on any of the visible commandbars. This code runs when the workbook is opened...

Code:
'See if we have a drawing control on a visible toolbar and log it as a constant
y = False
On Error Resume Next
For Each Cbar In Application.CommandBars
    If Cbar.Visible And Not Cbar.FindControl(ID:=204) Is Nothing Then
        y = True
        Exit For
    End If
Next Cbar
Const HaveDrawingControl As Boolean = y   '<<COMPILE  ERROR

However I get a compile error where indicated. If I modify the if statement to define the constant, I get a different compile error. The VBA help is pretty lean on explaining what the errors mean, so no progress there.

I suppose I could modify the code to this...

Code:
'See if we have a drawing control on a visible toolbar and log it as a constant
On Error Resume Next
For Each Cbar In Application.CommandBars
    If Cbar.Visible And Not Cbar.FindControl(ID:=204) Is Nothing Then
        Const HaveDrawingControl As Boolean = True
        Exit For
    End If
Next Cbar

...but now I have no way to set the Constant to False so I can use it elsewhere. Anyone have any ideas?

Thanks
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
Hi

As its name says, the value of a constant cannot change.

If you want a value that changes you have to use a variable.

HTH
PGC
 

ACommandLineKindaGuy

Active Member
Joined
May 11, 2002
Messages
304
Well, that's right--and I don't want it to change--but it seems I can't set it except in the declarations area.

I'd use a variable, but I need a variable that doesn't lose scope while the workbook is open and that can be accessed whenever code is run. My first try was to use a Public variable, but I found instances where it's scope was lost...
 

Watch MrExcel Video

Forum statistics

Threads
1,114,397
Messages
5,547,718
Members
410,807
Latest member
Coldesi
Top