How Can I Add a COUNTIF Formula to an Excel Spreadsheet?

ScriptingGuy1

Hey, Scripting Guy! How can I add a COUNTIF formula to an Excel spreadsheet?

— MF

Hey, MF. Thanks for reminding us: yes, we are getting closer and closer to the actual 500^th Hey, Scripting Guy! Celebration.

Anyway, thanks again, MF; we almost forgot to mention all that. As a token of our appreciation, we’d like to give you a little something in return. Obviously you would never take our money … hey, how about this: how about a script that adds a COUNTIF formula to an Excel spreadsheet? It’s not much, but, then again, it’s the thought that counts. Right?

As you noted in your email, MF, you tried adding a COUNTIF formula (a formula that simply counts the number of cells in a range whose value begins with the letters NK) using this line of code:

objExcel.Cells(6, 1).Formula = “=COUNTIF(A1:A4,’NK*’)”

That looks pretty good, but – as you found out – it doesn’t work. You’d like to know two things: why doesn’t this work, and what can you do to make it work. Can we help you with those questions? Hey, does a bear swim in the woods?

Let’s start off by explaining why your code doesn’t work. To begin with, take a look at the COUNTIF formula as typed directly into Excel:

If you look closely, you’ll notice that the criteria (NK) is surrounded by double quotes: “NK”. Are those double quotes important? You bet they are; if we replace the double quotes with single quotes we no longer have a formula:

As you found out.

Of course, when you first sat down to write your script you probably tried inserting double quotes, most likely by using code similar to this:

objExcel.Cells(6, 1).Formula = “=COUNTIF(A1:A4,”NK*”)”

There’s probably no need to tell you that that’s going to generate an “Expected end of statement” error. Why? Well, the double quote mark is used to indicate the beginning and the end of string values. Because of that, VBScript thinks that your string value is this:

“=COUNTIF(A1:A4,”

That’s fine; it’s perfectly valid VBScript code. However, that valid string value is immediately followed by this:

NK*”)”

Does that look like gibberish to you? Well, unfortunately, it looks like gibberish to VBScript as well; that’s why the script blows up. One of the many immutable laws of the universe is this: you can’t embed double quotes inside double quotes.

At least not if you want your script to actually run.

To your credit, though, we have to admit that it wasn’t a bad idea to try replacing the double quotes in the formula with single quotes; sometimes (especially when you’re working with HTML tags) you can get away with that. For the most part, though, that won’t work: if something expects to see double quotes, then you have no choice but to give it double quotes.

But didn’t we just that you can’t embed double quotes inside double quotes? Probably; that sounds like something we’d say. But that’s OK; we aren’t going to embed double quotes inside double quotes. Instead, we’re going to use a little workaround like this:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.Cells(1, 1).Value = “nkabc”
objExcel.Cells(2, 1).Value = “abcnk”
objExcel.Cells(3, 1).Value = “abnkc”
objExcel.Cells(4, 1).Value = “nk”
strFormula = “=COUNTIF(A1:A4,” & Chr(34) & “NK*” & Chr(34) & “)”
objExcel.Cells(6, 1).Formula = strFormula

Two quick notes before we explain how this script works. First, there are at least two ways to get around the fact that you can’t embed double quotes inside double quotes. We’ve chosen the method that we believe is easier for most people. If you prefer a different method, that’s fine.

Second, we actually added an extra step to our script. Where? Right near the end, where we stored our COUNTIF formula in a variable and then assigned that variable to a specific cell in the spreadsheet. We could have saved ourselves a step by assigning the formula directly to the cell, without ever storing that formula in a variable. We didn’t use that shortcut simply because we thought it was a little easier for people to read and understand the script if we broke that task into two steps.

But enough about that. We’ve now reached the point where we explain how this script works, something we’ll do by first telling you that we aren’t going to explain how this script works. Or at least not all of it. As it turns out, 80% of the script has nothing to do with inserting a formula; instead, it’s code that simply creates an Excel spreadsheet and adds some test values to four of the cells. We won’t explain those lines of code in any detail; if you aren’t familiar with writing scripts that use Microsoft Excel then you might want to take a look at the articles in the Office Space Archive. (In fact, we even have one article in there that talks about inserting formulas into an Excel spreadsheet.)

To make a long story short, we start off by creating a visible instance of Microsoft Excel, and then creating a new workbook. After binding to sheet 1 in that workbook we then use these four lines of code to add four values (two of which start with the letters NK and two of which don’t) into cells A1 through A4.

objExcel.Cells(1, 1).Value = “nkabc”
objExcel.Cells(2, 1).Value = “abcnk”
objExcel.Cells(3, 1).Value = “abnkc”
objExcel.Cells(4, 1).Value = “nk”

That leaves us with only two lines of code to worry about:

strFormula = “=COUNTIF(A1:A4,” & Chr(34) & “NK*” & Chr(34) & “)”
objExcel.Cells(6, 1).Formula = strFormula

The first line is the important one: that’s the line where we construct the COUNTIF formula. To do that we need to build the formula in pieces. As you might recall, the finished product needs to look like this:

“=COUNTIF(A1:A4,”NK*”)”

To get to that point, we start with this string:

“=COUNTIF(A1:A4,”

Note that this is a valid string: it begins and ends with double quote marks. We then need to insert a double quote mark; to do that we use the Chr function, which converts an ASCII value to an actual character. As it turns out, the ASCII value for the double quote mark is 34; that means that this piece of code takes the beginning portion of the string and then adds a double quote mark at the end:

“=COUNTIF(A1:A4,” & Chr(34)

Got that? Good. Next up: NK*. No problem: we just put those characters in a string and add that to the formula:

“=COUNTIF(A1:A4,” & Chr(34) & “NK*”

Make sense? Now we need a second double quote mark, which means it’s time for another Chr(34):

“=COUNTIF(A1:A4,” & Chr(34) & “NK*” & Chr(34)

All that’s left now is the closing parenthesis, which we can add as a little string value all its own:

“=COUNTIF(A1:A4,” & Chr(34) & “NK*” & Chr(34) & “)”

Admittedly, this can be a bit bewildering if you haven’t done much string concatenation. If that’s the case, then you can do two things that might help. First, take a look at the relevant section in the Microsoft Windows 2000 Scripting Guide. Second, think of this in terms of an addition problem:

“=COUNTIF(A1:A4,” 
+                Chr(34) 
+                  “NK*” 
+                Chr(34) 
+                    “)”
________________________

  “=COUNTIF(A1:A4,”NK*”)”

To turn this into a line of code just replace the plus signs with ampersands.

Fortunately all that’s left now is to assign the formula to cell A6:

objExcel.Cells(6, 1).Formula = strFormula

That should do it, MF. Again, thanks for your help.

ScriptingGuy1

Follow