How to clear Excel cache

[jbitar]

Hello,

I use a VBA form to enter data in an excel file (lots of information)
After a while the excel file becomes too slow (hiding and reloading the form takes too much time)

How could I clear the excel cache in order to keep the datat entry process smooth (and thus reloading the forms quickly)

Thks a lot!!!

[si_the_geek]

Thread moved from "Database Development" forum to "Office Development" forum

Welcome to VBForums

You haven't given much detail, but it sounds to me as if the problem is probably not due to what Excel is doing (or not doing), but rather due to your code doing things that are not apt.

Due to previous questions like this, I suspect you are loading large amounts of data on to the forms (possibly in multiple controls), and that will slow things down.

If you show us the code for the slow part (and give us a rough idea of the amount of data involved for each step), we can make suggestions of how to improve it.

[jbitar]

Thks for your prompt answer!

I have a form (that indeed when loaded, uploads a lot of information from an Excel sheet) The user selects the data to be entered and presses "Next"

After each "Next", the reloading of the sheet takes a lot of time when Excel has been open for a while. After 2 or 3 "Next", the file becomes too slow, and users have to exit excel and re-enter for the file to start working fast again.

I would like to speed up the process.

Three steps are involved when pressing "Next"
Step 1) Controls are done (coherence controls)
Step 2) Data entered in Excel sheet
Step 3) File reloaded and data cleared (I clear the comboboxes etc...)

Here is the code for step 2 that enters the data to the excel file, before re-loading the form.

Thks for the help!!!!!

JB

Application.ScreenUpdating = True
'I- LOAD DATA FROM THE FORM

MyLeader1 = frmEnterData2.lblLeader1.Caption
MyLeader2 = frmEnterData2.lblLeader2.Caption
MyLeader3 = frmEnterData2.lblLeader3.Caption
MyLeader4 = frmEnterData2.lblLeader4.Caption
MyLeader5 = frmEnterData2.lblLeader5.Caption
MyLeader6 = frmEnterData2.lblLeader6.Caption
MyLeader7 = frmEnterData2.lblLeader7.Caption
MyLeader8 = frmEnterData2.lblLeader8.Caption
MyLeader9 = frmEnterData2.lblLeader9.Caption
MyLeader10 = frmEnterData2.lblLeader10.Caption

MyTime1 = frmEnterData2.lblTime1.Caption
MyTime2 = frmEnterData2.lblTime2.Caption
MyTime3 = frmEnterData2.lblTime3.Caption
MyTime4 = frmEnterData2.lblTime4.Caption
MyTime5 = frmEnterData2.lblTime5.Caption
MyTime6 = frmEnterData2.lblTime6.Caption
MyTime7 = frmEnterData2.lblTime7.Caption
MyTime8 = frmEnterData2.lblTime8.Caption
MyTime9 = frmEnterData2.lblTime9.Caption
MyTime10 = frmEnterData2.lblTime10.Caption

MyLeaderCountry1 = frmEnterData2.lblC1.Caption
MyLeaderCountry2 = frmEnterData2.lblC2.Caption
MyLeaderCountry3 = frmEnterData2.lblC3.Caption
MyLeaderCountry4 = frmEnterData2.lblC4.Caption
MyLeaderCountry5 = frmEnterData2.lblC5.Caption
MyLeaderCountry6 = frmEnterData2.lblC6.Caption
MyLeaderCountry7 = frmEnterData2.lblC7.Caption
MyLeaderCountry8 = frmEnterData2.lblC8.Caption
MyLeaderCountry9 = frmEnterData2.lblC9.Caption
MyLeaderCountry10 = frmEnterData2.lblC10.Caption

