+ Reply to Thread
Results 1 to 8 of 8

Excel slows and grows larger with every use of VBA.

  1. #1
    Registered User
    Join Date
    08-10-2016
    Location
    Portsmouth, England
    MS-Off Ver
    2010
    Posts
    4

    Excel slows and grows larger with every use of VBA.

    I'm currently working on a spreadsheet that is designed so that the user can not alter any data without using a UserForm that automatically time, name and reason stamps the changes.
    In order to do this I created a UserForm that copies the entry that needs to be changed into a hidden spreadsheet and makes the changes before copying it back to the master sheet. The code does initially work, however, the problem is every time I use the userform, excel gets slower and increases in size until it becomes unusable. (The size and speed stay as they are despite saving, closing and reopening) I originally thought it could be a clipboard issue due to the copying and pasting but added in code to wipe the clipboard after every paste event, although this doesn't explain why the speed remains slow even after the programme is reopened. I've checked the end cells and they are correctly at the end of my used range. I also stopped auto calculations and all that good stuff but to no avail.
    Unfortunately I am on a workplace computer and do not have access rights to see if its a third party programme causing it, although I don't understand how it would effect the size of the spreadsheet if it was.

    All userforms which have this problem share a similar code but it seems to be the following which is the problem. (Though I seem to have successfully used it on another spreadsheet)

    The copy to the hidden sheet:
    Please Login or Register  to view this content.
    Or the copy back to the master sheet:
    Please Login or Register  to view this content.

  2. #2
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Excel slows and grows larger with every use of VBA.

    Please Login or Register  to view this content.
    I guess the A is a typo and must be a Y

    Secondly do you have to copy entire row to sheet Info?
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  3. #3
    Registered User
    Join Date
    08-10-2016
    Location
    Portsmouth, England
    MS-Off Ver
    2010
    Posts
    4

    Re: Excel slows and grows larger with every use of VBA.

    Sorry, yes the A is a typo, whilst typing the code in on here.
    The entire range does not need to be selected just columns A:L, however, I'm a newbie at VBA and wouldn't know how to just select that range.

  4. #4
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Excel slows and grows larger with every use of VBA.

    Something like this.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-10-2016
    Location
    Portsmouth, England
    MS-Off Ver
    2010
    Posts
    4

    Re: Excel slows and grows larger with every use of VBA.

    Although your code does work, the problem still persists. The spread sheet grows significantly in size and time taken to complete the macro the more it is used.
    The spread sheet has grown from 478KB to 1.42MB from simply using the macro 4 times.
    All the macro does is copy the entry to a hidden sheet, amend some of the data and copy it back over.

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Excel slows and grows larger with every use of VBA.

    Without the copy-event.
    Please Login or Register  to view this content.
    And how does rest of your userform code looks like?

  7. #7
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Excel slows and grows larger with every use of VBA.

    Without the copy-event.
    Please Login or Register  to view this content.
    And how does rest of your userform code looks like?

  8. #8
    Registered User
    Join Date
    08-10-2016
    Location
    Portsmouth, England
    MS-Off Ver
    2010
    Posts
    4

    Re: Excel slows and grows larger with every use of VBA.

    Ok so the process is split into 3 parts.
    The user needs to first identify the entry that they need to view which is:

    Please Login or Register  to view this content.
    After that, the data review page will appear where users can view the data and make minor amendments if needed:

    Please Login or Register  to view this content.
    From there, any amendments made will need to authorised by entering your name:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 13
    Last Post: 07-07-2015, 12:09 PM
  2. [SOLVED] Graph grows as data grows
    By butlerar in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-31-2014, 06:56 PM
  3. Replies: 0
    Last Post: 05-18-2011, 07:43 AM
  4. Changing screen resolution - combo box size grows larger/smaller
    By Carlsbergen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-21-2010, 06:50 AM
  5. Excel file size grows when copied
    By EsAssistant in forum Excel General
    Replies: 2
    Last Post: 12-10-2010, 05:53 AM
  6. one colomn grows as another one grows?
    By doctorteeth in forum Excel General
    Replies: 0
    Last Post: 07-28-2010, 06:37 AM
  7. [SOLVED] openoffice sheet to excel: grows considerably
    By Erik in forum Excel General
    Replies: 2
    Last Post: 01-15-2005, 01:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1