Changing Column values with VB code

Originally Posted by MarvinP

DonkeyOte scolded me one day as DNRs are Volatile and he feels they slow things down a lot. I think they have a use but are real hard to diagnose and track when other things go bad. Read one of his posts and follow his Volatile link in his signature line.

Not 100% accurate - DNR's constructed with Volatile functions (OFFSET, INDIRECT etc) are Volatile.

To be clear, I don't think I've ever argued that DNR's should not be used - just that they should not be used blindly - often the overhead isn't necessary - as always it comes down to context.

A volatile DNR is not really a concern if being used to source a Pivot or Data Validation.
If the DNR is being used en masse as a precedent range within formulae which are themselves inefficient (Arrays, SUMPRODUCT etc) it's generally a good idea to avoid a Volatile DNR construct [IMO]

Most functions are sufficiently efficient that they work only with the Used Range intersect of the Precedent range and as such the overhead in determining a "filled" range isn't necessary and will actually slow* the calculation
(*whether noticeable or not is an entirely different matter)

Notable others would disagree with me re: the above - it's very much a case of each to their own.

Apologies for sidetrack - just wanted to clarify

Hi DO,

In my pee brain DNRs and Offsets are in the same box What is an example of a DNR without using Offset or Index? I've searched looking for a DNR that doesn't use Offset and failed.

One Example is all I need.

Do you consider VBA named ranges that change with code, DNRs?

Originally Posted by MarvinP

What is an example of a DNR without using Offset or Index?

INDEX is not Volatile (since XL97) - it is considered "semi-volatile" in so far as it is Volatile upon Open (ie will calculate) but not thereafter.

So

Name: _Ex1
=OFFSET($A$1,0,0,COUNT($A:$A),1)

is 100% Volatile whereas

Name: _Ex2
=$A$1:INDEX($A:$A,COUNT($A:$A))

is not
(it is semi volatile - and that's misleading term IMO - "occasionally volatile" would be more appropriate)

To reiterate: how big an issue a Volatile construct is all depends on the context so it's not as simple as saying INDEX good OFFSET bad.

It should also be noted that no DNR will work with INDIRECT irrespective of Volatility concerns:

=ROWS(INDIRECT("_Ex1"))

=ROWS(INDIRECT("_Ex2"))

neither of the above would work
(in this scenario we are obliged to use an IF, CHOOSE or Evaluate method)

Originally Posted by MarvinP

Do you consider VBA named ranges that change with code, DNRs?

If you're saying: VBA alters RefersTo fixed range then No.

A Dynamic Named Range (IMO) is defined by the fact that it is capable of resizing itself by virtue of the RefersTo construct alone.
(per the above examples - as #'s are added to A so the height of the Named Range alters)

It's something of a contentious issue ... the above is my own personal opinion - nothing more - just didn't want to be misconstrued / misquoted.