Since function splitWithoutEmpties uses some non-built VBA functions, they also must be included in your code for the function to work properly.
Otherwise the following error will occur: Compile error: Sub or Function not defined.
Required functions are listed below. You can get to each function's source code by clicking its name:
When adding the functions above to your VBA project, make sure you haven't done it before. If there are two different public functions with the same name in a single VBA project, the following compilation error is thrown: Compile error: Ambiguous name detected: function_name.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
'**********************************************************************************************************
' Name: splitWithoutEmpties
' Author: mielk | 2014-09-20
'
' Description: Function to split the given string into the array of string by the given separator.
' Function is similar to the VBA built-in function VBA.Split. The only difference is
' that this function excludes all the empty strings from the result array.
'
' Parameters:
' text Text to be splitted into array.
' delimiter Character used to split one string from another.
'
'
' Returns:
' String() Array containing parts of base string delimited by the given character, excluding
' all the empty values.
'
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2014-09-20 mielk Function created.
'**********************************************************************************************************
Public Function splitWithoutEmpties(text As String, delimiter As String) As String()
Const METHOD_NAME As String = "splitWithoutEmpties"
'------------------------------------------------------------------------------------------------------
Dim arr() As String
Dim temp() As String
Dim i As Long
'------------------------------------------------------------------------------------------------------
'Use the VBA built-in function VBA.Split to split text into parts. ----------------------------------|
temp = VBA.Split(text, delimiter) '|
'----------------------------------------------------------------------------------------------------|
'Iterate through the items of the array obtained above and add all non-empty values into result -----|
'array. '|
For i = LBound(temp) To UBound(temp) '|
If Not isEmptyString(temp(i)) Then '|
Call addEntry(arr, temp(i)) '|
End If '|
Next i '|
'----------------------------------------------------------------------------------------------------|
'Assign temporary array to the result of this function.
splitWithoutEmpties = arr
End Function