Addressing ranges

[Ron Rosenfeld]

Dim c as Range

Assuming c is a single cell range, and I want to write a value into
the adjacent column, is there any advantage/disadvantage to using

c.offset(0,1)
vs
c(1,2)

??

c(1,2) is more compact.

 

[Jef Gorbach]

Dim c as Range

Assuming c is a single cell range, and I want to write a value into
the adjacent column, is there any advantage/disadvantage to using

c.offset(0,1)
  vs
c(1,2)

??

c(1,2) is more compact.

Havent seen this abbreviated style before so if it works, the problems
I would foresee would be
1) more difficult for others to decipher your code
2) possibly incompatible with alternative software/future versions.

 

[Ron Rosenfeld]

Havent seen this abbreviated style before so if it works, the problems
I would foresee would be
1) more difficult for others to decipher your code

Well, if they're not familiar with the notation, then I would agree.
But I would think most are. There are arguments about whether one
should specify default properties (actually, which of the default
properties), or not, but I'm curious about performance issues.

If c is set to some Range object representing a single cell, then:

c(1,2)

is equivalent to:

excel.application.ActiveSheet.c.item(1,2)

and should return the same result as:

excel.application.ActiveSheet.c.offset(0,1)

2) possibly incompatible with alternative software/future versions.

Why do you think it might be incompatible with future versions?

Maybe, since you are not familiar with the notation, I should rephrase
the question:

Is there an advantage/disadvantage to using the Item property vs using
the Offset property in specifying a single cell that is relative to
the base cell.

Of course, if the Range expression is a multicell range, the Item
property (using this notation) will only return a single cell (which
is what I want), whereas the Offset property will return a range of
the same size and shape.

 

[Dave Peterson]

I like the .offset() style better, but I think it's a personal (or
department/company) choice.

I have no idea if one is quicker than the other. And I don't care -- for the
same reasons that Jef wrote. (But you should be able to read and understand
both <vbg>.)

Chip Pearson has some notes written by Alan Beban:
http://www.cpearson.com/excel/cells.htm

It may be an interesting read for you.

 

[Dave Peterson]

(I don't think Jef's compatibility reason is as good as his first. I'm not
worried about that.)

 

[Ron Rosenfeld]

I like the .offset() style better, but I think it's a personal (or
department/company) choice.

I used to use the Offset property frequently, but I've been starting
to use the Item property. I guess it is a personal preference,
although the Offset property would be my choice, I think, if I needed
to refer to an entire range, rather than just a single cell.

I have no idea if one is quicker than the other. And I don't care -- for the
same reasons that Jef wrote. (But you should be able to read and understand
both <vbg>.)

Chip Pearson has some notes written by Alan Beban:
http://www.cpearson.com/excel/cells.htm

It may be an interesting read for you.

It was. Thanks.