passing many arguments to a function in Excel 
Author Message
 passing many arguments to a function in Excel

   .  Reply (E-mail)  Forward (E-mail)

 Subject:  Re: using arrays in visual basic with excel
Attachments:  None  

Sent:  12/30/2002 1:39:09 PM

Shaun

If you want to pass a range into your function, don't use
ParamArray, but
rather use a Range argument.  ParamArray is when you want
to pass an unknown
number of arguments.  In your example, this would work

=test(1,C3,C4,C5,C6,C7,C8)

but this won't work

=test(1,C3:C8)

C3:C8 is one argument.  It doesn't matter that you don't
know how many cells
are in the range argument, VBA still considers it to be
one argument.  If
that's the format you want, you might rewrite your
function like this:
I received this answer to one of my queries and it worked
well, but now I was wondering how I can get my range to
include cells not directly attached to each other (ie,
right now I can pass in (1, c3:c8), but I can't pass in an
extra cell reference to include in my range (ie, (1,
c3:c8,c10) or as many I wish to have.  For example in the
average function or other like it in Excel you can
continually add numbers and excel will expand the arglist.
either in range form or an integer or single reference.  

Thanks for any help.

Previous Answer:

Function test3(var As Variant, MyRng As Range)

Dim cell As Range

For Each cell In MyRng.Cells
    test3 = test3 + (cell.Value * var)
Next cell

End Function

The For Each statement will loop through the cell in the
range argument that
is passed.  As long as you are changing it, you probably
want to define var
as something other than variant, such as Long or Double.



Sun, 19 Jun 2005 03:06:03 GMT  
 passing many arguments to a function in Excel
Shaun

OK, now you can combine the two methods.  Here's how you might write an
average function.  This does not have any error checking, so you might want
to check to make sure that what you are getting is a number.

Function MyAvg(ParamArray VarList() As Variant) As Variant

Dim cell As Range
Dim i As Long, Cnt As Long
Dim Tot As Double

For i = LBound(VarList) To UBound(VarList)
    If TypeName(VarList(i)) = "Range" Then
        For Each cell In VarList(i).Cells
            Tot = Tot + cell.Value
            Cnt = Cnt + 1
        Next cell
    Else
        Tot = Tot + VarList(i)
        Cnt = Cnt + 1
    End If
Next i

MyAvg = Tot / Cnt

End Function

--
{*filter*} Kusleika
MVP - Excel

Post all replies to the newsgroup.


Quote:
>    .  Reply (E-mail)  Forward (E-mail)

>  Subject:  Re: using arrays in visual basic with excel
> Attachments:  None

> Sent:  12/30/2002 1:39:09 PM

> Shaun

> If you want to pass a range into your function, don't use
> ParamArray, but
> rather use a Range argument.  ParamArray is when you want
> to pass an unknown
> number of arguments.  In your example, this would work

> =test(1,C3,C4,C5,C6,C7,C8)

> but this won't work

> =test(1,C3:C8)

> C3:C8 is one argument.  It doesn't matter that you don't
> know how many cells
> are in the range argument, VBA still considers it to be
> one argument.  If
> that's the format you want, you might rewrite your
> function like this:
> I received this answer to one of my queries and it worked
> well, but now I was wondering how I can get my range to
> include cells not directly attached to each other (ie,
> right now I can pass in (1, c3:c8), but I can't pass in an
> extra cell reference to include in my range (ie, (1,
> c3:c8,c10) or as many I wish to have.  For example in the
> average function or other like it in Excel you can
> continually add numbers and excel will expand the arglist.
> either in range form or an integer or single reference.

> Thanks for any help.

> Previous Answer:

> Function test3(var As Variant, MyRng As Range)

> Dim cell As Range

> For Each cell In MyRng.Cells
>     test3 = test3 + (cell.Value * var)
> Next cell

> End Function

> The For Each statement will loop through the cell in the
> range argument that
> is passed.  As long as you are changing it, you probably
> want to define var
> as something other than variant, such as Long or Double.



Sun, 19 Jun 2005 03:55:13 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. passing many arguments to a function in Excel

2. passing arguments to VBA function in Excel

3. Passing Functions or Procedures as arguments in Function or Procedure calls

4. Passing an entire column as an argument in a function

5. pass arguments to function via OnAction?

6. How do I pass an Argument to Workbooks.Add.SaveAs function

7. Proper types of the arguments passed to a VBA function

8. Passing variables as arguments to functions!!

9. Passing an array as an argument to VBA function

10. Passing one dimension of a variant table as argument of a function


 
Powered by phpBB® Forum Software © phpBB Group