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?

 

[Peter_SSs]

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.

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.

 

[Ed Song]

Peter, thanks for your suggestion.

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]

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?

 

[Peter_SSs]

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]

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.

 

[Ed Song]

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

 

[Ed Song]

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]

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

Option Explicit

Public X As Double

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

4. Copy this code into Module 3

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]

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.