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.

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

---

Code:

---

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.

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

Code:

---

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

---

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.

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 :-)

Your opinion is right, I think so. It's complex way