Hi again,

Attached is a small Excel VBA workbook with array defined in VBA code Sheet1.

"Test1" is working:

Sub Test1()
Dim Arr As Variant
'reading into array
Arr = Sheets("Sheet1").Range("A2:B8").Value
'writing out of array
Sheets("Sheet2").Range("A2:B8").Value = Arr
End Sub

"Test2" is not working:

Sub Test2()
Dim Arr As Variant
'reading into array
Arr = Sheets("Sheet1").Range(Cells(1, 2), Cells(2, 8)).Value
'writing out of array
Sheets("Sheet2").Range(Cells(1, 2), Cells(2, 8)).Value = Arr
End Sub

Why is not Test2 working with this explicit cell reference? Thanx for any help - still trying to understand arrays better.

Trond

Hi Trond,

Can you run this code?

MsgBox Cells(2, 8).Address

You are using the range A2:B8 in the first test, but Cells(2,8) is not B8. Remember, B is the column, 8 is the row.

The syntax for Cells is: Cells(Row,Column)

Hi Catalin,

You’re rigth about the Cells(Row,Column) referanse.

However the problem in sub Test2 seems to be copying from Sheet1 to Sheet2, error message is: Run-Time error ‘1004’: Application-defined or object-defined error.

Acrually seems like Excel doesn’t support this kind of using an array. You’re working code above is within the same sheet.

Trond

You are using relative reference to cells, and that is wrong.

Cells(8,2) refers to active sheet.

What if Sheet1 is selected? The following line will try to build a range in sheet2, using cells from Sheet1 ??

Sheets("Sheet2").Range(Cells(1, 2), Cells(2, 8)).Value = Arr

Use full references:

Sheets("Sheet2").Range(Sheets("Sheet2").Cells(1, 2), Sheets("Sheet2").Cells(8, 2)).Value = Arr

Catalin:

Very interesting - and it's working!! Actually, a bit confused now...but happy!

First of all, I've never seen a "full" reference like this: Sheets("Sheet2").Range(Sheets("Sheet2").Cells(2, 1), Sheets("Sheet2").Cells(8, 2)).Value

I thought Sheet2 connected to Cells references in formula, with Arr input from Sheet1: Sheets("Sheet2").Range(Cells(1, 2), Cells(2, 8)).Value = Arr

-> Would you recommend using full reference to active Sheet1 as well, when reading range into Arr?

As you perhaps remember from context above, this is same VBA issue as last week. Besides I have been new to arrays as well.

Thank you for help with this. Looking forward to use in VBA projects.

Trond

Always use full references, unless you need it to work on active sheet by design (for an add-in).

To be more accurate, the reference is still not complete, a full reference can go way back to application.

For example:

Dim xlApp1 as Excel.Application: Set xlApp1=Application

Dim xlApp2 as Excel.Application: Set xlApp2=New Excel.Application

Dim Wb1 as Workbook

Dim Wb2 as Workbook

Dim Sh1 as Worksheet

Dim Sh2 as Worksheet

Set Wb1 =xlApp2.ThisWorkbook 'this refers strictly to the book where the code resides

Set Wb2=ActiveWorkbook 'this refers to any active workbook. Can be this workbook, if this is the active one, but can also be any other book, as long as it's active

Set Sh1=Wb1.Worksheets("Sheet1")

Set Sh2=Wb2.Worksheets("Sheet2")

Sh2.Range(Sh2.Cells(1, 2), Sh2.Cells(2, 8)).Value = Arr

Whenever you refer to Sh1, the code will know that it's a worksheet from Wb1, that is open in a new instance of excel - xlApp2.