Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the new column that offers tips and tricks for scripting Microsoft Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Inserting a Formula into an Excel Spreadsheet

When one of the Scripting Guys was working at a university, an administrative assistant there used to do her monthly budget reports in Excel. She would dutifully type in all the information, then take out her calculator and add up all the numbers. When it was pointed out to her that she could create simple little formulas and let Excel do all the calculating for her, she was stunned: she had no idea Excel could do things like that.

Note. Of course, this same person once went several months without using PowerPoint. When she needed to put together a presentation, she called and asked if PowerPoint would still work after sitting so long without being used.

The rest of you have no such excuse: you now know that Excel can do calculations for you. What you might not know, however, is how to programmatically insert a formula into a spreadsheet. But hey, is this your lucky day or what? By the time you finish reading this column, you’ll know how to do that, too.

Actually, you might be surprised how easy it is to programmatically insert a formula into a spreadsheet. For example, here’s a simple little script that opens up an instance of Excel and then types some numbers into cells A1, A2, A3, and A4. In cells A6, A7, A8, and A9, the script then inserts formulas that calculate, respectively, the sum of the numbers; the smallest number (minimum); the largest number (maximum); and the average of the four numbers:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Cells(1, 1).Value = 11
objExcel.Cells(2, 1).Value = 54
objExcel.Cells(3, 1).Value = 32
objExcel.Cells(4, 1).Value = 55

objExcel.Cells(6, 1).Formula = "=SUM(A1:A4)"
objExcel.Cells(7, 1).Formula = "=MIN(A1:A4)"
objExcel.Cells(8, 1).Formula = "=MAX(A1:A4)"
objExcel.Cells(9, 1).Formula = "=AVERAGE(A1:A4)"

As you can see, adding a formula by using a script is remarkably similar to adding a formula by hand. If you’re typing away in Excel and want to add a formula, you simply click on the cell and then type in the formula:

=SUM(A1:A4)

You do almost exactly the same thing when writing a script: you select the cell in question and then set the value of the Formula property to the very same formula you would type in if you were doing this by hand. Thus to insert a formula into cell A6 you use code like this:

objExcel.Cells(6, 1).Formula = "=SUM(A1:A4)"

Yes, it’s that easy. Here’s what the finished product looks like, in all its glory:

Incidentally, if you ever want to know if a specific cell has a formula you can create a Range object representing that cell and then check the value of the HasFormula property:

Set objRange = objExcel.Cells(7, 1)
Wscript.Echo objRange.HasFormula

In this case, if cell A7 has a formula the value True (-1) will be returned; if it doesn’t, the value False (0) will be returned.

Here’s another little tip for you. Formulas expect cell addresses to be in the A1 format; however, you typically assign values and formulas using the row column format: (7, 1). Here’s a way to get the A1-style address for a given cell:

Set objRange = objExcel.Cells(7, 1)
Wscript.Echo objRange.Address(False, False)

What does it all mean? Hey, you’ve had enough excitement for one day; we’ll talk about cell addresses in a future column.