Use Excel to Automate Repetitive Code

• Introducting to the Problem
• Simple Excel Concatenation Example
• User Defined VBA Function
• Fancy Calculation
• Retrieving the Result

Have you ever wanted to pull data from an Excel Spreadsheet and place it into a Class or Object in your program? If you need to use a large number of repetitive structures, do you think it would be easier to create and modify them in Excel first? I’ll show you how I’ve used Excel 2007 to create a large table, then used Excel tools to automatically turn them into JavaScript Objects.

Introduction to the Problem

I’ll show you the data I was working with so you can follow along. I had created my own time-zone database, compiled from numerous sources, for use in a JavaScript time-zone converter. Here’s the format:

A: countryCode | B: countryName | C: numberTZs | D: description | E: cities | F: abbreviation | G: fullName | H: databaseName | I: UtcOffset | J: UtcOffsetNumber | K: DstHiemisphere | L: DstStart2010 | M: DstEnd

There are 310 rows in my table, and I want to make each one into a JavaScript object, each a property of one containing object. You might ask me why I don’t just use a real database, and the answer is this data is final (I wont be updating Daylight Savings Times), and I’m trying to use this for a JavaScript drop-list in a specific post on a WordPress blog (yes, this blog). I don’t want to deal with any server-side code (mostly because I don’t know how yet). Anyway, the point is I’m going to use Excel to do all my work for me.

Simple Excel Concatenation Example

The tricky part is going to be dealing with quotation marks. In the language of Excel (VBA), a quotation mark signifies the beginning or end of a string. In order to actually display the quotation mark in a cell, you need to escape it by first entering another quotation mark. Also important to know: (1) To tell Excel you want a cell to perform a calculation, you need to start by entering an equal sign; and (2) The ampersand (&) is the concatenation operator.

I’ve already given you enough information to get started. Try it out. Enter some text in cell A1 and B1, then place this in cell C1:

= "A1: """ & A1 & """, B1: """ & B1 & ""

Here’s what it should look like:

User Defined VBA Function

Unfortunately, my final solution is going to be much more complicated. I started out using the technique above to simply write out the code required to create the objects, but I ran into a problem. In my column E, I have entered a list of cities (of varying size) separated by comma + space. In order to turn that list into the JavaScript code to create an array, I was forced to write a custom VBA function. Don’t worry, it’s not that hard. Just find the tool to View Macros (in Excel 2007, it’s in the View tab) as shown below:

In the new dialog, type in a name for your “Macro” and click Create. This will add the “Macro” to all open workbooks by default, which is fine with me. By the way, we’re not really going to be using a macro, but the process to create a User Defined Function is the same. You can change the name by simply changing the name of your function (as I did later).

Now at this point, you’re creating a custom VBA function (User Defined Function). If you’re like me, you don’t know much about programming in VBA, so you might want to use the Microsoft Visual Basic Reference. After a little reading, this is what I came up with (and it works):

Public Function TextToArray(str As String, Optional sepChar As String = ", ")
    Dim strList() As String
    Dim final As String
    Dim i As Integer
    strList = Split(str, sepChar)
    For i = 0 To UBound(strList)
        final = final & """" & strList(i) & """, "
    Next
    TextToArray = Left(final, Len(final) - 2)
End Function

Strangely, once you’re finished typing the function, it will automatically become available within the spreadsheet. There’s no need to save or even close the editor window, but I have noticed that changes may not update unless you do: Debug → Compile VBA Project. Your function should be listed as soon as you start tying the name (after an equals sign, of course). However, there will not be a description or any help with the parameters. Martin Green has some guidance for adding a function description in his post at ExcelTip.com: Writing your First VBA Function in Excel. However, I wont need a description for mine (I know what it does).

Here it is in action:

Fancy Calculation

Finally, we have all the tools we need to write some code with Excel. As you may have noticed, you can either do the concatenations inside each cell, or you can create a new User Defined Function that takes the cell references as parameters. The second solution would be more elegant, but it would also require more thought, and this is all about fast, easy automation! So I will be entering the entire calculation into a single cell, then copying that calculation to all the other cells using Excel’s Fill Handle, so it will modify the cell references as appropriate.

