+ Reply to Thread
Results 1 to 5 of 5

Reduce macro run time

  1. 03-01-2018, 12:55 PM #1
    charm1
    charm1 is offline
    Registered User
    Join Date
    12-18-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2010
    Posts
    55

    Reduce macro run time

    Hi Experts,

    I have created a dashboard (normal table lookalike) in excel "Sheet1" representing the values from couple of other open workbooks. However to show those values it takes lot of calculation which I have done through formulas and using VBA to paste those formulas. There are 52 different formulas few are very long and it takes 12 seconds for VBA to paste those formulas in the different ranges. Below is example how I am doing.

    Is there anyway to reduce run time of the macro? I am sure VBA masterminds here must be having some workaround.

    Please Login or Register  to view this content.
    Charm!
    Reply With QuoteRegister To Reply
  2. 03-01-2018, 01:06 PM #2
    Mumps1
    Mumps1 is offline
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,034

    Re: Reduce macro run time

    See if this helps:
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.
    Reply With QuoteRegister To Reply
  3. 03-01-2018, 01:07 PM #3
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Reduce macro run time

    Maybe:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
    Reply With QuoteRegister To Reply
  4. 03-01-2018, 01:22 PM #4
    charm1
    charm1 is offline
    Registered User
    Join Date
    12-18-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2010
    Posts
    55

    Re: Reduce macro run time

    Thanks for giving your time Mumps1 & xladept

    I will try both the ways and will use the fastest one.

    I will post my findings.

    Charm
    Reply With QuoteRegister To Reply
  5. 03-01-2018, 02:29 PM #5
    Zer0Cool
    Zer0Cool is offline
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Reduce macro run time

    As others have pointed out, manual calculation may help. However it has to calculate at some point, and how long that takes depends on the complexity of the formulas and the complexity of your calc chain. Those 2 aspects can only be addressed by streamlining the formulas.

    In other words, putting off 12 seconds of calculations until everything is ready may help a little, but if your calculations take 12 seconds after they are all placed on sheet....VBA wont help with that.

    If you can post sanitized sample files, at least something with the formulas you are using in them it would help.

    After looking at your code, its likely you can and should do this without formulas at all. You are putting formulas in cells, calculating the results and then using .value = .value I presume to replace the formulas with the results? You may consider instead using Application.WorksheetFunctions.x or it may be possible to use VBA functions to the same effect.
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to reduce a "time value(am/pm) from another time value(am/pm) and calculating totals
    By Anuru in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-23-2015, 09:30 AM
  2. Want to reduce Macro running time
    By rvkadu1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-27-2013, 12:31 PM
  3. Macro takes approx. 15 seconds to run, how do i reduce run time?
    By galvinpaddy in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 10-25-2012, 07:15 AM
  4. [SOLVED] Reduce Macro Time
    By pezalmendra in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-24-2012, 12:31 PM
  5. Reduce Macro Time
    By matt9man in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-05-2011, 03:01 PM
  6. Reduce Macro Time
    By aftabn10 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-25-2008, 11:06 AM
  7. how do I reduce calculation time?
    By Dora C in forum Excel General
    Replies: 14
    Last Post: 09-15-2008, 03: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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1