Syntax for executing a function on a range using CTRL-SHIFT-ENTER-CodePudding

Pretty inexperienced with VBA - I'm trying to understand basic syntax here - and I'm generalizing for simplicity's sake.

Given this function intended to accept a SINGLE CELL with a text value of a file path ( Example: "C:\Test\Path" ) and return an array of strings ( Example: {"C","Test","Path"} ) using the Split function with "\" as a delimitator.

Bare in mind - there is most likely a better way to achieve the desired result here - I'm not interested in that. I'm trying to understand VBA syntax more than anything here.

Function SplitPath(rng As Range) As String()
 Dim arr() As String
 Dim str As String
 str = rng.Cells(1, 1).Value
 arr = Split(str, "\")
 SplitPath = arr
End Function

Everything works fine when supplied with a range - such as SplitPath(A1:C1) - in the workbook and pressing enter. Assuming A1 contains a string that is a path - "C:\Test\Path", an array of strings is returned - {"C","Test","Path"} - and adjacent cells are filled with the text values.

It works because the function is hard-coded to pull the first cell's value (1,1) or in our example (A1). Which admittedly feels messy to me, I just don't know how to do it otherwise.

Now for the question - what modifications to this code need to be made in order to allow me to supply a range - such as SplitPath(A1:C1) - then using CRTL-SHIFT-ENTER to have VBA execute the Split on each individual cell in the range, returning an array that contains arrays of strings for each cell. For example if:

A1 = "C:\Test\Path"
B1 = "C:\Next\Path"
C1 = "C:\Last\Path"

and {SplitPath(A1:C1)} was executed in a cell in the workbook using CTRL-SHIFT-ENTER

the return should be

{
 {"C","Test","Path"};
 {"C","Next","Path"};
 {"C","Last","Path"};
}

or I guess

{
 {"C";"Test";"Path"},
 {"C";"Next";"Path"},
 {"C";"Last";"Path"},
}

( to be honest - I don't really understand the difference between arrays with a ',' and arrays with a ';' - other than a basic understanding that it's related to the dimension and you can TRANSPOSE to swap dimensions )

It's probably worth mentioning that I'm not interested in supplying the (A1:C1) range and having VBA 'manually' sort through each cell and create a two-dimensional array to return.

I explicitly want to apply Excel's CTRL-SHIFT-ENTER function within the workbook to the cell containing the split request - effectively allowing me to use the function on a single cell by pressing ENTER and then a range of cells by pressing CTRL-SHIFT-ENTER.

Hopefully I'm explaining myself well - I'm pretty inexperienced with this stuff.

CodePudding user response：

You need to create a 2D array and return that:

Function SplitPath(rng As Range) As String()
    Dim arr() As String
    ReDim arr(1 To rng.Cells.Count, 1 To 100) As String
    Dim t() As String
    Dim str As String
    Dim j As Long
    j = 1
    Dim k As Long
    Dim mx As Long
    
    
    Dim rngEch As Range
    For Each rngEch In rng
        str = rngEch.Value
        t = Split(str, "\")
        For k = 1 To UBound(t)   1
           arr(j, k) = t(k - 1)
           If k > mx Then mx = k
        Next k
        j = j   1
    Next rngEch
    ReDim Preserve arr(1 To UBound(arr, 1), 1 To mx) As String
    SplitPath = arr
End Function

Then depending on the version.

Office 365 put the formula in the upper left of the desired output range and it will spill.

With older versions, one will need to highlight a range of cells the size of the expected out and put the formula in the formula bar then hit Ctrl-Shift-Enter:

This function will take a 2D range and return one row per cell: