String to Decimial with Split function

Question

In VBA I wrote this

Function SPLITTER(data As String, delimiter As String) As String()
    SPLITTER = Split(data, delimiter)
End Function

Which works great for me

However, SPLITTER returns strings but I will exclusively use it for numbers. I now that I return it As String () but using any other type didnt work for me. I guess it interferes with the Split function

I use that function to automize things to converting the value of the cells to numbers manually does not work for me. Is there a way to let the function return the values as decimal?

Sorry if I may be incorrect in my terminology but I'm new to VBA.

Answer 1

Either you use the VALUE formula as @norie suggested or you need to change your function so it converts the strings you get form Split into Double values.

Note that this cannot be done for the whole array at once and you have to convert each value. This might be slightly slower (on huge data or extensive use of the function) than using the VALUE formula. For small data you won't see a difference.

Option Explicit

Public Function SPLITTER(ByVal Data As String, ByVal Delimiter As String) As Variant()
    ' split strings
    Dim SplittedStrings() As String
    SplittedStrings = Split(Data, Delimiter)
    
    ' create variant array of the same size (variant so we can return errors)
    Dim Values() As Variant
    ReDim Values(LBound(SplittedStrings) To UBound(SplittedStrings)) As Variant
    
    ' convert each value into double
    Dim i As Long
    For i = LBound(Values) To UBound(Values)
        If IsNumeric(SplittedStrings(i)) Then
            ' return value as double
            Values(i) = CDbl(SplittedStrings(i))
        Else
            ' return #VALUE! error if a value is not numeric
            Values(i) = CVErr(xlErrValue)
        End If
    Next i
    
    SPLITTER = Values
End Function

Note that the return array is defined as As Variant() and not As Double() so in case any of the splitted strings is not numeric it can return a #VALUE! error for this one value and still output the others. If you don't do that the whole function fails and does output #VALUE! for all values even if only one cannot be converted.

---

Since you re-write the whole array anyway you could even bring it into the correct direction: Output as row or as column:

Option Explicit

Public Function SPLITTER(ByVal Data As String, ByVal Delimiter As String, Optional ByVal OutputAsRow As Boolean = False) As Variant()
    ' split strings
    Dim SplittedStrings() As String
    SplittedStrings = Split(Data, Delimiter)
    
    ' create variant array of the same size (variant so we can return errors)
    Dim Values() As Variant
    If OutputAsRow Then
        ' 2-dimensional array with 1 row and n columns
        ReDim Values(1 To 1, LBound(SplittedStrings) To UBound(SplittedStrings)) As Variant
    Else
        ' 2-dimensional array with n rows and 1 column
        ReDim Values(LBound(SplittedStrings) To UBound(SplittedStrings), 1 To 1) As Variant
    End If
    
    ' convert each value into double
    Dim i As Long
    For i = LBound(SplittedStrings) To UBound(SplittedStrings)  ' for each value in the input string string
        Dim RetVal As Variant
        If IsNumeric(SplittedStrings(i)) Then  ' check if it is a number
            ' return value as double
            RetVal = CDbl(SplittedStrings(i))
        Else
            ' return #VALUE! error if a value is not numeric
            RetVal = CVErr(xlErrValue)
        End If
        
        If OutputAsRow Then
            Values(1, i) = RetVal  ' fill columns
        Else
            Values(i, 1) = RetVal  ' fill rows
        End If
    Next i

    SPLITTER = Values
End Function

So =SPLITTER($A$1,";") or =SPLITTER($A$1,";",0) will output it as a column and =SPLITTER($A$1,";",1) as a row.

Answer 2

You could return numbers without changing the function by adding VALUE to the formula.

=VALUE(TRANSPOSE(SPLITTER(D5,";")))