arraysvbaexcelsubroutine

Passing an array to subroutine VBA


I'm working on a macro for excel and have a sub that passes an array to another sub, but I keep getting

Run time error '9'

Subscript out of range

below is my code and I left a comment pointing to where this error is occurring. I'm new to VBA so it's possible I'm trying to pass an array incorrectly not sure though.

'Main Driver
Sub Main()
    WorkbookSize = size() 'Run function to get workbook size
    newbook = False
    Call create            'Run sub to create new workbook
    Call pull(WorkbookSize)              'Run sub to pull data
End Sub

'Get size of Worksheet
Function size() As Integer
    size = Cells(Rows.Count, "A").End(xlUp).Row
End Function

'Create workbook
Sub create()
    Dim wb As Workbook
    Set wb = Workbooks.Add
    TempPath = Environ("temp")
    With wb
        .SaveAs Filename:=TempPath & "EDX.xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

        .ChangeFileAccess Mode:=xlReadOnly, WritePassword:="admin"
    End With
End Sub

'pull data
Sub pull(size)
    Dim code() As Variant
    For i = 1 To size
    'Check code column fo IN and Doctype column for 810
        If Cells(i, 18).Value = "IN" Then
            code(i) = Cells(i, 18).Value 'store in array
        End If
    Next i
     Call push(code)
End Sub

'push data to new workbook
Sub push(ByRef code() As Variant)
    activeBook = "TempEDX.xlsm"
    Workbooks(activeBook).Activate 'set new workbook as active book
    For i = 1 To UBound(code)   ' <---here is where the error is referencing
        Cells(i, 1).Value = code(i)
    Next i
End Sub

Any help is appreciated.


Solution

  • Your problem is that you don't correctly initialize the code array.

    Do so using Redim See the modification below:

        'pull data
        Sub pull(size)
            Dim code() As Variant
            Redim code(size-1)  '<----add this here minus 1 because 0 index array
            For i = 1 To size
            'Check code column fo IN and Doctype column for 810
                If Cells(i, 18).Value = "IN" Then
                    code(i-1) = Cells(i, 18).Value 'store in array subtract 1 for 0 index array
                End If
            Next i
             Call push(code)
        End Sub
    

    Also, you'll need to update your Push method's code to accommodate the 0-indexed array

    'push data to new workbook
    Sub push(ByRef code() As Variant)
        activeBook = "TempEDX.xlsm"
        Workbooks(activeBook).Activate 'set new workbook as active book
        For i = 0 To UBound(code)   ' <0 to ubound
            Cells(i+1, 1).Value = code(i) 'add 1 to i for the cells reference
        Next i
    End Sub