Split() and Option Base (VBA Office 2000)

Topic

New Reply

I’m curious as to what will take precedence here. The Split() function is documented as returning a zero-based array. If I use
Option Base 1
in the same module, does the array returned by Split() become a 1-based array?

Alan

Reply | Quote

Replies

This is easily found out by experimenting. I didn’t know the answer either, but a quick test shows that Split ignores the Option Base setting: it always returns a zero-based array.

Reply | Quote

Yes, I was getting the same result, but I was wondering if it was a result of the way I was coding things, or whether it was the “correct behaviour”. Thanks for the confirmation – something I’ll have to keep in mind though. I’d think that similar builtin functions might override this setting too. As you say, try it and see.

cheers

Alan

Reply | Quote

If you look up Split function in VBA Help, this is what is says:

[indent]

---

Description

Returns a zero-based, one-dimensional array containing a specified number of substrings.

---

[/indent]
There are no qualifications or exceptions noted, so it appears the Option Base statement is not a factor. Most VB/VBA functions that return arrays are zero-based, unless specified otherwise.

HTH

Reply | Quote

In further reply, if desired, you can always copy contents of zero-based array into a one-based array if the one-based array is more “intuitive” for what you are doing. Simple example:

Sub TestBaseZeroToBaseOne()

Dim tmp() As String
Dim strTest As String
Dim intCount As Integer
Dim n As Integer

strTest = “A B C D E”

‘ Create zero-based array:
tmp = Split(strTest, Chr$(32), , vbBinaryCompare)

‘ Get number of items in array:
intCount = UBound(tmp) + 1

‘ Test results:
Debug.Print “Base Zero:”
For n = 0 To intCount – 1
Debug.Print n, tmp(n)
Next
Debug.Print ‘ blank line

‘ Pass array to sub by reference:
BaseZeroToBaseOne tmp

‘ Test results – should now be one-based:
Debug.Print “Base One:”
For n = 1 To intCount
Debug.Print n, tmp(n)
Next

Erase tmp

End Sub

Sub BaseZeroToBaseOne(ByRef tmp() As String)

‘ Purpose: copy contents of zero-based string array to one-based array
Dim tmp1() As String
Dim n As Integer
Dim intCount As Integer

‘ Get number of items in array:
intCount = UBound(tmp) + 1

ReDim tmp1(1 To intCount)

For n = 1 To intCount
tmp1(n) = tmp(n – 1)
Next

tmp = tmp1
Erase tmp1

End Sub

In VB/VBA, you can use a function to return an array, but I find it simpler to simply pass the array variable by reference to the sub that modifies the array, then use the results, as illustrated above. Test results, printed to Debug window:

Base Zero:
0 A
1 B
2 C
3 D
4 E

Base One:
1 A
2 B
3 C
4 D
5 E

It may or may not be worth the trouble to convert the array created by Split function to a one-based array.

HTH

Reply | Quote

I know it’s Friday when I can’t understand why this worked:

For n = 1 To intCount
tmp1(n) = tmp(n – 1)
Next

Shouldn’t your For/Next be counting down from the end to avoid overwriting data?

Oops, those are two different arrays. Never mind. More caffeine, please.

Actually, you probably could do it all with one array, as it was passed by reference… but I certainly haven’t tried it to see.

Reply | Quote

You’re right, this could be done with one array, only you can’t change the lower boundary of an array using ReDim Preserve, only the upper boundary. So the array will still be zero-based, but the data will be stored starting at a specified index (1 or other number). Example:

Sub TestBaseZeroToNonZero()

Dim tmp() As String
Dim strTest As String
Dim n As Long
Dim lngIndex As Long

‘ Create zero-based array:
strTest = “A B C”
tmp = Split(strTest, Chr$(32), , vbBinaryCompare)

‘ Test results:
Debug.Print “Base Zero:”
For n = 0 To UBound(tmp)
Debug.Print n & ” ” & tmp(n)
Next
Debug.Print ‘ blank line

‘ Specify new “lower bound” (index where data storage will start):
lngIndex = 1
‘ Pass array to sub by reference:
BaseZeroToNonZero tmp, lngIndex

