very new to VBA.

Suppose I have a 6 by 2 array with values shown on right, and I have an empty 2 by 3 array (excluding the header). My goal is to get the array on the left looks as how it is shown.

(Header) 1   2   3                           1    a
         a   c   e                           1    b                           
         b   d   f                           2    c
                                             2    d
                                             3    e
                                             3    f

Since the array on the right is already sorted, I noticed that it can be faster if I just let the 1st column of the 2 by 3 array take the first 2 values (a and b), the 2nd column takes the following 2 values (c and d), and so on. This way, it can avoid using a nested for loop to populate the left array.

However, I was unable to find a way to populate a specific column of an array. Another way to describe my question is: Is there a way in VBA to replicate this code from python, which directly modifies a specific column of an array? Thanks!

array[:, 0] = [a, b]

CodePudding user response：

Populate Array With Values From Another Array

• It is always a nested loop, but in Python, it is obviously 'under the hood' i.e. not seen to the end-user. They have integrated this possibility (written some code) into the language.

• The following is a simplified version of what you could do in VBA since there is just too much hard-coded data with 'convenient' numbers in your question.

• The line of your interest is:

  PopulateColumn dData, c, sData, SourceColumn
  

  to populate column c in the destination array (dData) using one line of code. It's just shorter, not faster.

• Sure, it has no loop but if you look at the called procedure, PopulateColumn, you'll see that there actually is one (For dr = 1 To drCount).

• You can even go further with simplifying the life of the end-user by using classes but that's 'above my paygrade', and yours at the moment since you're saying you're a noob.

• Copy the code into a standard module, e.g. Module1, and run the PopulateColumnTEST procedure.

• Note that there are results written to the Visual Basic's Immediate window (Ctrl G).

The Code

Option Explicit

Sub PopulateColumnTEST()
    
    Const SourceColumn As Long = 2
    
    ' Populate the source array.
    
    Dim sData As Variant: ReDim sData(1 To 6, 1 To 2)
    
    Dim r As Long
    
    For r = 1 To 6
        sData(r, 1) = Int((r   1) / 2) ' kind of irrelevant
        sData(r, 2) = Chr(96   r)
    Next r
    
    ' Print source values.
    
    DebugPrintCharData sData, "Source:" & vbLf & "R 1 2"
    
    ' Populate the destination array.

    Dim dData As Variant: ReDim dData(1 To 2, 1 To 3)
    
    Dim c As Long
    
    ' Loop through the columns of the destination array.
    For c = 1 To 3
        ' Populate the current column of the destination array
        ' with the data from the source column of the source array
        ' by calling the 'PopulateColumn' procedure.
        PopulateColumn dData, c, sData, SourceColumn
    Next c
    
    ' Print destination values.
    
    DebugPrintCharData dData, "Destination:" & vbLf & "R 1 2 3"
    
End Sub

Sub PopulateColumn( _
        ByRef dData As Variant, _
        ByVal dDataCol As Long, _
        ByVal sData As Variant, _
        ByVal sDataCol As Long)
    
    Dim drCount As Long: drCount = UBound(dData, 1)
    
    Dim dr As Long
    
    For dr = 1 To drCount
        dData(dr, dDataCol) = sData(drCount * (dDataCol - 1)   dr, sDataCol)
    Next dr

End Sub

Sub DebugPrintCharData( _
        ByVal Data As Variant, _
        Optional Title As String = "", _
        Optional ByVal ColumnDelimiter As String = " ")
    
    If Len(Title) > 0 Then Debug.Print Title
    
    Dim r As Long
    Dim c As Long
    Dim rString As String
    
    For r = LBound(Data, 1) To UBound(Data, 1)
        For c = LBound(Data, 2) To UBound(Data, 2)
            rString = rString & ColumnDelimiter & Data(r, c)
        Next c
        rString = r & rString
        Debug.Print rString
        rString = vbNullString
    Next r
    
End Sub

The Results

Source:
R 1 2
1 1 a
2 1 b
3 2 c
4 2 d
5 3 e
6 3 f
Destination:
R 1 2 3
1 a c e
2 b d f

CodePudding user response：

Alternative avoiding loops

For the sake of the art and in order to approximate your requirement to find a way replicating Python's code

    array[:, 0] = [a, b]

in VBA without nested loops, you could try the following function combining several column value inputs (via a ParamArray) returning a combined 2-dim array.

Note that the function

• will return a 1-based array by using Application.Index and
• will be slower than any combination of array loops.

Function JoinColumnValues(ParamArray cols()) As Variant
'Purp: change ParamArray containing "flat" 1-dim column values to 2-dim array !!
'Note: Assumes 1-dim arrays (!) as column value inputs into ParamArray
'      returns a 1-based 2-dim array
    Dim tmp As Variant
    tmp = cols
    With Application 
        tmp = .Transpose(.Index(tmp, 0, 0))
    End With
    JoinColumnValues = tmp
End Function

Example call

Assumes "flat" 1-dim array inputs with identical element boundaries

    Dim arr
    arr = JoinColumnValues(Array("a", "b"), Array("c", "d"), Array("e", "f"))