Variables inside .Offset Method

Question

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?

in VBA True equals to -1 and False to 0. Try MsgBox CInt(True) — Dmitry Pavliv
– Dmitry Pavliv, Commented Apr 22, 2014 at 15:53
@simoco Consider posting this as an answer as it may be of interest to others. — Gary's Student
– Gary's Student, Commented Apr 22, 2014 at 15:56

Community · Accepted Answer · 2017-05-23 12:05:09Z

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

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Apr 22, 2014 at 15:58

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Anthony Bird Over a year ago

do you have a link that explains why true evaluates to 1 in an excel formula but -1 in vba?

Dmitry Pavliv Over a year ago

I already gave you the link in my answer: 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 Over a year ago

also, as mentioned here: 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

Collectives™ on Stack Overflow

Variables inside .Offset Method

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related