Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2007 : variable value changes unexpectedly
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2007 : variable value changes unexpectedly
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2007 : variable value changes unexpectedly
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 "Patrick Molloy" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Format changes unexpectedly | Excel Discussion (Misc queries) | |||
Excel quits unexpectedly | Excel Worksheet Functions | |||
Sub unexpectedly jumps to another sub | Excel Programming | |||
Sub Exits Unexpectedly | Excel Programming | |||
data changes unexpectedly | New Users to Excel |