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.

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:

• Line 7: declare a dynamic array named PoA (PositionArray) of type Integer. The array is not initialized until line 14
• Line 8: declare a dynamic array named SpA (SplitArray) of type String. The array is not initialized until line 15
• Line 10: assign the test string to the InS variable. A comma separated string of numeric values
• Line 11: assign the test delimiter to the Delimiter variable. A comma ","
• Line 12: count the number of delimiters (and assign value to NoChars) by counting the characters in InS compared to the number of characters in InS after the delimiter "," is removed (replaced by ""). NoChar: = 4 (22 - 18)
• Line 14: ReDim the PoA array with 5 elements (4 delimiters means 5 elements), indexed 0 to 4. We use LBound(PoA) = 0 because the first value does not have a preceding delimiter
• Line 15: ReDim the SpA array with 5 elements, indexed 1 to 5.
• Lines 17 to 20: the position of delimiters (PoA) loop
• Line 17: Loop (For...Next) from 1 to 4 (4 delimiters). The variable j is initialized with value 0 (in line 6)
• Line 18: [i=1, j=0] Start at position 1, find the first occurrence of a comma in InS. The return value of 3 is assigned to PoA(1) and the variable j
• • [i=2, j=3] Start at position 4, find the first occurrence of a comma in InS. The return value of 7 is assigned to PoA(2) and the variable j and the loop continues. On completion, PoA has values PoA(0): 0, PoA(1): 3, PoA(2): 7, PoA(3): 13, PoA(4): 18
• Lines 22 to 25: the delimited values to array elements (SpA) loop
• Line 22: Loop (For...Next) from 1 to 4 (4 delimiters). The variable k is initialized with value 0 (in line 6). The 5th value does not have a trailing delimiter, so its value is assigned to the SpA array in code line 27
• Line 23: [i=1, k=0] Using the VBA Mid function, start at position 1 (0 + 1), and return 2 (3 - 0 - 1) characters from the InS string. The return value of 42 is assigned to SpA(1) and the value in PoA(1) 3, is assigned to the variable k
• • [i=2] start at position 4 (3 + 1), and return 3 (7 - 3 - 1) characters from the InS string. The return value of " 42" then has the leading space removed with the VBA Trim function, and "42" is assigned to SpA(2) and the value in PoA(2) 7, is assigned to the variable k and the loop continues. On completion of the For...Next loop, SpA has values SpA(1): 42, SpA(2): 40, SpA(3): 0.05, SpA(4): 0.2. The loop index i has value 5
• Lines 26: [k=18] - process the last value without a trailing delimiter. Start at position 19 (18 + 1) and return 4 (22 - 18) characters. The return value is " 0.5". Then use the Trim function to remove the leading space character, and assign "0.5" to SpA(5)

2. The xlfSplit function

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

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):

• Line 1: declare a function procedure named xlfSplit with compulsory argument Instring of type String, and optional argument Delimiter of type Variant.
• • The default value of a type Variant is Missing. This value is then processed by the IsMissing function in line 11.
• • The function return type is also of type Variant. This allows for an array assignment in line 28

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

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

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

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

• 
  

  ---

  
  
• This example was developed in Excel 2013, and VBA 7.1
• Published: 6 October 2015
• Revised: Saturday 25th of February 2023 - 09:37 AM, [Australian Eastern Standard Time (EST)]

Copyright © 2011 – 2025 ♦ Ian O'Connor, Central Gippsland   |   Privacy policy

Comment & Reply policy