9.1 My First Macro in Excel

You have learned many new Excel functions, created a variety of different charts in Excel, and used Solver, Comparative Statics Wizard, Monte Carlo simulation, and FRED Excel add-ins. But those are all front-end Excel skills.

For computer scientists, the front end is the presentation layer or the user interface of software. The front end is that part of the restaurant, where the diner sits down and orders food.

The back end of a restaurant is the kitchen, where the meal is actually made. Excel’s back end is built on a computer language called Visual Basic. Excel and other Office products such as Word and PowerPoint run a version called Visual Basic Applications edition—or VBA for short.

It is in VBA that we write code, also known as macros, to perform especially complicated tasks. The code is written and stored in modules. The resulting Excel workbook is a combination of spreadsheets and modules that must be saved as a macro-enabled workbook with the extension .xlsm. Without macros, an Excel spreadsheet has the file extension .xlsx.

There is no question that a macro-enabled workbook is more powerful than a simple spreadsheet, but this power comes at a cost. Not only do you have to know how to write code in VBA, but your end user will probably have to enable macros when opening the file. Sometimes, security settings in a particular installation of Excel are set so high that the macros will not be allowed to run. The user has to change Excel’s settings, adding another layer of difficulty, just to open the file.

Hello, World!

It is a tradition in computer science to introduce a new language by outputting “Hello, World!” Let’s do it.

STEP Open Excel and save the file as IntroVBA.xlsm, making sure to save it as a macro-enabled workbook with the .xlsm extension by clicking Save as type in the save window and choosing .xlsm. Click the Visual Basic button in the Developer tab in the Ribbon. If the Developer tab is not visible, press Alt, f, t and click Customize Ribbon, then check Developer in the list. You can also access Visual Basic by pressing Alt-F11 (you may need to use the function [fn] key) or right-clicking the sheet tab and selecting View Code.

You have a new window on your screen with a lot of things you have never seen before. Welcome to Excel’s kitchen! You are in the Visual Basic Editor (VBE).

Across the top there is a familiar menu of items. The top-left panel should be the Project Explorer (press Ctrl-r if you do not see it). You may also see other panels.

STEP In the Project Explorer panel, scroll, if needed, to find your IntroVBA.xlsm workbook (it will be in parentheses after VBAProject), and select it (highlighted in blue). Click Insert in the top menu and select Module.

You now see a blank window. This is where you will write your code. Notice also that your workbook in the Project Explorer panel now has a new component, the module you just inserted.

STEP The cursor should be blinking in the blank window, but if not, click in the window. Enter the text sub myfirstmacro and press Enter.

The text is transformed. The S is capitalized and Sub is in blue, parentheses have been appended, and a new line End Sub has been added. Apparently, VBE is a high-level editor with a great deal of support.

Sub stands for subroutine, a set of instructions or lines of code. The statements between the Sub and End Sub lines are the body of the macro. You could pass arguments to your Sub by entering them in the () on the first line.

STEP In the middle line (where the cursor is blinking), enter the text msgbox “Hello, World!” and press Enter.

As you entered the text, you undoubtedly noticed the yellow pop-up showing the various options for the MsgBox object. This shows again the strong support offered in the editor in the VBA environment. Also, this example reveals that VBA is an object-oriented programming language. We write code to apply different methods and options to the objects.

We have finished our first macro and are ready to run it.

STEP Click Run in the top menu and select Run Sub/User Firm (or press F5).

You did it! You are returned to the Excel spreadsheet, and it displays a message box with “Hello, World!” on it.

This is exciting, but how can we run the macro from the spreadsheet?

STEP Press OK to close the message box and click the Developer tab. Click the Insert button and select the top-left Button icon. Click and drag in the spreadsheet to create a button. In the AssignMacro dialog box, select myfirstmacro and click OK. Click on an empty cell in the spreadsheet.

You have added a button to the spreadsheet and attached a macro to it. When you click the button, the macro will run. Try it.

STEP Click the button to see the message box pop up.

Now the user does not have to know how to run macros in VBA. By attaching the macro to the button, you have made it easy for the user to run your code.

STEP Right-click your button and replace the Button 1 text with Click Me. Click the button to see that it works.

This shows that the caption of the button can be different from the name of the macro.

We used the Scroll Bar and Combo Box controls earlier, but they were not macro-enabled. By assigning macros to controls, we greatly expand the power of Excel.

Recording Macros

VBA is hard at first because beginning users do not know any of the objects or commands. It is like learning a new spoken language: You know the words exist for what you want to say, but you do not know what they are.

One way to start growing your VBA vocabulary is by recording macros. You turn on the recorder and do things in Excel, then examine the recorded code in VBA.

STEP Click the Developer tab and click the Record Macro button. Click OK in the pop-up dialog box. Select cell A1 and enter 100. Make the formatting $. Click the Stop Recording button. Press Alt-F11 to go to VBA.

There is a new module sheet in your VBA project.

STEP Double-click Module2 in the Project Explorer panel.

You are looking at the code needed to do the steps you did in Excel. You now know that Selection.Style = “Currency” applies currency formatting to the selected cell.

STEP Change the 100 in the recorded macro to 0.1 and change Currency to Percent. Return to Excel by pressing Alt-F11 (this toggles you between Excel and VBA). Right-click your button and select AssignMacro. Choose Macro1 (that you just recorded and edited) and click OK. Click an empty cell in the spreadsheet and click your button.

Your macro changed A1 to 0.1 and formatted it as percent! This demonstrates that you can definitely control Excel from VBA.

This example shows how you can record a macro to reveal the code needed to perform a task in Excel. The usual procedure is to record a series of steps and then edit the code, removing superfluous lines and changing values or other attributes.

