Search XL VBA help for 'union' (w/o the quotes).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
how I union this 3 arrays ?

example:

A = array("mary", "john")
B = array("Peter")
C = array("Roger")

How to do?

D = A + B + C

thanks

Marina

Oops!

The union method as supported by VBA/XL doesn't help in your case.

Sorry about that.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
hEm says...
Search XL VBA help for 'union' (w/o the quotes).

how I union this 3 arrays ?

example:

A = array("mary", "john")
B = array("Peter")
C = array("Roger")

How to do?

D = A + B + C

thanks

Marina

Well, I was kinda surprised that a search of the google.com archives of
the XL NGs didn't show up anything. So, I put something together:
Option Explicit
Option Base 0
Function VBAUnion(ParamArray Arr())
Dim x As Collection, I As Integer, J As Integer, Rslt
Set x = New Collection
For I = LBound(Arr) To UBound(Arr)
If IsArray(Arr(I)) Then 'handles only 1D array
For J = LBound(Arr(I)) To UBound(Arr(I))
On Error Resume Next
x.Add Arr(I)(J), CStr(Arr(I)(J))
On Error GoTo 0
Next J
Else
On Error Resume Next
x.Add Arr(I), CStr(Arr(I))
On Error GoTo 0

End If
Next I
ReDim Rslt(x.Count - 1)
For I = LBound(Rslt) To UBound(Rslt)
Rslt(I) = x.Item(I + 1)
Next I
VBAUnion = Rslt
End Function
Sub testUnion()
Dim x, y, z, w
x = Array("a", "b")
y = Array(1, "b")
z = Array(1, 2, 3)
w = VBAUnion(x, y, z)
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
how I union this 3 arrays ?

example:

A = array("mary", "john")
B = array("Peter")
C = array("Roger")

How to do?

D = A + B + C

thanks

Marina

Given that I screwed up once, the likelihood that I would post untested code
is...Zero!

I tested the code. It works. w contains the correct result.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
not correct this function

test the testunion is result empty !!!

Marina



Well, I was kinda surprised that a search of the google.com archives of
the XL NGs didn't show up anything. So, I put something together:
Option Explicit
Option Base 0
Function VBAUnion(ParamArray Arr())
Dim x As Collection, I As Integer, J As Integer, Rslt
Set x = New Collection
For I = LBound(Arr) To UBound(Arr)
If IsArray(Arr(I)) Then 'handles only 1D array
For J = LBound(Arr(I)) To UBound(Arr(I))
On Error Resume Next
x.Add Arr(I)(J), CStr(Arr(I)(J))
On Error GoTo 0
Next J
Else
On Error Resume Next
x.Add Arr(I), CStr(Arr(I))
On Error GoTo 0

End If
Next I
ReDim Rslt(x.Count - 1)
For I = LBound(Rslt) To UBound(Rslt)
Rslt(I) = x.Item(I + 1)
Next I
VBAUnion = Rslt
End Function
Sub testUnion()
Dim x, y, z, w
x = Array("a", "b")
y = Array(1, "b")
z = Array(1, 2, 3)
w = VBAUnion(x, y, z)
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
how I union this 3 arrays ?

example:

A = array("mary", "john")
B = array("Peter")
C = array("Roger")

How to do?

D = A + B + C

thanks

Marina

not correct this function

test the testunion is result empty !!!

Marina



Well, I was kinda surprised that a search of the google.com archives of
the XL NGs didn't show up anything. So, I put something together:
Option Explicit
Option Base 0
Function VBAUnion(ParamArray Arr())
Dim x As Collection, I As Integer, J As Integer, Rslt
Set x = New Collection
For I = LBound(Arr) To UBound(Arr)
If IsArray(Arr(I)) Then 'handles only 1D array
For J = LBound(Arr(I)) To UBound(Arr(I))
On Error Resume Next
x.Add Arr(I)(J), CStr(Arr(I)(J))
On Error GoTo 0
Next J
Else
On Error Resume Next
x.Add Arr(I), CStr(Arr(I))
On Error GoTo 0

