I have to select a sheet to copy range to array

[jimrobinson]

The following fails:
Private Sub GetHierarchy(htable As Variant)
Dim ws As Worksheet
Dim cmax As Integer
Dim rmax As Integer
' Dim htable As Variant
Set ws = Sheets("Hierarchy")
cmax = LastColumn(ws)
rmax = LastRow(ws)
htable = ws.Range(Cells(1, 1), Cells(rmax, cmax))

End Sub

This works:
Private Sub GetHierarchy(htable As Variant)
Dim ws As Worksheet
Dim cmax As Integer
Dim rmax As Integer
' Dim htable As Variant
Set ws = Sheets("Hierarchy")
cmax = LastColumn(ws)
rmax = LastRow(ws)
ws.Select

htable = ActiveSheet.Range(Cells(1, 1), Cells(rmax, cmax))

End Sub

I'd like to use the former, because it's more efficient
Do I chalk this up as a bug, and use the latter?
Excel 2010

( its the last line in the subroutine that fails)
If I make a demo subroutine without a parameter, and define htable
dim htable variant

the demo subroutine doesn't get failure

e.g.
Private Sub demo()
Dim ws As Worksheet
Dim cmax As Integer
Dim rmax As Integer
Dim htable As Variant
Set ws = Sheets("Hierarchy")
cmax = LastColumn(ws)
rmax = LastRow(ws)
htable = ws.Range(Cells(1, 1), Cells(rmax, cmax))

End Sub

Thanks,

Jim

 

[bertie]

Hi and welcome to the forum.

You missed out .Value when assigning the range to an array.
As you are updating the array variable you need to pass it ByRef.

Here is my full test code.

[COLOR=darkblue]Sub[/COLOR] test()
   [COLOR=darkblue]Dim[/COLOR] htable() [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
 
   GetHierarchy htable
 
   [COLOR=green]'if it is not visible click[/COLOR]
   [COLOR=green]'View => Locals Window[/COLOR]
   [COLOR=green]'and expand the array variable[/COLOR]
   Stop  '[COLOR=seagreen]delete this line when you no longer need it[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] GetHierarchy([COLOR=darkblue]ByRef[/COLOR] htable [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR])
   [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] cmax [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] rmax [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
 
 
   [COLOR=darkblue]Set[/COLOR] ws = Sheets("Hierarchy")
 
   cmax = LastColumn(ws)
   rmax = LastRow(ws)
   htable = ws.Range(Cells(1, 1), Cells(rmax, cmax)).Value
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Function[/COLOR] LastRow([COLOR=darkblue]ByVal[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
End [COLOR=darkblue]Function[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Function[/COLOR] LastColumn([COLOR=darkblue]ByVal[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   LastColumn = ws.Cells(1, Columns.Count).End(xlToLeft).Column
End [COLOR=darkblue]Function[/COLOR]

 

[jimrobinson]

Bertie,
Thanks for your suggestion. I copied your code into a blank workbook,
renamed one of the sheets "Hierarchy".

It still fails:
1004 Method 'Range' of object '_Worksheet' failed

Regards,
Jim

 

[bertie]

Which line did it fail on?

 

[bertie]

It's always something simple.

Change rmax and cmax to data type Long.

 

[Norie]

The problem could be because you don't use worksheet references with Cells here.

htable = ws.Range(Cells(1, 1), Cells(rmax, cmax)).Value

This means they will refer to the active sheet, not necessarily ws.

Try this.

htable = ws.Range(ws.Cells(1, 1), ws.Cells(rmax, cmax)).Value

Or this

htable = ws.Cells(1, 1).Resize(rmax, cmax).Value

Oh, and you should declare rmax and cmax as Long.

 

[jimrobinson]

Thank you Norie and Bertie.
Norie had the fix I needed.

htable = ws.Range(ws.Cells(1, 1), ws.Cells(rmax, cmax)).Value</pre>
Wow! that's really arcane. I would never have guessed that the Cells property wouldn't implicitly be properties of the range being indexed.

Again, you have preserved my sanity

Regards,

Jim