How to read data from a worksheet into an array in a module?

[cgphung]

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]

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.
[vba]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[/vba]

[Bob Phillips]

Lot of redundant code there

[vba]

Sub RangeToArray()
Dim Arr As Variant
Arr = Range("A1:B3")
End Sub
[/vba]

[BreakfastGuy]

Forgive my ignorance but isn't using ReDim expensive in the memory department?

[Bob Phillips]

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]

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]

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]

Indeed, and you also didn't account for the Option Base. By using

[vba]

ReDim Arr(R.Rows.Count, R.Columns.Count)
[/vba]

you are effectively saying

[vba]

ReDim Arr('option base' To R.Rows.Count, 'option base ' To R.Columns.Count)
[/vba]

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

[vba]

ReDim Arr(0 To R.Rows.Count, 0 To R.Columns.Count)
[/vba]

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

[vba]

ReDim Arr(1 To R.Rows.Count, 1 To R.Columns.Count)
[/vba]

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

[BreakfastGuy]

Bob, you lost me on the Option base stuff but as for the array i normally use your version.

BG

[Bob Phillips]

Ty this and see if it helps.

First run this code

[vba]

Public Sub Test()
Dim ary

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

End Sub
[/vba]

then add the line

Option Base 1

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

[BreakfastGuy]

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 [vba]rng = Cells(3, 6).Address[/vba]

[Bob Phillips]

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]

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]

tstav, 'xld' has written or at least co written some white papers! has a good very well explained website too!
xld's site

[tstav]

'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!