Thread: declaring 2darray in excel vba
2014-04-23, 01:55 #1
- 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....)
Any help please in pointing in right direction would be appreciated.
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!
+ 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!
2014-04-23, 12:52 #2
- Join Date
- Mar 2004
- Manning, South Carolina
- Thanked 784 Times in 718 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
2014-04-25, 04:59 #3
- Join Date
- May 2002
- Canberra, Australian Capital Territory, Australia
- Thanked 188 Times in 172 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) Next ReDim Preserve colArray(2, i - 1) End WithCheers,
[MS MVP - Word]