End If
Next I
ReDim Rslt(x.Count - 1)
For I = LBound(Rslt) To UBound(Rslt)
Rslt(I) = x.Item(I + 1)
Next I
VBAUnion = Rslt
End Function
Sub testUnion()
Dim x, y, z, w
x = Array("a", "b")
y = Array(1, "b")
z = Array(1, 2, 3)
w = VBAUnion(x, y, z)
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
how I union this 3 arrays ?

example:

A = array("mary", "john")
B = array("Peter")
C = array("Roger")

How to do?

D = A + B + C

thanks

Marina

Tushar's function works perfectly for me. If you have headed your module
"Option Base 1" then change

ReDim Rslt(x.Count - 1)
For I = LBound(Rslt) To UBound(Rslt)
Rslt(I) = x.Item(I + 1)

to
ReDim Rslt(x.Count )
For I = LBound(Rslt) To UBound(Rslt)
Rslt(I) = x.Item(I)

The function provides an extra bonus of not including any duplicate values
in the "unioned" array. However if you want to keep all original values,
incl duplicates, a different approach -

Function ArrUnion(vUnion, v)
Dim bIsArray As Boolean
Dim cnt As Long, nTop As Long
Dim i As Long, j As Long

' input value or 1xD array only (so not a 2xD range)

bIsArray = IsArray(v)

If bIsArray Then
cnt = UBound(v) - LBound(v) + 1
Else
cnt = 1
End If

If IsArray(vUnion) Then
nTop = UBound(vUnion) + 1
ReDim Preserve vUnion(0 To cnt + nTop - 1)
Else
nTop = 0
ReDim vUnion(0 To cnt - 1)
End If

If bIsArray Then
For i = LBound(v) To UBound(v)
vUnion(j + nTop) = v(i)
j = j + 1
Next
Else
vUnion(nTop) = v
End If
''for testing only
Dim s As String
For i = LBound(vUnion) To UBound(vUnion)
s = s & i & vbTab & vUnion(i) & vbCr
Next
MsgBox s

End Function

Sub testArrUnion()
Dim A, B, C
Dim x As Long
Dim D
A = Array("mary", "john")
B = Array("Peter")
C = Array("Roger", "Dick", "Harry")
x = 123

ArrUnion D, A
ArrUnion D, B
ArrUnion D, C
ArrUnion D, x

End Sub

Regards,
Peter T


"Marina Limeira" wrote in message
...
not correct this function

test the testunion is result empty !!!

Marina



Well, I was kinda surprised that a search of the google.com archives of
the XL NGs didn't show up anything. So, I put something together:
Option Explicit
Option Base 0
Function VBAUnion(ParamArray Arr())
Dim x As Collection, I As Integer, J As Integer, Rslt
Set x = New Collection
For I = LBound(Arr) To UBound(Arr)
If IsArray(Arr(I)) Then 'handles only 1D array
For J = LBound(Arr(I)) To UBound(Arr(I))
On Error Resume Next
x.Add Arr(I)(J), CStr(Arr(I)(J))
On Error GoTo 0
Next J
Else
On Error Resume Next
x.Add Arr(I), CStr(Arr(I))
On Error GoTo 0

End If
Next I
ReDim Rslt(x.Count - 1)
For I = LBound(Rslt) To UBound(Rslt)
Rslt(I) = x.Item(I + 1)
Next I
VBAUnion = Rslt
End Function
Sub testUnion()
Dim x, y, z, w
x = Array("a", "b")
y = Array(1, "b")
z = Array(1, 2, 3)
w = VBAUnion(x, y, z)
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
how I union this 3 arrays ?

example:

A = array("mary", "john")
B = array("Peter")
C = array("Roger")

How to do?

D = A + B + C

thanks

Marina

OK, if you step through the code using F8...when the yellow highlight is on
'End Sub' what does w contain? To see its content use View | Locals Window,
You should see the 'Locals' window pane. In there will be a reference to
all the variables including w. Click the + sign next to an array variable
to see its individual components.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
well.. I Try here and nothing
and step by step .. not union array...
also idea Tushar ?
thanks
Marina


