Defining Const in VBA

ACommandLineKindaGuy

Active Member
Joined
May 11, 2002
Messages
331
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
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
331
Office Version
  1. 365
Platform
  1. Windows
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...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,126
Messages
5,857,521
Members
431,883
Latest member
Hien

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top