Intersect Operator vs Explicit Cell References and the Dependency

[Guest]

Currently I am trying to improve performance on one of my excel apps. In
this app I use the intersect operator and ranges, rather than using explicit
cell references.

Intersect: (DataRange Column1) * (DataRange Column2)
Explicit: (A1) * (B1)

If I look at dependencies of one of the cells in a range that I use in the
intesects, it shows a dependency of all the formulas that reference that
range. Is this a real dependecy? Am I incurring quite a bit of overhead
when using intersects? Where do babies come from?

Any thoughts would be appreciated!

Regards

 

[Guest]

Currently I am trying to improve performance on one of my excel apps. In
this app I use the intersect operator and ranges, rather than using explicit
cell references.

Intersect: (DataRange Column1) * (DataRange Column2)
Explicit: (A1) * (B1)

If I look at dependencies of one of the cells in a range that I use in the
intesects, it shows a dependency of all the formulas that reference that
range. Is this a real dependecy? Am I incurring quite a bit of overhead
when using intersects? Where do babies come from?

Any thoughts would be appreciated!

Regards

Hello Adam,

I would review the Help files on the Intersect method. From the sounds of
it, you don't even need it. For example, let's look at the following ...

Range("A1:Z1")
...
Range("A1:A26")

The ranges are the same size, but in opposite directions. Each contain 26
cells, or unique Range Objects.

Intersect(Range("A1:Z1"), Range("A1:A26"))

This is a Range of 1 cell. (Can you guess which one?) So I'm a little
perplexed as to why you'd even want to use the Intersect method. Unless I am
not understanding something (highly possible/probable).

Regards,
Zack Barresse

 

[Guest]

Yeah, I think I perhaps have gotten you confused. This is the operator
intersect and not the VBA function.

Cells that use the intersect operator are able to figure out the row that
they reside in, with respect to a range that it lies in, to retrieve a
specific value in a target column.

Now I need to find out if this triangulation type of operator is causing me
undue performance issues.

Regards,
Adam

 

[Guest]

Well, it is real dependency. But without you posting what you actually have
it will be a little difficult to see if it's not efficient enough (as it
could be) or not. I think it really depends on what you are doing. And yes,
you could be incurring quite a lot of overhead; depends on it's uses, again.

Please post.