1

I've noticed some weird behavior using variables inside the offset method.

Here's my code

Dim r As Integer 
r=1
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(0, 0 + (r = 1)).Select

the first select seems to work as expected, it selects the cell one column to the right. However, the second actually selects the cell one column to the LEFT! it would seem that would mean that 0 + (r =1) was evaluating to -1, which seems strange.

Is there something I am missing?

Community
  • 1
  • 1
Anthony Bird
  • 261
  • 3
  • 10

1 Answers1

2

Here is quote from MSDN:

When Visual Basic converts numeric data type values to Boolean, 0 becomes False and all other values become True. When Visual Basic converts Boolean values to numeric types, False becomes 0 and True becomes -1.

You can easily check it using following test:

MsgBox CInt(True)  ' returns -1
MsgBox CInt(False) ' returns 0

Assuming that, you can change your code to:

ActiveCell.Offset(0, 0 - (r = 1)).Select

Also I suggest you to read this post: How to avoid using Select/Active statements

Community
  • 1
  • 1
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
  • do you have a link that explains why `true` evaluates to 1 in an excel formula but -1 in vba? – Anthony Bird Apr 22 '14 at 16:32
  • 1
    I already gave you the link in my answer: http://msdn.microsoft.com/en-us/library/wts33hb3%28v=vs.90%29.aspx VBA is not Excel language. It is independend programming language that was used in office programs (and many others) and has its own features like `True=-1`. There is no any explanation why Excel uses `1` and VBA `-1`. It's just by design. Excel developers team desided `True=1` and VBA developers team desided `True=-1` – Dmitry Pavliv Apr 22 '14 at 16:36
  • 1
    also, as mentioned [here](http://msdn.microsoft.com/en-us/library/ae382yt8(v=vs.90).aspx): _You should never write code that relies on equivalent numeric values for True and False. Whenever possible, you should restrict usage of Boolean variables to the logical values for which they are designed. If you must mix Boolean and numeric values, make sure that you understand the conversion method that you select_. E.g. if you'd work with excel through VBA, then `CInt(True)` returns `-1`. However in VB.NET coversion `ToInt32(True)` returns `+1`. You can use `Offset(0, 0 + IIF(r = 1,1,0)).Select` – Dmitry Pavliv Apr 22 '14 at 16:50