In Fun with custom lists, Mike Alexander shows us how to access custom lists built into Excel. Let's write a function that returns the custom list as an array.

Looking at a Custom List

The following function returns a given custom list as an array. First it checks that the index is valid.

Function GetCustomList(listNumber As Long) As Variant()

Dim listsCount As Long
Dim tempList() As Variant
Dim listCount As Long
Dim i As Long

  ' get number of lists
  listsCount = Application.CustomListCount

  ' if list requested doesn't exist, exit
  If (listNumber > listsCount Or listNumber <= 0) Then
    GetCustomList = Null
    Exit Function
  End If

  tempList = Application.GetCustomListContents(listNumber)

  GetCustomList = tempList

End Function

Sample usage

The following sample procedure will get each custom list, turn it into a string and print to the Immediate Window. It uses a secondary function called BuildString which is found below.

Sub TestCustomLists()

Dim cList() As Variant
Dim i As Long
Dim j As Long
Dim customList As String

  For i = 1 To Application.customListCount

    ' get next custom list
    cList = GetCustomList(i)

    customList = BuildString(cList)

    Debug.Print customList

  Next i

End Sub

The BuildString function

Function BuildString(arr() As Variant, Optional delimiter As String = ",") _
    As String

' loop through array and build text string consisting
' of all the array elements, delimited as specified
Dim j As Long

  For j = LBound(arr) To UBound(arr)
    BuildString = BuildString & delimiter & arr(j)
  Next j

  ' remove leading delimiter and return string
  BuildString = Right$(BuildString, Len(BuildString) - 1)

End Function

Add Custom Lists

Adding a new custom list is as simple as passing an array to the Application.AddCustomList function. Here's a function that will do so.

Function NewCustomList(arr() As Variant, Optional lr As Boolean = True)

  Application.AddCustomList arr, lr

End Function

Sample usage

The following sample procedure creates a new array and passes it to our custom function above. It uses a function called QuoteString which is found below.

Sub TestNewCustomList()

Dim arr() As Variant

  arr = Array(QuoteString("Old MacDonald had a farm", ","))

  Call NewCustomList(arr)

End Sub

The QuoteString function

The following function will create and return a string array for the Array function in TestNewCustomList above.

Function QuoteString(str As String, delimiter As String) As String()

Dim tempstring() As String
Dim newString As String

  newString = Replace(str, " ", delimiter)

  ' split the string into an array, using delimiter
  tempstring = Split(newString, delimiter)

  QuoteString = tempstring

End Function