VBA only - Is there an easier way to concatenate array values based on their position?

Question

I've had a little look and I've found similar questions but they all seem to be how to add one list to the end of another.

I've recently answered a question with a technique I use at work in Excel that relies on creating a 3rd column with a formula floodfill to concat col1 & col2 for each row. I figured there must be a better way to do this so had a little play in VBA and came up with the following from questions I could find. But now I have a couple of questions:

Is there a way to get rid of setting the 3rd array with x amount of arbitrary values which will just be replaced anyway? ReDim something maybe

Is there overall a better/neater way to combine elements from arr1 and arr2 based on their position without cycling through each one? (using in-built array commands or whatever)

(Apologies if this duplicates any thread somewhere, I did look, honest!)

Private Sub CommandButton1_Click()
Dim arr1() As Variant
Dim arr2() As Variant
Dim arr3() As Variant
Dim element As Variant
Dim pos As Integer

    arr1 = Array("ONE", "TWO", "THREE")
    arr2 = Array("1111", "2222", "3333")
    arr3 = Array("x", "x", "x")

    For Each element In arr1
        pos = Application.WorksheetFunction.Match(element, arr1, False) - 1
        arr3(pos) = arr1(pos) & arr2(pos)
        'MsgBox (arr1(pos) & arr2(pos) & arr3(pos))
    Next

    'Where arr3 will equal ("ONE1111", "TWO2222", "THREE3333")
End Sub

EDIT - Thanks all for the answers, giving me about 20 things to think about and play with over the next few days.

jam, technically any code you use will "cycle through each one". The only difference with built-in functions is that you can't see the code that is doing the cycling. If you write your own code, the way you avoid rewriting the cycling code every time is to generalize it and put it into a function. — Blackhawk
– Blackhawk, Commented Sep 22, 2016 at 22:43
That sounds about right, but I'm sure some inbuilt functions in certain codes/programs can utilise something like a "multithread" technique, so it'd actually collate arrays from both ends or in multiples instead of linearly going through (which'd be it's only choice if you told it to increment 1 by 1) — jamheadart
– jamheadart, Commented Sep 22, 2016 at 23:01
On that note, does anyone know why it's a lot quicker to floodfill, say, 10000 cells with an incremental value than it is to do a for x = 1 to 10000 next x loop in Excel? — jamheadart
– jamheadart, Commented Sep 22, 2016 at 23:05
this is because VBA has to load the entire range object each time, which includes dozens of nontrivial properties - possibly more than a hundred. Try creating a Watch window entry for a Range object and take a look at all the properties it exposes. If you can instead make the assignment all at once through the Excel object model, you're generally better off. — Blackhawk
– Blackhawk, Commented Sep 22, 2016 at 23:07
FYI I just ran a comparison of three different methods to fill 1 million cells with the values 1, 2, 3... etc. The Excel Autofill (with VBA) it takes 0.17 sec. The method I suggested of building a two dimensional array in VBA and then assigning it all at once takes 0.67 sec. The method you were originally talking about TAKES A WHOPPING 35.16 SECONDS! That all being said, while the method I suggested takes 3 times as long as AutoFill, I would still suggest it as the best general purpose method for cases where AutoFill doesn't know the logic you need to calculate the values. — Blackhawk
– Blackhawk, Commented Sep 22, 2016 at 23:26

Community · Accepted Answer · 2020-06-20 09:12:55Z

To expand on my comment, any code you might use for this WILL cycle through the array elements. The only difference with built-in code is you don't get to see the cycling code. Here is some code that allows you to make a simple function call and which supports any number of input arrays. The JoinArrayElements function does what you are asking, and you can use it without having to write the code to "cycle through the elements" each time.

Public Sub Main()
    Dim arr1, arr2
    arr1 = Array("ONE", "TWO", "THREE")
    arr2 = Array("1111", "2222", "3333")
    arr3 = Array("!@!@", "@#@#", "#$#$")
    
    Debug.Print arrayToString(joinArrayElements(arr1, arr2))
    Debug.Print arrayToString(joinArrayElements(arr1, arr2, arr3))
End Sub


Public Function arrayToString(arr As Variant) As String
    Dim output As String
    output = "["
    If UBound(arr) - LBound(arr) > 0 Then
        output = output & """" & arr(LBound(arr)) & """"
        For index = LBound(arr) + 1 To UBound(arr)
            output = output & ", " & """" & arr(index) & """"
        Next
    End If
    output = output & "]"
    arrayToString = output
End Function

Public Function joinArrayElements(ParamArray args() As Variant) As Variant
    'Validation to add:
    ' Are all the passed parameters actual valid arrays?
    ' Are they all the same length?
        
    Dim arrayNumber As Long
    Dim index As Long
    Dim arrOutput() As Variant
    
    ReDim arrOutput(LBound(args(0)) To UBound(args(0)))
    
    For arrayNumber = LBound(args) To UBound(args)
        For index = LBound(args(0)) To UBound(args(0))
            arrOutput(index) = arrOutput(index) & args(arrayNumber)(index)
        Next
    Next
        
    joinArrayElements = arrOutput
