Hey, Scripting Guy! I’m trying to determine – and mark – the first occurrence of a number in an Office Excel worksheet. However, I can’t figure out how to go about doing this. Can you help?

— BH

Hey, BH. Before we launch into today’s column we have an announcement to make: the Scripting Guys surrender. You have beaten us fair and square.

As of Monday morning the Scripting Guys were feeling a little cocky about the 2008 Winter Scripting Games. Thanks to a clever idea to start the Games on a Friday (a relatively low-traffic day in the Script Center), and thanks to our experience in testing and scoring the past two Scripting Games, we thought we had a handle on everything, and we thought we were going to be able to keep up with all the submissions. And for the first few days we did just that. But then we hit the Monday evening/Tuesday morning deluge.

We no longer think that we can keep up with all the submissions. You win.

In other words, for those of you who have submitted a bunch of scripts to the Scripting Games and are wondering when your scores will be posted all we can say is this: someday. What we’re trying to do now is get all the Events 1, 2, 3, and 4 scored first; that’s because Events 1 and 2 are now officially closed, and Events 3 and 4 close on Friday. (We’re also trying to keep the Sudden Death Challenge up-to-date, seeing as how that changes every single day. (Yet another idea that sounded so good on paper …) That means that, if you’ve already submitted scripts for Events 9 and 10, it might be another week or so before we get to them. Our suggestion? Enter the Sudden Death Challenge every day, as a way to keep you occupied.

Hmmm, you know, now that you mention it, that would just increase the number of scripts we have to test, wouldn’t it? But that’s OK; enter the Sudden Death Challenge every day anyway. Either that, or take a week off and send the bill to Microsoft.

Or, while you wait, avail yourself of the Script Center lounge. Like most waiting rooms, we have copies of National Geographic Magazine from 1947, and copies of Better Homes and Gardens where all the good recipes have been torn out. We also have copies of a script that can identify the first occurrence of a number in a Microsoft Excel worksheet:

Set objDictionary = CreateObject("Scripting.Dictionary")

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")

objExcel.Visible = True

Set objWorksheet = objWorkbook.Worksheets(1)

i = 1

Do Until objExcel.Cells(i, 1) = ""
    intValue = objExcel.Cells(i, 1)

    If Not objDictionary.Exists(intValue) Then
        objDictionary.Add intValue, intValue 
        objExcel.Cells(i, 2) = 1
    End If

    i = i + 1
Loop
In your email, BH, you mentioned that “this should be an easy problem.” Well, you were right: this is an easy problem. As you can see, we start out by creating an instance of the Scripting.Dictionary object; we’ll use the Dictionary object to keep track of which numbers we’ve already found in the worksheet. After we create the Dictionary object, we create an instance of the Excel.Application object, then use the Open method and the following line of code to open the spreadsheet C:\Scritps\Test.xls:
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
After the file is open we set the Visible Property to True; that simply makes Excel visible on screen so we can see what’s going on. Of course, the next thing we do won’t even show up on screen anyway; that’s because we next use this line of code to bind to the first worksheet in Test.xls:
Set objWorksheet = objWorkbook.Worksheets(1)
What do you mean you’re bored? Listen, we’re almost to the good stuff now; all we have to do is set the value of a counter variable named i to 1 and then we’re ready for action.
What kind of action? Well, as it turns out, BH has a worksheet that looks something like this:

What he wants to do is read through all the numbers in column A, in order, looking for the first occurrence of each number. For example, the number 5 appears in cell A1. That’s the first 5 to show up in column A, which means that BH would like to set the value of cell B1 to 1, indicating the first occurrence:

When all is said and done, BH would like the worksheet to look like this, with the first occurrence of each number dutifully indicated in column B:

So how do we get to that point? Well, for starters, we set up a Do Until loop that runs until we encounter a blank cell in column A:
Do Until objExcel.Cells(i, 1) = ""





Note. For this script, we’re assuming that there are no blank rows in the spreadsheet.





Inside that Do loop, we use this line of code to grab the value of cell A1 (that is, cell row 1, column 1, with the counter variable i representing the row number):
intValue = objExcel.Cells(i, 1)
The first time through the loop, intValue will be equal to 5. Is this the first occurrence of 5 in the worksheet? To tell you the truth, we have no idea. But you know what they say: when in doubt, ask the Dictionary object:
If Not objDictionary.Exists(intValue) Then
    objDictionary.Add intValue, intValue 
    objExcel.Cells(i, 2) = 1
End If
What we’re doing here is using the Exists method to determine if this value (5) does not exist in the Dictionary. Let’s say the Exists method returns True, meaning that it’s true that the value 5 cannot be found in the Dictionary. That can mean only one thing: this is the first time we’ve encountered the value 5 in our worksheet. With that in mind, we perform two simple tasks:



•

We use the Add method to add this value to the Dictionary (using 5 as both the Dictionary item and the Dictionary key).



•

We set the value of cell B1 (row i, column B) to 1.




From there we increment the value of i by 1, then return to the top of the loop and repeat the process with row 2, column 1. Suppose this cell is also equal to 5; what then? Well, in that case, the value 5 will exist in the Dictionary. Therefore, we simply go back to the top of the loop and try again with the next cell in column A.
Etc., etc.
So is that really going to work? Oh, come on; you’re hurting our feelings by even asking such a question!
In other words, yes, it really will work.
Speaking of which, the Scripting Guys need to do some work ourselves. But don’t worry about us: just keep sending in those scripts. As it turns out, we’re getting $1,000 for every single script we score during the 2008 Winter Scripting Games. 
Or at least we think we are; our manager told us to go ahead and score all the scripts first, and then “just send the bill to Microsoft.”