I am attempting to select a worksheet by name from an Add-In that is loaded in my workbook. My code iterates up to the line of With worksheets(ws1)
then it throws a
Type mismatch error
What would be the correct way to perform actions on that worksheet and use the with
feature?
Dim ws2 As Worksheet, ws1 As Worksheet
Dim aData
Set ws2 = ActiveWorkbook.ActiveSheet
Set ws1 = ThisWorkbook.Worksheets("Tastatas")
With Worksheets(ws1)
aData = .Range("a1").CurrentRegion
End With
With Worksheets(ws1)
When you do Worksheets(ws1)
, you're actually calling Application.Workbooks.Item
(the default property of the Excel.Worksheets
collection class.
The Item
property of a collection class is parameterized - it needs a key to retrieve an Object
reference. So you need a key.
A VBA.Collection
is keyed with a String
. The Item
property of the Excel.Worksheets
collection class takes a Variant
- this means at compile-time, anything you give it will work. The problem happens at run-time, when the Variant
value is used to retrieve the Object
associated with the key you gave it.
You can do Set ws1 = ThisWorkbook.Worksheets(1)
with any Long
(or Integer
) to retrieve the item by index.
You can do Set ws1 = ThisWorkbook.Worksheets("Sheet1")
with any String
to retrieve the item by name.
And that's all. A string or a number.
Dim ws2 As Worksheet, ws1 As Worksheet
Beats my why you'd have ws2
before ws1
, but anyway ws1
is a Worksheet
object reference, not a String
, not a Long
.
So your code compiles because you're passing that reference to a Variant
parameter, and blows up at run-time with a type mismatch error, because the type you supplied (Worksheet
) mismatches the type that was expected (a String
, or any integer numeric type).
The With
block could very well hold that reference for you - which spares you the variable declaration:
With ThisWorkbook.Worksheets("Tastatas")
aData = .Range("A1").Value
End With
Notice the explicit .Value
in the right-hand side of the assignment: it's still there if you leave it out, only it's implicit. That's because the Range
class has a default property that points to its Value
, which is a Variant
that can contain a string, a date, a number, an error value... reading the value into a Variant
is a very good idea: it spares you from another type mismatch error if the cell you're reading contains a #REF!
or any other cell erorr value.
But because of the implicit default value, the only thing that can tell us whether aData
is a value or a reference, is the assignment itself:
[Let] aData = .Range("A1") ' value assignment: aData is the cell's value.
Set aData = .Range("A1") ' reference assignment: aData is a Range object.
Things are clearer with explicit code:
aData = .Range("A1").Value
Set aData = .Range("A1")