+ Reply to Thread
Results 1 to 6 of 6

Very Slow Process Code

  1. #1
    Registered User
    Join Date
    05-10-2017
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    23

    Very Slow Process Code

    A VBA code built for exchanging images position and numbers is working fine however it's extremely slow to accomplish the process any advises what might be wrong?

    Please Login or Register  to view this content.
    Thank you in advanced

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,754

    Re: Very Slow Process Code

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.
    Crossposted: http://www.ozgrid.com/forum/showthread.php?t=205166
    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    08-08-2014
    Location
    Lancaster, PA
    MS-Off Ver
    2016 (windows & mac)
    Posts
    94

    Re: Very Slow Process Code

    There are a couple of things going on here

    The first few lines of code have been commented out; these lines help speed up code execution so un-commenting them will help; although that said only ScreenUpdating & EnableEvents will really have much of an effect with the operations you're doing, so you can probably lose the other ones. You'll also need to un-comment out the respective matching lines at the end of the sub-routine to re-enable these settings.

    Also the structure of the code is such that for each cell you test you're looping all pictures in the sheet. Granted you exit the loop when you've found a match; but worst case scenario you're redundantly looping through all the pictures multiple time.

    A better approach would be to just loop the pictures once and test each picture name against the cell range values using a switch statement, like this...

    Please Login or Register  to view this content.
    Note: the above code is untested.

    I also put this code in a Worksheet_Change event, your original code ran every time the sheet re-calculated, which was likely causing a lot of unnecessary code executions and wasted overhead. I have written the Worksheet_Change code so that it exits unless one of the cells you're comparing against has changed.

    I also not really sure what your timer function was doing, other than seeing how long it takes your machine to count a million! I have added timing to this sub-routing as well so you can see how long it takes to execute.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    91,068

    Re: Very Slow Process Code

    Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

    If you are unclear about the request or instruction then send a private message to them asking for clarification.

    All participants:
    Please do not post a reply in a thread where a moderator has requested an action that has not yet been complied with e.g Title change or Code tags...etc. Thanks.

    There should be no further responses until cross-post links have been added by the OP.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    08-08-2014
    Location
    Lancaster, PA
    MS-Off Ver
    2016 (windows & mac)
    Posts
    94

    Re: Very Slow Process Code

    Quote Originally Posted by AliGW View Post
    Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

    If you are unclear about the request or instruction then send a private message to them asking for clarification.

    All participants:
    Please do not post a reply in a thread where a moderator has requested an action that has not yet been complied with e.g Title change or Code tags...etc. Thanks.

    There should be no further responses until cross-post links have been added by the OP.
    apologies Ali - Alan's post indicated that the OP shouldn't cross post without links to the other threads and also included a link to the other thread that the OP had created, so I assumed (incorrectly it would appear) that had taken care of the issue.

  6. #6
    Registered User
    Join Date
    05-10-2017
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    23

    Re: Very Slow Process Code

    My apology i just posted the question to receive asap help, I apologize again. I can't edit the question to add the link as needed, is it considered ok?

+ 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. Data Entry Process is Slow
    By rana19 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-02-2015, 01:52 AM
  2. Vertical grouping & ungrouping process slow
    By Gti182 in forum Excel General
    Replies: 2
    Last Post: 09-30-2014, 12:53 PM
  3. VBA Slow Process
    By mcmunoz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-23-2013, 10:41 PM
  4. Replies: 6
    Last Post: 02-04-2013, 02:18 AM
  5. [SOLVED] Slow process to replace blocks of zeros with null
    By Ariostea in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-27-2012, 10:49 PM
  6. VBA code slow to process. asynchronous fetch for sql statement?
    By tasxj in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-01-2012, 05:02 AM
  7. Slow process in calculation in cell
    By Lewis Koh in forum Excel General
    Replies: 0
    Last Post: 08-23-2010, 03:32 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