McMH
06-27-2017, 08:19 AM
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.
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.