End Function

The output of this code is:

["ONE1111", "TWO2222", "THREE3333"]

["ONE1111!@!@", "TWO2222@#@#", "THREE3333#$#$"]

Tim Williams · Accepted Answer · 2016-09-22 22:10:36Z

2

If you don't need to keep arr1 in its original form then:

arr1 = Array("ONE", "TWO", "THREE")
arr2 = Array("1111", "2222", "3333")


For x =lbound(arr1) to ubound(arr1)
    arr1(x) = arr1(x) & arr2(x)
Next

There are few useful built-in array functions/methods in VBA, but looping isn't necessarily a bad thing.

You could always do something like this, but it may not scale well and is a worse approach I think:

Dim r, arr1, arr2

arr1 = Array("ONE", "TWO", "THREE")
arr2 = Array("1111", "2222", "3333")

r = ActiveSheet.Evaluate("={""" & Join(arr1, """,""") & """} & {""" & _
                                  Join(arr2, """,""") & """}")

Debug.Print Join(r, ",") '>> ONE1111,TWO2222,THREE3333

edited Sep 22, 2016 at 22:10

answered Sep 22, 2016 at 21:59

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

3 Comments

jamheadart Over a year ago

Fantastic, that simplifies things a fair bit. I was trying to avoid cycling through each array point for time saving on doing the same thing with lots of sets of arrays but it's probably not very noticeable anyway.

jamheadart Over a year ago

Hi Tim, thanks again, the second method is cool but actually seems to run slower than the looping (about five times slower!) so you're right on the money with your first shout.

Tim Williams Over a year ago

Second method is only really there to illustrate my point that there's no need to avoid looping (since non-looping alternatives are not necessarily better)

user6432984 · Accepted Answer · 2016-09-22 22:15:01Z

1

Here is how you would ReDim the array. The first time you size the array use ReDim array(size) and after that you'll use ReDim Preserve array(size) to preserve the information already in the array.

Private Sub CommandButton1_Click()
    Dim arr1() As Variant, arr2() As Variant, arr3() As Variant
    Dim pos As Integer

    arr1 = Array("ONE", "TWO", "THREE")
    arr2 = Array("1111", "2222", "3333")

    ReDim arr3(pos)

    For pos = 0 To UBound(arr1)
        ReDim Preserve arr3(pos)
        arr3(pos) = arr1(pos) & arr2(pos)
    Next
End Sub

answered Sep 22, 2016 at 22:15

user6432984

5 Comments

user3598756 Over a year ago

Why not simply go "ReDim arr3(UBound(arr1))" instead of "ReDim arr3(pos)" and get rid of the "ReDim Preserve arr3(pos)" in the for loop?

user6432984 Over a year ago

That would have been a better answer because it's simpler. I wanted to demonstrate how to use Redim Preserve which I didn't do all that well. I think that Dim arr3(2) would have satisfied the OP.

jamheadart Over a year ago

Thanks Thomas, clearly explained and a big help on getting my head round arrays. Thanks to user359 for improving even more! Seeing both ways has taught me more :) I'm just getting used to arrays in place of ranges within Excel so I can shift on to other languages where ranges/cell contents and Excel formulae simply don't exist.

jamheadart Over a year ago

Yeah that is definitely something else, I'm trying to play with it now to see how it handles larger arrays and which method might be fastest.

user6432984 Over a year ago

You should watch "The Wise Owl Tutorials - An Introduction to VBA" on Youtube here is the relavent one: Excel VBA Introduction Part 25 - Arrays

JCro · Accepted Answer · 2016-09-22 22:34:13Z

1

You may look at just using UDTs instead.

Public Type Car
    Manufacturer As String
    Model As String
    Price As Currency
    Doors As Integer
    Manufactured As Date
End Type

Dim myCars(0 To 2) as Car

myCars(0).Manufacturer="Toyota"
myCars(0).Model="86"

myCars(1).Manufacturer="Mazda"
myCars(1).Model="MX5"

'Then you can just use the fields together when needed like...
msgbox myCars(0).Manufacturer & " " & myCars(0).Model
msgbox myCars(1).Manufacturer & " " & myCars(1).Model

'Or even a function to return..
Public Function CarInfo(intCarID as Integer)

CarInfo = myCars(intCarID).Manufacturer & " " & myCars(intCarID).Model

End function

answered Sep 22, 2016 at 22:34

JCro

6965 silver badges10 bronze badges

2 Comments

jamheadart Over a year ago

I like it, that's an interesting way of doing it for plucking out some specific data on occasion without actually making a new array. I'll try to remember this method. I did have in mind creating one whole new array with all the good stuff in in one go but I like this.

JCro Over a year ago

@jamheadart They're supported by intelliSense too!

Collectives™ on Stack Overflow

VBA only - Is there an easier way to concatenate array values based on their position?

4 Answers 4

Comments

3 Comments

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related