Given that I screwed up once, the likelihood that I would post untested
code
is...Zero!

I tested the code. It works. w contains the correct result.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions


not correct this function

test the testunion is result empty !!!

Marina



Well, I was kinda surprised that a search of the google.com archives of
the XL NGs didn't show up anything. So, I put something together:
Option Explicit
Option Base 0
Function VBAUnion(ParamArray Arr())
Dim x As Collection, I As Integer, J As Integer, Rslt
Set x = New Collection
For I = LBound(Arr) To UBound(Arr)
If IsArray(Arr(I)) Then 'handles only 1D array
For J = LBound(Arr(I)) To UBound(Arr(I))
On Error Resume Next
x.Add Arr(I)(J), CStr(Arr(I)(J))
On Error GoTo 0
Next J
Else
On Error Resume Next
x.Add Arr(I), CStr(Arr(I))
On Error GoTo 0

End If
Next I
ReDim Rslt(x.Count - 1)
For I = LBound(Rslt) To UBound(Rslt)
Rslt(I) = x.Item(I + 1)
Next I
VBAUnion = Rslt
End Function
Sub testUnion()
Dim x, y, z, w
x = Array("a", "b")
y = Array(1, "b")
z = Array(1, 2, 3)
w = VBAUnion(x, y, z)
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
how I union this 3 arrays ?

example:

A = array("mary", "john")
B = array("Peter")
C = array("Roger")

How to do?

D = A + B + C

thanks

Marina

well.. I Try here and nothing
and step by step .. not union array...
also idea Tushar ?
thanks
Marina


Given that I screwed up once, the likelihood that I would post untested
code
is...Zero!

I tested the code. It works. w contains the correct result.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions


not correct this function

test the testunion is result empty !!!

Marina



Well, I was kinda surprised that a search of the google.com archives of
the XL NGs didn't show up anything. So, I put something together:
Option Explicit
Option Base 0
Function VBAUnion(ParamArray Arr())
Dim x As Collection, I As Integer, J As Integer, Rslt
Set x = New Collection
For I = LBound(Arr) To UBound(Arr)
If IsArray(Arr(I)) Then 'handles only 1D array
For J = LBound(Arr(I)) To UBound(Arr(I))
On Error Resume Next
x.Add Arr(I)(J), CStr(Arr(I)(J))
On Error GoTo 0
Next J
Else
On Error Resume Next
x.Add Arr(I), CStr(Arr(I))
On Error GoTo 0

End If
Next I
ReDim Rslt(x.Count - 1)
For I = LBound(Rslt) To UBound(Rslt)
Rslt(I) = x.Item(I + 1)
Next I
VBAUnion = Rslt
End Function
Sub testUnion()
Dim x, y, z, w
x = Array("a", "b")
y = Array(1, "b")
z = Array(1, 2, 3)
w = VBAUnion(x, y, z)
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
how I union this 3 arrays ?

example:

A = array("mary", "john")
B = array("Peter")
C = array("Roger")

How to do?

D = A + B + C

thanks

Marina

Hello Tushar,

what about
Sub testUnion()
Dim x, y, z, w
x = Array(Array("a", "b"), "c")
y = Array(1, "b")
z = Array(1, 2, 3)
w = VBAUnion(x, y, z)
?

I would expect w to be = Array(Array("a", "b"), "c",1,"b",2,3)
Right? But it is not.

Regards,
Bernd

That example is beyond the scope of the code. As the only comment in the
code indicates it works strictly on 1D arrays.

Of course, one could easily argue that the definition of union in the
example you present is not (array("a", "b"), "c",1,"b",2,3) but rather
("a","b","c",1,2,3)

In any case...

What you want to do is in fact a subset of a larger class of possible data
sources: n-dimensional arrays or variants containing arrays of variants
containing arrays of...

