Passing an array to subroutine VBA

Question

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.

Read this for starters cpearson.com/excel/scope.aspx. code is defined within one procedure and hence is not available to others. — SJR
– SJR, Commented Aug 31, 2018 at 14:25
You'll also wand to put Option Explicit at the top of your module, and declare every variable you're using. — Mathieu Guindon
– Mathieu Guindon, Commented Aug 31, 2018 at 14:28

GisMofx · Accepted Answer · 2023-07-11 15:58:26Z

3

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

edited Jul 11, 2023 at 15:58

answered Aug 31, 2018 at 14:30

GisMofx

1,03412 silver badges36 bronze badges

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

4 Comments

Mathieu Guindon Over a year ago

Beat me to it by 5 seconds =)

Austin Jones Over a year ago

Thanks I've implemented everyone's suggestions and the array is now being passed, but I do have one other problem, it's beyond the scope of my original question, but when outputting the data from my array there is nothing placed in the cells, yet it appears there is a wait as if the array is being looped through. I also tried to display the array to see it's contents and the msgbox is empty.

GisMofx Over a year ago

@AustinJones post a new question and share the link to the new question in these comments. I’ll take a look.

Austin Jones Over a year ago

Thanks, here is my new question stackoverflow.com/questions/52119961/…

QHarr · Accepted Answer · 2018-08-31 17:02:21Z

2

I will add these other points

You are also working with Rows but have Integer as a return for a function risking overflow e.g.

Function size() As Integer

Change to a Long.

You have lots of implicit activesheet references. Get rid of those and give a parent sheet. For example, you could set the sheet in a variable called ws and pass, if required, as a parameter.

E.g.

Public Function size(ByVal ws As Worksheet) As Long
    With ws
        size = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
End Function

As mentioned, put Option Explicit at the top of your code and declare all your variables.

edited Aug 31, 2018 at 17:02

answered Aug 31, 2018 at 14:34

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

1 Comment

Austin Jones Over a year ago

Thanks for the pointers, I have implemented them, coming from JavaScript, theres a bit of a learning curve here.

Collectives™ on Stack Overflow

Passing an array to subroutine VBA

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related