How to set a project-wide variable

[EdNerd]

This feels like a stupid, basic question that I should know. But I can't get it!! I have a named range on Sheet1, cell $D$4. In Workbook_Open, I need to grab the value in this cell and use it to determine a Boolean variable to use in two separate modules (workbook, not worksheet). "If range = 1, Boolean = True". How do I set this up, please?? (XL 365)

 

[Vogateer]

If I'm understanding you correctly, I would set aside a sheet for such variables and save your variable there. So in Workbook_Open, you would run the code that reads $D$4, then write the variable you need to some named range in Sheets("Variables"), and use that named range in your other modules.

 

[EdNerd]

If I'm understanding you correctly, I would set aside a sheet for such variables and save your variable there. So in Workbook_Open, you would run the code that reads $D$4, then write the variable you need to some named range in Sheets("Variables"), and use that named range in your other modules.

Thank you for the input. I have two questions here:
-- The cell at Sheet1, $D$4, is already a named range with Workbook scope. I'm having trouble referencing it to set my Boolean.
-- How do I declare and set this variable in the Workbook module to be available in any module across the entire workbook?

 

[Vogateer]

Now that I'm looking at it again, I guess what I'm confused about why the value needs to be read when the Workbook is opened, since you can read that value at any time with any module. I might be missing something here.

If it's a named range already, then you should be able to reference it within VBA as Sheet("[sheet_name_here]").Range("[named_cell_here]"). If the value in that cell is TRUE or FALSE, Excel tends to treat it as Boolean. No matter where your code is, I would reference that cell for the Boolean value I need.

Dim named_cell As Range, named_cell_value As Boolean
    Set named_cell As ThisWorkbook.Sheets(1).Range([named_cell_here])
    ' if you're using 1 and 0 instead of TRUE or FALSE
    If named_cell.Value2 = 1 Then
        named_cell_value = True
    Else
        named_cell_value = False
    End If

You could set the value of that cell through VBA if you need to change it.

named_cell.Value2 = 0

Then you can read the value again whenever you think you might need an updated value.

Or if you're just trying to do a Workbook named reference without using Sheets at all, I think you can do it this way:

Range("[Your_sheet_name].xls![named_cell")

 

[EdNerd]

In ThisWorkbook module, I have

'
Public DoDebug As Boolean
'

Sub Workbook_Open()

Dim bolVar As Variant
bolVar = ThisWorkbook.Worksheets("Sheet1").Range(["IEdebug"]).Value

If bolVar = 1 Then
  DoDebug = True
Else
  DoDebug = False
End If

Debug.Print bolVar
Debug.Print DoDebug

bolVar and DoDebug set correctly, according to the value in the cell named "IEdebug".

Clicking in a specific cell in Sheet1 starts the code running in Module 1, which occasionally calls to a sub in Module 2.
In that sub, I have

 If DoDebug = False Then Exit Sub

The Boolean DoDebug does not exist in that sub.

 

[RoryA]

How do I declare and set this variable in the Workbook module to be available in any module across the entire workbook?

Declare the variable as Public in a normal module. If you declare it in ThisWorkbook you always have to refer to it as ThisWorkbook.DoDebug, not just DoDebug.

 

[EdNerd]

Thank you, Rory!! That did the trick. :8>)