Sample Array

  • Thread starter Thread starter Todd Huttenstine
  • Start date Start date
T

Todd Huttenstine

Below is a test array. I am trying to use the Option Base
statement to make the default index set to 1 instead of
0. If I were to insert the code: Option Base 1 at the
start of the code, then I would think the result in cell
J2 would = 7. When I dont use Option base 1 statement(run
code like it is shown below) the result of cell J2 is
equal to 3 as expected, when I run the code and change the
line of the code from Range("J2").Value = Bird to Range
("J2").Value = Bird(1), the result of J2 is equal to 7 as
expected, but why when I keep the code as it is below and
add the Option Base 1 below the Private Sub
CommandButton1_Click() it gives me the error "Compile
error: Invalid inside procedure."?

Private Sub CommandButton1_Click()
Dim Bird(2) As Integer
Bird(0) = 3
Bird(1) = 7
Range("J2").Value = Bird
End Sub

Thanx

Todd Huttenstine
 
Hi Todd,

Option Base 1 must be placed at the very top of the code module. It
can't go inside a procedure.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
Option statements apply to the entire module. They are not valid within a
procedure.
 
Todd,

Perhaps you should explain what you are trying to do, but I doubt that you will achieve it using Option Base.

Option Base can only be used in the declarations section (at the top of a module before any routines are defined.) It affects the
starting index of all arrays in the module. Under Option Base 0 the following declares a four element arrray with indeces 0, 1, 2
and 3

Dim x(3)

Under Option Base 1 it defines a three element array with indeces 1, 2 and 3.

Option Base can always be over-riden by explicitly setting the range:

Dim x (1 to 3)

When you assign an array to a range it makes no difference what the option base is or what the first index value is. The first
element of the array is assigned to the first cell in the range.
 
BTW, I should have mentioned that Option Base 1 makes the lower bound of
all array variables 1 instead of 0 if you don't explicitly specify
otherwise. Therefore, if you added Option Base 1 to the code module
containing the procedure below you're going to get "Subscript out of range"
error on the line:

Bird(0) = 3

because the Bird array will no longer have an element with an index of 0.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
Back
Top