Loading an array from a spreadsheet

[medic5678]

I've got approximately 200 records, with each record consisting of 12 string fields. I have a two dimensional array. The question is how to declare it and how to load it from the Excel spreadsheet, then how to rewrite the info back to to spreadsheet after the data is changed/manipulated.

Assuming my spreadsheet is from 10 to 210 (rows) and from A to W (columns., with each row containing a record across the columns. I'm having problems grappling with this, but I'm sure it's pretty common.

Obviously, I've got a lot of reading to do on this.

An alternative way to do this would be to create a record object, and have a simple array of 200 objects.

Thanks for any discussion.

 

[Rick Rothstein]

Dim your array variable as Variant and then simply assign the range to it. For example...

Dim MyArray As Variant
MyArray = Range("A10:W210")

MyArray is now a two dimensional array (first element is rows, second element is columns) containing all of the values within the specified range (A10:W210). The array indexes for both element always start at 1, not 0, no matter what Option Base setting you use. So you would iterate the array from 1 to UBound(MyArray,1) for the first element and from 1 to UBound(MyArray,2) for the second element. Assigning it back to the worksheet is just as easy. If you want to put it back over top of the original cells, just do this...

Range("A10:W210") = MyArray

If you want to move the output to a different range, say starting at X5, then do this...

Range("X5").Resize(UBound(MyArray,1), UBound(MyArray,2)) = MyArray

 

[medic5678]

Seem to be having trouble getting this to work in my program. I have a named range in the spreadsheet, and I'm trying to load it and then test a value, so I know it's in there. I'm hampered by a less than good understanding of scope.

I have a userform where I'll display and update data, which will be written back to the spreadsheet when the user updates it. Where would you declare this array? on userform initiation? If I do this, will it have scope throughout the userform? Sorry to muddle this with newbie questions.

 

[medic5678]

I've definitely got the array loading with the spreadsheet range by declaring the array as public in a module and then using it in my form. Thanks for your help. Will continue to tinker with scope, etc. I'm sure I probably didn't need a public variable, but it's working for the moment and now it's time to fumble forward.


Public INPATIENTARRAY As Variant


Sub load_array()
INPATIENTARRAY = Range("BEDRANGE")
End Sub