ranges in formulas

  • Thread starter Thread starter Nicholas Flowers
  • Start date Start date
N

Nicholas Flowers

Hi, have decided it is finally time for me to learn Excel. Presently
struggling with the range/formula interaction. It feels like I have a
lot of questions, but think I can boil many of them down to this
scenario.

I. Suppose I have a named range "foo" in A1:A5. Now the formula =foo is
somehow context sensitive -- in B6 it is #VALUE!, but in B2 it
"guesses" that what I really wanted was A2. How does this work? I
mean, suppose you wanted to write a VBA function that performed the same
magic. It would take a Range argument, but how could it snoop the
context of evaluation to produce the single value you are looking for?

II. Now I have two named ranges, foo & foo2. I can write the formula =
foo*foo2, and somehow cells (similarly to case I) figure out which value
to pluck from the ranges and multiply. But it behaves differently if I
write the vba function

function myproduct(a as double, b as double) as double
myproduct = a*b
end function

I would like to be able to enter the formula =myproduct(foo, foo2),
similarly to operator*, but this is #VALUE!. So Excel isn't able to
break this into a loop for us. Presumably because myproduct was told to
expect doubles, but was actually handed Ranges. How can we get a UDF to
act the same as a built-in in this regard (or can't we?). Of course, I
could assign the formula for each cell to be =myproduct(A1, B1), etc,
but would prefer to use the name references.

Of course the actual function I am trying to write is rather more
complicated, but I think these capture the semantics I am looking for.

Many thanks.
 
I believe you have declared you name range using relative cell references -
so if you had B1 selected and define foo to be

Sheet1!A1:A5

then if you selected D1 and put in =sum(Foo), it would be =Sum(C1:C5)

normally you don't want this, so you should use absolute cell references

Foo
Refersto: =Sheet1!$A$1:$A$5

then the definition isn't affected by the currently activecell, nor does it
change when you use it.


as far as your vba function, you arguments don't match your declarations,
so #Value.

You can declare your arguments as variants and they will come in as ranges.
 
Back
Top