Write formulas to Excel cell using VBA variables instead of a long string representing the formula?

Question

I typically write formulas from VBA to Excel cells using the following method:

wksActual.Range("D48:D52").Formula = "=SUMIF('6. Calculation sheet'!A:A,C48,'6. Calculation sheet'!J:J)"

Is there an alternative way to similarly write a formula to a range of cells but to use more VBA variables and fewer strings?

Something like

wksActual.Range("D48:D52").Formula = Application.Worksheetfunction.Sumif(wksActual.Range("A:A"), wksActual.Range("C48"), wksActual.Range("J:J"))

The Application.Worksheetfunction.Sumif function will actually try to calculate its results before making the Formula on the range equal to it. This is not likely what you want. In your first example though as you are writing the formula in as a string you can manipulate that string with variables. So you could do wksActual.Range("D48:D52").Formula = "=SUMIF(" & CustomRange1.Address & ", " & CustomRange2.Address & ", " & CustomRange3.Address & ")" Hope this helps. Cheers. — nbayly
@nbayly - why not write that as an answer rather than a comment? — ChipsLetten
I guess I could have. Though I was just planning on giving a general counsel on how to proceed I guess I ended up being fairly specific. Anyways, your answer provided gave more thorough details than I was planning. Cheers. — nbayly

EEM EEM · Accepted Answer · 2015-06-06T23:52:34

Basically writing formulas in excel, most of the time, is about filling the function's parameters with addresses. These addresses only need an External Reference when they are referring to a range outside of the worksheet where the formula is entered.

OP's sample formulas are referring to the same worksheet in which they will be written

wksActual.Range("D48:D52").Formula = "=SUMIF('6. Calculation sheet'!A:A,C48,'6. Calculation sheet'!J:J)"
wksActual.Range("D48:D52").Formula = Application.WorksheetFunction.SumIf(wksActual.Range("A:A"), wksActual.Range("C48"), wksActual.Range("J:J"))

(with the issue on the Application.WorksheetFunction very well explained by @ChipsLetten let’s concentrate on the excel formula)

Therefore the formula above can be written as follows:

wksActual.Range("D48:D52").Formula = "=SUMIF(A:A,C48,J:J)"

or

wksActual.Range("D48:D52").Formula = "=SUMIF($A:$A,$C48,$J:$J)"

Now, if you really want to use variables to write this formula, I will suggest to create string variables like:

sRngTrg = "D48:D52"
sFmlRng = "$A:$A”
sFmlCri1 = "$C48"
sFmlRsm1 = "$J:$J"

then the formula will be

wksActual.Range(sRngTrg).Formula = _
    "=SUMIF(" & sFmlRng & "," & sFmlCri1 & "," & sFmlRsm1 & ")"

The above would be practical and efficient if these ranges are used several times in the code, in which case it will be more practical to create range variables like

With wksActual
    Set rRngTrg = .Range("D48:D52")
    Set rFmlRng = .Range("$A:$A")
    Set rFmlCri1 = .Range("$C48")
    Set rFmlRsm1 = .Range("$J:$J")
End With

the formula should be

rRngTrg.Formula = "=SUMIF(" & rFmlRng.Address & "," & _
    rFmlCri1.Address(0) & "," & rFmlRsm1.Address & ")"

If the formula refers to another workbook or worksheet and we are using a range variable. rOthFmlRng and rOthFmlRsm1 are ranges from another worksheet in the same workbook

With wksOther
    Set rOthFmlRng = .Range("$A:$A")
    Set rOthFmlRsm1 = .Range("$J:$J")
End With

Just need to add the External parameter to the Address of the range as below:

rRngTrg.Formula = "=SUMIF(" & rOthFmlRng.Address(External:=1) & "," & _
    rFmlCri1.Address(0) & "," & rOthFmlRsm1.Address(External:=1) & ")"

rExtWbkRng and rExtWbkRsm1 are ranges from a worksheet in another workbook

With Workbooks("Book Open.xlsx").Sheets(1)
    Set rExtWbkRng = .Range("$A:$A")
    Set rExtWbkRsm1 = .Range("$J:$J")
End With

same as before

rRngTrg.Formula = "=SUMIF(" & rExtWbkRng.Address(External:=1) & "," & _
    rFmlCri1.Address(0) & "," & rExtWbkRsm1.Address(External:=1) & ")"

Write formulas to Excel cell using VBA variables instead of a long string representing the formula?

3 Answers