Unexplained change in variable

  • Thread starter Thread starter David
  • Start date Start date
D

David

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,
 
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.
 
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,
 
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,
 
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
 
AB said:
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.
Click to expand...

That would make sense...
 
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)
 
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,
 
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,
 
"passing your variable (TotalValue) ByRef and not
ByVal" this is not happening anywhere.
Click to expand...

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)
 
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 said:
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.
Click to expand...

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?
 
Back
Top