While a solution can be created (I would use a recursive algorithm), it is
not included in this code. The code also doesn't explicitly handle objects,
either native or user-defined, or variables of a custom user type or....

Instead it relies on the default value, if any -- with the attendant and
potentially unintended consequences.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article . com,
says...
Hello Tushar,

what about
Sub testUnion()
Dim x, y, z, w
x = Array(Array("a", "b"), "c")
y = Array(1, "b")
z = Array(1, 2, 3)
w = VBAUnion(x, y, z)
?

I would expect w to be = Array(Array("a", "b"), "c",1,"b",2,3)
Right? But it is not.

Regards,
Bernd

I'm curious, for what purpose would you want to do that.

FWIW if you try your example with the function I posted it returns exactly
what you say you would expect (but comment the testing msgbox stuff).

Regards,
Peter T

wrote in message
ups.com...
Hello Tushar,

what about
Sub testUnion()
Dim x, y, z, w
x = Array(Array("a", "b"), "c")
y = Array(1, "b")
z = Array(1, 2, 3)
w = VBAUnion(x, y, z)
?

I would expect w to be = Array(Array("a", "b"), "c",1,"b",2,3)
Right? But it is not.

Regards,
Bernd

yes Peter
your example run correct

thanks

Marina

I like to use a slight variation to Tushar's excellent code example. This
small example does not have much error checking thou.

Function VBA_Union(ParamArray V())
Dim J, K
Dim Sd
Const Dummy As Byte = 0

Set Sd = CreateObject("Scripting.Dictionary")

On Error Resume Next
For J = 0 To UBound(V)
For K = 0 To UBound(V(J))
Sd.Add V(J)(K), Dummy
Next K
Next J
VBA_Union = Sd.Keys
End Function

Sub TestIt()
Dim x, y, z, w
x = Array("a", "b", 1)
y = Array(1, "b")
z = Array(1, 2, 3)
w = VBA_Union(x, y, z)
End Sub

Certain math programs by default have the function 'Union' remove all
duplicate items. (and will Sort the results also).
--
Dana DeLouis
Win XP & Office 2003


"Tushar Mehta" wrote in message
om...
That example is beyond the scope of the code. As the only comment in the
code indicates it works strictly on 1D arrays.

Of course, one could easily argue that the definition of union in the
example you present is not (array("a", "b"), "c",1,"b",2,3) but rather
("a","b","c",1,2,3)

In any case...

What you want to do is in fact a subset of a larger class of possible data
sources: n-dimensional arrays or variants containing arrays of variants
containing arrays of...

While a solution can be created (I would use a recursive algorithm), it is
not included in this code. The code also doesn't explicitly handle
objects,
either native or user-defined, or variables of a custom user type or....

Instead it relies on the default value, if any -- with the attendant and
potentially unintended consequences.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article . com,
says...
Hello Tushar,

what about
Sub testUnion()
Dim x, y, z, w
x = Array(Array("a", "b"), "c")
y = Array(1, "b")
z = Array(1, 2, 3)
w = VBAUnion(x, y, z)
?

I would expect w to be = Array(Array("a", "b"), "c",1,"b",2,3)
Right? But it is not.

Regards,
Bernd

Nice touch, Dana, using the Scripting.Dictionary object.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I like to use a slight variation to Tushar's excellent code example. This
small example does not have much error checking thou.

Function VBA_Union(ParamArray V())
Dim J, K
Dim Sd
Const Dummy As Byte = 0

Set Sd = CreateObject("Scripting.Dictionary")

On Error Resume Next
For J = 0 To UBound(V)
For K = 0 To UBound(V(J))
Sd.Add V(J)(K), Dummy
Next K
Next J
VBA_Union = Sd.Keys
End Function

Sub TestIt()
Dim x, y, z, w
x = Array("a", "b", 1)
y = Array(1, "b")
z = Array(1, 2, 3)
w = VBA_Union(x, y, z)
End Sub

Certain math programs by default have the function 'Union' remove all
duplicate items. (and will Sort the results also).