Formulas with variables stored in different models return all values as zero

[Ed Song]

Hello,

I have a fairly complex mathematical model where variables and its value are currently stored in three modules.
If I use a formula where the variables are stored in the same module, the formula will return the correct value.
However, if I call up a value from a different module to module 3, the formula will always return a value of 0.

For example, in module 1, I have executed the procedure:

PotentialWagesGroup1 = 750
Worksheets(1).Range("D10").Value = PotentialWagesGroup1

In module 2

FullTimeWagesGroup1Year1 = GrowthRate ^ 0 * PotentialWagesGroup1
Worksheets(1).Range("J3").Value = FullTimeWagesGroup1Year1

Then in module 3

BeforeTaxIncomeGroup1Year1 = FullTimeWagesGroup1Year1
Worksheets(1).Range("Z71").Value = BeforeTaxIncomeGroup1Year1

All variables in this example are declared as public variables as double



Cell D10 reads 750
Cell J3 reads 750
but Cell Z71 reads 0

This is just one example of many tests I've done. In all cases Cell Z71 reads 0.

I've done a test in module 3 to see if variables stored only in that module also return a 0 value.

Dim Test As Double
Test = 20

Dim ProductTest
ProductTest = Test * 2
Worksheets(1).Range("Z72").Value = ProductTest


Cell Z72 reads 40, which is correct.

Anyone have an idea what the problem is?

 

[Ed Song]

Problem Solved!

To make public variables retain its value from module to module, you have to declare the subroutines that the public variables run in as public subroutines. Thus, Peter's example should have been written as: In module 1


Option Explicit

Public X As Double

Public Sub Test1()
X = 17
MsgBox "X = " & XEnd Sub

and for module 2


Option Explicit
Public Y As Double
Public Sub Test2()
MsgBox "X = " & X & vbLf & "Y = " & Y
Y = 5 * X
MsgBox "Y = " & Y Worksheets(1).Range("Z71").Value = Y MsgBox "Cell Z71 = " & Worksheets(1).Range("Z71").Value

End Sub

Now, I get the right answer. FYI, the formulas worked in modules 1 and 2 because I had typed the Public before the sub, but failed to do it in module 3. I worked on this for hours and hours without ever noticing that.