VBA ActiveSheet vs declared worksheet

[OxfordCurmudgeon]

I'm baffled. This code works:

Set DataSourceSheet = ActiveSheet
With DataSourceSheet
DataHoldRows = .UsedRange.Rows.Count
DataHoldCols = .UsedRange.Columns.Count
DataHold = .Range(.Cells(1, 1), .Cells(DataHoldRows, DataHoldCols))
End With

This code does not:

With ActiveSheet
DataHoldRows = .UsedRange.Rows.Count
DataHoldCols = .UsedRange.Columns.Count
DataHold = .Range(.Cells(1, 1), .Cells(DataHoldRows, DataHoldCols))
End With

I get "Run-time error '13': Type mismatch" on the DataHold = line. The first two assignments work correctly either way. I've used the Watch window to verify that ActiveSheet is pointing where I thought it would.

The code to declare the variables was unchanged:

Dim DataSourceSheet As Worksheet
Dim DataHold() As Variant
Dim DataHoldRows As Integer
Dim DataHoldCols As Integer

I'm trying to gain some mastery of Excel VBA and would like to understand why the code is failing.

 

[RoryA]

Use:

Dim DataHold as Variant

not:

Dim DataHold() as Variant

The Value property returns a Variant type, not a Variant array, and the late binding context causes the type mismatch.

 

[OxfordCurmudgeon]

Use:

Dim DataHold as Variant

not:

Dim DataHold() as Variant

The Value property returns a Variant type, not a Variant array, and the late binding context causes the type mismatch.

Thank you for the prompt response!

Your suggestion works, but I'm still confused. To me, ActiveSheet (created by VBA) and DataSourceSheet (created by my code) are each instances of a Worksheet object -- and should be interchangeable.

 

[RoryA]

ActiveSheet returns a generic Object type (the active sheet might not be a worksheet), hence it is late bound if you don’t assign it to a typed variable.

 

[OxfordCurmudgeon]

ActiveSheet returns a generic Object type (the active sheet might not be a worksheet), hence it is late bound if you don’t assign it to a typed variable.

Thank you! That's the mistake I was making: Assuming ActiveSheet was a WorkSheet object. With that corrected, everything else falls into place.