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
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
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 <[email protected]>,
[email protected] 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 <[email protected]>,
[email protected]hEm says...
> Search XL VBA help for 'union' (w/o the quotes).
>
>
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 <[email protected]>,
[email protected] 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 <[email protected]>,
> [email protected] 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 <[email protected]>,
[email protected] 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 <[email protected]>,
> > [email protected] 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 <[email protected]>,
>> > [email protected] 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", "****", "Harry")
x = 123
ArrUnion D, A
ArrUnion D, B
ArrUnion D, C
ArrUnion D, x
End Sub
Regards,
Peter T
"Marina Limeira" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> > [email protected] 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 <[email protected]>,
[email protected] 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 <[email protected]>,
> >> > [email protected] 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 <[email protected]>,
[email protected] 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
<[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> [email protected] 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 <[email protected]>, [email protected]
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).
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks