VBA How to create an If to close a file if it was open-CodePudding

At the end of the macro, I have functions that close the open sheets of the fumulators you fill out. However, sometimes one of them will not be there. Let's say data_wb1 has not been started. When the macro finishes, it shows me error : Required object. here If Not data_wb1 Is Nothing Then

If Not data_wb2 Is Nothing Then
data_wb2.Close True
Else
End If
If Not data_wb1 Is Nothing Then
data_wb1.Close True
Else
End If
If Not data_wb Is Nothing Then
data_wb.Close True
Else

CodePudding user response：

Problem is that either you don't use Option Explicit and never declared the Variable (so VBA implicitly creates it on the fly as Variant), or you declared it as Variant instead of Workbook.

Now a Variant can be anything, a Number, a Date, an Object. If you don't assign anything to a Variant, it has it's initial value which is Empty.

An Object Variable (eg of type Workbook) points to an Object when you use the Set-command. Usually, this is called It gets a reference to it. When declared, it's initial value is Nothing which means it doesn't point to an object.

Now you need to distinguish between Empty and Nothing. To check for Nothing. you need an Object Variable, else you will get the Object required runtime error.

In your case:
(a) Use Option Explicit (always!)
(b) Declare your variables (data_wb, data_wb1, data_wb2) as Workbook

Dim data_wb as Workbook, data_wb1  as Workbook, data_wb2 as Workbook

That should solve your problem

Note: You should use Variant only if necessary and in your case I don't think it's necessary). But if you insist in using Variants, you can assign Nothing to them.

Dim data_wb as Variant, data_wb1 as Variant, data_wb2 as Variant
Set data_wb = Nothing
Set data_wb1 = Nothing
Set data_wb2 = Nothing

In that case, they are no longer Empty and the VBA runtime understands that you are dealing with Object references and that they currently don't point to anything.