Converting 2D array to a list in VBA

Question

I want to write a simple piece of code to calculate forces in a bolt group. the bolts are placed in a coordinate system of x columns and y rows. lets say i have a 3x4 system i get the following points:

(0,0) , (1,0) , (2,0) , (3,0)
(0,1) , (1,1) , (2,1) , (3,1)
(0,2) , (1,2) , (2,2) , (3,2)
(0,3) , (1,3) , (2,3) , (3,3)

I can create this with the folowing code:

Dim myarray(3, 4) As String

    For x = LBound(myarray, 1) To UBound(myarray, 1)
        For y = LBound(myarray, 2) To UBound(myarray, 2)
            myarray(x, y) = x & "," & y
            Cells(x + 1, y + 1).Value = myarray(x, y)
        Next y
    Next x

now i need to get a list with x and y coordinates to calculate the vector r:

x  y  
0  0  
1  0 
2  0
3  0 
0  1
1  1
2  1
3  1
... and so on

if tried different things try and split the array, but it always fails :( any help would be greatly appreciated!

Regards,

Batman

QHarr · Accepted Answer · 2021-01-31 22:06:55Z

3

Don't know where you want to put it but you can loop your current array and lengthen across 2 columns by using split on each entry

Option Explicit

Public Sub Flatten()

    Dim myarray(3, 4) As String, x As Long, y As Long

    For x = LBound(myarray, 1) To UBound(myarray, 1)
        For y = LBound(myarray, 2) To UBound(myarray, 2)
            myarray(x, y) = x & "," & y
            ActiveSheet.Cells(x + 1, y + 1).Value = myarray(x, y)
        Next y
    Next x
    
    Dim results(), r As Long, arr() As String
    ReDim results(1 To (UBound(myarray, 1) + 1) * (UBound(myarray, 2) + 1), 1 To 2)
    
    For x = LBound(myarray, 1) To UBound(myarray, 1)
        For y = LBound(myarray, 2) To UBound(myarray, 2)
            r = r + 1
            arr = Split(myarray(x, y), ",")
            results(r, 1) = arr(0)
            results(r, 2) = arr(1)
        Next y
    Next x
    
    ActiveSheet.Cells(1, 1).CurrentRegion.Offset(0, UBound(myarray, 2) + 1).Resize(UBound(results, 1), 2) = results

End Sub

answered Jan 31, 2021 at 22:06

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

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

1 Comment

Batman28 Over a year ago

Thats exactly what i was looking for! thank you so much!

Collectives™ on Stack Overflow

Converting 2D array to a list in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related