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

Ed Song · Oct 11, 2014

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 · Oct 11, 2014

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.

Ed Song · Oct 11, 2014

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 · Oct 11, 2014

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 · Oct 11, 2014

Ed Song said:
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 · Oct 11, 2014

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 · Oct 11, 2014

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 · Oct 12, 2014

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 · Oct 12, 2014

Ed Song said:
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 · Oct 12, 2014

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.

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

Ed Song

Board Regular

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

Ed Song

Board Regular

Ed Song

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Ed Song

Board Regular

Ed Song

Board Regular

Ed Song

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Ed Song

Board Regular

Similar threads

Forum statistics

Share this page

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

Board Regular

Excel Facts

MrExcel MVP, Moderator

Board Regular

Board Regular

MrExcel MVP, Moderator

Board Regular

Board Regular

Board Regular

MrExcel MVP, Moderator

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock