Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexplained change in variable
Hi Group,
I have something very strange happening, that I can not explain. I have 2 variables: TotalValue = ActiveCell.Offset(16, 0).Value (Gets set to 102,000, which is a correct value.) TargetTotal = ActiveCell.Offset(64, 0).Value (Gets set to 35000, which is a correct value, BUT TotalValue becomes 35,000) I am not sure what I am missing in this process. Can anyone tell me why this would happened. Thanks, -- David |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexplained change in variable
I'd think that people here will ask for a bit more of your code to
tell where the problem is. From the top of my head the first reason that would come to my mind is that somewhere in your code you're passing your variable (TotalValue) ByRef and not ByVal and that makes the two variables being pretty much one variable (when one changes, so does the other. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexplained change in variable
It might help if you shows us more of the code surrounding those two
statements... Eric "David" wrote: Hi Group, I have something very strange happening, that I can not explain. I have 2 variables: TotalValue = ActiveCell.Offset(16, 0).Value (Gets set to 102,000, which is a correct value.) TargetTotal = ActiveCell.Offset(64, 0).Value (Gets set to 35000, which is a correct value, BUT TotalValue becomes 35,000) I am not sure what I am missing in this process. Can anyone tell me why this would happened. Thanks, -- David |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexplained change in variable
Hi,
There is a lot of code, what difference do you think it would make? These 2 lines follow each other, one right after the other. The code is about 11 pages when copied into Word. These lines appear about 5 pages into the Module. They are one statement right after the other: TotalValue = ActiveCell.Offset(16, 0).Value TargetTotal = ActiveCell.Offset(64, 0).Value The first line ends with a correct value. After the second line runs, it has a correct value in TargetTotal, but TotalValue has changed to the value of TargetTotal. In other words they are now both the same value. As I step through the code, which I have done several times, the opposite also happens: TotalValue = ActiveCell.Offset(16, 0).Value (Captures correct amount, but changes the value of TargetTotal, before the line is even run and puts the value of TotalValue in it) TargetTotal = ActiveCell.Offset(64, 0).Value (Changes the value of BOTH variable to the value of TargetTotal) It is not complicated code, but I sure can not explain it. Thanks, -- David "EricG" wrote: It might help if you shows us more of the code surrounding those two statements... Eric "David" wrote: Hi Group, I have something very strange happening, that I can not explain. I have 2 variables: TotalValue = ActiveCell.Offset(16, 0).Value (Gets set to 102,000, which is a correct value.) TargetTotal = ActiveCell.Offset(64, 0).Value (Gets set to 35000, which is a correct value, BUT TotalValue becomes 35,000) I am not sure what I am missing in this process. Can anyone tell me why this would happened. Thanks, -- David |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexplained change in variable
Hi Again,
There are no passes taking place and the lines run one right after the other. Which part of the 11 pages do you think would help? Thanks, -- David "AB" wrote: I'd think that people here will ask for a bit more of your code to tell where the problem is. From the top of my head the first reason that would come to my mind is that somewhere in your code you're passing your variable (TotalValue) ByRef and not ByVal and that makes the two variables being pretty much one variable (when one changes, so does the other. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexplained change in variable
How are the
TotalValue TargetTotal dimensioned? (i.e. declared with Dim statment) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexplained change in variable
There is a lot of code, what difference do you think it would make?
Since these two lines of code function properly by themselves (outside of your code), posting just these two lines doesn't provide enough information for anyone to provide anything more than guesses. You don't have to post all of your code, just sections that are more likely to be relevant (like the sub that contains these two lines, and if you have any code in worksheet_change event, etc.). You also don't mention what version of Excel you are using, which (at least for some problems) might help narrow down possible causes. FWIW, I ran this sample code on both 2003 and 2007, and it worked fine in both, returning the expected offset value. Sub test_offset_problem() ActiveCell = Sheet1.Range("A1") TotalValue = ActiveCell.Offset(1, 1).Value Debug.Print TotalValue TargetTotal = ActiveCell.Offset(2, 2).Value Debug.Print TargetTotal End Sub "David" wrote: Hi, There is a lot of code, what difference do you think it would make? These 2 lines follow each other, one right after the other. The code is about 11 pages when copied into Word. These lines appear about 5 pages into the Module. They are one statement right after the other: TotalValue = ActiveCell.Offset(16, 0).Value TargetTotal = ActiveCell.Offset(64, 0).Value The first line ends with a correct value. After the second line runs, it has a correct value in TargetTotal, but TotalValue has changed to the value of TargetTotal. In other words they are now both the same value. As I step through the code, which I have done several times, the opposite also happens: TotalValue = ActiveCell.Offset(16, 0).Value (Captures correct amount, but changes the value of TargetTotal, before the line is even run and puts the value of TotalValue in it) TargetTotal = ActiveCell.Offset(64, 0).Value (Changes the value of BOTH variable to the value of TargetTotal) It is not complicated code, but I sure can not explain it. Thanks, -- David "EricG" wrote: It might help if you shows us more of the code surrounding those two statements... Eric "David" wrote: Hi Group, I have something very strange happening, that I can not explain. I have 2 variables: TotalValue = ActiveCell.Offset(16, 0).Value (Gets set to 102,000, which is a correct value.) TargetTotal = ActiveCell.Offset(64, 0).Value (Gets set to 35000, which is a correct value, BUT TotalValue becomes 35,000) I am not sure what I am missing in this process. Can anyone tell me why this would happened. Thanks, -- David |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexplained change in variable
"AB" wrote:
From the top of my head the first reason that would come to my mind is that somewhere in your code you're passing your variable (TotalValue) ByRef and not ByVal and that makes the two variables being pretty much one variable (when one changes, so does the other. That would make sense... |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexplained change in variable
People here are just trying to help you here. It's obviously abnormal
behaviour of your code and therefore there must be something in the code. Once again: How are the TotalValue TargetTotal dimensioned? (i.e. declared with Dim statment) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexplained change in variable
Hi Again,
"passing your variable (TotalValue) ByRef and not ByVal" this is not happening anywhere. The code is very linear with out any calls or passes. Neither variable is Dimentioned, there are no Dim statements. Thanks, -- David "ker_01" wrote: "AB" wrote: From the top of my head the first reason that would come to my mind is that somewhere in your code you're passing your variable (TotalValue) ByRef and not ByVal and that makes the two variables being pretty much one variable (when one changes, so does the other. That would make sense... |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexplained change in variable
Hello Again,
I did try to Dim these two values and it made the difference. They are the only 2 that are Dim-ed. I still am not sure why I had to do this, but it did worked. Any idea why? Thanks, -- David "ker_01" wrote: "AB" wrote: From the top of my head the first reason that would come to my mind is that somewhere in your code you're passing your variable (TotalValue) ByRef and not ByVal and that makes the two variables being pretty much one variable (when one changes, so does the other. That would make sense... |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexplained change in variable
"passing your variable (TotalValue) ByRef and not
ByVal" this is not happening anywhere. Just because you didn't explicitly force it doesn't mean that it isn't happening accidently. Again, since you seem unwilling to post any relevant code, you probably won't get a concrete answer, at least not without more back-and-forth. Is all 11 pages of your code in one sub, or do you have multiple subs that call each other? Are you passing any parameters (and specifically, either of these 2 variables) among any of your subs, including other than the sub where this code is located? If so, consider posting your Sub statement(s) and associated parameter declaration(s) "David" wrote: Hello Again, I did try to Dim these two values and it made the difference. They are the only 2 that are Dim-ed. I still am not sure why I had to do this, but it did worked. Any idea why? Thanks, -- David "ker_01" wrote: "AB" wrote: From the top of my head the first reason that would come to my mind is that somewhere in your code you're passing your variable (TotalValue) ByRef and not ByVal and that makes the two variables being pretty much one variable (when one changes, so does the other. That would make sense... |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexplained change in variable
Hi,
AB's suggestion for the Dim statement fixed the problem. I still do not know why with as many variables as there are and none, other than these 2 having been DIM'ed that it was necessary? If anyone has some insight, I would like to know. Thanks, -- David "ker_01" wrote: "passing your variable (TotalValue) ByRef and not ByVal" this is not happening anywhere. Just because you didn't explicitly force it doesn't mean that it isn't happening accidently. Again, since you seem unwilling to post any relevant code, you probably won't get a concrete answer, at least not without more back-and-forth. Is all 11 pages of your code in one sub, or do you have multiple subs that call each other? Are you passing any parameters (and specifically, either of these 2 variables) among any of your subs, including other than the sub where this code is located? If so, consider posting your Sub statement(s) and associated parameter declaration(s) "David" wrote: Hello Again, I did try to Dim these two values and it made the difference. They are the only 2 that are Dim-ed. I still am not sure why I had to do this, but it did worked. Any idea why? Thanks, -- David "ker_01" wrote: "AB" wrote: From the top of my head the first reason that would come to my mind is that somewhere in your code you're passing your variable (TotalValue) ByRef and not ByVal and that makes the two variables being pretty much one variable (when one changes, so does the other. That would make sense... |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexplained change in variable
"David" je napisao u poruci interesnoj
... Hi, AB's suggestion for the Dim statement fixed the problem. I still do not know why with as many variables as there are and none, other than these 2 having been DIM'ed that it was necessary? If anyone has some insight, I would like to know. trying to code anything longer than two-liner without 'option explicit' which forces every variable to be 'dim-as-type' is madness otherwise, it is unlikely that you have found some excel vba bug, it is most possible to be some miss-type reducing and isolating code as long as the effect happens is one of the methods if you don't want to post the whole code, maybe to post just 'find' from cmd, containing lines with those variables? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unexplained size increase | Excel Programming | |||
#N/A Error unexplained | Excel Worksheet Functions | |||
Unexplained color shift | Excel Discussion (Misc queries) | |||
Unexplained/Unknow"Object variable or With block variable not set" | Excel Programming | |||
File sizes unexplained bloating | Excel Discussion (Misc queries) |