You are here:

Excel/writing formulas using code

Advertisement


Question
Hi Tom

I have a workbook laden with formulas.  I believe that there are so many of them it is slowing down the calculation speed of the workbook.  I have two questions:

1. would converting the formulas to VBA code speed up the overall calculation time of the file

and

2. if so would each formula have to be, individually,rewritten in code?

thanks in advance

Chris Mitchell

Answer
Chris Mitchell,

It depends on the specifics.  If you mean you will removed many formulas and then put their functionality in a big macro/VBA code that will be run manually when the data needs to be updated, then having less work that needs to be done on each calculate will speed things up.  If the formulas removed were not the source of the slowdown (all formulas are not created equally in terms of time to recalculate), then you would see little benefit.  But if they were big formulas with a lot of recalculation time, then you would see a big payoff.  

If the formulas need to be recalculated whenever a value is changed, then they probably should be formulas.  But if they only need to be updated occasionally, then in a problem workbook, they probably would benefit by being converted to VBA code which is manually run only when needed.

If you are talking about building formulas in VBA that are used in worksheets as user defined functions, e.g.  =Myfunction(argument)  , then I doubt those would help and probably would take more time.

These are general answers with no knowledge of  your workbook.

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.