union array

[Tushar Mehta]

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

 

[Tushar Mehta]

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

 

[Marina Limeira]

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 Mehta]

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

 

[Tushar Mehta]

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

 

[Marina Limeira]

not correct this function

test the testunion is result empty !!!

Marina

 

[Peter T]

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

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

 

[Tushar Mehta]

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

 

[Marina Limeira]

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

 

[bplumhoff]

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

 

[Tushar Mehta]

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

 

[Peter T]

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

 

[Marina Limeira]

yes Peter
your example run correct

thanks

Marina

 

[Dana DeLouis]

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).

 

[Tushar Mehta]

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