MyLeaderInstitution1 = frmEnterData2.lblF1.Caption
MyLeaderInstitution2 = frmEnterData2.lblF2.Caption
MyLeaderInstitution3 = frmEnterData2.lblF3.Caption
MyLeaderInstitution4 = frmEnterData2.lblF4.Caption
MyLeaderInstitution5 = frmEnterData2.lblF5.Caption
MyLeaderInstitution6 = frmEnterData2.lblF6.Caption
MyLeaderInstitution7 = frmEnterData2.lblF7.Caption
MyLeaderInstitution8 = frmEnterData2.lblF8.Caption
MyLeaderInstitution9 = frmEnterData2.lblF9.Caption
MyLeaderInstitution10 = frmEnterData2.lblF10.Caption

MyPlace1 = frmEnterData2.lblPlace1.Caption
MyPlace2 = frmEnterData2.lblPlace2.Caption
MyPlace3 = frmEnterData2.lblPlace3.Caption
MyPlace4 = frmEnterData2.lblPlace4.Caption
MyPlace5 = frmEnterData2.lblPlace5.Caption
MyPlace6 = frmEnterData2.lblPlace6.Caption
MyPlace7 = frmEnterData2.lblPlace7.Caption
MyPlace8 = frmEnterData2.lblPlace8.Caption
MyPlace9 = frmEnterData2.lblPlace9.Caption
MyPlace10 = frmEnterData2.lblPlace10.Caption

MySubjectCountry1 = frmEnterData2.lblSC1.Caption
MySubjectCountry2 = frmEnterData2.lblSC2.Caption
MySubjectCountry3 = frmEnterData2.lblSC3.Caption
MySubjectCountry4 = frmEnterData2.lblSC4.Caption
MySubjectCountry5 = frmEnterData2.lblSC5.Caption
MySubjectCountry6 = frmEnterData2.lblSC6.Caption
MySubjectCountry7 = frmEnterData2.lblSC7.Caption
MySubjectCountry8 = frmEnterData2.lblSC8.Caption
MySubjectCountry9 = frmEnterData2.lblSC9.Caption
MySubjectCountry10 = frmEnterData2.lblSC10.Caption

MyAlert = frmEnterData2.txtAlert.Value

''II- ENTER DATA IN WORKSHEET

'Check out Number of rows to be entered
NumbRows = frmEnterData2.lblNumber.Caption

If NumbRows = 0 Or NumbRows = "" Then 'no leaders or one leader
        Worksheets("Log").Activate
        lastRow = Range("A65536").End(xlUp).Row
        Range("A" & MyRow).Value = MyCoder
        Range("B" & MyRow).Value = MyCodingDate
        Range("C" & MyRow).Value = MyChannel
        Range("D" & MyRow).Value = MyProgramName
        Range("E" & MyRow).Value = MyProgramDate
        Range("F" & MyRow).Value = MyPresenter
        Range("G" & MyRow).Value = MySegment
        Range("H" & MyRow).Value = MySubjectType
        Range("I" & MyRow).Value = MySubjectDesc
        Range("J" & MyRow).Value = MyNewsType1
        Range("K" & MyRow).Value = MyNewsType2
        Range("L" & MyRow).Value = MyNewsGrid
        Range("M" & MyRow).Value = MyLeader
        Range("N" & MyRow).Value = MyLeaderInstitution
        Range("O" & MyRow).Value = MyLeaderCountry
        Range("P" & MyRow).Value = MyCompanyName
        'Range("Q" & MyRow).Value = MyInstitutionCountry
        Range("R" & MyRow).Value = MyPlace
        Range("S" & MyRow).Value = MyPlaceCountry
        Range("T" & MyRow).Value = MyErrorType
        Range("U" & MyRow).Value = MyErrorDesc
        Range("V" & MyRow).Value = MyAlert
        Range("W" & MyRow).Value = MyTarget
        Range("X" & MyRow).Value = MyTargetType
        'Range("Y" & MyRow).Value = MyWho
        'Range("Z" & MyRow).Value = MyWhoType
        Range("AA" & MyRow).Value = MyExplicit
        Range("AB" & MyRow).Value = MyStartTime
        Range("AC" & MyRow).Value = MyEndTime
        Range("AD" & MyRow).Value = MyTimeSec
        Range("AE" & MyRow).Value = MyTimeMin
        Range("AF" & MyRow).Value = MySegmentType
        Range("AG" & MyRow).Value = MyScreen
        Range("AH" & MyRow).Value = MyTheme
        Range("AI" & MyRow).Value = MyLeaderSide
        Range("AJ" & MyRow).Value = MyInstitutionSide
        Range("AK" & MyRow).Value = MySubjectType1
        Range("AL" & MyRow).Value = MySubjectType2
        Range("AM" & MyRow).Value = MyOpinionText
        Range("AN" & MyRow).Value = MyCompany
        Range("AO" & MyRow).Value = MySubjectCountry
        Range("AP" & MyRow).Value = MyRegion
        
        If MyRow = 3 Then 'first entry start hour already entered
        Range("AT" & MyRow).Value = "=" & Range("AS" & MyRow).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "+" & "(" & Range("AC" & MyRow).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "-" & Range("AB" & MyRow).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
        Else
        Range("AS" & MyRow).Value = "=" & Range("AT" & MyRow - 1).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "+" & 1.15740740740741E-05 'ads one second
        Range("AT" & MyRow).Value = "=" & Range("AS" & MyRow).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "+" & "(" & Range("AC" & MyRow).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "-" & Range("AB" & MyRow).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
        End If
        'Create subject log
        'CreateSubjectLog.CreateSubjectLog
   
