CODEWITHSUNDEEP
excelvba
Graeme Wilkinson
Graeme Wilkinson

Reputation: 419

Passing array through a function in vba

I'm trying to call a function to process some data in an array, I will be duplicating this for lots of different reasons, so just want to get the basic foundations right and I'm getting errors with data types. I've simplified my code to try and get it going from the ground up but still can't find the cause.

 Sub VBA_Split_Print()
    Dim last_row As Long
    last_row = Cells(Rows.Count, 1).End(xlUp).Row
    Range("J2:AE90000").ClearContents
    For J = 2 To last_row
        Dim arr() As String
        arr = Split(Worksheets("Raw").Cells(J, 9).Value, ",")
        Call ElectiveAdd(arr)
    Next J
 End Sub


Function ElectiveAdd(ByRef arr() As String)
    
    Dim arrLength As Integer
    arrLength = UBound(arr, 1) - LBound(arr, 1)
    Dim x As Integer
    x = 0
    For i = 24 To (arrLength + 24)
        Worksheets("Raw").Cells(J, i).Value = arr(x)
        x = x + 1
       Next i

End Function

When I'm trying to run this I am getting an Run-Time Error '1004' Application-Defined or Object-Defined Error message.

So revised code due to feedback and I feel that J is another issue so I have excluded it, thanks for pointing out the error though!

Sub VBA_Split_Print()
    Dim last_row As Long
    last_row = Cells(Rows.Count, 1).End(xlUp).row
    
    Range("J2:AE90000").ClearContents
    Dim arr() As String
    arr = Split(Worksheets("Raw").Cells(2, 9).Value, ",")
    Call ElectiveAdd(arr)
End Sub

Function ElectiveAdd(ByRef arr() As String)
    
    Dim arrLength As Integer
    arrLength = UBound(arr, 1) - LBound(arr, 1)
    Dim x As Integer
    x = 0
    For i = 24 To (arrLength + 24) + 1
        Worksheets("Raw").Cells(2, i).Value = arr(x)
        x = x + 1
       Next i

End Function

Now I am getting a different error message of subscript out of range, cell 2,9 = "enter image description here

Upvotes: 0

Views: 82

Answers (1)

norie
norie

Reputation: 9857

Instead of passing the array, why not pass the cell?

Option Explicit

Sub VBA_Split_Print()
Dim last_row As Long
Dim I As Long
Dim arr() As String

    With Worksheets("Raw")
        last_row = .Cells(Rows.Count, 1).End(xlUp).Row

        .Range("J2:AE90000").ClearContents

        For I = 2 To last_row
            Call ElectiveAdd(Worksheets("Raw").Cells(I, 9))
        Next I

    End With
End Sub

Function ElectiveAdd(ByRef rng As Range)
Dim arr As Variant
Dim I As Long

    arr = Split(rng.Value, ",")

    For I = LBound(arr, 1) To UBound(arr, 1)
        rng.Parent.Cells(rng.Row, 24 + I).Value = arr(I)
    Next I

End Function

If you still want to pass the array you'll probably need to pass the row as well so the data goes in the right place.

Option Explicit

Sub VBA_Split_Print()
Dim last_row As Long
Dim I As Long
Dim arr() As String

    With Worksheets("Raw")
        last_row = .Cells(Rows.Count, 1).End(xlUp).Row

        .Range("J2:AE90000").ClearContents

        For I = 2 To last_row
            arr = Split(Worksheets("Raw").Cells(I, 9), ",")
            Call ElectiveAdd(arr, I)
        Next I

    End With
End Sub

Function ElectiveAdd(ByRef arr As Variant, rw As Long)
Dim I As Long

    For I = LBound(arr, 1) To UBound(arr, 1)
        Worksheets("Raw").Cells(rw, 24 + I).Value = arr(I)
    Next I

End Function

Upvotes: 1

Related Questions