How to clear Excel cache-VBForums
Results 1 to 7 of 7

Thread: How to clear Excel cache

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2009
    Posts
    4

    How to clear Excel cache

    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!!!

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,238

    Re: How to clear Excel cache

    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.

  3. #3

    Thread Starter
    New Member
    Join Date
    Jul 2009
    Posts
    4

    Re: How to clear Excel cache

    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




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

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,238

    Re: How to clear Excel cache

    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:
    Code:
    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:
    Code:
            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.

  5. #5

    Thread Starter
    New Member
    Join Date
    Jul 2009
    Posts
    4

    Re: How to clear Excel cache

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

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,238

    Re: How to clear Excel cache

    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.

  7. #7

    Thread Starter
    New Member
    Join Date
    Jul 2009
    Posts
    4

    Re: How to clear Excel cache

    wil do.

    thks for the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.