VBA function xlfSplit

xlfSplit :: procedures - VBA code


Split a delimited string and assign values to a one-dimensional array

This module provides a variation of the VBA split function, using components of VBA code. It demonstrates a group of VBA functions, loops, and arrays.


1. The VBA code

The procedure in code 1 is used for initial testing and development. Running the procedure with F5, the inclusion of a Stop statement (to suspend execution of the code), and Locals Window allows easy inspection of the array elements.



Code 1: Sub procedure xlfSplitMacro
Sub xlfSplitMacro()
Dim InS As String        ' InString
Dim Delimiter As String
Dim NoChars As Integer

Dim i As Integer, j As Integer, k As Integer
Dim PoA() As Integer     ' PositionArray
Dim SpA() As String      ' SplitArray
    
    InS = "42, 40, 0.05, 0.2, 0.5"
    Delimiter = ","
    NoChars = Len(InS) - Len(Replace(InS, Delimiter, ""))
    
ReDim PoA(0 To NoChars)
ReDim SpA(1 To NoChars + 1)
    
    For i = 1 To UBound(PoA)
        PoA(i) = InStr(j + 1, InS, Delimiter)
        j = PoA(i)
    Next i
    
    For i = 1 To UBound(PoA)
        SpA(i) = Trim(Mid(InS, PoA(i - 1) + 1, PoA(i) - PoA(i - 1) - 1))
        k = PoA(i)
    Next i
        SpA(i) = Trim(Mid(InS, k + 1, Len(InS) - k))  ' last element (no ending delimiter)
    Stop
    
End Sub

Code 1 is a two stage procedure - the position of each delimiter is determined, then the string is parsed / split based on the position values.


Code 1 by line number:



2. The xlfSplit function

In this section, the code 1 macro is converted to a function procedure (code 2).



Code 2: Function procedure xlfSplit - returns a one-based, one-dimensional array of strings
Function xlfSplit(InString As String, Optional Delimiter As Variant) As Variant
'' Split text string on optional delimiter
'' Delimiter default: space character
'' Returns a one-based, one-dimensional array of strings

Dim NoChars As Integer
Dim i As Integer, j As Integer, k As Integer
Dim PoA() As Integer     ' Delimiter PositionArray
Dim SpA() As String      ' SplitArray

    If IsMissing(Delimiter) Then Delimiter = " "
    NoChars = Len(InString) - Len(Replace(InString, Delimiter, ""))
    
ReDim SpA(1 To NoChars + 1)
ReDim PoA(0 To NoChars)
    
    For i = 1 To UBound(PoA)
        PoA(i) = InStr(j + 1, InString, Delimiter)
        j = PoA(i)
    Next i
    
    For i = 1 To UBound(PoA)
        SpA(i) = Trim(Mid(InString, PoA(i - 1) + 1, PoA(i) - PoA(i - 1) - 1))
        k = PoA(i)
    Next i

	SpA(i) = Trim(Mid(InString, k + 1, Len(InString) - k))
    xlfSplit = SpA
    
End Function

Code 2 by line number (selected components):


3. Function testing

This section sends a test string to the xlfSplit function (code 3 - lines 1 to 6), and to the VBA Split function (code 3 - lines 9 to 14). The output of each test macro is shown in the Locals Window view in figure 1



Code 3: Sub procedures Test macros
Sub TestxlfSplit()
Dim ReturnV() As String
	'' Returns a one-based, one-dimensional array of strings
    ReturnV = xlfSplit("42, 40, 0.05, 0.2, 0.5", ",")

End Sub

'' ====================================
Sub TestSplit()
Dim ReturnV As Variant
	'' Returns a zero-based, one-dimensional array of substrings
    ReturnV = Split("42, 40, 0.05, 0.2, 0.5", ",")

End Sub


xlf-split-sub-array
Fig 1: Locals Window: (top panel) - code 3 - array of strings - lines 1 to 6 , and (bottom panel) code 3 - array of substrings - lines 9 to 14

The array of substrings in TestSplit is produced by the Dim ReturnV As Variant in code 3 line 10. If this was written as Dim ReturnV() As String, the same as code 3 line 2, then an array of strings would be returned instead.


2. VBA functions


FunctionDescription
InStr([start, ]string1, string2[, compare])Returns a Variant of subtype Long, specifying the position of the first occurrence of one string within another
Len(string)Returns a Long containing the number of characters in a string
Mid(string, start[, length])Returns a Variant of subtype String containing a specified number of characters from a specified starting position in a string
Replace(expression, find, replace[, start[, count[, compare]]])Returns a string where a specified substring has been replaced with another substring
Split(expression[, delimiter[, limit[, compare]]])Returns a zero-based, one-dimensional array of substrings
Trim(string)Returns a string with the leading and trailing spaces removed
Ubound(arrayname[, dimension])Returns a Long containing the largest available subscript for the specified dimension of an array
Val(string)Returns the numbers (values) contained in a string as a numeric value

Related materials

  1. About the Black-Scholes model
  2. A sequence of input boxes with the Black-Scholes model
  3. Coding Box Cancel buttons - with message boxes and input boxes