Ms Excel -> 2 columns into a 2 dimensional array

Question

Hi,

Im coming from a Unix world where I never had to develop something for Office with VBA, I have to do some now and Im having a hard time! Please help me! :)

So I've got 2 Excel Sheets(lets call them Sheet1 and Sheet2) and 2 forms(Form1 and Form2) to edit/add data.

In Sheet1, the first two columns are MovieId and MovieName. We dont know how many rows they will be in this columns. Form1 controls data in Sheet1, and Form2... in Sheet2.

At Form2 initialization, I want to create a 2 Dimensional Array that will be like (MovieId1,MovieName1;MovieId2,MovieName2;...,...;MovieIdN,MovieNameN), where this data has been extracted from Sheet1, like a sort of Map in Java if you will...

It would actually be ok for me if it was like: (0,"MovieId0;MovieName0";1,"MovieId1,MovieName1";..,"..";N,"MovieIdN,MovieNameN")

I dont know how to create the array with an variable last row number, since the compiler seems to always want a constant to initialize an Array...

Please enlighten me!

Answer 1

Rather use the Range object, with this you can also use the UsedRange from the sheet

Sub Macro1()
Dim sheet As Worksheet
Dim range As range
Dim row As Integer

    Set sheet = Worksheets("Sheet1")
    Set range = sheet.UsedRange

    For row = 1 To range.Rows.Count

    Next row
End Sub

Answer 2

Look at the Value method or Value2 property.

e.g. Range("$A$2:$B$4").Value2(1,1) or Range("$A$2:$B$4").Value()(1,1)

Array's lower bound start from 1.
lbound(Range("$A$2:$B$4").Value2, 1) - row element starts from ubound(Range("$A$2:$B$4").Value2, 2) - row element ends lbound(Range("$A$2:$B$4").Value2, 2) - column element starts from ubound(Range("$A$2:$B$4").Value2, 2) - column element ends

EDIT: Code to traverse through the array

Dim myAddress As String
Dim dataArray As Variant

Dim rowStart As Long, rowEnd As Long
Dim colStart As Long, colEnd As Long

Dim rowCtr As Long
Dim colCtr As Long

myAddress = "$A$2:$B$4"
dataArray = Range(myAddress).Value2

rowStart = LBound(dataArray, 1)
rowEnd = UBound(dataArray, 1)
colStart = LBound(dataArray, 2)
colEnd = UBound(dataArray, 2)

For rowCtr = rowStart To rowEnd
    For colCtr = colStart To colEnd
        Debug.Print rowCtr & ":" & colCtr, vbTab & dataArray(rowCtr, colCtr)
    Next
Next

EDIT2: In my example, I have assumed the address to be $A$2:$B$4.
You can prefix it with sheet name. e.g. Sheet1!$A$2:$B$4 or Sheet2!$A$2:$B$4

On a side note, array can be defined dynamic (if it is 1 dimensional). e.g dim my1DArray() as Integer

For double dimension array, see the following code

Dim myArray
Dim dynamicRows As Integer
dynamicRows = 2

ReDim myArray(0 To dynamicRows, 0 To dynamicRows)
myArray(0, 0) = "hello"

dynamicRows = 20
ReDim myArray(0 To dynamicRows, 0 To dynamicRows)
MsgBox myArray(0, 0)
myArray(0, 0) = "hello"

ReDim Preserve myArray(0 To dynamicRows, 0 To dynamicRows)
MsgBox myArray(0, 0)

Answer 3

assuming the data starts in A1

Dim vArr as variant

vArr=worksheets("Sheet1").range("A1").resize(worksheets("Sheet1").range("A65535").end(xlup).row,2)

Answer 4

Do you mean:

Dim thearray() As Variant

ReDim thearray(1, range.Rows.Count)

You can also use a recordset and GetRows to return an array from a worksheet.

Answer 5

Slight mod to Charles' answer:

Dim vArr as variant vArr = Worksheets("Sheet1").Range("A1").CurrentRegion.Value

Assuming of course that there isn't any stray data in Sheet1.