‘ Test results – data will start at new “lower bound”:
Debug.Print “Base NonZero (” & lngIndex & “):”
For n = 0 To UBound(tmp)
Debug.Print n & ” ” & tmp(n)
Next
Erase tmp

End Sub

Sub BaseZeroToNonZero(ByRef tmp() As String, ByRef lngIndex As Long)

Dim n As Long
Dim lngLBound As Long
Dim lngNewUBound As Long

If lngIndex lngIndex – 1 Then
tmp(n) = tmp(n – lngIndex)
Else
tmp(n) = vbNullString
End If
Next n

End Sub

Sample test results:

Base Zero:
0 A
1 B
2 C

Base NonZero (0):
0 A
1 B
2 C

Base Zero:
0 A
1 B
2 C

Base NonZero (1):
0
1 A
2 B
3 C

Base Zero:
0 A
1 B
2 C

Base NonZero (5):
0
1
2
3
4
5 A
6 B
7 C

As noted in Help, “If you use the Preserve keyword, you can resize only the last array dimension and you can’t change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array…. Similarly, when you use Preserve, you can change the size of the array only by changing the upper bound; changing the lower bound causes an error.” So the example above merely resizes the array and copies the data starting at the new upper bound; a null string is assigned to those elements whose index is less than the specified “lower boundary” index. When looping thru the redimensioned array you could simply ignore the null values.

I don’t know if this is any more efficient than previous example, or whether it’s worth the trouble. The example would have to be modified for arrays of other data types (I tried using a Variant but if using Split function to populate array this resulted in a Type Mismatch error.) Besides, if you’re planning to eventually use VB.NET you may as well get used to working with zero-based arrays….

Reply | Quote

Mark, I would have accepted a third of that, sorry to put you to so much trouble. And I’ve totally gotten used to the idea of 0-based arrays.

Reply | Quote

In view of the potential complications involved with using Option Base 1 I think I’ll also stick with the zero-based arrays I’ve come to know and love/hate. Too many possibilities for errors otherwise, it appears. If I really want to start at 1 (in my homespun arrays) for some reason, like correspondence between item indexes and array subscripts, I just ignore the zero element or use it as a counter or something. Thanks for all the enlightening info in this thread everybody.

Alan

Reply | Quote

In view of the potential complications involved with using Option Base 1 I think I’ll also stick with the zero-based arrays I’ve come to know and love/hate. Too many possibilities for errors otherwise, it appears. If I really want to start at 1 (in my homespun arrays) for some reason, like correspondence between item indexes and array subscripts, I just ignore the zero element or use it as a counter or something. Thanks for all the enlightening info in this thread everybody.

Alan

Reply | Quote

There is a better alternative than using Option Base 1. Option Base 1 affects all your local array declarations.

Instead, you can specify the upper AND lower bounds when declaring your own arrays. This is my preferred approach, since it allows you much more flexibility. For example:

Dim lsNames(10) As String

is equivalent to

Dim lsNames(0 To 10) As String

If you really want to save space (and not use Option Base), declare the lower array bound as 1:

Dim lsNames(1 To 10) As String

The flexibility you get by declaring both array bounds is demonstrated by this example.

Dim lsPay(-10 To 10, 1950 To 2010) As String

I am declaring some Pay arrays that correspond to calendar years. The first subscript corresponds to some existing data, which actually can be negative. I can use the 4 digit year for the second subscript, which makes the code easier to understand.

Reply | Quote

That is very true for standard arrays but you don’t have that option when using Split(), which is fast and easy but returns a single dimension, zero-based array, period.

Reply | Quote

That is very true for standard arrays but you don’t have that option when using Split(), which is fast and easy but returns a single dimension, zero-based array, period.

Reply | Quote

> I can use the 4 digit year for the second subscript, which makes the code easier to understand.

This is potentially very useful in cases where your “index value” is continuous and you otherwise might choose to use a Collection/Dictionary and make that value the key. The methods for dealing with arrays tend to be better than those for dealing with collections. Hmmm…

Reply | Quote

That depends on what you’re doing with them, Jefferson. I use both, and you can do some things with collections (like referencing an item by the key instead of an index) that you can’t do with arrays.

Reply | Quote

