Excel200: Strange behaviour for Name when referred from VBA

A

Arvi Laanemets

Hi

In my VBA I rever to various names (the ones returning a value) in my
workbook, like:
Var1=[Name1]
It works nicely, except when the name definition contains function ROW()
(maybe there are other behaving similarily - I myself suspect COLUMN()).

The problematic name is something like:
HeaderCnt=ROW(SheetName!$A$2)
(It's needed to adjust automatically formulas and procedures, when some rows
are added to/removed from table header)
VarHeaderCnt=[HeaderCnt]
returns an 1-dimension object instead of value. To get the value, I have to
use the code:
VarHeaderCnt=[HeaderCnt]
VarHeaderCnt=VarHeaderCnt(1)

(NB! [HeaderCnt](1) doesn't work - it returns an error 'Wrong number of
arguments or invalid property assignment'.)

Can someone explain me this? Thanks in advance!
 
C

Charles Williams

Hi Arvi,

I think ROW() always returns an array because it "returns an array if
entered as a vertical array formula".
And EVALUATE (including the [] syntax) always evaluates formulae as array
formulae whenever it can.

BTW Name Manager evaluates your NAME as 1 col 1 row containing 2

regards
Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com
 
A

Arvi Laanemets

Hi


Charles Williams said:
Hi Arvi,

I think ROW() always returns an array because it "returns an array if
entered as a vertical array formula".
And EVALUATE (including the [] syntax) always evaluates formulae as array
formulae whenever it can.

BTW Name Manager evaluates your NAME as 1 col 1 row containing 2
Click to expand...

Thanks for reply!

But is there a better way to get the value, instead of my 2-step approach?

Btw. this ROW's behaves strangely anyway. At start I used it in a complex
formula (=Expr1+Expr2), which also returned the range. I found out, that the
problem was in ROW(), when divided the complex name into 2 different names -
after that the one returned a value, the second (HeaderCnt) a range. It
looks a bit strange to me, that a value (number) added to a range is
returning a range!
 
C

Charles Williams

Evaluate uses excel's formula evaluator to return one of
- an error value
- a scalar value
- a row of values
- a column of values
- an array of values
- a range

What it returns depends on what the input was (formula, row/col/rectangular
array of values, range, name etc)
Usually I just assign the result to a variant and work from there: the
default property of Range is value anyway so that simplifies things.

Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com


Arvi Laanemets said:
Hi


Charles Williams said:
Hi Arvi,

I think ROW() always returns an array because it "returns an array if
entered as a vertical array formula".
And EVALUATE (including the [] syntax) always evaluates formulae as array
formulae whenever it can.

BTW Name Manager evaluates your NAME as 1 col 1 row containing 2
Click to expand...

Thanks for reply!

But is there a better way to get the value, instead of my 2-step approach?

Btw. this ROW's behaves strangely anyway. At start I used it in a complex
formula (=Expr1+Expr2), which also returned the range. I found out, that the
problem was in ROW(), when divided the complex name into 2 different names -
after that the one returned a value, the second (HeaderCnt) a range. It
looks a bit strange to me, that a value (number) added to a range is
returning a range!
Click to expand...
 
Top