Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sample Array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sample Array
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 * "Todd Huttenstine" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sample Array
Option statements apply to the entire module. They are not valid within a
procedure. -- Vasant "Todd Huttenstine" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sample Array
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. -- John Green - Excel MVP Sydney Australia "Todd Huttenstine" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sample Array
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 * "Todd Huttenstine" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sample Array
Ok thanx, I did not know it applied to all arrays.
-----Original Message----- 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I random sample from a set until every sample is selected? | Excel Discussion (Misc queries) | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
random sample | Excel Worksheet Functions | |||
Sample | Excel Discussion (Misc queries) | |||
Sample workbook | Excel Programming |