CODEWITHSUNDEEP
vbaexcel
Cooper5114
Cooper5114

Reputation: 53

Global scope lost in vba-excel

Goal = create a variable that gets calculated when i start excel and have that variable globally available to all other modules -- i.e. the size of a given worksheet.

In ThisWorkbook i have this in the declaration part. I thought that each of these would be visible/persistent to other modules. This does actually get called as expected when i start excel. 

Public BillingMaxRows As Integer
Public BillingMaxColumns As Integer
Public MonthlyMaxRows As Integer
Public MonthlyMaxColumns As Integer

and then this outside of the declarations part. 

 Sub Workbook_Open()
    BillingMaxRows = Sheets("billing").UsedRange.Rows.Count
    BillingMaxColumns = Sheets("billing").UsedRange.Columns.Count
    MonthlyMaxRows = Sheets("Monthly Billings").UsedRange.Rows.Count
    MonthlyMaxColumns = Sheets("Monthly Billings").UsedRange.Columns.Count
    MsgBox (BillingMaxRows)    
End Sub

Then in a generic module i try enter code hereand use any of these variables but they do not appear to be in scope.

Upvotes: 0

Views: 112

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

ThisWorkbook, as well as any Worksheet module, is an object - an instance of a class module - in this case an instance of the Workbook class.

Public members in class modules aren't global; they're publicly accessible to whoever is able to access an instance of the class.

So Public Foo As Long declared as a field in ThisWorkbook, would be accessible through:

Debug.Print ThisWorkbook.Foo

And assigned all the same:

ThisWorkbook.Foo = 42

If you want the variable to be publicly accessible, but read-only, you must encapsulate it. Declare it Private, and expose a Property Get accessor for it:

Private mFoo As Long

Public Property Get Foo() As Long
    Foo = mFoo
End Property

And then you can have any member in ThisWorkbook assign mFoo, and any member in any other module of the project read Foo.

If you want an actual global variable (hint: you probably don't), then you need to declare it in a standard module (.bas) instead.

Upvotes: 3

Related Questions