Yes, I agree. I can’t think of any applications I’ve done where the index is important, but I’ve used Collection/Dictionary objects as a way to eliminate duplicates (the exact approach escapes me at the moment, but I seem to recall it involves trapping the error for “duplicate key” on .Add).

Reply | Quote

Just in case you find this useful:

I use two functions uboundsafe and lboundsafe so that I can get all the elements of an array regardless of whether it is 0 or 1 based. The code was supplied by a friend and cuts out errors when using ubound and lbound

Function UBoundSafe(arr As Variant) As Long
‘stops errors when array is empty
Dim lngRetVal As Long

lngRetVal = 0

On Error Resume Next
lngRetVal = UBound(arr)

UBoundSafe = lngRetVal

End Function

Function LBoundSafe(arr As Variant) As Long
‘stops errors when array is empty
Dim lngRetVal As Long

lngRetVal = 0

On Error Resume Next
lngRetVal = LBound(arr)

LBoundSafe = lngRetVal

End Function

Reply | Quote

I’m not 100% sure, but I think that code could lead you into error by telling you that you have a one-element array with a 0 base when in fact array(0) would give you an error. Something along the following lines might be a safer test, after which you could use the built-in functions if you get a True back:

Function IsArrayDimensioned(arr As Variant) As Boolean
' Dynamic array has been assigned dimensions: true or false?
Dim lngTest As Long
On Error Resume Next
lngTest = UBound(arr) - LBound(arr)
If Err.Number = 9 Then 'subscript out of range
    Err.Clear
    IsArrayDimensioned = False
Else
    IsArrayDimensioned = True
End If
On Error GoTo 0
End Function

I imagine there are many variations on this theme posted on the web…

Reply | Quote

I’m not 100% sure, but I think that code could lead you into error by telling you that you have a one-element array with a 0 base when in fact array(0) would give you an error. Something along the following lines might be a safer test, after which you could use the built-in functions if you get a True back:

Function IsArrayDimensioned(arr As Variant) As Boolean
' Dynamic array has been assigned dimensions: true or false?
Dim lngTest As Long
On Error Resume Next
lngTest = UBound(arr) - LBound(arr)
If Err.Number = 9 Then 'subscript out of range
    Err.Clear
    IsArrayDimensioned = False
Else
    IsArrayDimensioned = True
End If
On Error GoTo 0
End Function

I imagine there are many variations on this theme posted on the web…

Reply | Quote

Just in case you find this useful:

I use two functions uboundsafe and lboundsafe so that I can get all the elements of an array regardless of whether it is 0 or 1 based. The code was supplied by a friend and cuts out errors when using ubound and lbound

Function UBoundSafe(arr As Variant) As Long
‘stops errors when array is empty
Dim lngRetVal As Long

lngRetVal = 0

On Error Resume Next
lngRetVal = UBound(arr)

UBoundSafe = lngRetVal

End Function

Function LBoundSafe(arr As Variant) As Long
‘stops errors when array is empty
Dim lngRetVal As Long

lngRetVal = 0

On Error Resume Next
lngRetVal = LBound(arr)

LBoundSafe = lngRetVal

End Function

Reply | Quote

Yes, I agree. I can’t think of any applications I’ve done where the index is important, but I’ve used Collection/Dictionary objects as a way to eliminate duplicates (the exact approach escapes me at the moment, but I seem to recall it involves trapping the error for “duplicate key” on .Add).

Reply | Quote

That depends on what you’re doing with them, Jefferson. I use both, and you can do some things with collections (like referencing an item by the key instead of an index) that you can’t do with arrays.

Reply | Quote

> I can use the 4 digit year for the second subscript, which makes the code easier to understand.

This is potentially very useful in cases where your “index value” is continuous and you otherwise might choose to use a Collection/Dictionary and make that value the key. The methods for dealing with arrays tend to be better than those for dealing with collections. Hmmm…

Reply | Quote

Using other than a lower bound of 0 may break code when migrated to VB .NET.
Develop the habit of using a lower bound of 0.

Reply | Quote

This post is giving me the weirdest sense of deja vu…

Reply | Quote

My deja vu on this subject likely tops everyone else’s deja vu.

