Funny Red Spot On Breast

Join

The CONCATENATE function in Excel has many limitations, you can not use formulas with matrix, you can not pass a range of multiple cells ... the UDF MCAT Harlan Grove solves these problems so dear, a really good feature. Basically it's a chain with the characteristics of the SUM function, and then accepts an arbitrary number of parameters, which can be represented by the range of multiple cells ... can also be used in a feature matrix, really cool ... here it is: Function

MCAT (ParamArray S ()) As String

'Copyright (C) 2002, Harlan Grove ' This is free software. It's use in derivative works is covered 'under the terms of the Free Software Foundation's GPL. See

'http://www.gnu.org/copyleft/gpl.html

'------------------------------------

'string concatenation analog to SUM Dim R As Range, x As Variant, y As Variant

For Each x In S

If TypeOf x Is Range Then
For Each R In x.Cells
mcat = mcat & R.Value Next R
ElseIf IsArray(x) Then
For Each y In x
mcat = mcat & IIf(IsArray(y), mcat(y), y)
Next y
Else
mcat = mcat & x
End If Next x End Function








I drew inspiration from this function to create a UDF like to extremely useful to Join VB ... JoinUDF returns a string created from the combination of substrings contained in the source parameter is bounded by the parameter delimiter.


Source will be a range of more cells or an array, delimiter can be any value or range or an array (in this case is the concatenation of substrings).


Like Join this feature can be used to concatenate strings, omitting it will be used as a delimiter Delimiter string length zero (la funzione Join viceversa usa come defoult lo spazio).

Function JoinUDF( _
Source As Variant, _ Optional Delimiter As Variant = "") As String
'di Roberto Mensa Nick r Dim Rng As Range, x() As String, y As Variant Dim i As Long
If TypeName(Source) = "Range" Then ReDim x(Source.Count - 1)
For Each Rng In Source
x(i) = CStr(Rng.Value) i = i + 1 Next
ElseIf IsArray(Source) Then For Each y In Source
ReDim Preserve x(i)
x (i) = CStr (IIf (IsArray (y), JoinUDF (y), y))
i = i + 1 Next


Else ReDim x (0)
x (0) = CStr (Source) End
If

If IsArray (Delimiter) Or TypeName (Delimiter) = "Range" Then Delimiter =
JoinUDF (Delimiter) End If

JoinUDF = Join (x, CStr (Delimiter)) End Function




greetings
r