try this:
Option Explicit
Sub test()
Dim stat
stat = xyz("one,two,three", "three", 4, 5, 6, 7, 8, 9)
End Sub
'------------------------------------------------------------
Function xyz(ParamArray nArray() As Variant)
Dim msg, I
msg = UBound(nArray) & " elements:" & Chr(13) & "------------"
For I = 0 To UBound(nArray)
msg = msg & Chr(13) & I & " : " & nArray(I)
Next I
MsgBox msg
End Function
'------------------------------------------------------------
now, nArray becomes a variable-sized array containing the values passed to the
"test" function.
If you're wanting to do calculations, you may need to test them to ensure they
are "numeric"
also note, the array begins with element "0".
8 values are indexed 0-7.
hope this helps.
Paul
________________________________
From: Sukiyaki <nas...@yahoo.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Sat, March 13, 2010 11:51:48 AM
Subject: $$Excel-Macros$$ Re: Uncertain number of input for VBA function
Since no one is asswering my question, may be people do not understand
it. Let me give more example.
Usually, we use a fixed number of parameters/inputs or a range for a
UDF.
Example:
Function xyz(a as double, b as double) as double
xyz = a + b
End Function
Or
Function xyz(rng as range) as double
for each cell in rng
xyz = xyz + cell
next
End Function
But how can I have a function that allows users to select any number
of parameters like the SUM function?
In a SUM function, users can select multiple cells and ranges. As a
programmer, we do not know how many they willl select into the
function. How can we code such a function?
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
