Worksheet Formulas in VBA Part I

Posted on by Dick Kusleika

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.

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.

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:

Boy, that was easy, maybe I’ll have to buckle down and learn R1C1. Nah.

35 thoughts on “Worksheet Formulas in VBA Part I”

  1. 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.

  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. 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. 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(???,???,??)

  8. 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. 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. 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. 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. 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. 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. 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. 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

  17. 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. 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

  21. 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. 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. 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. 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

  25. Pingback: WHY can't I paste this formula into a VBA macro ?
  26. Pingback: Insert Formula into Cell (VBA)
  27. Pingback: Exclude Numbers From A List

  28. Thanks for the help! in case anyone is interested this other option (to introduce a formula in a sheet) can be used.

  29. uuups here is the code!

    Sub sumif_ejemplo()

    var1 = Sheets(“Hoja1”).Range(“F1”)
    var2 = Sheets(“Hoja1”).Range(“F2”)
    var3 = Sheets(“Hoja1”).Range(“F3”)

    Cells(2, 5) = Application.WorksheetFunction.SumIfs(Sheets(“Hoja1”).Range(“C:C”), Sheets(“Hoja1”).Range(“A:A”), var1, _
    Sheets(“Hoja1”).Range(“B:B”), var2, Sheets(“Hoja1”).Range(“D:D”), var3)
    End Sub

  30. Hi
    I am looking for a code where in I have three formulas that I need to put.
    have an excel spreadsheet with more than 4 tabs where in I have 3 Products namely FS, DCF and RX
    I want to put a formula in one of the columns in Column AN
    The formula is different for the commodity.
    The issue I have is how to input formula
    For example one the 1st sheet, the FS product information starts on Row 4 down to row 200
    2nd Product starts at row 201 to 400
    3rd Product starts at 401 to 600
    On the 2nd sheet, the rows are different
    the FS product information starts on Row 4 down to row 150
    2nd Product starts at row 150 to 200
    3rd Product starts at 251 to 400
    The formulas are
    SF – IF(AP=0,0,+AP/1.85+1)
    FCD – IF(AP=0,0,+AP/1.7+0.1)
    XR – A +100
    Do you have any advise

  31. Hi Shahid. I suggest you post this question on an excel help forum like MrExcel.com/Forum or Chandoo.org/Forum and post a sample workbook outlining what you need to do. Note: only post on one forum…not both.

  32. I have been trying to implement this formula to an active cell using vba but have been unable to do so.

    IF(COUNTIF(SheetName!B2:B20000,Sheet6.Cells(1, j).Value)> 0,COUNTIF(SheetName!B2:B20000,Sheet6.Cells(1, j).Value,NA())

    How can i do this?

  33. I need to add values from different cells and need to retain formula

    for eg :
    worksheet(“Sheet1”).Range(“Z1”).formula = worksheet(“Sheet1”).Range(“C1”)+ worksheet(“Sheet1”).Range(“F1”)+ worksheet(“Sheet1”).Range(“Q1”) +worksheet(“Sheet1”).Range(“T1”) + worksheet(“Sheet1”).Range(“Y1”)

    Columns may vary based on reports. The output should have value and when F2 is pressed, formula should appear.

    Could anyone please help me.

  35. Hema: If the columns vary based on reports, I’m not sure what you expect as an answer. But for the formula you provided, here’s how I would do it. Go to cell Z1 and type in the formula

    Then in the VBE, go to the Immediate Window (Ctrl+G) and type

    and you’ll get

    Now set the FormulaR1C1 to that string in your code.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.