When to Write Formulas to Excel Spreadsheets using VBA Macros
The video guide introduces the ‘.Formula’ property as a method for writing formulas directly into Excel spreadsheets. If you can type a formula into a cell, you can type the same formula into a macro using expressions of the form Range("C1").Formula = "=A1+B1". In practice, cell references can be derived from variables in the code. The simplest example would be to re-write the above as Range("C" & RowNum).Formula = "=A" & RowNum & "+B" & RowNum, then column C will add together columns A and B wherever it is copied.
Any code that performs calculations on numbers found in your spreadsheet can be replaced by formulas. If you want cell B1 to display the age of the date in cell A1, then you can write either Range("B" & RowNum).Formula = "=TODAY()-A" & RowNum or Cells(1, 2) = VBA.Date - Cells(1, 1). One puts a formula in the cell, the other doesn’t. The most obvious reason to enter a formula in the spreadsheet is that you need cell B1 to update when A1 is changed without having to run the code again.
What if you don’t need cell B1 to contain a formula because cell A1 is never going to change? There are circumstances under which you would elect to add a formula to your spreadsheet when your macro runs. Sometimes you would even want to leave it in place once the macro has run.
What if you need to add formulas to thousands of rows? Should you loop through every single row as in the video? Or are there better methods?
This article looks at the pros and cons of different techniques, allowing you to make an informed decision on when to use ‘.Formula’ in your macros. The conclusions are summarised in the table below:
Should I use a formula in place of code?
The number one reason to use a formula to calculate a cell value is that you need it to update when your code isn’t running. A second reason is to show an end user where a cell value comes from. A third reason is that it is often faster to process an Excel formula copied across a whole range of cells, than it is to loop through and process each cell individually using Visual Basic. Finally, where performance is no issue, it can make development easier.
The requirement to show users how a spreadsheet works is often overlooked by developers on the basis that Excel formulas are a means to an end. Excel4Business frequently gets asked to automate the production of monthly dashboards. The typical client brief involves them downloading a CSV file, opening it in Excel, removing unwanted data, adding a few formula columns to the end, and then creating some pivot tables. An example formula column would be one that categorises invoices by size, where the thresholds are defined by some KPIs.
As this is a once-a-month process, performance is not a consideration. If you need to loop through all the rows of the spreadsheet to identify unwanted data, it would be easy to categorise invoices within the code using an IF/ELSEIF/END IF statement. However, our consultants would prefer to use formulas so the client can see we have faithfully replicated their process.
Whether a single formula is faster than a VBA equivalent depends on the proposed formula and the proposed VBA code. For single calculations, it rarely matters because the time involved tends to be negligible. If you want to run a calculation over 10,000 rows in a single column of data then formulas tend to perform better than code because a single formula can be copied to every row in a single operation, and everything can be calculated in Excel’s internal engine. It’s certainly a good starting point as the code can be written quickly. If it transpires the formula produces the results you want but runs slow, you can always use that as a prompt for AI to develop a faster replacement.
Part of being able to write code quickly is not having to re-invent existing Excel functions. The WORKDAY function can be used to calculate the next working day after a given date. If the given dates are always Monday to Friday and there are no public holidays then you could write code to calculate the next working day as ‘tomorrow, or 3 days later if it’s a Friday’. As you’re not exploiting the full power of the WORKDAY function, the VBA solution may well run faster. However, it requires more coding and makes the solution less transparent.
Should I replace formulas with values at the end of my code?
If cells do not need to need formulas installed to automatically update as changes are made to your spreadsheet then the sheet will always run faster without them. If there is a significant time cost associated with these unnecessary formulas being re-calculated, it generally makes sense to remove them. An alternative framing then becomes whether it makes sense to run code more often to keep formulas out of the spreadsheet. This is a question of how the user experiences the sheet and the best approach may not be fastest, but the one in which the delays are least disruptive.
Whether your spreadsheet needs to automatically update when the underlying data is changed is more nuanced than it might appear. Well-designed spreadsheets often keep source data apart from outputs on separate worksheets. Calculations may only need to refreshing when you move from input to output sheets. Using the Worksheet_Deactivate event, you can write code to install and calculate formulas during the transition. When the calculated values are up-to-date, you can copy the formula cells and paste over them with their own values.
Running code whenever a user clicks away from an input sheet might cause a longer delay than that experienced when editing the raw data. It’s even possible the total lag incurred by the Worksheet_Deactivate across the course of a day is greater than that incurred from formulas re-calculating as worksheets are edited. However, when a user switches between worksheets, they will always take a couple of seconds to find their place on the newly selected sheet. Whereas a half second processing delay when moving to an adjacent cell on an Excel worksheet can feel very slow.
One argument for leaving formulas in a sheet is that they can help users understand how a macro is processing their data. If leaving the formulas in the spreadsheet slows the sheet down then you can just leave a cell comment for other users so they know which formula was used. They could then choose to re-instate the formulas manually.
In VBA, is copying and pasting a single formula better than writing every formula directly?
Any coding method that reduces the number of individual writes to an Excel spreadsheet is generally a good thing. Exceptions arise where array formulas interact in such a way that doubling the number of formulas more than doubles the processing time and the Excel file struggles to perform the calculations at all. However, if Excel cannot cope with having all the formulas in the worksheet at once then the solution is either to use different functions, write code to achieve the same end, or selectively install formulas where inputs may have changed.
The simplest method for copying and pasting a macro-installed formula down a table column is to write the formula to the top row, copy that cell, and paste it down the rest of the column. As with the manual version of this process, the formula in the top cell should include relative, not absolute, references. If you want to keep your spreadsheet design flexible, one method for locating source cells would be to use expressions of the form Cells(CurrentRow, SourceCol).Address as in the video guide. This will return a cell address of the form $A$1. To create a relative reference, you can use the Replace function to get rid of $ symbols e.g. VBA.Replace(Cells(CurrentRow, SourceCol).Address, "$", "").
Our Excel experts recommend keeping the formulas out the code entirely where possible. Instead, add a row above your table that serves as a template for the body of your table, and put your formulas in there. Your code fills your data table by copying and pasting formulas from the template row. This can done with the single line of code of the form Cells(TemplateRow, FormulaCol).Copy, with the added advantage that you can choose to copy features like conditional formatting at the same time.
This method tends to be superior because it is easier to work with formulas directly in Excel due to its more developed user interface. It is also easier for other users to see what is going on if the formulas are replaced by values at the end of your macro. Most the advantages are lost when working with formal Excel tables because a formula written inside the table might refer to a column name, whereas a formulas written outside can only refer to cell addresses.
Should I set calculations to manual while my macros write formulas to a worksheet?
Essentially yes. If you do not use Application.Calculation=xlCalculationManual at the start of your code, Excel’s calculation engine will run every time you edit the sheet. When the code is writing formulas into cells, this will cause those cells to re-calculate, as well as any dependent or precedent cells. If you are writing formulas in a column to be totalled elsewhere, it is completely unnecessary to update that total more than once and it can cause your code to run very slowly.
The qualifier on the ‘yes’ is that switching calculations off can create two problems. One is that the calculations will need resetting to Automatic at the end of the macro which can require some additional planning around error handling. The bigger issue is that your formulas may need to be calculated in the middle of your macro for your code to run as intended.
One example would be the use of formulas to calculate delivery dates where the final data table should be sorted in date order. If you do not re-calculate your delivery dates after writing formulas to the sheet, the subsequent sort will not be performed correctly. The very simple solution is to force a spreadsheet re-calculation prior to the sort. This can be done using code of the form Range(<DeliveryDateColumn>).Calculate. If you need to re-calculate the worksheet more widely, such as if you are planning to refresh a pivot table, then Application.Calculate may be better. Alternatively, if you are done writing to the worksheet, you could just turn calculations back on at that point.
Both the issues with turning off calculations could be considered poor coding. However, they are easy mistakes for both a human and an AI (given insufficient context) to make. So if you are using copy and pastes to limit the number of formulas you’re writing to a worksheet and there aren’t many dependent cells, you may find performance acceptable with automatic calculations switched on. If so, it is best not to introduce a potential source of error.
Should I call Excel functions like VLOOKUP from VBA?
As well as writing formulas to Excel worksheets, you can use Excel’s internal calculation engine to run formula functions like VLOOKUP within VBA. Using code of the form Application.WorksheetFunction.VLookup(<parameters go here>) can save you having to write Visual Basic to mimic those operations yourself. It is almost always worthwhile where a single calculation is required but, if you are looking to perform thousands of calculations on sheet data, better techniques will tend to be available.
Calling functions with Application.WorksheetFunction is clearly superior to using formulas when the data being processed doesn’t exist on an Excel worksheet. At Excel4Business, we build VBA forms to facilitate data entry. One example would be a quote builder in which a user selects a product from a list by its SKU. It is normally helpful to display the product price within the user-form. If the quote was being compiled in a worksheet, you would look up the price from a pricing table with a VLOOKUP. You can do the same using an expression of the form frmQuote.Price = Application.WorksheetFunction.VLookup(frmQuote.Product, PricingTable, 2, False) whenever frmQuote.Product is changed.
Sometimes there is the option of using an in-cell formula or a VBA function call to return a value. If you do not want to retain the formula once your macro has run, then using the VBA function call saves you writing a formula to a cell and then subsequently replacing it with a value so it is a cleaner method.
The issue with VBA function calls is that you cannot perform 10,000 VBA function calls at once. So if you need to perform the same operation on 10,000 rows of data then you would nearly always be better off copying a formula across 10,000 rows of your worksheet. Assuming you follow the guidance elsewhere in this article, you can perform a single copy and get Excel to process all the formulas in one go instead of looping through every line individually. Both formulas and function calls will run on Excel’s internal engine so performance differences should be minimal but the formula method is more visual so can be used to diagnose problems.
By Ed Bolton, founder of Excel4Business Ltd. Last reviewed October 2026.