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
– 99moorem, Commented Jun 15, 2015 at 13:57
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
– Paul Ogilvie, Commented Jun 15, 2015 at 14:00
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
– Abe, Commented Jun 15, 2015 at 14:30
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
– John Coleman, Commented Jun 15, 2015 at 15:20
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
– Jaehead, Commented Jun 15, 2015 at 15:33

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

1

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.

answered Jun 15, 2015 at 14:45

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

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

1 Comment

Jaehead Over a year ago

Thanks for the reply. That worked fine, so I'm assuming that the value is getting reset somewhere in module2, although I'm not sure where. I've updated the question to give a more in depth look at the modules. The misc code in module 2 are just code that take a value from a specific cell and assign it to a string variable.

Nyozha · Accepted Answer · 2019-02-26 10:54:55Z

0

I add the same issue, do not put "Public s As String" in module 2. Definition resets variable.

answered Feb 26, 2019 at 10:54

Nyozha

1

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related