How to merge two Arrays without loop in Excel VBA?

Question

I have two arrays in Excel VBA:

myArr1 = Array("a", "b", "c")
myArr2 = Array("1", "2", "3")

Now I want the Array myArr3, the dummy code is myArr3 = myArr1 & myArr2, so the content of myArr3 is ("a1", "b2", "c3").

Could anyone teach me how to implement this step without Loop functions?

The big question is ... why do you need to do it without a loop? — YowE3K
– YowE3K, Commented Dec 26, 2016 at 7:37
@YowE3K The reason of “Why I need to do ti without a loop” is : In my opinion, the loop function will cause the poor efficiency, so I need a way to do it directly. — sniperhgy
– sniperhgy, Commented Dec 26, 2016 at 9:01
@GSerg There question named "Avoid Loop over Arrays in VBA? " only has one Array, my question involved two Arrays. So our questions are not the same. Thank you. — sniperhgy
– sniperhgy, Commented Dec 26, 2016 at 9:02
It does not matter how many arrays you have. Please read the accepted answer. It applies in full. — GSerg
– GSerg, Commented Dec 26, 2016 at 9:37

YowE3K · Accepted Answer · 2016-12-26 07:36:19Z

1

The following will do it without (explicit) loops, but it is not a good idea to do it like this:

myArr1 = Array("a", "b", "c")
myArr2 = Array("1", "2", "3")
Range("A1:A3").Value = Application.Transpose(myArr1)
Range("B1:B3").Value = Application.Transpose(myArr2)
Range("C1:C3").FormulaR1C1 = "=RC[-2]&RC[-1]"
myArr3 = Application.Transpose(Range("C1:C3").Value)

Note: Because of the way they are created, myArr1 and myArr2 will be dimensioned 0 To 2, but myArr3 will be dimensioned 1 To 3.

A somewhat simpler method that doesn't require loops would be:

myArr1 = Array("a", "b", "c")
myArr2 = Array("1", "2", "3")
Dim myArr3(0 To 2)
myArr3(0) = myArr1(0) & myArr2(0)
myArr3(1) = myArr1(1) & myArr2(1)
myArr3(2) = myArr1(2) & myArr2(2)

answered Dec 26, 2016 at 7:36

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sniperhgy Over a year ago

Thank you for the reply at first. About method 1, the assisted columns and formula are used, it's low efficiency. About method 2, when I have only 3 elements in the array, this method is doable, but if there are 100,000 elements in my array, should I use this method too? So, I want a way to do that just like myArr1 & myArr2 (EXCEL VBA doesn't support this operation)

YowE3K Over a year ago

@sniperhgy - Yes, method 1 is low efficiency - I only suggested it because you explicitly stated in your question that you didn't want to use highly-efficient methods such as loops. And method 2 is unwieldy for large volumes, but loops are what should be used to operate on large volumes if you wish to keep your code manageable.

sniperhgy Over a year ago

About method 1, we reached a consensus, it's low efficiency. But about the efficiency of Loop function, in my opinion, sometimes it's low efficiency too. For example : We want to fill a array into a range, the direct method will be fast, if we use a Loop to do it, we'll cost more time.

Collectives™ on Stack Overflow

How to merge two Arrays without loop in Excel VBA?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related