Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Warrington, Cheshire
    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.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Manning, South Carolina
    Thanked 1,608 Times in 1,452 Posts

    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:
    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.

    May the Forces of good computing be with you!


    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator macropod's Avatar
    Join Date
    May 2002
    Canberra, Australian Capital Territory, Australia
    Thanked 470 Times in 387 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:
    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)
      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.

    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