1

i am developing an app that will be working for days and implements a Timer (From user32 lib) that is running its routine every 500ms. The problem is that every time the routine executes, the memory required by the Excel App is being increased by 8KB.

As i said i will like the app to be running for days so there is a point that it's memory consumptions is too high, and the app starts to be too slow.

I've searched in this and other places for a way to solve but i haven't find a solution. I read about forcing GC but in vba i cant do it. ¿Can anyone give me some advice?

P.d.: Thank you and sorry for my poor english.

Edit:

Hi again, i use the Timer Event to communicate to a PLC and act in consequence. Maybe 0.5 seconds is such a short period of time that the code can't finish the routine and the events are being located in the stack. I think i could extend the time to 1 second without losing performance.

Here is my code:

API DECLARATIONS

Private Declare Function SetTimer Lib "user32" _
(ByVal hWnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long

Private Declare Function KillTimer Lib "user32" _
(ByVal hWnd As Long, _
ByVal nIDEvent As Long) As Long

TIMER EVENT ROUTINE

Private Sub TimerEvent()

    On Error Resume Next
    Hoja1.cmdFecha.Caption = Format(Now, "dd/mm/yy hh:mm:ss")
    Hoja6.Range("I40") = 0
    'Zona Lectura PLC
    Call readFromPLC

    If Hoja6.Range("I40") = 0 Then
        Hoja4.Range("c11") = 1
    Else
        Hoja4.Range("c11") = 0
    End If
    'Zona alarmas
    If Hoja4.Range("C7") <> AlarmaAnterior Then
        ' Interrupcionpo calculo
        AlarmaAnterior = Hoja4.Range("D10")
        If Hoja4.Range("c7") = 0 Then
           Hoja1.Label1.Visible = False
        Else
            'Hoja4.Range("d8") = Hoja4.Range("d8") + 1
            'Call Control
            Call AlarmasNuevo
            Hoja1.Label1.Visible = True
        End If
    End If

    'Zona actuacion


    If Hoja6.Range("d61") <> Hoja6.Range("d62") Then
           Hoja6.Range("d62") = Hoja6.Range("d61")
             Hoja6.Range("d66") = Hoja6.Range("d66") + 1
            Call ControlArchivos
                End If
    If Hoja6.Range("d63") <> Hoja6.Range("c63") Then
        Hoja6.Range("d63") = Hoja6.Range("c63")
        Call ResetContadores
    End If


    If Hoja6.Range("I50") = 0 Then

    ElseIf Hoja6.Range("I49") <> Hoja6.Range("j49") Then
           Hoja6.Range("J49") = Hoja6.Range("i49")
           If Hoja6.Range("I49") <> 0 Then
            Call Medir
            Else
                Call StopAcq
                Sheets("ESCPLC").Range("J58") = 0
                Hoja1.cmdAvisos.Visible = False
            End If

                    End If


    'Zona escritura PLC
    If Hoja6.Range("J57") <> Hoja6.Range("L57") Or Hoja6.Range("J58") <> Hoja6.Range("L58") Or Hoja6.Range("J59") <> Hoja6.Range("L59") Or Hoja6.Range("J60") <> Hoja6.Range("L60") Or Hoja6.Range("J61") <> Hoja6.Range("L61") Then
        Hoja6.Range("L57") = Hoja6.Range("J57")
        Hoja6.Range("L58") = Hoja6.Range("J58")
        Hoja6.Range("L59") = Hoja6.Range("J59")
        Hoja6.Range("L60") = Hoja6.Range("J60")
        Hoja6.Range("L61") = Hoja6.Range("J61")
        Call writeToPLC
    End If 
End Sub

Thank You very much

Alex K.
  • 171,639
  • 30
  • 264
  • 288
MMM
  • 11
  • 2
  • 1
    You will need to provide more details/show some code; what timer API are you using? – Alex K. Feb 03 '14 at 12:10
  • exactly like Alex said, we need more details, specially the code. It would be possible to implement this as a COM solution using the .NET framework and use the `GC.Collect()` to force the GC. [**`SEE THIS`**](http://stackoverflow.com/questions/20398477/how-to-set-application-name-in-adodb-connection-string/20406284#20406284) to get an idea of what im talking about using a com library and .net framwork. Specifically the `.Dispose` method called from Excel but clearing the COM objects created in .NET runtime –  Feb 03 '14 at 12:30
  • 1
    I am also curious as to what does your application do. Why do you want to run the app after every .5 seconds? Also was wondering what does the app do that it actually finishes in .5 seconds so that it is called again... – Siddharth Rout Feb 03 '14 at 16:28
  • Are you killing the timer as soon as it fires, doing your stuff then recreating it? If 1 seconds is ok, use the built-in application.ontime – Alex K. Feb 03 '14 at 17:22
  • Hi again, first of all let me apologize because i was so busy that i cant even test the solutions you provided. I have discovered that the code line that increases the memory required by the app is when i refresh the caption of a label placed in a Sheet. Hoja1.cmdFecha.Caption = Format(Now, "dd/mm/yy hh:mm:ss") I will try different ways of refreshing the caption in order to prevent that. Thank you, i will update this post if i find the solution. – MMM Mar 25 '14 at 08:17

0 Answers0