PDA

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



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.

Jan Karel Pieterse
06-29-2017, 06:43 AM
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

McMH
06-29-2017, 07:44 AM
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.

Jan Karel Pieterse
06-29-2017, 08:18 AM
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 :-)

McMH
06-30-2017, 04:31 AM
Your opinion is right, I think so. It's complex way