Speed up slow calculation in vba

  • This is my first code ive written in vba so I apologise in advance...

    My problem is this...
    I have created a macro that calculates mduration, it take the relevent inputs and plugs it into the macro version of mduration (a function). The macro runs fine all the way through. However, it takes about 13mins to complete!!!

    I have tried everything that I could think of but alas my knowledge of vba is not good enough.

    My question is:
    Can anyone help me find a way to reduce this speed, in an ideal world to under a minute.

    - The this macro runs on 130,000 rows!
    - The only way i could think to create this macro is with a loop (suggestions welcome)

    I have used alot of "offset" in my coding so the actual columns are the following;

    AD = where i want the results to appear
    AK = coupon
    K = NAV
    E = Nominal
    AB = Mdate
    Q = asset type
    AC = maturity

    Once again this is my first code and first post so its not perfect.

    Thank you!!!!

  • Re: Speed up mduraiton calculation in vba

    Sorry i posted a messy code.............here is a neater version with very slight (but irrevent) bits taken out.................

  • Re: Speed up slow calculation in vba

    Ughhhh - wouldn't know where to start!

    If the worksheet contains 130,000 then you do have some work to do.

    All those Element.property.property.value identifiers take time for VBA to workthrough. It might be a start to:

    If you know where each column is, code them specifically rather than relying on Offset. You could set then as a variable in case they change (But I seriously doubt they will!)

    That first If... statement could be rationalised. Perhaps get the value of the cell in offset(0, -13) and then compare the value in the variable.

    You assign values to variables which are only used once.
    As ease of reading is probably not an issue here, why assign to a variable and then use that variable only once in a call to a function?
    For example, replace

    maturity = ActiveCell.Offset(0, -1).Value
    mdateround = Round((40178 + (maturity * 365)), 0)


    mdateround = Round((40178 + (ActiveCell.Offset(0, -1).Value* 365)), 0)

    Where possible use With...End With

    Only my thoughts - without a copy of the workbook it's hard to comment, but I doubt you'd manage to post a resonably sized example even if you could remove all confidential data.

  • Re: Speed up slow calculation in vba

    Well, you've discovered the pitfall of looping - it is inefficient because you are working on a cell-by-cell basis. You've already included a few tricks to speed up the code by turning off calculation and screen updating, etc.

    If possible, re-develop your approach to use filtering and the SpecialCells(xlCellTypeVisible) property to act upon a entire range of cells in one go.
    Just looking at the code, it seems you could/should be using formula directly in the cells, which is typically much faster than using code.

  • Re: Speed up slow calculation in vba

    Thank you both for your quick replies and help!

    I have changed the coding as you've said and am now looking into With, End With...so thank you!!

    It has to run using code. Im looking into special cells and filtering. So thank you aswell!!

    If I manage to reduce the time i will update the post so you can (hopefully) approve.

    Once again thank you both for putting the time in to help!


  • Re: Speed up slow calculation in vba


    It has to run using code. Im looking into special cells and filtering.

    I meant: use VBA to automate filtering and using the SpecialCells(xlCellTypeVisible) property to act on the visible cells.

  • Re: Speed up slow calculation in vba

    Just to chip in here with some further ideas about optimisation.

    There are times when


    It has to run using code

    but in my experience, people often reach for VBA too soon. VBA is excellent when used correctly and does solve problems that cannot be done even by the most complex of formulas. However if used incorrectly it can bring with it huge performance issues.

    I would look again at your calculations and see if there is a way to perform some of the terms of the calculation as formulae. The less that has to be done by VBA the better. These partial terms can be hidden either in hidden columns, rows or even on a separate 'pre-calculation' sheet. Try to take an approach that minimises the amount of processing the VBA is required to do.

    A brief skim of the code to me shows no clear reason why it has to be in VBA. You perform a number of calculations which are used as parameters for the financial function MDURATION the result of which is assigned to the ActiveCell. On the surface this looks like it could all be done as a formula.

    As a side note I would avoid having your own procedure with the same name as the built-in financial formula and I would avoid having a variable within that procedure also with the same name. Although this compiles and runs it makes it difficult to read.

  • Re: Speed up slow calculation in vba

    Hi Rob,

    Basically I wanted to find a way to calculate mduration quickly for over 130,000 cells, although it takes a long time (at the moment). It has to run off a button so that other users (who are not familular with the mduration calculation) can easily calculate it, or be it have a long coffee break while it is.

    Im looking into autofiltering so that it can run faster and applying the suggestions from "AAE", but for me it's a slow process as i'm very new to vba.

    Thank you for taking the time to help (I have changed the names as suggested, many thanks)

  • Re: Speed up slow calculation in vba


  • FYI

    After much debating and taking into account the helpful responses. I have re-designed my code to the following:

    As most can see it pastes the formula into the first cell in the column.

    Autofills that formula to the last row.

    Copys the cells and then paste special the values back.

    displays a "elapsed time" box

    COMPLETION TIME: 20sec approx (for over 130,000 rows)

    Once again many thanks for all your posts, they have been a great help to me!

  • Re: Speed up slow calculation in vba

    And thanks for posting your results, it's always nice to see a good outcome. A speed up by a factor of 60x is no mean achievement... so well done :congrats:

  • Re: Speed up slow calculation in vba

    Untested, but perhaps this is faster:

    In any case, it's easier (according to me).




    Excel MVP 2011-2014

    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!