Hello all,

I'm having a problem using UBound with a dynamic array.  Basically, I want
to check the upper bound (size) of an array.  But the array has no dimension,
so I keep getting an error.  But I can't find a way to catch the error, and
set the dimension of the arry to 1.

The reason I'm trying this is I'm writing a Split function to take a
comma-separated string of values and splitting the values into a dynamic
array.  I'm using Access 97, so I don't have a predefined Split function to
use, so I figured this was the easiest way to do it.

*** If anyone can suggest better ways to do it, please let me know.  I'd
love to get some feedback on the function itself, if you're so inclined.  ***

CODE:

Public Function Split(csvString As String) As Variant    
   Dim arrSplit() As Variant   ' array holding string (field) values
   Dim locStart As Long        ' start location of string
   Dim locEnd As Long          ' end location of string
   
   ' itialize variables
   locStart = 1  ' start at beginning of string
       
   Do
       ' find location of comma
       locEnd = InStr(locStart, csvString, ",")
       
       ' resize the array to add the next value
       ReDim Preserve arrSplit(UBound(arrSplit) + 1)   <-- ERROR occurs here
       
       ' if no comma is found
       If locEnd = 0 Then
           
           ' extract the remainder of the string
           arrSplit(UBound(arrSplit)) = Trim(Mid(csvString, locStart))
       
       Else
           
           ' extract the section of the string between the commas
           arrSplit(UBound(arrSplit)) = Trim(Mid(csvString, locStart,
locEnd - locStart))
           
           ' set start to next space after comma
           locStart = locEnd + 1
       
       End If
   
   ' loop while commas delimiters are found in the string
   Loop While locEnd <> 0
   
   Split = arrSplit
   
End Function ' == Split ==

I appreciate any pointers on this one.  I might try to replicate the Split
function (because I like the idea of choosing the delimiter) but that's
future improvements.

Thanks!
Jay