vb.netclassexcel-dna

The problem of using the Constructor and Class Deconstructor in Excel-DNA VB.net


In this code, I expected that the vCallLevel variable inside the uTest1 function will be equal to 1, and upon exiting it will be reset to 0. However, when returning from the function, it retains its value, and when the uTest1 function is called again, the vCallLevel value is increased by 1 again (MsgBox in the destructor is also not executed). This means that the Protected Overrides Sub Finalize () destructor is not called on exit. However, when I close Excel, I get MsgBox messages multiple times. What am I doing wrong?

{  
Imports ExcelDna.Integration
Public Module Fun
    Public vCallLevel As Double
    Public Class tcLocal
        Public Sub New()
            vCallLevel = vCallLevel + 1
        End Sub
        Protected Overrides Sub Finalize()
            vCallLevel = vCallLevel - 1
            MsgBox(vCallLevel)
            MyBase.Finalize()
        End Sub
    End Class
    Public Function uTest1() As Double
        Dim oLocal As New tcLocal
        Return vCallLevel
    End Function
End Module
}

I did it like that. Doesn't work anyway ...

Imports ExcelDna.Integration
Public Module Fun
   Public vCallLevel As Double

    Public Class tcLocal
        Implements IDisposable
#Region "IDisposable Support"
        Private disposedValue As Boolean
        Protected Overridable Sub Dispose(disposing As Boolean)
            If Not disposedValue Then
                If disposing Then
                    vCallLevel = vCallLevel - 1
                    MsgBox(vCallLevel)
                End If
            End If
            disposedValue = True
        End Sub
        Public Sub Dispose() Implements IDisposable.Dispose
            Dispose(True)
        End Sub

        Public Sub New()
            vCallLevel = vCallLevel + 1
        End Sub
#End Region
    End Class

Public Function uTest1() As Double
    Dim oLocal As New tcLocal
    Return vCallLevel
End Function
End Module

In fact, I only need these actions to take place inside oLocal - Initialization and Deactivation when exiting the uTest function:

Public vCallLevel As Double
    Public Class tcLocal
        Public Sub New ()
            vCallLevel = vCallLevel + 1
        End Sub
        Public Sub "Called on exit from uTest function" ()
            vCallLevel = vCallLevel - 1               
        End Sub
    End Class
Public Function uTest1 () As Double
    Dim oLocal As New tcLocal
    Return vCallLevel
End Function

I'm trying to translate my code from VBA, there are just 2 standard SUBs inside a class ...

  Private Sub Class_Initialize()
     CallLevel = CallLevel + 1      
  End Sub
  Private Sub Class_Terminate()
     CallLevel = CallLevel - 1
  End Sub

Solution

  • Here's an implementation of IDisposable in VB.NET:

    Public Class Fun
        Implements IDisposable
    #Region "IDisposable Support"
        Private disposedValue As Boolean ' To detect redundant calls
    
        ' IDisposable
        Protected Overridable Sub Dispose(disposing As Boolean)
            If Not disposedValue Then
                If disposing Then
                    ' TODO: dispose managed state (managed objects).
                End If
    
                ' TODO: free unmanaged resources (unmanaged objects) and override Finalize() below.
                ' TODO: set large fields to null.
            End If
            disposedValue = True
        End Sub
    
        ' TODO: override Finalize() only if Dispose(disposing As Boolean) above has code to free unmanaged resources.
        'Protected Overrides Sub Finalize()
        '    ' Do not change this code.  Put cleanup code in Dispose(disposing As Boolean) above.
        '    Dispose(False)
        '    MyBase.Finalize()
        'End Sub
    
        ' This code added by Visual Basic to correctly implement the disposable pattern.
        Public Sub Dispose() Implements IDisposable.Dispose
            ' Do not change this code.  Put cleanup code in Dispose(disposing As Boolean) above.
            Dispose(True)
            ' TODO: uncomment the following line if Finalize() is overridden above.
            ' GC.SuppressFinalize(Me)
        End Sub
    #End Region
    End Class
    

    Public Function uTest1() As Double
        Using oLocal As New tcLocal
            Return vCallLevel
        End Using
    End Function