Yes, I can see th point you are making. But as far as I can see, you are
still trying to pass two variables to the function. You could parse it like
so
Function abc(val)
Dim amt
Dim rng As Range
Dim cell As Range
amt = Left(val, InStr(1, val, "-") - 1)
For Each cell In Range(Right(val, Len(val) - InStr(1, val, "-")))
abc = abc + amt - cell.Value
Next cell
End Function
and call it like so
=abc("50-A1:A6")
because you cannot pass it like =abc(50-A1:A6) as Excel will evaluate the
parameter and pass that to your function.
When it comes down to it, there are two parameters, an amount and a range,
and it would be far simpler to hadle that. If it coul be more complex, you
need a parsing engine, which could get complex.
You could just evauate a full formula with
Function abc(val)
abc = Evaluate(val)
End Function
and use the call with =abc("SUM(50-A1:A6)")
--
HTH
Bob Phillips
Cactus said:
Bob
SUM(50-A1:A6) is array formula.
use CTRL+SHIFT+ENTER close enter,
and get sum of {50-A1, 50-A2,...}.
let I try more.
thx
AS far as I can see, they are not the same thing.
If A1:A6 all contain 20,21, etc. SUM(50-A1:A6) returns 30, whereas
50-20+50-21+etc comes to 165.
Functions can handle a paramarray, a variable umber of parameters, but you
have to manage that in the code.
--
HTH
Bob Phillips
Cactus said:
Bob
I think it should have resolved.
try type "SUM(50-A1:A6)" in a cell.
many function can access that parameter correct.
thx
That is two parameters that you need to setup, and code accordingly.
--
HTH
Bob Phillips
"Cactus" <zhanglihome [at] 21cn [dot] com> wrote in message
VBA function parameter for sheets range is Variant.
eg. function Test(Arr as Variant)
the "Arr" can be Range in sheet.
now I try send "50-A1:A6" as parameter.
the parameter should be {50-A1, 50-A2,...}
But I got error.
How I fix that wrong?
