workbook open event not storing global variables

[zack_]

Hey All,

I have the below code:

Option Explicit
Public lr As Long


Private Sub Workbook_Open()
Let lr = Range("A" & Rows.Count).End(xlUp).Row
End Sub

my understanding of global variables is that they are saved and available to all modules within the workbook. When I try to test this variable to see if it stored the correct value with a msgbox in a new worksheet, the message box returns a blank value.

does anyone know why the workbook open event would not be storing this variable?

 

[Norie]

Public variables should be declared in standard modules (Insert>Module)?

 

[zack_]

Public variables should be declared in standard modules (Insert>Module)?

Thanks a lot for the help! You fixed my issue, and i think finally got me back on track with my project!

 

[mikerickson]

That definition of LR will find the last row of whatever worksheet is active when the workbook is open.
If the user adds rows to that worksheet, the value will not reflect the added data, nor will it be applicable to any other worksheet in the workbook.

It would make more sense (i.e. set values in the objects that they are part of) to put this in every sheet's code module (not the ThisWorkbook)

Property Get LR() as Long
    LR = Me.Range("A" * Rows.Count).End(xlUp).Row
End Property

Then one could refer to the Last row of any worksheet with code like this.

MsgBox Worksheets("Sheet2").LR & " is the last row in " & Worksheets("Sheet2").Name

And it would reflect any additions made by the user.