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.
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