Sì, quando dichiari l'array devi specificarne la dimensione, questo causa errori qualora il numero di elementi della stringa sia superiore alla dimensione specificata.
Potresti usare il comando ReDim per ridimensionare il vettore (array).
Questo però prevede che tu sappia già proma quanti saranno gli elemeti della stringa (per intenderci devi prima contarli).
Per evitare tutto ciò potresti usare un oggetto collection.
Ecco una funzione che ho scrittto che divide una stringa con un separatore e ne inserisce gli elementi in una collection.

codice:
Public Function f_Lista(lista As String, Optional Separatore As String) As Collection
    Dim pos As Integer
    Dim ipos As Integer
    Dim lung As Integer
    Dim campo As String
    Set f_Lista = Nothing
    Set f_Lista = New Collection
    If Separatore = "" Then Separatore = ","
    pos = 0
    ipos = 1
    Do
       pos = InStr(pos + 1, lista, Separatore, vbTextCompare)
       If pos = 0 Then
          lung = (Len(lista) - ipos) + 1
        Else
          lung = pos - ipos
       End If
       campo = Mid(lista, ipos, lung)
       f_Lista.Add campo
       ipos = pos + 1
    Loop Until pos = 0
End Function
Spero di essere stato d'aiuto, fammi sapere!!
CIAO