Function gives Value error when returning array of arrays

Question

I am trying to create a TextSplit function in Excel that can accept either a single reference or a range.
If it is a single string it returns an array of sub strings.
If it is a range it should return an array of sub string arrays.

A single string works but when I pass it a single column range it give me a #VALUE! error.

The commented lines work.
If I store the result of Array to arr Excel displays a grid of "test" strings.
If instead I set TextSplit to just arr(1) I get a single array of substrings similar to the single string version.

Function TextSplit(text, delimiter)
If IsArray(text) Then
    Dim arr() As Variant: ReDim arr(0 To text.Count - 1)
    For i = 1 To text.Count
        arr(i-1) = Split(text(i), delimiter)
        'arr(i-1) = Array("test", "test")
    Next
    TextSplit = arr
    'TextSplit = arr(1)
Else
    TextSplit = Split(text, delimiter)
End If

How are you actually calling it? If from a cell, it's unlikely to work as is since you have a jagged array. — Rory
– Rory, Commented Oct 20, 2022 at 15:38
You'd need to return a single 2D array sized to (# of input texts) x (max. size of arrays from split texts) — Tim Williams
– Tim Williams, Commented Oct 20, 2022 at 16:23
Yes, I am calling it from a cell as a function. So what you are all saying is that it needs to have a set column width, the problem is that there could be variable lengths in the split texts array (there isn't in my test data, but VBA doesn't know that). I will try that. Thank you. — user1509130
– user1509130, Commented Oct 21, 2022 at 16:05

Notus_Panda · Accepted Answer · 2022-10-21 13:56:10Z

0

With the help of a different question Array and Split commands to create a 2 dimensional array

I was able to work your question out a bit, however I'm still unable to fill out the array from the cell where you'd call the function like with your single string which fills out in the columns next to it. If it's for a column, you could just autofill text.split(cell,delimiter) if you're working from Excel.

If you're working from out vba and want to return the split array (2D like @Tim said) back to a sub:

Sub testingTextSplitter()
    Dim arr As Variant, tArr As Variant
    Dim testStr As String
    
    testStr = Range("A1").Value 'Testing single cell
    Range("G2").Value = TextSplit(testStr, "-")
    arr = Range("A1:A8").Value
    tArr = TextSplit(arr, "-")
    For i = 0 To UBound(tArr, 1)
        For j = 0 To UBound(tArr, 2)
            Cells(i + 3, j + 3).Value = "'" & tArr(i, j) 'fills out from Range("C3"), adjust as needed
' This writing out is basically the same as fillingdown the formule of text.split() btw
        Next j
    Next i
    
End Sub

With the Function

Function TextSplit(tArray As Variant, delimiter As String) As String()

    If IsArray(tArray) Then
        Dim uBoundInput As Long, uBoundCells As Long 'I couldn't get your arr.Count to work on my end so gotta use the UBound
        Dim arr() As String, testArr() As String
        Dim i As Long, j As Long, maxColumns As Long
        
        uBoundInput = UBound(tArray)
        
        maxColumns = 0
        For i = 0 To uBoundInput - 1
            Debug.Print (tArray(i + 1, 1))
            testArr = Split(tArray(i + 1, 1), "-")
            uBoundCells = UBound(testArr)
            If maxColumns < uBoundCells Then
                maxColumns = uBoundCells
            End If
        Next i
        
        ReDim arr(0 To uBoundInput - 1, 0 To maxColumns)
        
        For i = 0 To uBoundInput - 1
            testArr = Split(tArray(i + 1, 1), "-")
            For j = 0 To UBound(testArr)
                arr(i, j) = testArr(j)
            Next j
        Next i
        TextSplit = arr()
    Else
        TextSplit = Split(tArray, delimiter)
    End If
End Function

I'm quite new to VBA as well so apologies in advance for redundancies like not filling testArray when figuring out the maxColumns, I couldn't figure that one out. First time working with 2D arrays.

Other question that might help: VBA UDF Return Array (I tried using the array formulay with {} but got same Value error as before)

Hope this helps.

edited Oct 21, 2022 at 13:56

answered Oct 21, 2022 at 13:50

Notus_Panda

2,8112 gold badges8 silver badges21 bronze badges

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

2 Comments

user1509130 Over a year ago

Thanks for this answer. I will take a look at some of this code to see what works. About the autofill suggestion, I am trying to return an array because ultimately I need to do multiple splits on multiple delimiters. The first split would create new rows, the second split creates the columns. VBA from a cell function does not support inserting rows (from what I have read) so I am using the array. Thanks for the suggestion.

Notus_Panda Over a year ago

If that doesn't pan out, you could write a sub that works from a selection (or asks the range), asks the delimiter(s) and fills out the result in the destinated start cell. And you're correct, a function can only return a value, not change cells

user1509130 · Accepted Answer · 2022-10-26 17:21:17Z

I don't know what happened, but the array branch of my code is now working. I have been messing with a few things, but I am not sure why it is working. The "As Variant()" declaration is new from the above code, but that may have been omitted before. (This code is on my work machine but I wrote the original post from my personal computer so I couldn't copy and paste. I am on my work computer now.)

The only other change that I made was to the index values of the arr array.

Thanks for your help, not sure what was wrong or how it got fixed though.

Function TextSplit(text, delimiter) As Variant()
If IsArray(text) Then
    Dim arr() As Variant: ReDim arr(1 To text.Count)
    For i = 1 To text.Count
        arr(i) = Split(text(i), delimiter, -1, 1)
    Next
    TextSplit = arr
Else
    TextSplit = Split(text, delimiter, -1, 1)
End If
End Function

Collectives™ on Stack Overflow

Function gives Value error when returning array of arrays

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related