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

Ed Song

Board Regular
Joined
Sep 1, 2014
Messages
90
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?
 

Some videos you may like

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
Joined
May 28, 2005
Messages
46,227
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 1, 2014
Messages
90
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

Board Regular
Joined
Sep 1, 2014
Messages
90
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
Joined
May 28, 2005
Messages
46,227
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Sep 1, 2014
Messages
90
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
Joined
Sep 1, 2014
Messages
90

ADVERTISEMENT

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
Joined
Sep 1, 2014
Messages
90
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
Joined
May 28, 2005
Messages
46,227
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 1, 2014
Messages
90
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,880
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top