XL2007 : variable value changes unexpectedly

[ker_01]

I am pulling data from a grid and manipulating the numbers to interpolate map
points between the existing coordinate values.

Here is the entire sub (not including the two grids of source values from a
worksheet). The problem that I'm seeing is with the value for AvgYRights.
This value calculates correctly (140.5) up until the line I've marked below.
When that line executes, the value of AvgYDiff correctly calculates to -24.5.
However, when that line executes, it also resets the value of AvgYRights to
-24.5. As far as I can tell, that is the only variable that is unexpectedly
changed to a different value.

Are these reserved Excel names, or is there something else I'm not seeing
that would cause AvgYRights to suddenly recalculate?

Many thanks,
Keith

Sub TestShape()

'in map coordinates
SourceLat = 47.931
SourceLong = 122.04 '(minus)

'Source sheet of map coordinate values
XList = Sheet3.Range("B1:L1")
XArray = Sheet3.Range("B2:L7") '1 to 6 rows, 1 to 11 columns
YList = Sheet3.Range("A2:A7")
YArray = Sheet3.Range("B12:L17")

'Adjust the used values; map coordinate grid for USA is from 66,25 to 126,50
AdjLong = SourceLong - 66
AdjLat = SourceLat - 25

'Longitude is in increments of 6 on my map
LowerX = AdjLong \ 6
UpperX = LowerX + 1
RemainderX = AdjLong Mod 6
'Latitude is in increments of 5 on my map
LowerY = AdjLat \ 5
UpperY = LowerY + 1
RemainderY = AdjLat Mod 5

'Verified: gets the correct values from my worksheet grid of coordinates
XVal1 = XArray(11 - LowerX, 6 - LowerY) 'lower right
XVal2 = XArray(11 - UpperX, 6 - LowerY) 'upper right
XVal3 = XArray(11 - LowerX, 6 - UpperY) 'lower left
XVal4 = XArray(11 - UpperX, 6 - UpperY) ' upper left

YVal1 = YArray(11 - LowerX, 6 - LowerY) 'lower right
YVal2 = YArray(11 - UpperX, 6 - LowerY) 'upper right
YVal3 = YArray(11 - LowerX, 6 - UpperY) 'lower left
YVal4 = YArray(11 - UpperX, 6 - UpperY) ' upper left

'Start the actual calculations
AvgXUppers = (XVal2 + XVal4) * 0.5
AvgXLowers = (XVal1 + XVal3) * 0.5
AvgXDiff = AvgXUppers - AvgXLowers
AvgYRights = (YVal1 + YVal2) * 0.5 'correctly evaluates
AvgYLefts = (YVal3 + YVal4) * 0.5 'AvgYRights is still correct
'when this next line executes, AvgYRights is changed too!
AvgYDiff = AvgYLefts - AvgYRights

AvgXLefts = (XVal3 + XVal4) * 0.5
AvgXRights = (XVal1 + XVal2) * 0.5
AvgXDiff2 = AvgXUppers - AvgXLowers
AvgYUppers = (YVal2 + YVal4) * 0.5
AvgYLowers = (YVal1 + YVal3) * 0.5
AvgYDiff2 = AvgYLefts - AvgYRights

'more calculations will be added here

End Sub

 

[Patrick Molloy]

add
OPTION EXPLICIT
to the top of the module, then DIM all your variables.

when I step through, I cannot replicate your issue. However, all your
variables are variant by defualt if you don't declare them, plus you run the
risk - especially with this many variables - of typing errors

Dim SourceLat As Double
Dim SourceLong As Double
Dim XList As Variant
Dim XArray As Variant
Dim YList As Variant
Dim YArray As Variant
Dim AdjLong As Double
Dim AdjLat As Double
Dim LowerX As Double
Dim UpperX As Double
Dim RemainderX As Double
Dim LowerY As Double
Dim UpperY As Double
Dim RemainderY As Double
Dim XVal1 As Double
Dim XVal2 As Double
Dim XVal3 As Double
Dim XVal4 As Double
Dim YVal1 As Double
Dim YVal2 As Double
Dim YVal3 As Double
Dim YVal4 As Double
Dim AvgXUppers As Double
Dim AvgXLowers As Double
Dim AvgXDiff As Double
Dim AvgYRights As Double
Dim AvgYLefts As Double
Dim AvgYDiff As Double
Dim AvgXLefts As Double
Dim AvgXRights As Double
Dim AvgXDiff2 As Double
Dim AvgYUppers As Double
Dim AvgYLowers As Double
Dim AvgYDiff2 As Double

 

[ker_01]

Patrick-

Thank you for your response. Admittedly, as an untrained (and lazy)
programmer, I don't use Option Explicit as often as I should. After trying to
figure out the mysterious value change for over an hour yesterday, I can't
get it to replicate this morning. I'll still add the Option Explicit and
variable definitions anyway, to avoid any typo problems as this module will
continue to grow.

Many thanks,
Keith