Excel VBA: type mismatch error

Hi x-perts,

I am disposing my object (trend) on workbook close (see the code).

It returns a compile error "type mismatch" highlighting the "trend".

Trend is defined as:

Public trend As TrendSeg.MainForm
Set trend = New TrendSeg.MainForm

What is wrong here? I used that construction many times, and cannot understand what is wrong in that case.

Please, advise

thanks

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Cancel = False And Not trend Is Nothing Then
        trend.Dispose
        Set trend = Nothing
    End If
End Sub

Open in new window

andy7789Asked:
Who is Participating?
 
RobSampsonConnect With a Mentor Commented:
Does trend.Dispose already destroy the object, thereby not making it an object anymore?  Instead of
Set trend = Nothing

what if you use
If TypeName(trend) = "Object" Then Set trend = Nothing

Regards,

Rob.
0
 
andy7789Author Commented:
Hi Rob,

It was not the case, but your code helped me to spot the problem. As I mentioned, i have been using the same construction for a while with no problems.

So, I tested your code and it returned an error "Expected variable, not module". Then I realized that I have one of the modules with the same name "trend". That's it.

Thank you!
Andy
0
 
RobSampsonCommented:
Ah yes, that would do it! Well spotted!

Rob.
0
All Courses

From novice to tech pro — start learning today.