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

#### Ed Song

##### Board Regular
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

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?

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Peter_SSs

##### MrExcel MVP, Moderator
With just those codes in the modules you describe, and executing Module 1 then Module 2 then Module 3, I get 750 in Z71 as expected.
So, given that you have stated that you have a complex module, I suspect that some other part of your model is interfering by either ..

a) Changing the value of FullTimeWagesGroup1Year1 before that line in Module 3 is executed, or

b) Changing Z71 after that code is executed.

As a staring point insert these lines in Module 3 and see if that sheds any light.

Rich (BB code):
``````MsgBox "FullTimeWagesGroup1Year1 = " & FullTimeWagesGroup1Year1
BeforeTaxIncomeGroup1Year1 = FullTimeWagesGroup1Year1
Worksheets(1).Range("Z71").Value = BeforeTaxIncomeGroup1Year1
MsgBox "Cell Z71 = " & Worksheets(1).Range("Z71").Value``````

BTW: I've moved your post to the Excel Questions forum.

Last edited:

#### Ed Song

##### Board Regular

However, I'm still stomped. I followed your suggestion and all of my variables stored in a different module come out with a value of zero in module 3. As a test, I pare down module three to just have my variable declarations and the test you describe. (so only a few lines of code). The values of the variables and cell are still zero. When I run module 1 and module 2, I get the right values. When I test cell z71 with a formula that uses only variables in module three, I get the right value. Thus, I don't think its the cell. But I don't see what's converting the values to zero because even when module three is pared down to just a couple lines of code, the variables in the other modules get converted to zero. I am completely stomped.

#### Ed Song

##### Board Regular
PS, I just ran a real simple test. In module 2

Public X
X=17

In module 3 I wrote

Public Y
Y=5*X

message box gave values of X and Y as zero.

I don't think its the coding. I think it has to do with the settings in module 3. Anyone has any other ideas?

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator

PS, I just ran a real simple test. In module 2

Public X
X=17

In module 3 I wrote

Public Y
Y=5*X

message box gave values of X and Y as zero.

I don't think its the coding. I think it has to do with the settings in module 3. Anyone has any other ideas?
Even For this simple test, show us the whole code. That is your Sub/End Sub lines MsgBox etc so we can repliacte exactly what you have.

#### Ed Song

##### Board Regular
Heres the full test

Module 2

Option Explicit

Public X As Double

Sub Test1()

X = 17

End Sub

Module 3

Option Explicit

Public Y As Double

Sub Test()

MsgBox "Y = " & Y
Y = 5 * X
Worksheets(1).Range("Z71").Value = Y
MsgBox "Cell Z71 = " & Worksheets(1).Range("Z71").Value = Y

End Sub

When I did the test the first time, I accidently wrote the last line before End Sub as

MsgBox "Cell Z71 = " & Worksheets(1).Range("Z71").Value

The dialog box said

Dialog boxes say Y=0, Cell Z71 = 0

Now it says Y=0, False.

Last edited:

#### Ed Song

##### Board Regular

I guess I was right the first time around. (just .value, no .value = y) So the test gives me a 0,0 result.

Last edited:

#### Ed Song

##### Board Regular
I've done further testing and I'm completely at a loss. I really need help.

As I mentioned. I performed the simple test mentioned above with module 2 and module 3. The values were zero. However,
I replicated the test with module 1 and 3 (deleting all the contents of those models to make the test real simple), and I got the right results, Y = 85,
Cell value 85. I then created module 4 and conducted the test with module 2. Unfortunately, the results were once again zero and zero.

My conclusion is that my workbook was infected by a virus sometime after module 2 was created. Anyone have any other ideas? How do I test to see if this is a virus and how do I disinfect the workbook of the virus?

#### Peter_SSs

##### MrExcel MVP, Moderator
When I did the test the first time, I accidently wrote the last line before End Sub as

MsgBox "Cell Z71 = " & Worksheets(1).Range("Z71").Value
That is what it should have said if you were following my suggested test.

Let's just step back a little and do this test.

1. Create a brand new workbook & save it.

2. Insert 3 Modules into the new workbook.

3. Copy this code into Module 2
Code:
``````Option Explicit

Public X As Double

Sub Test1()
X = 17
MsgBox "X = " & X
End Sub``````

4. Copy this code into Module 3
Code:
``````Option Explicit

Public Y As Double

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``````

5. Run Test1 then run Test 2.
In doing that you should get 4 message boxes. What do they report?

#### Ed Song

##### Board Regular
Hello Peter,

I did the test as you suggested. Last night, in a different workbook I got the right answer, 85. But if I deleted the contents of my original workbook and did the test I got zeros. Today, I redid the test in a new workbook, but I got zeros.

Replies
2
Views
57
Replies
0
Views
37
Replies
3
Views
73
Replies
1
Views
57
Replies
0
Views
34