User defined function with variable inputs? How do I do it

[johnryanday]

I want to build something like this....

function getdb(a,b,c,d,e,f........x)
getdb = a & "," & b & "," & c &....& x
end function

where x is the total number of variables.

So if in excel, I run getdb (a,b) that it will know that the funciton has only 2 variables but if i run getdb(a,b,c,d,e,f,g,h,i,j) that it knows.

I am trying to avoid getdb(a,b,,,,,,,,,,,,,,,,,,,) as I bet the user will not know how many ","s to use

any help would be appreciated

Ryan

 

[Jon von der Heyden]

Hi Ryan

Have a look at the MCONCAT function available thru the MOREFUNC addin.

Regards
Jon

 

[Jonmo1]

Use ParamArray

Public Function getdb(ParamArray myval() As Variant)
For i = LBound(myval) To UBound(myval)
    x = x & myval(i) & ","
    If i = UBound(myval) Then
        x = Left(x, Len(x) - 1)
    End If
Next i
getdb = x
End Function

See CPearson's website for more help on that
http://www.cpearson.com/excel/OptionalArgumentsToProcedures.aspx

 

[pgc01]

Hi

I agree with jonmo and would also use ParamArray but with Join instead of the loop

Public Function getdb(ParamArray myval() As Variant)
getdb = Join(myval, ",")
End Function

 

[Jonmo1]

Here's a more robust function I found (and tweaked a little bit) a while ago on this board. Not sure who wrote it.

With this, you just select the whole range that contains the values you want concatenated together.

the 2nd and 3rd argument are optional

2nd. Deliminator
what you want the values seperated by. If left blank, it uses a space

3rd. Direction.
If you select a multi column Range. By Default Excel reads left to right, then Up/Down..

for example, if you have values in A1:B2..
by default it would be
A1,B1,A2,B2

If you put TRUE in the 3rd argument, it will go
A1,A2,B1,B2

Public Function SpecialConcatenate(myrange As Range, Optional delim As String = " ", _
Optional direction As Boolean = False) 'As String
Dim aCell As Range
Dim NewString
NewString = ""
If direction = False Then
    For Each aCell In myrange
        If aCell <> "" Then
            NewString = NewString & aCell & delim
        End If
    Next aCell
Else:
    Top = myrange.Row
    Bot = myrange.Row + myrange.Rows.Count - 1
    lft = myrange.Column
    rgt = myrange.Column + myrange.Columns.Count - 1
    For i = lft To rgt
        For j = Top To Bot
            If Cells(j, i).Value <> "" Then
                NewString = NewString & Cells(j, i).Value & delim
            End If
        Next j
    Next i
End If
SpecialConcatenate = Left(NewString, Len(NewString) - Len(delim))
End Function

 

[Jonmo1]

but with Join instead of the loop

That's cool. I literally learn something new every day on this board.
Thanks PCG

 

[johnryanday]

this works great. thanks all for the help. I ended up using the join function after all. Again, much appreciated. Does everyone just troll this site and look for questions to solve? If so, I am indebted to you all.

 

[Jonmo1]

Does everyone just troll this site and look for questions to solve?

Yep. That is probably the best way to learn.

People stop learning when they stop trying to do new things.

 

[johnryanday]

now that I have ....
"myval (1), myval(2)...myval(ubound)" with getdb, any idea how to efficeintly pick off each val?

i.e. I want to set...
a1 = myval(1)
a2 = myval(2)
aubound = myval (ubound)

I need to seperate these to then run particular macros given the value of the individual myval()

 

[Jonmo1]

Modifying PCG's code.
The first argument in the function is the position # you want to return.
all arguments to right will be the string concatenation

If you put 0 for the first argument, it returns the whole string. Otherwise, returns the position number specified.

=getdb(1,A1,B1,C1) = A1
=getdb(2,A1,B1,C1) = B1
=getdb(3,A1,B1,C1) = C1
=getdb(0,A1,B1,C1) = A1,B1,C1

Public Function getdb(pos As Long, ParamArray myval() As Variant)
If pos > UBound(myval) + 1 Then pos = UBound(myval) + 1
If pos = 0 Then
    getdb = Join(myval, ",")
Else:
    getdb = myval(pos - 1)
End If
End Function