STEP Use the macro recorder to write a macro that takes a random number from a cell and pastes its value into the cell below it. Assign your macro to your button and click the button to run it. If you need help, see the appendix.

9.2 Functions in VBA

Attaching a macro to a button or other object allows you and your users to run VBA code, but there is another way to access code from an Excel workbook: a user-defined function (UDF).

Unlike a native function, like =RAND() or =SUM(cell range), which is part of the Excel application itself, UDFs are functions that you write in VBA and are stored in a module in a macro-enabled workbook (file name extension .xlsm).

To the user, UDFs work the same way as native functions, so they require no special knowledge. The only extra step is that the user has to enable the content in the workbook (or disable all security, which is not recommended).

To demonstrate how UDFs work, we will create two user-defined functions, the first without any arguments and the second allowing the user to pass information to the function, making it more flexible and useful.

UDF with No Arguments

Recall from our work on Monte Carlo simulation that we can create a 90% free throw shooter in Excel with the formula =IF(RAND()<0.9,1,0). Alternatively, we could create a function in VBA—say, FT()—that the user could enter in any cell. Ninety percent of the time it would produce a 1, and the remaining 10% would be 0.

STEP Open your IntroVBA.xlsm macro-enabled workbook and press Alt-F11 to go to Visual Basic. Insert a new module sheet in this workbook. Type the text function FT() (uppercase FT) and hit enter.

Excel’s Visual Basic Editor capitalizes the F in function, adds an End Function for you, and colors the text blue for the beginning and ending lines of the code.

Instead of the Excel function =RAND(), VBA has its own random number generator Rnd. It produces random numbers in the interval from 0 to 1. It is supereasy to make our UDF output a random number.

STEP Enter the text FT = Rnd and press Enter. Return to Excel (Alt-F11 is a toggle) and click on cell H1. Enter the function =FT() and press Enter.

Congratulations! You just wrote your first function in Excel and accessed it from Excel.

There is, however, a problem with our UDF.

STEP Press F9 a few times. Nothing happens to cell H1. It is not bouncing like RAND(), generating a new random number each time we recalculate the sheet.

The problem is that UDFs, by default, are nonvolatile functions. This means they do not get recalculated unless they depend on other cells that have changed. We must add code to make our function recalculate when F9 is pressed.

STEP Return to your VBA code for the FT function. Click on the top line, after the close parenthesis, and press Enter so that you are on a new, blank line and enter the text Application.Volatile True. Press Enter. Return to Excel and press F9 a few times. Success!

But we do not want to simply output a random number; we want to see if we made (1) or missed (0) a free throw attempt. We need to add some code to do this. Like the Excel formula we used to model a free throw result, we need an If statement to separate made from missed free throws.

STEP Go to the FT code. Click after True and press Enter to create a new line after the volatile statement. Enter the text if rnd < 0.9 then and press Enter.

As usual, the editor capitalizes and colors the text for you. The next line of code defines what happens if the random number is less than 0.9. It is followed by lines of code for missed free throws.

STEP Press the Tab key to indent, type the text FT = 1, and press enter. Type the word Else and press enter. Press Tab, type FT = 0, and press enter. Type end if and press enter. Put a straight single quote (‘) in front of the FT=Rnd line to comment it out (so it does not get executed).

Your masterpiece of code should look like this (with color added to keywords and lines):

Function FT()

Application.Volatile True

If Rnd < 0.9 Then

FT = 1

Else

FT = 0

End If

‘FT = Rnd

End Function

The macro draws a uniformly distributed random number on the interval from 0 to 1 (Rnd), and if it is less than 0.9, it goes to the FT = 1 line. Since the function is called FT, it outputs the number 1 if Rnd < 0.9. If the random number drawn is not less than 0.9, it goes to the FT = 0 line and outputs a 0.

The code is easy to read, but does it actually work? Let’s find out.

STEP Return to Excel, fill cell H1 down to cell H10, and press F9 a few times.

That is pretty cool, but what if we wanted a more generalized version, where the user tells us the chances of success?

UDF with an Argument

In native Excel functions—like SUM, for example—the arguments are passed in the parentheses: SUM(A1:A3). UDFs work the same way. We will add an argument to our code in the parentheses and modify the code to enable it to incorporate the information provided by the user.

STEP Copy the FT code and paste it below the End Function line. Since you cannot have two functions with the same name, change the name of the newly pasted function to FTARG (for argument). In the parentheses, type the text Shoot as Double. Replace the 0.9 in the If statement line with Shoot. Return to Excel and enter the formula =FTARG(0.5) in cell I1.

Excel displays an error message in cell I1. Can you figure out what is wrong with the UDF and fix it? If you cannot, take a look at the appendix.

We can make the function even more flexible by having it accept a value in another cell.

STEP Copy the FTARG code and paste it below the End Function line. Change the name of the newly pasted function to FTARGCELL (for argument from another cell). In the parentheses, change the text to myShootCell as Range. Replace Shoot in the If statement line with myShootCell.Value. Change the FTARG = 1 and FTARG = 0 lines to FTARGCELL = 1 and FTARGCELL = 0. To see how this works, return to Excel and enter the formula =FTARGCELL($K$1) in cell J1. Fill it down to cell J10. In cell K1, enter a number from 0 to 1, such as 0.25. Press F9 a few times.

With a 25% chance of success, your 10 numbers in column J are bouncing around every time you press F9, and you usually get 2 or 3 ones.

Is it better to allow the user to input a number as the argument or a cell address? That depends on the context of the problem, including the user’s familiarity with Excel. In fact, if you do not need to change the success rate, our original UDF, FT(), might be the best choice.