Lately I’ve been finding myself toying around more with Microsoft Excel — a piece of software that I’m sure many of us have wrangled with at some point or another. It turns out that Microsoft Excel is an exceptionally powerful piece of software.

One of Excel’s best kept secrets is Visual Basic for Applications¹. At its core, VBA works like a programming language that can be embedded in Microsoft Office documents to certain tasks. I know, programming sounds really scary — but bear with me! It can be quite fun and even rewarding to become familiar with this feature of Excel*

The purpose of #HiEdVBA is two-fold — as always I want to document my learning as I explore new tools and technology. I also hope that as these VBA macros become more complex, folks are able to use them to enhance their workflow.

This week, I want to begin by creating a simple list. Working in residence life, I often am looking at a list of the residential communities on my campus (13 at the time this post was posted). Instead of writing out the name of each residential community and formatting the sheet to make it somewhat more visually appealing, let’s start by creating a VBA macro.

The first thing we need is access to the Developer tab in the Excel ribbon. It’s as simple as ticking a box — you just have to know where to look! We’ll go to File -> Options -> Customize Ribbon. You’ll see a list of the default ribbon tabs that you normally see. Let’s activate the Developer tab by making sure that box is ticked. Click “Ok” to exit this menu.

There are two ways to create an Excel macro: You can either use the Record Macro button, or you can tell Excel exactly what you want to do using the built in text-editor. I reccomend you become comfortable with writing your code directly, as (1) the record macro feature spits out some extraneous information that just clutters your script and (2) it really empowers users to tinker and try messing with new ideas or concepts². I’ll leave the reader to read further about storing macros in such a way that other Excel workbooks can access them³.

Under the Developer ribbon tab, select Macros -> Name Accordingly (I reccomend HallNames for this example) –> Create. You should end up with a minimalistic screen that looks like a text editor. Like any other text editor, be sure to save your work frequently and double check the details as you go.

Now, there are two things that we want to accomplish with this script. We want to nicely format a header for our spreadsheet and then follow that with a list of our residence halls. Let’s look at each of these components seperately.

Spreadsheet Header

Let’s start by creating a header for our spreadsheet.

Sub HallNames()

Sheet1.Range("A1:B1").Merge
ActiveCell.FormulaR1C1 = "Residence Centers"
ActiveCell.Font.Bold = True
ActiveCell.Font.Size = 14
ActiveCell.Interior.ColorIndex = 37

It looks like there’s a lot going on here, but it’s not so scary once we break it down.

Every VBA script begins with a Sub header — a name that we can use to reference the script that we’re about to write. After that you might be able to parse out that the 2nd line of code above is just merging two cells together. The remaining lines format the header cells in the following steps:

1. Add the text “Residence Centers” to the newly merged cells.
2. Makes the newly inputted text bold
3. Changes the font size to 14
4. Changes the cell background color to a light blue color.

Now we turn our attention to the last chunk of the script.

List of Halls

    Range("A2") = "Ashton"
    Range("A3") = "Collins"
    Range("A4") = "Eigenmann"
    Range("A5") = "Wright"
    Range("A6") = "USC"
    Range("A7") = "Forest"
    Range("A8") = "Read"
    Range("A9") = "Spruce"
    Range("A10") = "Wells"
    Range("A11") = "Willkie"
    Range("A12") = "Briscoe"

End Sub

You might find this bit of the script a bit easier to follow along with. Starting in Cell A2 we input the name of a residence hall (“Ashton”) and procede down the A column until we’ve entered all of the items that we want to list out. End sub marks the end of the script by telling Excel that we’re done adding commands to our script!

Conclusion

So why does VBA matter? I admit that after reading through this first entry in the #HigherEdVBA blog post series, you might not be totally convinced. After all, you could have easily decided to create an Excel template for the script we’ve created and be done with it. I purposely began with a simple example and script to illustrate some of the principles that undergird VBA. As our needs become more complex, we might find ourselves exceeding the limits of how much time a template can realistically save us. VBA scripts are also portable — they can be dropped into any Excel file and aren’t limited to one document.

¹Due to security concerns, some institutions may prevent users from using .xlsm files, the filetype used to run an Excel Macro-Enabled Workbook.

²All materials for these tutorials are hosted on my GitHub profile, linked here. If you’re not familar with GitHub, it’s basically a Google Drive or DropBox for code with some pretty cool features. Also, I don’t intend on getting into the nuts and bolts of how to properly create these scripts — I’ll leave that to the reader to explore further if so desired. My primary aim with each of these series is to provide helpful scripts that folks can plug into their Excel workbook and run with.

³See the corresponding Microsoft Office documentation, linked here.