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 occurrences 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. Thirty-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 decipher 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.

27 Comments

  1. Bill Simoni says:

    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.

  2. MN says:

    Very good hint. Thank you.

  3. Viscount Haldane says:

    Many thanks for a valuable tip clearly explained.

  4. 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?????

  5. John Groat says:

    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

  6. Felipe says:

    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
    Pond01 1/7/200717.9
    Pond01 1/18/200718.3
    Pond01 1/23/200719.4
    Pond01 1/28/200719.5
    Pond01 2/2/200720.0
    Pond01 2/7/200720.3
    =Trend(???,???,??)

  7. Jon Peltier says:

    Felipe -

    Turn on the macro recorder, insert th function manually, then see what code the recorder has generated.

  8. muna says:

    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.

  9. Warren Williams says:

    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.

  10. Warren Williams says:

    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

  11. Warren Williams says:

    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.

  12. Lin says:

    I found the last code very useful, though it gave me some syntax error at first.
    Just change the quote from “” to “”, and it will work as it is.

  13. Lin says:

    haha, ok.
    It seems the font of this webpage will change the quote automatically.
    Just replace the quote in VBA editor and you will notice the difference

  14. dave says:

    I am a bit puzzled, I cant get to work the simpliest formula, when I enter

    Worksheets(“Sheet2?).Range(“A2?).Formula = “=TODAY()”

    After executing the macro I get ########## in the cell, when I go to that cell the content is “=TODAY() and I just have to press enter to get the result, but it isnt processed automatically. Anyone knows what the problem is?

  15. dave says:

    it seems the ######### result was a problem because the data shown wont fit the column, I see now that it shows a name error, but as previously pressing enter makes it work

  16. Leo Yang says:

    Great hint. It saves me a lot of time.

  17. JN says:

    How do I get VBA to let me code this? I tried everything including Chr$34 and it does not seem to work.

    =BDH(F5 & ” ” & keyword,statistic,beg_date,end_date,”Dir=v”,”Per=M”,”cols=2;rows=76?)

    Thanks in advance.

  18. JN says:

    How do I get VBA to let me code this? I tried everything including Chr$34 and it does not seem to work.

    =BDH(F5 & ” ” & keyword,statistic,beg_date,end_date,”Dir=v”,”Per=M”,”cols=2;rows=76?)

    Thanks in advance

  19. e-go says:

    Tks a lot!!!

    your hint save me a lot of work.

  20. spade says:

    Thanks for the hint.

    Keep up the good work …

  21. BVR says:

    Hello Mr. Dick Kusleik

    I was stuggling to use double quotes in the COUNTIF function… and your chr$(34) suggestion worked wonders for me… thanks a lot :-)

    Sheets(“Sum”).Cells(iSummaryRow, iSummaryColumn + 1).Value = “=COUNTIFS(F5:F” & iDataRange & “,” & Chr$(34) & “*” & PlantLocation(iCount) & “*” & Chr$(34) & “)”

    Thank you once again.

  22. Kevin says:

    Hey man,

    I am looking to get a function to search a database with a User’s ID.

    I have the following code in my developer

    VLOOKUP(A2,Table2[[User ID]:[Occupation]],3,FALSE)

    but it keeps saying “expected: list seperator or )”

    do you know how to fix this to write the code?

    cheers

  23. ShadowCat says:

    Hello all,
    I’m needing help on what may be a rather complex scenario.

    First, I have a formula with a file path that is in text form (well just the ‘ character before it, so maybe not text) that depending on a variable changes two words that reveal the location of the file path.

    i.e. small part of the formula….. where YEAR & FILE are replaced with the variables
    =IF(ISERROR(INDEX(‘L:RESOURCE CENTERTICKET INFORMATIONTICKET CONFIRMATIONSYEAR[FILE.xls]INFO’!….

    So replacing the text values are a simple
    Cells.Replace what:=”YEAR”, Replacement:=Range(“T25?)…etc

    That works fine…

    But what I cannot find out is to 1. remove the ‘ character then 2. transition the overall formula (way more than 255 characters) to an array formula with braces {}. I don’t know if I can transition the formula to a string since the formula itself changes with the above variables.

    For the complete formula before variable changes looks like this (brace yourself)

    =IF(ISERROR(INDEX(‘L:RESOURCE CENTERTICKET INFORMATIONTICKET CONFIRMATIONSYEAR[FILE.xls]INFO’!$A$1:$G$6020,SMALL(IF(‘L:RESOURCE CENTERTICKET INFORMATIONTICKET CONFIRMATIONSYEAR[FILE.xls]INFO’!$AA$1:$AA$6020=0,ROW(‘L:RESOURCE CENTERTICKET INFORMATIONTICKET CONFIRMATIONSYEAR[FILE.xls]INFO’!$A$1:$A$6020)),ROW(‘L:RESOURCE CENTERTICKET INFORMATIONTICKET CONFIRMATIONSYEAR[FILE.xls]INFO’!3:3)),4)),”",(INDEX(‘L:RESOURCE CENTERTICKET INFORMATIONTICKET CONFIRMATIONSYEAR[FILE.xls]INFO’!$A$1:$G$6020,SMALL(IF(‘L:RESOURCE CENTERTICKET INFORMATIONTICKET CONFIRMATIONSYEAR[FILE.xls]INFO’!$AA$1:$AA$6020=0,ROW(‘L:RESOURCE CENTERTICKET INFORMATIONTICKET CONFIRMATIONSYEAR[FILE.xls]INFO’!$A$1:$A$6020)),ROW(‘L:RESOURCE CENTERTICKET INFORMATIONTICKET CONFIRMATIONSYEAR[FILE.xls]INFO’!3:3)),4)))

  24. dm_london says:

    I usually do this:

    When I have a working formula in a cell, I copy/paste it in notepad (temporarily).
    In notepad, I find/replace the double quotes with a pipe (|).
    Then, in VBA, I save this as a string and then find/replace the “|” with Chr$(34)
    This allows me to be able to read my formula (and keep my sanity!)…

    e.g.

    myFormula = “=IF(c1 > d1,|c is bigger than d|, |c is not bigger than d|)”
    myFormula = WorksheetFunction.Substitute(myFormula, “|”, Chr$(34))
    Sheets(“data”).Cells(1, 1).Formula = myFormula

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: