Public variable value reset when called in another module, Excel VBA

Question

In Excel, I have two modules, let's say module1 and module2.

In module1, I have declared a public variable:

Public offsetNumber As Integer

Within the sub, I assign a number to the 'offsetNumber' variable.

offsetNumber = 2
Msgbox offsetNumber

This successfully displays the number 2.

Now, in module2, I try to call the variable offsetNumber.

Msgbox offsetNumber

However, the value displayed is 0, not 2.

How can I carry over the variable value from module1 to module2?

UPDTATE:

I've tried the module1.offsetNumber and checking "require variable declarations". I've also checked to make sure that the value is not being overwritten (at least in module1).

To be more in depth, this is how my module1 is currently structured:

Public offsetNumber as Integer

Sub **********

Dim .... (declare variables)
Activecell.EntireRow.Select
currentRow = ActiveCell.Row
offsetNumber = currentRow - 48
Msgbox offsetNumber (returns correct value)

........... (misc code)

Msgbox offsetNumber (returns correct value)

End Sub

This is how my module2 is structured:

Sub *********

Dim ..... (declare variables)

.............. (misc code)

Msgbox offsetNumber (returns 0)

For rowCounter = 2 to lastRow
    If Sheets("****").Cells(rowCounter, 3) = ****** Then
        Sheets("****").Activate
        Rows(rowCounter + offsetNumber).Select
    End If
Next rowCounter

End Sub

In "Immediate" window after executing module1, the following line

? offsetNumber

returns a value of 0.

SOLUTION:

This is what worked for me:

Module1:

Public globalOffsetNumber as Integer

Sub **********

Dim .... (declare variables)
Activecell.EntireRow.Select
currentRow = ActiveCell.Row
offsetNumber = currentRow - 48
globalOffsetNumber = offsetNumber (new line)

Msgbox offsetNumber (returns correct value)

........... (misc code)

Msgbox offsetNumber (returns correct value)

End Sub

Module2:

Sub *********

Dim ..... (declare variables)

.............. (misc code)

Msgbox globalOffsetNumber (now returns correct value)

For rowCounter = 2 to lastRow
    If Sheets("****").Cells(rowCounter, 3) = ****** Then
        Sheets("****").Activate
        Rows(rowCounter + globalOffsetNumber).Select
    End If
Next rowCounter

End Sub

try Module1.offsetNumber Where module1 is the name of the module where offsetNumber is declared — 99moorem
I suggest you set in VB Tools, Options, "Require variable declarations" on. This will then inform you whether the variable is found as otherwise it is automatically declared and has a default value of zero. As user 99moorem says, you might find it needs the module identifier to be found. — Paul Ogilvie
My guess is that somehow you have overwrited the offsetNumber public variable with some other one inside that scope with the same name, did you check this? — Abe
You said "lets say module 1" - are you sure that it isn't in something like the sheet1 code module? Those modules behave differently then modules you insert — John Coleman
I'm not sure how to distinguish between different module types, but all of my modules were created by insert -> module in the VBA editor. — Jaehead

John Coleman John Coleman · Accepted Answer · 2015-06-15T14:45:05

You don't have to do anything for a public variable which is declared in one module to be accessible in another. Try this experiment:

In module 1 have:

Option Explicit
Public s As String

Sub setvar()
    s = "Hi"
End Sub

In module 2 have:

Sub readvar()
    Debug.Print s
End Sub

If you first run setvar and then immediately run readvar you should see "Hi" displayed in the immediate window.

My guess is that you are somehow or other resetting the project after running your first sub but before running the second.

Public variable value reset when called in another module, Excel VBA

2 Answers