Error on UBound with Dynamic Array

[Guest]

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

 

[Brendan Reynolds]

There are two different implementations of Access 97-compatible Split
functions at the following URL ...

http://groups.google.com/group/microsoft.public.access/browse_frm/thread/9afd2d3659e99192

 

[Guest]

Talk about taking the wind out of my sails!

I'd still like to know if there's a way to deal with a UBound on a dynamic
array. But thanks for the clue about Split, it'll save me some time.

Jay

 

[Guest]

The reason you are getting this error:
ReDim Preserve arrSplit(UBound(arrSplit) + 1) <-- ERROR occurs here

is that arrSplit has not yet been dimmed as having any dimension. If you
need to use a dynamic array and may be checking the boundries, then you can
ReDim varArray(0) at the beginning of the procedure and you will not have
the error problem.

 

[Douglas J. Steele]

Either that, or you can define a flag that you set to True once it's been
dimensioned, and check that flag:

If booDimensioned Then
ReDim Preserve arrSplit(UBound(arrSplit) + 1)
Else
ReDim arrSplit(0)
booDimensioned = True
End If

BTW, ReDim is an "expensive" operation in terms of resource requirements.
What I typically do is increase the size by, say, 50 elements at a time, and
then do a final ReDim once I know the actual size. Yes, it means you have to
keep track of how many elements you've added, and then ReDim when you've
used up those 50 slots.

 

[Guest]

Adding 50 at a time is a reasonable approach. When possible, I try to
determine the number of elements needed prior to any dimensioning. Sometimes
you can't. In this situation, however, I would count the number of delimiter
characters first and that would be the number of elements needed.

 

[Douglas J. Steele]

An easy way to know how many of specific character exists in a string is to
use Replace to replace that character with a ZLS (zero-length string), then
determine the difference in length of the string.

Of course, the OP is using Access 97, which doesn't have the Replace
function either...

 

[Guest]

For intCtr = 1 To Len(strInput)
If Mid(strInput, intCtr, Len(strDelim)) = strDelim Then
intDelimCount = intDelimCount + 1
End If
Next intCtr

 

[Guest]

Thanks to all three of you. I got more than I expected.

Hadn't realized ReDim was 'expensive'. As expected, the solutions linked to
by Brendan were more elegant then anything I came up with.

Thanks again though. Interesting stuff.

 

[Douglas J. Steele]

Or

intPos = InStr(strInput, strDelim)
Do While intPos > 0
intDelimCount = intDelimCount + 1
intPos = InStr(intPos + 1, strInput, strDelim)
Loop