Excel VBA: type mismatch error

Posted on 2009-12-17
Last Modified: 2013-11-25
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


Private Sub Workbook_BeforeClose(Cancel As Boolean)

    If Cancel = False And Not trend Is Nothing Then


        Set trend = Nothing

    End If

End Sub

Open in new window

Question by:andy7789
    LVL 65

    Accepted Solution

    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



    Author Comment

    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!
    LVL 65

    Expert Comment

    Ah yes, that would do it! Well spotted!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
    Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  ( Here (http…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now