VBA parameter.

  • Thread starter Thread starter Cactus
  • Start date Start date
C

Cactus

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?
 
Bob

I think it should have resolved.

try type "SUM(50-A1:A6)" in a cell.

many function can access that parameter correct.


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.
 
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?
Click to expand...
Click to expand...
Click to expand...
 
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?
Click to expand...
Click to expand...
Click to expand...
 
Bob


I found another way.

'vba code.
function Test(Arr as Variant)

type "=Test(50-A1:A6)" in a cell.
closing enter by Ctrl+Shift+Enter.

now "Arr" in function Test() is a Array of Double.
(you can check it by set breakpoint at debug code.)

for ensure the parameter.

'vba code.
select case typename(Arr)
case "Range"
'this is range.
case else
'This a array.
end select
 
"Bob Phillips"
Yeah, but have you looked at what arr holds?
Click to expand...


that parameter could be any type in possibly.
but in this case, it's numbers only.

I have not handling all cases.
 
Back
Top