Consulting

Results 1 to 5 of 5

Thread: How To Destroy An Class Object's Instance From Dll(VB.NET) In VBA

  1. #1
    VBAX Newbie
    Joined
    Aug 2015
    Posts
    3
    Location

    How To Destroy An Class Object's Instance From Dll(VB.NET) In VBA

    Hi, I have a question which I couldn't find the answer with my searching skill.

    I've created a class library with VB.NET for using in EXCEL VBA, and successfully compiled, registered and reference it in VBA.

    So, I can use the class.

    The class is about 'Timer' which replace the timer in VBA 'application.ontime'.

    And it works well, but I can't destroy the instance of it.

    'Set ... = Nothing' doesn't work.

    Here are my Class Library Code.

    Imports System.Runtime.InteropServices
    Imports System.Timers
    
    
    Public Interface ITimer
        Function fSetTimer(ByVal iIntervalInMilliSec As Integer, ByVal iCallBackObj As Object, ByVal iCallBackProc As String, _
                                      Optional ByVal iCallBackArg As Object = Nothing) As Byte
        Sub pKillTimer()
    End Interface
    
    
    <ClassInterface(ClassInterfaceType.None)>
    Public Class cTimer
        Implements ITimer
    
    
        Private cTIM_Var_Timer As Timer
        Private cTIM_Var_CallBackObj As Object
        Private cTIM_Var_CallBackProc As String
        Private cTIM_Var_CallBackArg As Object
        Private cTIM_Var_Running As Boolean
    
    
        Public Sub pKillTimer() Implements ITimer.pKillTimer
            cTIM_Var_Timer.Stop()
            cTIM_Var_Running = False
        End Sub
    
    
        Public Function fSetTimer(ByVal iIntervalInMilliSec As Integer, ByVal iCallBackObj As Object, ByVal iCallBackProc As String, _
                                             Optional ByVal iCallBackArg As Object = Nothing) As Byte Implements ITimer.fSetTimer
    
    
            If iIntervalInMilliSec < 1 Or iCallBackObj Is Nothing Or iCallBackProc = "" Then
                Return 0
            End If
    
    
            If cTIM_Var_Running = True Then Return 1
    
    
            cTIM_Var_Running = True
            cTIM_Var_Timer = New Timer(iIntervalInMilliSec)
            cTIM_Var_CallBackObj = iCallBackObj
            cTIM_Var_CallBackProc = iCallBackProc
            cTIM_Var_CallBackArg = iCallBackArg
    
    
            AddHandler cTIM_Var_Timer.Elapsed, New ElapsedEventHandler(AddressOf pHandler)
            cTIM_Var_Timer.AutoReset = False
            cTIM_Var_Timer.Start()
    
    
            Return 255
    
    
        End Function
    
    
        Public Sub pHandler(ByVal iSender As Object, ByVal iArgs As ElapsedEventArgs)
    
    
            If cTIM_Var_CallBackArg Is Nothing Then
                CallByName(cTIM_Var_CallBackObj, cTIM_Var_CallBackProc, CallType.Method)
            Else
                CallByName(cTIM_Var_CallBackObj, cTIM_Var_CallBackProc, CallType.Method, cTIM_Var_CallBackArg)
            End If
    
    
            cTIM_Var_Running = False
        End Sub
    
    
    
    
        Protected Overrides Sub Finalize()
            cTIM_Var_Timer.Close()
            MyBase.Finalize()
        End Sub
    
    
    End Class
    Option Explicit
    
    
    Private aaa As cTimer
    
    
    
    
    Sub test()
        
        Set aaa = Nothing
        Set aaa = New cTimer
        
        Debug.Print aaa.fSetTimer(3000, Me, "kkk", "5000")
        
    End Sub
    
    
    Sub kkk(iarg As String)
        
        Debug.Print iarg
        Set aaa = Nothing
        
    End Sub
    And This is Code in VBA.

    When I Run 'test()' procedure in VBA Code and re-rerun it again before 3 secs, the Old one is not destroyed and runs 'kkk' procedure.

    so, the result in console window shows "5000" twice. I hope to destroy the class library's instance in VBA.

    Please, help. Thank you.

  2. #2
    Why not use the Windows SetTimer API function instead and forget about VB.NEt entirely :-) ?

    Option Explicit
    'API Declarations
    Declare Function SetTimer Lib "user32" _
                                (ByVal hwnd As Long, _
                                 ByVal nIDEvent As Long, _
                                 ByVal uElapse As Long, _
                                 ByVal lpTimerFunc As Long) As Long
    Declare Function KillTimer Lib "user32" _
                                (ByVal hwnd As Long, _
                                 ByVal nIDEvent As Long) As Long
    
    ' Public Variables
    Public mlTime As Long
    Public mlTimerID As Long
    Public mbTimerState As Boolean
    Sub TimerOn()
        mlTime = 1
        If mbTimerState = False Then
            mlTimerID = StartTimer
        End If
        If mlTimerID = 0 Then
            MsgBox "Unable to create the timer", vbCritical + vbOKOnly, "Error"
            Exit Sub
        End If
        mbTimerState = True
    End Sub
    Sub TimerOff()
        If mbTimerState = True Then
            mlTimerID = KillTimer(0, mlTimerID)
            If mlTimerID = 0 Then
                MsgBox "Unable to stop the timer", vbCritical + vbOKOnly, "Error"
            End If
            mbTimerState = False
        End If
        Application.StatusBar = False
    End Sub
    Function StartTimer()
        StartTimer = SetTimer(0, 0, mlTime * 1000, AddressOf TimedSub)
    End Function
    Sub TimedSub()
        If IsExcelInEditMode Then
            Application.StatusBar = Now & ", Editing a cell!"
        Else
            Application.StatusBar = Now & ", Not editing a cell!"
        End If
    End Sub
    Private Function IsExcelInEditMode() As Boolean
        IsExcelInEditMode = (Not Application.CommandBars("Worksheet Menu Bar").FindControl(ID:=18, recursive:=True).Enabled)
    End Function
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  3. #3
    VBAX Newbie
    Joined
    Aug 2015
    Posts
    3
    Location
    The reason is that 'SetTimer' needs a global procedure in module for eventhandler. With SetTimer, the class calls a procedure outside of the class for eventhandler. It makes a class diffused into two(module and class). So, VBA Project will be messy and reusing the class will become difficult because of this problem.

    But, for my testing, I found that an instance of a class from dll is finalized by garbage collector(?) when system memory is lacking and after 'set object =nothing'.

    Thank you, Jan Karel Pierterse.

  4. #4
    Arguably I find tying a .NET dll into the VBA project waaaaay more complex than having the callback proc in a module, but that is personal :-)
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  5. #5
    VBAX Newbie
    Joined
    Aug 2015
    Posts
    3
    Location
    Your opinion is right, I think so. It's complex way

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •