How to parse dynamic array arguments to a variable in excel vba

Question

※ This question is a continuation of below problem

How to use nested loop for a Matrix cell in excel vba

I would like to pass dynamic Array(arguments), and i was trying below, but it is not working correctly. Could you please help me.

    Dim StartrowArr, Startrow1Arr, J As Integer
    Dim flRow, dtRow As String

    Set filRng = Worksheets("Sheet1").Range("C1:C50")
    Set dtlRng = Worksheets("Sheet1").Range("F1:F50")

    For Each cell In filRng
        If cell.Value <> "" Then
            ftRow = ftRow & cell.Row & ","
        End If
    Next cell
    ftRow = Left(ftRow, Len(ftRow) - 1)
    Debug.Print ftRowNo

    For Each cell In dtlRng
        If cell.Value <> "" Then
            dtRow = dtRow & cell.Row & ","
        End If
    Next cell
    dtRow = Left(dtRow, Len(dtRow) - 1)
    Debug.Print dtRow

    StartrowArr = Array(filRowNo) ※ dynamic array args
    Startrow1Arr = Array(dtlRowNo)

but after initializing Array(args) like above, it returns error 1004 on [Startrow1, init with 0]. I also tried CInt(ftRow) to typecast to Integer from String. nothings changed.
Moreover, log shows that Startrow, Startrow1 got values like below.

    Startrow: 2, 4, 7
    Startrow1: 2611 ※ should be 2, 6, 11

However, If I initialize the StartrowArr = Array(2,4,7), statically. it works perfect.
How can I pass the arguments correctly to StartrowArr and Startrow1Arr.

What are those arguments mapRowNo and tcRowNo you're passing to Array() ? — Tim Williams
– Tim Williams, Commented Aug 4, 2017 at 2:10

Dy.Lee · Accepted Answer · 2017-08-04 02:49:28Z

0

To making Dynamic array, use redim preserve .

Sub test()
    Dim StartrowArr(), Startrow1Arr(), J As Integer
    Dim flRow, dtRow As String
    Dim Cell As Range
    Dim k As Long, n As Long

    Set filRng = Worksheets("Sheet1").Range("C1:C50")
    Set dtlRng = Worksheets("Sheet1").Range("F1:F50")

    For Each Cell In filRng
        If Cell.Value <> "" Then
            'ftRow = ftRow & cell.Row & ","
             ReDim Preserve StartrowArr(k)
             StartrowArr(k) = Cell.Row
             k = k + 1
        End If
    Next Cell
    'ftRow = Left(ftRow, Len(ftRow) - 1)
    Debug.Print Join(StartrowArr, ",")

    For Each Cell In dtlRng
        If Cell.Value <> "" Then
            'dtRow = dtRow & Cell.Row & ","
             ReDim Preserve Startrow1Arr(n)
             Startrow1Arr(n) = Cell.Row
             n = n + 1
        End If
    Next Cell
    'dtRow = Left(dtRow, Len(dtRow) - 1)
    Debug.Print Join(Startrow1Arr, ",")

    'StartrowArr = Array(mapRowNo) '※ dynamic array args
    'Startrow1Arr = Array(tcRowNo)
End Sub

answered Aug 4, 2017 at 2:49

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

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

1 Comment

DIO Over a year ago

Thank You Dy Lee. It was awesome. Perfectly worked. <br/>I was also trying almost very near to your solution but could not manipulate my code properly and messed.

Collectives™ on Stack Overflow

How to parse dynamic array arguments to a variable in excel vba

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related