PDA

View Full Version : How to read data from a worksheet into an array in a module?



cgphung
03-11-2008, 09:35 PM
I imported a set of data in a work sheet and need to use it as an array for all the macros the I used in the workbook. Just how do I make this work?

Please help!!!

CGP

tstav
03-11-2008, 11:42 PM
I imported a set of data in a work sheet and need to use it as an array for all the macros the I used in the workbook. Just how do I make this work?

Suppose your data range is Range("A1:B3"). (You change it to whatever your data range is).
Declare a dynamic Array of variant data type.
Dimension it according to the RowsNumber and ColumnsNumber of your range.
Assign the range to this Array.
Dim Arr() As Variant
Sub RangeToArray()
Dim R as Range
Set R = Range("A1:B3")
ReDim Arr(R.Rows.Count, R.Columns.Count)
Arr = R
End Sub

Bob Phillips
03-12-2008, 01:47 AM
Lot of redundant code there



Sub RangeToArray()
Dim Arr As Variant
Arr = Range("A1:B3")
End Sub

BreakfastGuy
03-12-2008, 02:01 AM
Forgive my ignorance but isn't using ReDim expensive in the memory department?

Bob Phillips
03-12-2008, 02:05 AM
Forgive my ignorance but isn't using ReDim expensive in the memory department?

It may well be, but one ReDim isn't going to hurt anyone.

But, as you can see from my post, it was unnecessary, as by loading the array directly the system dimensions the array.

tstav
03-12-2008, 02:29 AM
Lot of redundant code there

Hi xld,
1. This thread's owner asked in his opening post that the array variable is global. That's why I placed it outside the sub...end sub (now that I come to think of it again, I should also have declared it as public. Anyways...)
Doing this, one can only use Redim inside the code.
2. As for the comment on redundancy, I wanted to make the code as clear as possible. The Range will not allways be straight "A1:B3", it may need to be calculated perhaps by UsedRange, xlCellTypeWhatever, you name it. In those cases the assignment of that range to Arr might be quite lengthy. I made it short. That's all.

tstav
03-12-2008, 02:31 AM
To xld,
I just noticed that you said "by loading the array directly the system dimensions the array".

In that case I sure was redundant... Thank you for the comment.

tstav

Bob Phillips
03-12-2008, 02:57 AM
Indeed, and you also didn't account for the Option Base. By using



ReDim Arr(R.Rows.Count, R.Columns.Count)


you are effectively saying



ReDim Arr('option base' To R.Rows.Count, 'option base ' To R.Columns.Count)


so if you have Option Base 0 as I do that means



ReDim Arr(0 To R.Rows.Count, 0 To R.Columns.Count)


or a 4x3 array, bigger than is necessary, Arr(0), Arr(0,0), Arr(0,1) and Arr(0,2) and so on.

When you load the array, it gets redimensioned again to



ReDim Arr(1 To R.Rows.Count, 1 To R.Columns.Count)


regardless of Option Base, or an array of 3x2, Arr(1), Arr(1,1), Arr(1,2), etc.

BreakfastGuy
03-12-2008, 03:18 AM
Bob, you lost me on the Option base stuff but as for the array i normally use your version.

BG

Bob Phillips
03-12-2008, 03:36 AM
Ty this and see if it helps.

First run this code



Public Sub Test()
Dim ary

ReDim ary(5)
MsgBox UBound(ary) - LBound(ary) + 1

End Sub


then add the line

Option Base 1

at the start of the module, before any of the code, and run it again.

BreakfastGuy
03-12-2008, 04:01 AM
I see it removes 1 from the total displayed in the msgbox, but i'm sorry it still didnt explain the use of it really as i find it can only be set to 1 or 0, as far as i can see it seems to allow for calculation from row 1 onwards (0) or row 2 (1), is that right?, just in calculations? it didn't make any difference for displaying an address like rng = Cells(3, 6).Address

Bob Phillips
03-12-2008, 04:29 AM
Well it wouldn't as we are discussing arrays not ranges.

The point is that an array can have whatever lower bound that you want. If you don't specify it when dimensioning the array, the system will take the current option base setting.

To make it worse, some array commands such as Split will dimension the lower bound of the array to 0 regardless.

S, overall, the point is that you HAVE to manage the bounds of the array actively.

tstav
03-12-2008, 04:36 AM
To xld (concerning post #8)

101% correct. Ever thought of writing a book, Bob?...

Still, I didn't care about the user's Option Base setting exactly because I knew that the loading of the array would redimension it using Base 1, thus eliminating the (0) items I had already created with my Redim statement.
I just thought that it needed an initial hard-coded first Redim by me...

Anyhow, I repeat that your comment in post #5 (i.e. the loading of the array redimensions it) makes everything else redundant.

Thank you again

BreakfastGuy
03-12-2008, 04:40 AM
tstav, 'xld' has written or at least co written some white papers! has a good very well explained website too!
xld's site (www.xldynamic.com)

tstav
03-12-2008, 05:07 AM
'xld' has written or at least co written some white papers! has a good very well explained website too
Didn't know that BG, thanks for letting me know.
xld, I'm paying you a visit right now!