VBA: Strings and arrays - splitting 1d arrays into 2d arrays

Question

I've got a String array containing a string of items separated by "."

Ex.:

0: Test1.Test2.Test3.Test4.Test5
1: Testa.Testb.Testc.Testd.Teste
...

I need to split these strings into a 2D String array with 5 elements per array.

Ex.:

0, 0: Test1
0, 1: Test2
...
1, 0: Testa
1, 1: Testb
...

I'm struggling a bit, since this isn't quite like C or C#.

When testing and trying, this works:

NB: testList is an array like the first example, containing entries of "."-separated strings. It's created like this, from a long string called strArray:

testList = Split(textline, "<")     

Dim temp() As String
temp = Split(testList(0), ".")
MsgBox Join(temp, vbCrLf)

Above, I just create a new 1D String array temp() and split the first index of the String array testList into that. Works fine.

However, when I try to create a 2D String array and split the first index of the testList String array into the first index of that, it does NOT work:

Dim indTestsList() As String               'New String array
ReDim indTestsList(arrSize, 5)             'Initialize the 2D size
'MsgBox "arrSize " & arrSize
indTestsList(0) = Split(testList(0), ".")  'Split into the first array
'MsgBox Join(indTestsList(0), vbCrLf)

This gives the error

Type mismatch

So, it seems obvious that I've not declared or created the 2D array correctly, or I'm trying to insert something wrong into the wrong place. I just cannot see what/where?

However, if I just insert strings into the 2D array instead of splitting, it works:

Dim indTestsList() As String
ReDim indTestsList(arrSize, 5)
indTestsList(0, 0) = "dritt"
indTestsList(0, 1) = "piss"
MsgBox indTestsList(0, 0)
MsgBox indTestsList(0, 1)

'However, this does not work. Why? Subscript out of range...
MsgBox Join(indTestsList(0), vbCrLf)

So it seems that I AM able to create a 2D String array and populate it in the most simple way. Just not beyond that.

Sorry for not getting the VBA syntaxes and standards here, but I've been banging my head against this for a while. There might be help for this on the web, of course, but what I've found about this hasn't been what I needed. Any help is appreciated here, and all constructive answers will of course get credited.

(FINAL) UPDATE

There's lots of good advice below. I was struggling a bit, but ExcelinEfendisi put me on the track to a working, simple solution. I'm posting the solution I chose here, and crediting the answer to him.

Dim intCount        As Integer
Dim intCount2       As Integer
Dim tempArray1d()   As String
Dim finalArray2d()  As String

...

'Resize the array for holding the final ordered sets of tests:
ReDim finalArray2d(arrSize, 5)

'Loop through the testList() array and Split each array into the 2d 
'finalArray2d:
For intCount = LBound(testList) To UBound(testList) - 2
    'Split the first line in testList() into tempArray1d():
    tempArray1d = Split(testList(intCount), ".")
    'Copy the elements from tempArray1d() to the current x index of 
    'tempArray2d():
    For intCount2 = 0 To 4
        finalArray2d(intCount, intCount2) = tempArray1d(intCount2)
    Next intCount2
Next intCount

I've removed the other updates, as I think they developed into more noise and confusion than necessary. Thanks to those who answered and helped.

must you have forgotten to put the code with strArray declaration? could u update it again — Volkan Yurtseven
– Volkan Yurtseven, Commented Sep 12, 2017 at 11:20
sorry, you put the strArray in UPDATE1. ok the problem is that we define de resltArray 1D, but u r trying to access the 2nd dimension. just correct the declaration part as i did in my example — Volkan Yurtseven
– Volkan Yurtseven, Commented Sep 12, 2017 at 11:30
Hm, no? It's declared, then initialized with strArray = Split(textline, "<"). Anyway, the strArray is temporary; testList is the cleaned version of it, and the one I'm using from there on. — user8465989
– user8465989, Commented Sep 12, 2017 at 11:31
Isn't that what I've done? See the update. Even if I declare the resultArray with Dim resultArray (0 To 100, 0 To 500), which is much higher than necessary, I still get "Subscript out of rante". — user8465989
– user8465989, Commented Sep 12, 2017 at 11:45
I could post the complete code, but I think my question is getting a bit large with all the updates. Don't want to spam the site :-) — user8465989
– user8465989, Commented Sep 12, 2017 at 11:57