I recommend using Notepad++ to edit your concatenation string to help with visualizing syntax. Make sure you set the Language to VB. After much tweaking, here is the formula I came up with:

=IF(EXACT(A1, A2), 
    "", 
    """" & A2 & """: {" & CHAR(10) & 
    "    ""countryName"": """ & B2 & """, " & CHAR(10) & 
    "    ""countryCode"": """ & A2 & """, " & CHAR(10) & 
    "    ""numberTZs"": " & C2 & ", " & CHAR(10) & 
    "    ""tzData"": {" & CHAR(10)) & 
"        """ & H2 & """: {" & CHAR(10) &
"            ""UtcOffset"": """ & I2 & """, " & CHAR(10) & 
"            ""UtcOffsetNumber"": " & J2 & ", " & CHAR(10) & 
"            ""databaseName"": """ & H2 & """, " & CHAR(10) & 
"            ""abbreviation"": """ & F2 & """, " & CHAR(10) & 
"            ""fullName"": """ & G2 & """, " & CHAR(10) & 
"            ""cities"": new Array(" & TextToArray(E2, ", ") & ")" & 
IF(ISBLANK(D2), 
    "",
    "," & CHAR(10) & 
    "            ""Description"": """ & D2 & """") &
IF(ISBLANK(K2), 
    "",
    "," & CHAR(10) & 
    "            ""DstHemisphere"": """ & K2 & """, " & CHAR(10) & 
    "            ""DstStart2010"": new Date(Date.UTC(""" & L2 & """)), " & CHAR(10) & 
    "            ""DstEnd"": new Date(Date.UTC(""" & M2 & """))") & 
IF(EXACT(A2, A3), 
    CHAR(10) & 
    "        },",
    CHAR(10) & 
    "        }" & CHAR(10) &
    "    }" & CHAR(10) &
    "}")

A few notes about the calculation above:

• Notice that you can use the IF function within the concatenation string itself
• CHAR(10) is a line feed character, used to start a new line for better clarity. However, you wont be able to see the improved formatting in your cells until you change the Cell Alignment to Wrap Text, enabling multiple lines per cell.
• I used ISBLANK to check for empty cells before adding the optional properties.
• I used EXACT to check for identical country codes above and below the current row to determine whether or not to create a new object for the country. Note that this will cause an error on the first row (there is no row above the first row), so I designed the entire calculation for the second row. It’s easy to fix the first row after dragging this to it with the Fill Handle.

Retrieving the Result

After finishing the calculation, paste it into the next available cell in the second row of the spreadsheet:

Then press Enter to complete the calculation. You should see something like your desired output displayed in the target cell. If not, begin debugging. The next step is to change the cell alignment for every cell in the column you’re using for calculations, so that you can see the formatting you worked so hard on with your calculation. First select the entire column (the one you’re using for the calculations), and then change the alignment to Wrap Text (either from the right-click menu or from the toolbar) as shown below (Excel 2007 example shown):

Excellent! Now just drag that calculation down to the rest of the table as shown below. This will copy the calculation but change the cell references as appropriate:

Note: I also dragged the calculation to the first row with the fill handle, but I had to modify it slightly to prevent an invalid reference to cell A0.

So now the cells are all populated. Isn’t it beautiful?

Ok, it’s a little hard to see, but it’s great. We’re not done yet, though. There’s one last tricky part: You can’t just copy and paste this into your text editor / IDE !!!. For some reason I don’t fully understand, Excel adds extra quotation marks around seemingly random portions of the output, even if you export it to a text file. Here’s what it looks like (thanks for nothing, Excel):

That is a disaster for our code wherein quotation marks are everything!, so you can’t just replace all quotation marks with an empty string, as you might see suggested in some internet forums. The only way to paste this and preserve your careful formatting is to paste it into Microsoft Word, then copy and paste it into your editor. Thank goodness for Word.

That’s it, the finished product! Even with all the time I spend learning this, I’m still very much time-ahead. Plus, I think I would have shot myself by now if I tried to do that by hand.