I was an original member of the committee that developed the ANSI standard for BASIC and I was chair of the subcommitte on matrices.
And, I hosted the April 1976 meeting in Evanson, Illinois at which the “final” decision on whether 0 or 1 should be the lower bound was made.

You would not believe the hours and hours and … hours spent discussing this issue.

The folkes from Dartmouth got their way and a default of 0 was chosen and the Option Base statement was added to the BASIC language.

I’ve been dealing with the 0/1 base issue in BASIC since at least 1974, perhaps earlier.

Reply | Quote

Whatever the history is- one must learn the language at hand- with all its faults, discrepancies, and strengths.

Zero based or one based- any programmer must be aware of what is happening. There have always been exceptions anyway.

Things from the past are interesteing for illustrative purposes, but hardly relevant to today.

Reply | Quote

Whatever the history is- one must learn the language at hand- with all its faults, discrepancies, and strengths.

Zero based or one based- any programmer must be aware of what is happening. There have always been exceptions anyway.

Things from the past are interesteing for illustrative purposes, but hardly relevant to today.

Reply | Quote

My deja vu on this subject likely tops everyone else’s deja vu.

I was an original member of the committee that developed the ANSI standard for BASIC and I was chair of the subcommitte on matrices.
And, I hosted the April 1976 meeting in Evanson, Illinois at which the “final” decision on whether 0 or 1 should be the lower bound was made.

You would not believe the hours and hours and … hours spent discussing this issue.

The folkes from Dartmouth got their way and a default of 0 was chosen and the Option Base statement was added to the BASIC language.

I’ve been dealing with the 0/1 base issue in BASIC since at least 1974, perhaps earlier.

Reply | Quote

This post is giving me the weirdest sense of deja vu…

Reply | Quote

Using other than a lower bound of 0 may break code when migrated to VB .NET.
Develop the habit of using a lower bound of 0.

Reply | Quote

There is a better alternative than using Option Base 1. Option Base 1 affects all your local array declarations.

Instead, you can specify the upper AND lower bounds when declaring your own arrays. This is my preferred approach, since it allows you much more flexibility. For example:

Dim lsNames(10) As String

is equivalent to

Dim lsNames(0 To 10) As String

If you really want to save space (and not use Option Base), declare the lower array bound as 1:

Dim lsNames(1 To 10) As String

The flexibility you get by declaring both array bounds is demonstrated by this example.

Dim lsPay(-10 To 10, 1950 To 2010) As String

I am declaring some Pay arrays that correspond to calendar years. The first subscript corresponds to some existing data, which actually can be negative. I can use the 4 digit year for the second subscript, which makes the code easier to understand.

Reply | Quote

Mark, I would have accepted a third of that, sorry to put you to so much trouble. And I’ve totally gotten used to the idea of 0-based arrays.

Reply | Quote

You’re right, this could be done with one array, only you can’t change the lower boundary of an array using ReDim Preserve, only the upper boundary. So the array will still be zero-based, but the data will be stored starting at a specified index (1 or other number). Example:

Sub TestBaseZeroToNonZero()

Dim tmp() As String
Dim strTest As String
Dim n As Long
Dim lngIndex As Long

‘ Create zero-based array:
strTest = “A B C”
tmp = Split(strTest, Chr$(32), , vbBinaryCompare)

‘ Test results:
Debug.Print “Base Zero:”
For n = 0 To UBound(tmp)
Debug.Print n & ” ” & tmp(n)
Next
Debug.Print ‘ blank line

‘ Specify new “lower bound” (index where data storage will start):
lngIndex = 1
‘ Pass array to sub by reference:
BaseZeroToNonZero tmp, lngIndex

‘ Test results – data will start at new “lower bound”:
Debug.Print “Base NonZero (” & lngIndex & “):”
For n = 0 To UBound(tmp)
Debug.Print n & ” ” & tmp(n)
Next
Erase tmp

End Sub

Sub BaseZeroToNonZero(ByRef tmp() As String, ByRef lngIndex As Long)

Dim n As Long
Dim lngLBound As Long
Dim lngNewUBound As Long

If lngIndex lngIndex – 1 Then
tmp(n) = tmp(n – lngIndex)
Else
tmp(n) = vbNullString
End If
Next n

End Sub

