I've set multiple Worksheet objects, only the last object is set Nothing, the first X are empty
Multiple line declaration is working
Dim ws1, ws2 As Worksheet
If ws1 is nothing then
If ws1 = "" then
If ws1 = "Empty" then
If ws1 = vbEmpty then
If IsEmpty(ws1) then
If IsNull(ws1) then
'tried all of these but get
'runtime error
If ws2 is nothing then
'works fine
Dim ws1 As Worksheet, ws2 As Worksheet
If ws1 is nothing then
'works fine
If ws2 is nothing then
'works fine
Follow-Up because of answer from Pᴇʜ:
Dim ws1, ws2 As Worksheet
If ws1 Is Nothing Or IsEmpty(ws1) then
'runtime error
If IsEmpty(ws1) then
'works fine
If ws2 Is Nothing then
'works fine
runtime error 424
I just want to make the code cleaner with multiple line declarations, maybe it's not possible
Conclusion: We have to declare each variable separately! Further details in the first answer
"I've set multiple 'worksheet' objects"
No, you didn't.
If you declare
Dim ws1, ws2 As Worksheet
then ws2 is of type Worksheet but ws1 is of type Variant because you didn't specify a type for the first variable. Therefore ws1 is not an object unless you set it to one, and If ws1 Is Nothing Then fails because it can only be used with objects.
It is exactly the same as
Dim ws1 As Variant, ws2 As Worksheet
If you declare multiple variables in one line (in VBA) you still need to specify a type for every variable (or it will be Variant by default):
Dim ws1 As Worksheet, ws2 As Worksheet
Note:
This is only valid in VBA. If you also use VB.NET be carefull because there Dim a, b As Long will define both variables as Long! In VBA it doesn't!
Follow up answer:
The following If statement …
Dim ws1, ws2 As Worksheet
If ws1 Is Nothing Or IsEmpty(ws1) then
errors, because in If statements with Or/And operators always all of the tests are performed no matter what the result of the others is. So in this case it tests both ws1 Is Nothing and IsEmpty(ws1).
The first one errors because ws1 is Variant and not an object but Is Nothing only works with objects. Therfore the whole statement errors.