Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Split() and Option Base (VBA Office 2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Split() and Option Base (VBA Office 2000)

    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.

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split() and Option Base (VBA Office 2000)

    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

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Split() and Option Base (VBA Office 2000)

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

    <hr>Description

    Returns a zero-based, one-dimensional array containing a specified number of substrings.<hr>
    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

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Split() and Option Base (VBA Office 2000)

    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

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Split() and Option Base (VBA Office 2000)

    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. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    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.

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Split() and Option Base (VBA Office 2000)

    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 < 0 Then
    lngIndex = Abs(lngIndex)
    End If

    ' Determine new Upper bound:
    lngLBound = LBound(tmp)
    lngNewUBound = UBound(tmp) - lngLBound + lngIndex

    ' Only upper bound of array can be changed:
    ReDim Preserve tmp(lngLBound To lngNewUBound)

    For n = lngNewUBound To lngLBound Step -1
    If n > 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....

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Split() and Option Base (VBA Office 2000)

    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. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split() and Option Base (VBA Office 2000)

    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

  10. #10
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split() and Option Base (VBA Office 2000)

    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.

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Marietta, Georgia, USA
    Posts
    296
    Thanks
    9
    Thanked 4 Times in 4 Posts

    Re: Split() and Option Base (VBA Office 2000)

    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.
    Rick Groszkiewicz
    Life is too short to drink bad wine (or bad coffee!)

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Split() and Option Base (VBA Office 2000)

    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.
    Charlotte

  13. #13
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Split() and Option Base (VBA Office 2000)

    > 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...

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Split() and Option Base (VBA Office 2000)

    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.
    Charlotte

  15. #15
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Split() and Option Base (VBA Office 2000)

    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).

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •