• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Getting parameters from the Formula Bar for creating a UDF in Excel VBA

S

Sarathi_citi

New Member
Hi

I am writing a user defined function to concatenate the text present in the selected Range.

I am facing issues in identifying the input parameter type for the UDF.

The user will use UDF as formula in a cell as shown below

=Concat(A1:A4,A7:A12,B4:B9, ":")

ideally i am trying to concatenate the text present in each of the cells present in the ranges with the Seperator given.

-------

Code: 
Function Concat(Input As string,Seperator as String) As String
'My logic to concatenate the text with seperator goes here
End Function

I am unable to identify what datatype should be used in the UDF
***Function Concat(Input As string,Seperator as String) As String***
for the highlighted text in the Concat formula.

Note: the number of ranges the user can select in the concat formula can vary.

Could someone please give me an idea ?

Thanks
Sarathi
 
Hi Sarathi ,

Since you are passing ranges to the function , you need to declare the first parameter as Range.

The following is example code for the UDF :
Code: 
Public Function temp(in1 As Range, separator As String) As String
                For Each cell In in1
                    s = s & " " & cell.Value
                Next
               
                temp = Replace(Trim(s), " ", separator)
End Function

Calling this UDF as follows :

=temp((I1:I4,L5:L8,N1,O4,Q5:Q7),";")

with the brackets "(" and ")" enclosing the multiple ranges will be accepted.

Narayan
 
Hi Narayan,
Thanks for the reply. Is there a way can we write the formula as
=temp(I1:I4,L5:L8,N1,O4,Q5:Q7,";") instead of =temp((I1:I4,L5:L8,N1,O4,Q5:Q7),";")

Kanti,
I am looking into the link you have shared. I think it will take time for me to understand that code :(. However thanks for the help.

Thanks
Sarathi
 
Hi kanti,

I am unable to use like this

Function myConcat(ParamArray X(), Optional Seperator as String)

I want to take a seperator as string input. If i use ParamArray, i am unable to get another input parameter as shown above. any idea?
 
Sarathi_citi said:
Hi Narayan,
Thanks for the reply. Is there a way can we write the formula as
=temp(I1:I4,L5:L8,N1,O4,Q5:Q7,";") instead of =temp((I1:I4,L5:L8,N1,O4,Q5:Q7),";")
Thanks
Sarathi
Click to expand...
Hi Sarathi ,

The point of using the brackets is that Excel automatically does a UNION of all the ranges within the brackets ; your declaration of the first input parameter of the UDF needs to have just one range parameter.

If you do not use the brackets , and separate the several ranges by commas , then you need to declare all of them as input parameters , and you need to have a fixed upper limit ; if you declare 5 input range parameters , you cannot use the UDF with a 6th input range.

Narayan
 
Hi Narayan,
Thanks for the explanation. I looked into the code what Kanti has shared. I am trying to use that Function with one more input parameter, but its giving an error.

Do you have any idea why i am not able to take another input parameter after the ParamArray?
Function myConcat(ParamArray X(), Optional Seperator as String)

Just curious to understand whats the error.

Thanks
Sarathi
 
Hi Sarathi ,

The explanation is straightforward ; the ParamArray usage conveys to VB that there can be any number of input parameters ; the number of parameters which are passed to the function decide how many parameters are used ; if you call the function with 3 parameters , the function will use those 3 parameters ; if you call the function with 17 parameters , it will use all 17 !

Thus , when you make use of ParamArray , you do not need to declare any optional parameter ; using this is actually an error.

All you need to do is ensure that any usage of the UDF calls it with the range parameters first , however many of them there are , and the separator string parameter is the last parameter.

Thus calling the UDF as :

=concat(A1:A17 , B1 , C5 , D33 , D45:D65 , ";")

will be OK , but calling it as :

=concat(A1:A17 , B1 , C5 , D33 , D45:D65 , ";", F13:F17)

will not.

Of course , within the UDF , you need to have a lot of error checking to ensure that the parameters are of the right type.

Narayan
 
The other option would be to put the delimiter first in the parameters, like so:
Code: 
Function ConCat(Delimiter As Variant, ParamArray CellRanges() As Variant) As String
  Dim cell As Range, Area As Variant
  If IsMissing(Delimiter) Then Delimiter = ""
  For Each Area In CellRanges
  If TypeName(Area) = "Range" Then
  For Each cell In Area
  If Len(cell.Value) Then ConCat = ConCat & Delimiter & cell.Value
  Next
  Else
  ConCat = ConCat & Delimiter & Area
  End If
  Next
  ConCat = Mid(ConCat, Len(Delimiter) + 1)
End Function
 
You must log in or register to reply here.
Back
Top