Sample test results:

Base Zero:
0 A
1 B
2 C

Base NonZero (0):
0 A
1 B
2 C

Base Zero:
0 A
1 B
2 C

Base NonZero (1):
0
1 A
2 B
3 C

Base Zero:
0 A
1 B
2 C

Base NonZero (5):
0
1
2
3
4
5 A
6 B
7 C

As noted in Help, “If you use the Preserve keyword, you can resize only the last array dimension and you can’t change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array…. Similarly, when you use Preserve, you can change the size of the array only by changing the upper bound; changing the lower bound causes an error.” So the example above merely resizes the array and copies the data starting at the new upper bound; a null string is assigned to those elements whose index is less than the specified “lower boundary” index. When looping thru the redimensioned array you could simply ignore the null values.

I don’t know if this is any more efficient than previous example, or whether it’s worth the trouble. The example would have to be modified for arrays of other data types (I tried using a Variant but if using Split function to populate array this resulted in a Type Mismatch error.) Besides, if you’re planning to eventually use VB.NET you may as well get used to working with zero-based arrays….

Reply | Quote

In further reply, if desired, you can always copy contents of zero-based array into a one-based array if the one-based array is more “intuitive” for what you are doing. Simple example:

Sub TestBaseZeroToBaseOne()

Dim tmp() As String
Dim strTest As String
Dim intCount As Integer
Dim n As Integer

strTest = “A B C D E”

‘ Create zero-based array:
tmp = Split(strTest, Chr$(32), , vbBinaryCompare)

‘ Get number of items in array:
intCount = UBound(tmp) + 1

‘ Test results:
Debug.Print “Base Zero:”
For n = 0 To intCount – 1
Debug.Print n, tmp(n)
Next
Debug.Print ‘ blank line

‘ Pass array to sub by reference:
BaseZeroToBaseOne tmp

‘ Test results – should now be one-based:
Debug.Print “Base One:”
For n = 1 To intCount
Debug.Print n, tmp(n)
Next

Erase tmp

End Sub

Sub BaseZeroToBaseOne(ByRef tmp() As String)

‘ Purpose: copy contents of zero-based string array to one-based array
Dim tmp1() As String
Dim n As Integer
Dim intCount As Integer

‘ Get number of items in array:
intCount = UBound(tmp) + 1

ReDim tmp1(1 To intCount)

For n = 1 To intCount
tmp1(n) = tmp(n – 1)
Next

tmp = tmp1
Erase tmp1

End Sub

In VB/VBA, you can use a function to return an array, but I find it simpler to simply pass the array variable by reference to the sub that modifies the array, then use the results, as illustrated above. Test results, printed to Debug window:

Base Zero:
0 A
1 B
2 C
3 D
4 E

Base One:
1 A
2 B
3 C
4 D
5 E

It may or may not be worth the trouble to convert the array created by Split function to a one-based array.

HTH

Reply | Quote

If you look up Split function in VBA Help, this is what is says:

[indent]

---

Description

Returns a zero-based, one-dimensional array containing a specified number of substrings.

---

[/indent]
There are no qualifications or exceptions noted, so it appears the Option Base statement is not a factor. Most VB/VBA functions that return arrays are zero-based, unless specified otherwise.

HTH

Reply | Quote

Do not use a base of 1.

VB .NET requires a base of 0, so if you want to facilitate converting VB/VBA to VB.NET, use only 0 as the lower bound.

I’ve got VSTO, but have not yet installed to see the impact in this area.

Reply | Quote

Do not use a base of 1.

VB .NET requires a base of 0, so if you want to facilitate converting VB/VBA to VB.NET, use only 0 as the lower bound.

I’ve got VSTO, but have not yet installed to see the impact in this area.

Reply | Quote

Yes, I was getting the same result, but I was wondering if it was a result of the way I was coding things, or whether it was the “correct behaviour”. Thanks for the confirmation – something I’ll have to keep in mind though. I’d think that similar builtin functions might override this setting too. As you say, try it and see.

cheers

Alan

Reply | Quote

This is easily found out by experimenting. I didn’t know the answer either, but a quick test shows that Split ignores the Option Base setting: it always returns a zero-based array.

Reply | Quote