Option
Explicit
Public
Enum
cxTriState: cxAsUsed = -2: cxRTrue: cxFalse: cxPTrue:
End
Enum
Rem Verbinden aller Elemente 1es belieb Vektors
Function
VJoin(Bezug,
Optional
ByVal
BindeZ,
Optional
ByVal
NurUngl
As
cxTriState)
Dim
lix
As
Long
, pix
As
Long
, erg, xBez
As
Variant
On
Error
Resume
Next
: NurUngl = Sgn(NurUngl) +
CInt
(NurUngl < cxRTrue)
If
Not
IsMissing(BindeZ)
Then
If
IsError(BindeZ)
Then
BindeZ = Application.International(xlListSeparator)
Else
: BindeZ =
" "
End
If
With
WorksheetFunction
If
TypeName(Bezug) =
"Range"
Then
Bezug = .Transpose(.Transpose(Bezug))
If
IsError(LBound(Bezug))
Then
erg = Bezug
ElseIf
CBool
(NurUngl)
Then
If
NurUngl = cxRTrue
Then
ReDim
erg(0)
For
Each
xBez
In
Bezug
If
NurUngl = cxRTrue
Then
If
CBool
(lix)
And
xBez <>
""
Then
pix = 0: pix = .Match(xBez, erg, 0)
If
pix = 0
Then
ReDim
Preserve
erg(lix): _
erg(lix) = xBez: lix = lix + 1
ElseIf
xBez <>
""
Then
erg(0) = xBez: lix = lix + 1
End
If
ElseIf
Not
IsEmpty(erg)
Then
If
NurUngl = cxAsUsed
Or
(xBez <>
""
And
_
InStr(erg, xBez) = 0)
Then
erg = erg & BindeZ & xBez
ElseIf
xBez <>
""
Then
erg = xBez
End
If
Next
xBez
ElseIf
IsError(LBound(Bezug, 2))
Then
erg = Join(Bezug, BindeZ)
Else
: Bezug = .Transpose(Bezug)
If
IsError(LBound(Bezug, 2))
Then
erg = Join(Bezug, BindeZ)
Else
: erg = CVErr(xlErrRef)
End
If
End
If
End
With
If
NurUngl = cxRTrue
Then
VJoin = Join(erg, BindeZ)
Else
VJoin = erg
End
Function