Volkan Yurtseven · Accepted Answer · 2017-09-12 09:10:25Z

1

this code will do

Sub Macro3()
Dim testList(0 To 1) As String
Dim temp1 As Variant
Dim temp2 As Variant
Dim resultArray(0 To 1, 0 To 4) As String

testList(0) = "Test1.Test2.Test3.Test4.Test5"
testList(1) = "Testa.Testb.Testc.Testd.Teste"


temp1 = Split(testList(0), ".")
temp2 = Split(testList(1), ".")


For i = 0 To 1 'actually UBound(testList)
    For j = 0 To 4 'actually UBound(temp, 2)
        resultArray(i, j) = IIf(i Mod 2 = 0, temp1(j), temp2(j))
    Next j
Next i

MsgBox resultArray(0, 2)

End Sub

if you need more dynamic version, you could use collections instead of temporary variant arrays as below

Sub Macro4()
Dim testList(0 To 1) As String
Dim temp As New Collection

Dim resultArray(0 To 1, 0 To 4) As String

testList(0) = "Test1.Test2.Test3.Test4.Test5"
testList(1) = "Testa.Testb.Testc.Testd.Teste"

For i = 0 To UBound(testList)
    temp.Add (Split(testList(i), "."))
Next i


For i = 0 To UBound(testList)
    For j = 0 To UBound(resultArray, 2)
        resultArray(i, j) = IIf(i Mod 2 = 0, temp(i + 1)(j), temp(i + 1)(j))
    Next j
Next i

MsgBox resultArray(0, 2)

End Sub

edited Sep 12, 2017 at 9:10

answered Sep 12, 2017 at 8:57

Volkan Yurtseven

4444 silver badges15 bronze badges

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

1 Comment

user8465989 Over a year ago

I've updated my question with details about how I create the testList() array. Can you advice on what I need to do to get your code to work with that? I'm getting errors when just replacing your test array with my array.

Vityata · Accepted Answer · 2017-09-12 08:34:44Z

1

This is how would I solve it:

Public Sub TestMe()

    Dim arrInitial  As Variant
    Dim arrTemp     As Variant
    Dim arrResult   As Variant

    Dim lngRowS     As Long
    Dim lngColS     As Long

    Dim lngRow      As Long
    Dim lngCol      As Long

    arrInitial = Array("Test1.Test2.Test3", "TestA.TestB.TestC")
    lngRowS = UBound(arrInitial)
    lngColS = UBound(Split(arrInitial(0), "."))

    ReDim arrResult(lngRowS, lngColS)

    For lngRow = 0 To lngRowS
        For lngCol = 0 To lngColS
            arrTemp = Split(arrInitial(lngRow), ".")
            arrResult(lngRow, lngCol) = arrTemp(lngCol)
        Next lngCol
    Next lngRow

    For lngRow = 0 To lngRowS
        For lngCol = 0 To lngColS
            Debug.Print lngRow; lngCol; arrResult(lngRow, lngCol)
        Next lngCol
    Next lngRow

End Sub

This is what you get as output:

 0  0 Test1
 0  1 Test2
 0  2 Test3
 1  0 TestA
 1  1 TestB
 1  2 TestC

Probably it can be done with less variables, but this way it is understandable. The idea is to take the total Rows and Columns of the new 2D array (lngRowS and lngColS) and to write them with a nested loop.

answered Sep 12, 2017 at 8:34

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

1 Comment

user8465989 Over a year ago

No matter what I do, when I try creating the initialArray() like I create my testList() array, I get a subscript out of range error when I try copying from arrTemp to arrResult.

Collectives™ on Stack Overflow

VBA: Strings and arrays - splitting 1d arrays into 2d arrays

(FINAL) UPDATE

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

(FINAL) UPDATE

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related