Application.ScreenUpdating = True

[si_the_geek]

There is certainly room for improvement there.

To start with, the first line should end with =False instead of =True , as that will save Excel from re-drawing itself every time your code changes a cell value etc.

Your section "'I- LOAD DATA FROM THE FORM" seems pointless based on what you have shown, as the variables aren't used - but I assume that they are used in code you haven't shown us.

I don't know why you have the code to set lastRow (which will be relatively slow), as that doesn't seem to be used either.


When specifying a cell it is better to specify the Sheet too (and preferably the Workbook), as that way Excel doesn't need to work out which one you intended, eg:

Worksheets("Log").Range("A" & MyRow).Value = MyCoder

..doing that also means that you don't need the .Activate line, which slows things down.

In terms of speed setting the values of cells individually is very poor (because Excel re-calculates everything after each one), it is much better to set them all at once, which you can do using an array, eg:

        Worksheets("Log").Range("A" & MyRow & ":P" & MyRow).Value = _
           Array(Array(MyCoder, MyCodingDate, MyChannel, MyProgramName, _
                          MyProgramDate, MyPresenter, MySegment, MySubjectType, _
                          MySubjectDesc, MyNewsType1, MyNewsType2, MyNewsGrid _
                          MyLeader, MyLeaderInstitution, MyLeaderCountry, _
                          MyCompanyName))

The formulas at the end could also be done in a better way, but I would need to do a bit of research to work out what your current code is achieving.


However... the majority of these recommendations will have a very small effect, as the code you have shown doesn't actually do that much (in terms of the time it would take to run). I suspect that step 3 is where the most time is used.

[jbitar]

Thks a lot!!

Lots of more code, but the limit of posting on the forum was reached (understandably

I will rework the code to make use of arrays and better use of loops (found lots of helpful info on the forum) and With/End With

But the problem still remains that after a while it all slows down (and runs faster after i reopen excel). Is there a hidden excel memory that I could purge?

I read on the forum that

Dim objVar As Excel.Application
Set objVat = Nothing

could do the trick, but it doesn't seem to be working, any idea on cleaning the memory (which could explain why it slows down after a while?)

[si_the_geek]

The slowdown is unlikely to be anything to do with Excel itself, and I have no idea if there is a way to purge memory. The best I can think of is to save the workbook(s) periodically.

The issue is probably due to your code etc, but we can't see enough of it to tell. Feel free to put your code into a .txt file, and attach it here (click on "post reply", then "manage attachments").

The code snippet you posted wont help, as that is for use outside of Excel.

[jbitar]

wil do.

thks for the help!