Vba Macro and Excel Sheet results are not the same

[arb1985]

Hello!

I am doing an excel sheet with a very big table for some calculations (tens of thousands of rows and many columns too). I must deliver the excel file with quite nice appearance, so I decided to make a VBA Macro.

Each row takes into account the previous one, and I have found that an error is introduced when using VBA (if it is not an error, the result is not the same). The interesting result comes in the very end, so the difference is quite big (I work with quite small numbers, even 1e-7)

The first idea I had was that it is a matter of precision, so I changed all the variables to Double, which is the variable type that Excel uses (I am using "not arrays" and arrays). It didn't work.

Then I configured every cell as a number with 16 decimal points, and configured excel to apply the "set precision as displayed", and it was also useless...

I have checked the VBA Macro several times, and it seems to be OK (the differences would be much bigger, I think, if it was wrong).

I must finish the file tomorrow... Any suggestions??

THANK YOU VERY MUCH!!

PS: I use Excel 2010

 

[Jonmo1]

It's very hard to say with seeing examples.

Can you post a formula that returns the 'correct' value
And the code that produces the 'incorrect' value based on the same values and math
?

 

[arb1985]

The equation that I use in Excel is (in the cell S415):
=S414+($R$12*(R414-S414)+$T$12*(T414-S414))*($C$1/($C$4^2*($R$13+$T$13)/2*($R$14+$T$14)/2))

Of course, the $ symbols go to cells with just some fixed parameters.


In VBA:

'FILL IN THE REST OF THE ARRAY
    For ArrayTimeStep = 1 To NumberOfTimeSteps          'Start in the second element, the first was t=0
       
        'All the nodes that have no contact with fluid
        For Node = 2 To NumberOfNodes - 3
            Ti = ValueArray(ArrayTimeStep - 1, Node)              'Value same node previous timestep
            TiMinusOne = ValueArray(ArrayTimeStep - 1, Node - 1)  'Value of the node in the left, previous timestep
            TiPlusOne = ValueArray(ArrayTimeStep - 1, Node + 1)   'Value of the node in the right, previous timestep
            ValueArray(ArrayTimeStep, Node) = Ti + (ThetaArray(Node - 2) * (TiMinusOne - Ti) + ThetaArray(Node - 1) * (TiPlusOne - Ti)) * (TimeStep / (DiscreteElementThickness ^ 2 * (MassArray(Node - 2) + MassArray(Node - 1)) / 2 * (CoefficientArray(Node - 2) + CoefficientArray(Node - 1)) / 2))
        Next
       
        'The node in contact with the left wall -COLD-
        TiMinusOne = ValueArray(ArrayTimeStep, 0)
        Ti = ValueArray(ArrayTimeStep, 1)
        TiPlusOne = ValueArray(ArrayTimeStep, 2)
        ValueArray(ArrayTimeStep, 1) = (ThetaCold / DiscreteElementThickness * TiPlusOne + NuCold * TiMinusOne) / (ThetaCold / DiscreteElementThickness + NuCold)
       
        'The node in contact with the right wall -HOT-
        TiMinusOne = ValueArray(ArrayTimeStep, NumberOfNodes - 3)
        Ti = ValueArray(ArrayTimeStep, NumberOfNodes - 2)
        TiPlusOne = ValueArray(ArrayTimeStep, NumberOfNodes - 1)
        ValueArray(ArrayTimeStep, NumberOfNodes - 2) = (ThetaHot / DiscreteElementThickness * TiMinusOne + NuHot * TiPlusOne) / (ThetaHot / DiscreteElementThickness + NuHot)
        
    Next

That is the most important equation and Array. One row always depending on the previous one.

 

[arb1985]

An example of the results is here:

https://drive.google.com/file/d/0B5qHwxgz9AsXakN4YVlpeUo3Rnc/edit?usp=sharing

THANK YOU VERY MUCH

 

[Jonmo1]

I take it that this is the part not returning the same result as the formula you posted

ValueArray(ArrayTimeStep, Node) = Ti + (ThetaArray(Node - 2) * (TiMinusOne - Ti) + ThetaArray(Node - 1) * (TiPlusOne - Ti)) * (TimeStep / (DiscreteElementThickness ^ 2 * (MassArray(Node - 2) + MassArray(Node - 1)) / 2 * (CoefficientArray(Node - 2) + CoefficientArray(Node - 1)) / 2))

I put that exact formula in a cell (B14), and put some random numbers in each relevant cell.

I then ran this code

Sub test()
Dim a As Double, b As Double, c As Double, d As Double, e As Double, f As Double
Dim g As Double, h As Double, i As Double, j As Double, k As Double
Dim x As Double, y As Double, z As Double
a = Range("C1").Value
b = Range("C4").Value
c = Range("R12").Value
d = Range("R13").Value
e = Range("R14").Value
f = Range("R414").Value
g = Range("S414").Value
h = Range("T12").Value
i = Range("T13").Value
j = Range("T14").Value
k = Range("T414").Value

x = g + (c * (f - g) + h * (k - g)) * (a / (b ^ 2 * (d + i) / 2 * (e + j) / 2))

MsgBox x = Range("B14").Value
End Sub

The message box says TRUE.

So VBA gives the same result as the formula.


I then took the coded part, and replaced all the Array(...) bits with their corresponding Range address from the formula.
And they came out the same.
So the syntax of the coded part is the same as your formula.
All the ()'s and + - / * are in the correct places.


So I think you need to do some evaluating of each individual part of the coded calculation.
to make sure they are getting the correct values.

 

[arb1985]

Thank you very much Jonmo1!

It was really nice from you to check it, and so fast! I will try to find some error as you did.

And, yes, that is the main array. Once I have it I print out the values to a table and there is where I find the difference...

 

[Jonmo1]

You're welcome.