Option Base 1 is not working

TotallyConfused · Oct 22, 2018

Hello all you MrExcel experts

To try to better familiarize myself with arrays, I set up the following simple macro using Windows 10 and Office 365. You will notice that I have commented out the command of ‘Option Base 1’. With this done, and I run the macro, I get the word ‘for’ displayed, which I what I should receive. This tells me that everything is working okay. Now, here is where my problem starts. If I activate the ‘Option Base 1’ command by removing the apostrophe, and I make NO OTHER changes, when I run the macro, I now receive the word ‘for’ once again. My understanding of the command ‘Option Base 1’ is that I should now have received the word ‘is’. In the line of Msgbox MyArray(5), I can substitute any number I want, but the result is that whatever is returned, is always one array position more than it should be. To see what would happen, and with the ‘Option Base 1’ active (no leading apostrophe), I changed the ‘5’ to a ZERO. I now received the word ‘This’. It’s as if Excel is simply ignoring the ‘Option Base 1’ line, whether it is commented out or not. What am I doing wrong?

Code:

'Option Base 1
Sub Testing()
    Dim MyData As String
    Dim MyArray() As String
  MyData = "This line of data is for testing the code."
  MyArray() = Split(MyData, " ")
MsgBox MyArray(5)
End Sub

I will appreciate any help you may be able to offer. THANK YOU in advance.

TotallyConfused

=ODIN= · Oct 22, 2018

I'm pretty sure it's the split function that you're using does not care what the option base 1 says. A couple things that will change your life when working with arrays:

1. When cycling through them, don't worry about option base 1. if you want to cycle through the whole thing do it like this:

Code:

for q = lbound(arrayName, arrayDimension#)  to ubound(arrayName, arrayDimension#)

next q

2. to see what's in you're array, use the Locals Window! If you turn that window on and then step through your code using the F8 key until right after this line:

Code:

[COLOR=#333333]  MyArray() = Split(MyData, " ")[/COLOR]

you can actually click the + sign in the window and see everything loaded into the array -- no more msgboxing...

joeu2004 · Oct 22, 2018

Perhaps if you read the help page for the Split function, you would understand, to wit:

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

In contrast, experiment with the Array function. Or read the help page, to wit:

``The lower bound of an array created using the Array function is determined by the lower bound specified with the Option Base statement, unless Array is qualified with the name of the type library (for example VBA.Array). If qualified with the type-library name, Array is unaffected by Option Base``

MSFT documentation is notoriously incomplete, if not misleading or just plain wrong. I usually experiment in order to confirm or learn the true behavior.

PS.... I might also note that MSFT products are notoriously inconsistent. Again, I usually experiment first.

And as "odin" wrote, when in doubt, use LBound and UBound.

TotallyConfused · Oct 24, 2018

=ODIN= said:
I'm pretty sure it's the split function that you're using does not care what the option base 1 says. A couple things that will change your life when working with arrays:

1. When cycling through them, don't worry about option base 1. if you want to cycle through the whole thing do it like this:

Code:

for q = lbound(arrayName, arrayDimension#) to ubound(arrayName, arrayDimension#) next q

2. to see what's in you're array, use the Locals Window! If you turn that window on and then step through your code using the F8 key until right after this line:

Code:

[COLOR=#333333] MyArray() = Split(MyData, " ")[/COLOR]

you can actually click the + sign in the window and see everything loaded into the array -- no more msgboxing...

Hi Odin

My apologies for not getting back to you sooner. Things have been a bit hectic around here lately. I wanted to THANK YOU for your prompt and very helpful reply to my question about not getting the Option Base 1 command to work. Your suggestion of using ‘Lbound’ is a very good one because with it, I don’t have to be concerned with the location of the lowest element. I can let Excel worry about that.

Thanks also for your suggestion of using the ‘Locals Window’. I’ve known about that feature but hardly ever used it, which is something I need to change. I wasn’t aware of the ability to view all the contents of an array by clicking on the ‘+’ sign. That is marvelous. Let me see the contents of the whole array at once rather than one element at a time by use of Msgbox. That is a tip I’ll remember and use. THANKS AGAIN for your help.

TotallyConfused

TotallyConfused · Oct 24, 2018

Hi Joeu2004
I’m sorry I didn’t get a chance to properly say THANK YOU for your help in solving my problem with the non-working Option Base 1 command before this. I had first encountered that ‘Split’ function in an earlier post to another member. Because I do a lot of work with customer’s names, etc., and quite often need to be able to pull out their first name, it occurred to me that if I understood how to use this command, it might be very useful. That’s why I set up the dummy macro, just to play around with this command. It never occurred to me that it could be what was causing my Option Base 1 command to be ignored. I’m guilty of that age-old mistake so many of us make, and that is, ‘When all else fails, go read the instructions.’ My wife used to take great delight in reminding me of not doing that. THANKS AGAIN for your help.

TotallyConfused

My Aswer Is This · Oct 24, 2018

I had never heard about Option Base 1 before seeing this question

I see a nice reason for using Option Base 1 when using arrays.

My first script here uses Option Base 1

See Here:

Code:

Option Base 1
Sub My_Array_With_Option()
'Modified  10/24/2018  3:52:53 PM  EDT
Dim i As Long
Dim Del As Variant
Dim ans As Long
Dim anss As Long
Del = Array("Me", "You", "Us")
ans = LBound(Del)
anss = UBound(Del)
    For i = ans To anss
        Cells(i, 1).Value = Del(i)
    Next
End Sub

My script using array without using Option Base 1

Code:

Sub My_Array_Without_Option()
'Modified  10/24/2018  3:52:53 PM  EDT
Dim i As Long
Dim Del As Variant
Dim ans As Long
Dim anss As Long
Del = Array("Me", "You", "Us")
ans = LBound(Del)
anss = UBound(Del)
For i = ans + 1 To anss + 1
    Cells(i, 3).Value = Del(i - 1)
Next
End Sub

It just seems a lot easier to understand when using Option Base 1

Any comments?

Option Base 1 is not working

TotallyConfused

Board Regular

Excel Facts

=ODIN=

Active Member

joeu2004

Banned user

TotallyConfused

Board Regular

TotallyConfused

Board Regular

My Aswer Is This

Well-known Member

Similar threads

Forum statistics

Share this page

Option Base 1 is not working

TotallyConfused

Board Regular

Excel Facts

=ODIN=

Active Member

joeu2004

Banned user

TotallyConfused

Board Regular

TotallyConfused

Board Regular

My Aswer Is This

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock