+ Reply to Thread
Results 1 to 10 of 10

VBA code execution time

  1. #1
    Registered User
    Join Date
    08-25-2017
    Location
    England
    MS-Off Ver
    2016
    Posts
    36

    VBA code execution time

    Hi

    I'm hoping some one might be able to review this code and offer some ideas why it can take so long to run in Excel 2016.

    Its a very basic copy and paste set of instructions that copies data from multiple different tabs and paste it into a single tab.

    Thing is sometimes it takes a few seconds to run, and other times it can take 15 minutes. Is there anything you experts can see that I could amend to make it run more consistently?

    Thanks in Advance.

    Please Login or Register  to view this content.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,898

    Re: VBA code execution time

    Without knowing more specifics it's hard to give definitive answer but always a good idea to optimise your environment, and avoid native copies etc {in my experience!}

    The below is a quick update on your code - including some environment settings, and tmpArray use etc...

    Please Login or Register  to view this content.
    I'd suggest replacing reference to ActiveSheet with the Sheets("name") reference, unless it's meant to vary on activation of routine.

    On a final aside, I'm assuming (given explicit reference to row 2500) that you have lots of blanks in these worksheets, else you will end up overwriting data generated on previous worksheet iterations.

    If the above is true you can likely use a different construct to identify populated area on your precedent worksheet(s) as opposed to fixing on a hard coded range.

  3. #3
    Registered User
    Join Date
    08-25-2017
    Location
    England
    MS-Off Ver
    2016
    Posts
    36

    Re: VBA code execution time

    Hi

    Thanks for the reply, and the code you suggested runs a lot faster. Although for reasons I am not sure of, stops coping data at Thursday and terminates with "Computer Says No". There is nothing different about the data from Thursday onwards.

    The data itself is a just basic numeric data and a couple of columns that contain basic formulas. It comes in daily and never really longer than 400 to 500 rows, therefore the copy range doesn't need to be more than that.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,278

    Re: VBA code execution time

    How many rows have been copied before it fails?
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,292

    Re: VBA code execution time

    What does the computer actually say when trying to copy the Thursday data?
    If posting code please use code tags, see here.

  6. #6
    Registered User
    Join Date
    08-25-2017
    Location
    England
    MS-Off Ver
    2016
    Posts
    36

    Re: VBA code execution time

    Error is;

    1004
    Application-defined or object-defined error

    1239 lines of data are copied, which is exactly Monday to Wednesday

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,292

    Re: VBA code execution time

    When that happens which line of code is highlighted if you click Debug?

    By the way, is there anything at all different about the data that's causing the problem?

  8. #8
    Registered User
    Join Date
    08-25-2017
    Location
    England
    MS-Off Ver
    2016
    Posts
    36

    Re: VBA code execution time

    It's strange, as soon as I hit run the error message appears, but the code runs anyway.
    As for the data, Thursday data is identical in format and rough size.

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,278

    Re: VBA code execution time

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  10. #10
    Registered User
    Join Date
    08-25-2017
    Location
    England
    MS-Off Ver
    2016
    Posts
    36

    Re: VBA code execution time

    Hi

    Uploading was not an option for privacy reasons, however I was able to experiment and find that simply adding the code to turn events on and off had a massive impact, and reduced the copy time down to seconds.

    I am still curious as to why the code example stopped at Thursday, but I have achieved the desired results regardless.

    Thanks to everyone that helped and rep has been added.

+ 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. [SOLVED] Time of execution
    By Marius44 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-06-2016, 02:06 PM
  2. No Error in Code but thee is no code execution
    By barillas1089 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-21-2016, 06:45 PM
  3. Time limit on Code Execution
    By bgunawan in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 07-15-2009, 07:51 AM
  4. Code Execution Degrades Over Time
    By KDT in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-25-2008, 04:28 PM
  5. Lookup execution time
    By Zade in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-26-2007, 01:34 PM
  6. Date Time of Last Execution
    By JB2010 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-22-2006, 10:35 AM
  7. Macro execution time
    By Henry in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-08-2005, 11:05 AM

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