Defining variables on Excel VBA

Question

Dears,

I'm starting a new project using Excel VBA and I would like to declare some variables to be loaded during the system's initialization, but during the utilization it may be changed. How can I do it?

Example: When the system is loaded, the variable RADIO must be equal to "OFF", but during the utilization the user will change this value to "ON", and even to "OFF" again.

This information (RADIO) will be used during the system's utilization in some other pages.

How can I do it? Thank you so much

Bruno Lelli

You need to use a global variable (declared outside of any method and potentially flagged as Public) — Tim Williams
– Tim Williams, Commented Jun 4, 2018 at 23:18
If the "sysem initiation" you're referring to is "opening the Excel file" then you can use the Workbook_Open event to automatically execute code. — ashleedawg
– ashleedawg, Commented Jun 4, 2018 at 23:27

Kawi42 · Accepted Answer · 2018-06-09 15:34:46Z

2

Like Tim Williams said, you need a global variable. Best practice is to have it declared in the declaration block (very first lines) of a code mudule.

To have it available within the code module only: Private booRadio as Boolean

To have it available within the whole VBA project, i.e. all modules, user forms and for the workbook and worksheet events: Public booRadio as Boolean

When VBA starts, all Boolean variables get initialized to be False (likewise, all Integer or Long get initialized to be 0, String to be "", etc.). This can be used to have a known initial status after system start.

Or - which is better to read in code reviews and like-I-feel more robust - you use an event to initilize the startup status. E.g. like ashleedawg said, you may use the workbook_open event for this. In that case, you need your variable dacelared with the Public statement, if you want to access it outside the ThisWorkbook code.

EDIT:

Copy this code into the ThisWorkbook code module:

Private Sub Workbook_Open()
    booRadio = True
    End Sub

This will use the event Workbook_Open to initialize the variable upon every opening of the Excel file, because that event gets automatially raised then.

edited Jun 9, 2018 at 15:34

answered Jun 5, 2018 at 7:53

Kawi42

1194 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bruno Lelli Over a year ago

Ok, I got it, but how and where shoud I "declare" the initial value? Example... initially I would like to have the booRadio = "ON"

Collectives™ on Stack Overflow

Defining variables on Excel VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related