Worksheet Formulas in VBA Part I
The Range object has a property called Formula. With this property you can insert a worksheet formula into a cell programmatically (that’s geek-speak for “from VBA”). The Formula property is a String data type (just text), so for simple formulas, it’s easy to create.
In cell A11, let’s insert the formula =SUM(A1:A10):
Range(”A11″).Formula = “=SUM(A1:A10)”
Pretty easy, huh? Well, there are few things you should know before you consider yourself an expert. First, VBA doesn’t like double quotes inside of strings. It gets confused. To deal with this dilemma, you can use two double quotes (”") or you can use the Chr function to create double quotes. Take this formula
=COUNTIF(A1:A10,”Bill”)
It counts the number of occurences of Bill in the range A1:A10. But there are double quotes in this formula, so we need the special handling mentioned above.
Range(”A11″).Formula = “=COUNTIF(A1:A10,”"Bill”")”
Range(”A11″).Formula = “=COUNTIF(A1:A10,” & Chr$(34) & “Bill” & Chr$(34) & “)”
Both do the same thing. Thrity-four is the ascii code for double quotes. The first example is easier to read, in my opinion. Unfortunately, the second example is the way I learned, and old habits are hard to break. I’m working on it though.
While it may be easy to insert a worksheet formula into a cell when the cell references are known, you can run into problems when they must be relative. That is, when you want to insert a formula that sums the previous ten rows regardless of which cell you’re using. For that, the string we pass to Formula gets a little more complicated. I use the Offset property and the Address property to create the formulas.
With ActiveCell
.Formula = “=SUM(” & _
.Offset(-10, 0).Address(0, 0) & “:” & _
.Offset(-1, 0).Address(0, 0) & “)”
End With
It looks complicated, but it’s not. The Offset property returns a cell a specified number of rows and/or columns away from the reference cell. The Address property returns, as a string, the address of the cell like “A1″. The two arguments for Address determine if dollar signs are used in the reference. Using Offset and Address, it doesn’t matter which cell you’re in, the formula will sum the 10 cells above.
Some people will tell you that you should write the formula in R1C1 notation. For me, Offset/Address is easier to understand. In the time it takes for me to decifer R1C1, I can write a formula like the above 100 times. But for the sake of completeness, it would look like this:
ActiveCell.Formula = “=SUM(R[-10]C[0]:R[-1]C[0])”
Boy, that was easy, maybe I’ll have to buckle down and learn R1C1. Nah.
Bill Simoni:
Excellent! R1C1 is the bane of my existence and I shudder when I have to write formulas that way. Thanks for the prod in the right direction. I’ll need to give Part II a closer look as well.
16 April 2004, 1:31 pmMN:
Very good hint. Thank you.
21 April 2005, 1:56 amViscount Haldane:
Many thanks for a valuable tip clearly explained.
1 July 2005, 4:45 amKeith Rozario:
Let’s say you want to sum ALL previous rows that preceed it. So in your example you want to take the 10 previous rows, let’s say If i was at row 10, i want to the 10 previous rows to sum. However, if I was at row 54, I want to sum the previous 54 rows…and so on. Is there a way to do that?????
15 November 2005, 6:58 pmJohn Groat:
Thanks for the hints. I was having difficulty just getting the formula pasted in, let alone working out how to adjust the cell references row by row. It was throwing an “Application defined error” or similar. I wanted to have columns that were coloured and represented the new values for a recordset. If the line (row) and therefore the record for that recordset had new values those coloured cells would have values in them. When that happened the status at the end of the line under the column heading “Changes” would indicate that the cells were not blank and would change status to “CHANGE”. I then read these lines into code and change the record (using ID numbers for each record) and making the appropriate changes.
The Paste Records I have used over and over in oledb reporting from SQLServer to Excel and I am indebted to the person who pasted that on the web for us all.
Below is the code I worked on
Public Sub PasteRecords(ExcelWorksheet As Excel.Worksheet, rst As Recordset)
‘ The code below uses the ADO Fields collection to fill cells on
‘ the worksheet. The code also uses the Worksheet object’s Cells
‘ object to reference the columns and rows.
‘ RowCnt is a counter for rows in the worksheet. FieldCnt is a
‘ counter for the number of fields in the recordset.
Dim RowCnt, FieldCnt As Integer
Dim strFormula As String
Dim cell As Range
ExcelWorksheet.Activate
If ExcelWorksheet.Cells.SpecialCells(xlCellTypeLastCell).Row = 1 Then
RowCnt = ExcelWorksheet.Cells.SpecialCells(xlCellTypeLastCell).Row
Else
RowCnt = ExcelWorksheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 3
End If
‘ Use field names as headers in the first row.
For FieldCnt = 0 To rst.Fields.Count - 1
With ExcelWorksheet.Cells(RowCnt, FieldCnt + 1).Font
.Name = “Arial”
.FontStyle = “Bold”
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
ExcelWorksheet.Cells(RowCnt, FieldCnt + 1).Value = rst.Fields(FieldCnt).Name
Next FieldCnt
ExcelWorksheet.Cells(RowCnt, FieldCnt + 2).Value = “CHANGES”
‘ Fill rows with records, starting at row 2.
RowCnt = ExcelWorksheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
While Not rst.EOF
For FieldCnt = 0 To rst.Fields.Count - 1
If FieldCnt = 2 Then
ExcelWorksheet.Cells(RowCnt, FieldCnt + 1).NumberFormat = “@”
ExcelWorksheet.Cells(RowCnt, FieldCnt + 1).Value = rst.Fields(FieldCnt).Value
ElseIf InStr(rst.Fields(FieldCnt).Name, “New”) Then
With ExcelWorksheet.Cells(RowCnt, FieldCnt + 1).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ExcelWorksheet.Cells(RowCnt, FieldCnt + 1).Value = rst.Fields(FieldCnt).Value
Else
ExcelWorksheet.Cells(RowCnt, FieldCnt + 1).Value = rst.Fields(FieldCnt).Value
End If
Next FieldCnt
Set cell = ExcelWorksheet.Cells(RowCnt, FieldCnt + 2)
strFormula = “=IF(formatblank(” & cell.Offset(0, -20).Address(0, 0) & “,” & cell.Offset(0, -20).Address(0, 0) & “:” & cell.Offset(0, -12).Address(0, 0) & “),” & Chr$(34) & “NO CHANGE” & Chr$(34) & “,” & Chr$(34) & “CHANGE” & Chr$(34) & “)”
ExcelWorksheet.Range(ExcelWorksheet.Cells(RowCnt, FieldCnt + 2).Address).Formula = strFormula
RowCnt = RowCnt + 1
rst.MoveNext
Wend
End Sub
Thanks once again for your help
John Groat
14 February 2006, 4:26 pmFelipe:
Hi:
I know that the sum,average,etc formulas
can be inserted programmatically, but what about
inserting the TREND function programmatically?
I have the ficticious data below and I am trying
to find the AvgWeight trend. How can I insert the
function with a macro. Any ideas?
PondName Date AvgWeight
21 January 2007, 8:03 pmPond01 1/7/2007 17.9
Pond01 1/18/2007 18.3
Pond01 1/23/2007 19.4
Pond01 1/28/2007 19.5
Pond01 2/2/2007 20.0
Pond01 2/7/2007 20.3
=Trend(???,???,??)
Jon Peltier:
Felipe -
Turn on the macro recorder, insert th function manually, then see what code the recorder has generated.
22 January 2007, 11:59 ammuna:
I love the offset method as well but couldnt figure out which were the commas
so I didnt really want to even look at the R1C1 method but hey I finally decided to use it and im getting results first time! so you never know.
24 August 2007, 4:00 pmWarren Williams:
I have been looking for something like this, so was glad to find it. However, my computer is giving me a compile error. It does not like the “:”, the error message says: Expected: list separator or )
I have tried everything that my limited VBA knowledge knows how to do, and I cannot fix it so it will run.
3 October 2007, 8:14 pmWarren Williams:
This is my second comment about the offset.address code to sum no matter where you are. Typed in the code on my office computer, did some manipulations of the offset numbers and, it was like a dream come true. I also changed the “Sum” to “Average”.
So, thanks very much to the wizard who wrote this code, and thanks for allowing me to copy and use.
WW
4 October 2007, 10:13 amWarren Williams:
My third, and I promise, my last comment about the OffSet.Address code. Do not know why, but copying the code from this website to my code module did not work. But, if I type the code into my macro, then it works just fine, both at home and at work. So, again, thank you for this code. It really solved a difficult problem for me.
5 October 2007, 3:49 pmLin:
I found the last code very useful, though it gave me some syntax error at first.
11 May 2008, 8:19 pmJust change the quote from “” to “”, and it will work as it is.
Lin:
haha, ok.
11 May 2008, 8:23 pmIt seems the font of this webpage will change the quote automatically.
Just replace the quote in VBA editor and you will notice the difference