How To Add A Formulas To A Worksheet Using Excel VBA
Programmatic data entry is a fairly frequent requirement in Excel VBA and entering values in cells is often not dissimilar to the manual entering of data. If you want to enter a given piece of data into a cell, you simply use a statement like the following.
rngHeader.value = “Variance”
In this example, “rngHeader” would be the name of a variable containing a reference to a worksheet cell. But what happens when we want to enter a formula into a cell? Well, in one sense, we could say that the process is no different to entering an ordinary value into a cell. Thus, for example, if we wanted to create a formula that totalled a series of cells in column “A” of the worksheet, we could just use a statement along the following lines.
rngTotal.value = “=SUM(A2:A96)”
Nothing wrong with that! However, in most scenarios, formulas need to be entered into several adjacent cells and using cell addressing which includes column letters becomes impractical. We need to be able to work with numbers so that we can get the maximum benefit from such techniques as looping and the use of the Offset method of the range object. The solution is not to use the Value property of the Range object when writing formulas to a worksheet but, instead, to use FormulaR1C1.
During manual data entry, if you create a formula containing cell references in Excel and then copy it to other cells, Excel updates the cell references to reflect the new location of the formula. It replaces the original references with references which are in the same relative positions as the original ones. It is able to do this because it stores references relatively. (You can force Excel to display formulas in the way that Excel actually stores them by choosing Excel Options from the office button, clicking on the formulas category then activating the option “R1C1 reference style”.)
Returning to our programmatic example, if we use the FormulaR1C1 property instead of the Value property and assuming that the cell containing the formula is A97, our statement would look like this.
rngTotal.FormulaR1C1 = “=SUM(R[-96]C:R[-1]C)”
The number between the square brackets specifies the row and column numbers of the cells being referenced relative to the cell containing the formula. Thus “R[-96]C” refers to a cell which is 96 rows above the cell which contains the formula but in the same column (since there is no qualifier after the “C” part of the reference).
You can find out more about Excel VBA training courses, visit Macresource Computer Training, a UK IT training company offering Excel VBA training courses at their central London training centre.
Tags: computer, excel, macros, microsoft, programming, software, VBA