Passing array through a function in vba

Question

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 = "

Most likely J in Worksheets("Raw").Cells(J, i).Value = arr(x) = 0. — BigBen
– BigBen, Commented Aug 3, 2021 at 16:09
Thanks @BigBen - so I've taken the loop out and made it focus on 2,9 instead, which contains the value "Happy,Dull,Priceless" so it should be returning an Array of 3 strings? — Graeme Wilkinson
– Graeme Wilkinson, Commented Aug 3, 2021 at 16:14
Where have you declared J? What do you expect J to equal in Worksheets("Raw").Cells(J, i).Value = arr(x)? What happens if you add Option Explicit to the very top of the module? Does it flag J as an undeclared variable? — BigBen
– BigBen, Commented Aug 3, 2021 at 16:19

norie · Accepted Answer · 2021-08-03 16:45:12Z

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

Collectives™ on Stack Overflow

Passing array through a function in vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related