+ Reply to Thread
Results 1 to 8 of 8

Option Base

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Option Base

    Ok, i have this code i have been working on where i set "option base 1"......ie...arrays start at "1". However, i have this code (which i am including in the enclosed excel file) which takes data from a col and allows user to select items they want.........however this code i can only get to work with option base 0.

    So my options as i see them are

    1) change my option base to 0 and then RE-define my arrays which are currently option base 1 such that they start at 1. I think, but dont know how, there is a way to define the arrays such taht they go from say 1 to 10 or something like that. I have a multidimensional array (2D array) so how would i define it so it starts at 1 ....

    2) the other option is to figure out how to fix the enclosed code so it works using option base 1..........instead of option base 0


    any thoughts?
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    04-11-2011
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    325

    Re: i have code with option base 1.....want to add code with option base 0

    Any thoughts?
    Read this. http://www.cpearson.com/excel/DeclaringVariables.aspx

    Specifically the part about declaring array bounds.

  3. #3
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: i have code with option base 1.....want to add code with option base 0

    Consider the following code:

    in this code i have option base set to 0 and i think i defined the arrays to go from 0 to 10........but if you look in the locals window you will see that the arrays are really from 1 to 10.

    any ideas what i am doing wrong?



    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: i have code with option base 1.....want to add code with option base 0

    ok, what i found out is that if i remove these two statements


    Please Login or Register  to view this content.

    then the arrays start at 0..........any ideas why?

  5. #5
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: i have code with option base 1.....want to add code with option base 0

    One solution would be to adopt programming practices that make Option Base 1 or 0 irrelivant.

    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  6. #6
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: i have code with option base 1.....want to add code with option base 0

    what is wrong with the "Redim" statements.........dont i specify everything that is required?


    i tried specifying an array like you said (see code below) and i get a compile error "Cant assign to array" at this statement

    "myWellDefinedArray = ws.Range("A1:A8").Value"

    any ideas why?




    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: i have code with option base 1.....want to add code with option base 0

    ok found the answer to my question

    it was at this link

    http://msdn.microsoft.com/en-us/libr...ffice.10).aspx


    At this link i found the following statment:
    "When the values in a range are assigned to a Variant, the indexes of the array that is created are always one-based, not zero-based, regardless of the Option Base setting in the declarations section of the module. Also, the array always has two dimensions, even if the range has only one row or one column. This preserves the inherent column and row structure of the worksheet in the array and is an advantage when you write the array back to the worksheet"


    so this is why regardless of my option base 0 statement the arrays start out at 1 instead of 0.



    Another statement: If you assign a range to a variable vba automatically makes data type variant.
    "When you assign range values to a variable , the variable must have a Variant data type. VBA copies all the values in the range to the variable, creating an array with two dimensions. The first dimension represents the rows and the second dimension represents the columns, so you can access the values by their row and column numbers in the array."

  8. #8
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: i have code with option base 1.....want to add code with option base 0

    Specify the lower limits of all dimensions.
    Please Login or Register  to view this content.
    If you want an array with esoteric starting points.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1