excel-vba Excel VBA Tips and Tricks Using Strings with Delimiters in Place of Dynamic Arrays
Example
Using Dynamic Arrays in VBA can be quite clunky and time intensive over very large data sets. When storing simple data types in a dynamic array (Strings, Numbers, Booleans etc.), one can avoid the ReDim Preserve statements required of dynamic arrays in VBA by using the Split() function with some clever string procedures. For example, we will look at a loop that adds a series of values from a range to a string based on some conditions, then uses that string to populate the values of a ListBox.
Private Sub UserForm_Initialize()
Dim Count As Long, DataString As String, Delimiter As String
For Count = 1 To ActiveSheet.UsedRows.Count
If ActiveSheet.Range("A" & Count).Value <> "Your Condition" Then
RowString = RowString & Delimiter & ActiveSheet.Range("A" & Count).Value
Delimiter = "><" 'By setting the delimiter here in the loop, you prevent an extra occurance of the delimiter within the string
End If
Next Count
ListBox1.List = Split(DataString, Delimiter)
End Sub
The Delimiter string itself can be set to any value, but it is prudent to choose a value which will not naturally occur within the set. Say, for example, you were processing a column of dates. In that case, using ., -, or / would be unwise as delimiters, as the dates could be formatted to use any one of these, generating more data points than you anticipated.
Note: There are limitations to using this method (namely the maximum length of strings), so it should be used with caution in cases of very large datasets. This is not necessarily the fastest or most effective method for creating dynamic arrays in VBA, but it is a viable alternative.
