Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    345
    Thanks
    2
    Thanked 0 Times in 0 Posts

    declaring 2darray in excel vba

    I need to declare a 2d array purely in VBA to cater for checking an incoming file for completeness.
    each line of the array would be of strings such as "Person ID", "BE"

    I know the complete list would be limited to 17 items so "colArray(1 to 17, 1 to 17) as String" is accepted but then I get constant expression problem when I try to populate the array.

    In my simple mind I envisaged it to work with first setting the array constraints and then populating the array with such as colArray = ("person ID","BE";"Reference","CB"; et cetera....)

    Not so....

    Any help please in pointing in right direction would be appreciated.
    Thanks

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,828
    Thanks
    185
    Thanked 706 Times in 644 Posts
    Ase,

    Normally arrays need to be populated in loops. From your data it appears that you want a 17 row by 2 column array, e.g. Dim MyArray(1 to 17, 1 to 2). However, if the items (person ID, Reference, etc.) are always the same then you only really need a single column array as you will know the title by the position.

    Since you're in Excel I assume that the data will be drawn from sheet cells? If this is the case you want some code like this:
    Code:
    Sub Test()
    
       Dim MyArray(1 To 17, 1 To 2) As String
       Dim lRow                     As Long
       Dim lCol                     As Long
       Dim rngActiveCell            As Range
       
    'Need code here to select the starting row of your data
    
    ' I'll assume the activecell for demo purposes
    
       Set rngActiveCell = ActiveCell
    'Populate the array
    
       For lRow = 1 To 17
          For lCol = 1 To 2
             MyArray(lRow, lCol) = rngActiveCell.Offset(lRow - 1, lCol - 1)
             'Of course the right side of the equation will vary according to your data layout
          Next lCol
       Next lRow
       
    End Sub
    Some sample data in a worksheet would make this easier to answer.

    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,783
    Thanks
    0
    Thanked 162 Times in 150 Posts
    Given that there are always two elements per item, you can also get by with a single-dimension array, using two entries per item. Then, when stepping through the array, simply step in increments of 2. in either case, if the final size of the array is unknown and the variation in potential sizes is large, you'd do best to start off with a 0-sized array, with something like:
    Code:
    Dim i as long, colArray() ' Array
    With ActiveSheet.UsedRange
      ReDim Preserve colArray(2, 0)
      For i = 1 to .Rows.Count
        colArray(1, i - 1) = .Range("A1").Offset(i, 0)
        colArray(2, i - 1) = .Range("A1").Offset(i, 1)
        ReDim Preserve colArray(2, i)
      Next
      ReDim Preserve colArray(2, i - 1)
    End With
    This also has the advantage that you can always work thereafter with the full array without continually having to test whether you've reached the last populated element.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Tags for this Thread

Posting Permissions

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