{"id":44,"date":"2025-04-21T03:26:05","date_gmt":"2025-04-21T03:26:05","guid":{"rendered":"https:\/\/pressbooks.palni.org\/gatewaytobusinessanalytics\/?post_type=chapter&p=44"},"modified":"2025-06-12T12:23:22","modified_gmt":"2025-06-12T12:23:22","slug":"introduction-to-vba","status":"publish","type":"chapter","link":"https:\/\/pressbooks.palni.org\/gatewaytobusinessanalytics\/chapter\/introduction-to-vba\/","title":{"raw":"Introduction to VBA","rendered":"Introduction to VBA"},"content":{"raw":"
\r\n

Once you become acquainted with VBA, you\u2019ll wonder how you ever got along without it.<\/p>\r\n

Reed Jacobson<\/p>\r\n\r\n<\/div>\r\n

9.1 My First Macro in Excel<\/h1>\r\n

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.<\/p>\r\n

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.<\/p>\r\n

The back end of a restaurant is the kitchen, where the meal is actually made. Excel\u2019s 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\u2014or VBA for short.<\/p>\r\n

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<\/em><\/span>. The resulting Excel workbook is a combination of spreadsheets and modules that must be saved as a macro-enabled workbook with the extension .<\/em>xlsm<\/em><\/span>. Without macros, an Excel spreadsheet has the file extension .xlsx<\/em><\/span>.<\/p>\r\n\r\n

\r\n

EXCEL TIP <\/em><\/strong><\/span>Excel warns you if you save a workbook with macros as a simple Excel workbook (.xlsx). If you ignore the warning, it will simply save the workbook without the macros, and your code will be lost.<\/p>\r\n\r\n<\/div>\r\n

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\u2019s settings, adding another layer of difficulty, just to open the file.<\/p>\r\n\r\n

\r\n

EXCEL TIP\u00a0<\/em><\/strong><\/span>If you can accomplish a task without macros, always<\/em><\/span> do so. Sometimes, however, VBA is the only solution.<\/p>\r\n\r\n<\/div>\r\n

Hello, World!<\/h2>\r\n

It is a tradition in computer science to introduce a new language by outputting \u201cHello, World!\u201d Let\u2019s do it.<\/p>\r\n

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

You have a new window on your screen with a lot of things you have never seen before. Welcome to Excel\u2019s kitchen! You are in the Visual Basic Editor (VBE).<\/p>\r\n

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

STEP<\/em> <\/span>In the Project<\/em><\/span> Explorer<\/em><\/span> panel, scroll, if needed, to find your IntroVBA.xlsm<\/em><\/span> workbook (it will be in parentheses after VBAProject), and select it (highlighted in blue). Click Insert<\/em><\/span> in the top menu and select Module<\/em><\/span>.<\/p>\r\n

You now see a blank window. This is where you will write your code. Notice also that your workbook in the Project<\/em><\/span> Explorer<\/em><\/span> panel now has a new component, the module you just inserted.<\/p>\r\n

STEP<\/em> <\/span>The cursor should be blinking in the blank window, but if not, click in the window. Enter the text sub<\/em><\/span> myfirstmacro<\/em><\/span> and press Enter<\/em><\/span>.<\/p>\r\n

The text is transformed. The S<\/em><\/span> is capitalized and Sub<\/em><\/span> is in blue, parentheses have been appended, and a new line End<\/em><\/span> Sub<\/em><\/span> has been added. Apparently, VBE is a high-level editor with a great deal of support.<\/p>\r\n

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

STEP<\/em> <\/span>In the middle line (where the cursor is blinking), enter the text msgbox<\/em><\/span> \u201cHello<\/em><\/span>, World!\u201d<\/em><\/span> and press Enter<\/em><\/span>.<\/p>\r\n

As you entered the text, you undoubtedly noticed the yellow pop-up showing the various options for the MsgBox<\/em><\/span> 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.<\/p>\r\n

We have finished our first macro and are ready to run it.<\/p>\r\n

STEP<\/em> <\/span>Click Run<\/em><\/span> in the top menu and select Run<\/em><\/span> Sub\/User<\/em><\/span> Firm<\/em><\/span> (or press F5<\/em><\/span>).<\/p>\r\n

You did it! You are returned to the Excel spreadsheet, and it displays a message box with \u201cHello<\/em><\/span>, World!\u201d<\/em><\/span> on it.<\/p>\r\n

This is exciting, but how can we run the macro from the spreadsheet?<\/p>\r\n

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

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.<\/p>\r\n

STEP<\/em> <\/span>Click the button to see the message box pop up.<\/p>\r\n

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.<\/p>\r\n\r\n

\r\n

EXCEL TIP\u00a0<\/em><\/strong><\/span>Regular (left) clicks enable you to use objects in Excel. Right-clicks select objects so that you can modify them.<\/p>\r\n\r\n<\/div>\r\n

STEP<\/em> <\/span>Right-click your button and replace the Button 1<\/em><\/span> text with Click Me<\/em><\/span>. Click the button to see that it works.<\/p>\r\n

This shows that the caption of the button can be different from the name of the macro.<\/p>\r\n

We used the S<\/em>croll <\/em>B<\/em>ar<\/em><\/span> and C<\/em>ombo <\/em>B<\/em>ox<\/em><\/span> controls earlier, but they were not macro-enabled. By assigning macros to controls, we greatly expand the power of Excel.<\/p>\r\n\r\n

Recording Macros<\/h2>\r\n

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.<\/p>\r\n

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.<\/p>\r\n

STEP<\/em> <\/span>Click the Developer<\/em><\/span> tab and click the Record Macro<\/span> button. Click OK<\/em><\/span> in the pop-up dialog box. Select cell A1 and enter 100<\/em><\/span>. Make the formatting $. Click the Stop Recording<\/span> button. Press Alt<\/em>-F11<\/em><\/span> to go to VBA.<\/p>\r\n

There is a new module sheet in your VBA project.<\/p>\r\n

STEP<\/em> <\/span>Double-click Module2<\/em><\/span> in the Project<\/em><\/span> Explorer<\/em><\/span> panel.<\/p>\r\n

You are looking at the code needed to do the steps you did in Excel. You now know that Selection.Style<\/em><\/span> =<\/em><\/span> \u201cCurrency\u201d<\/em><\/span> applies currency formatting to the selected cell.<\/p>\r\n

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

Your macro changed A1 to 0.1 and formatted it as percent! This demonstrates that you can definitely control Excel from VBA.<\/p>\r\n

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.<\/p>\r\n

STEP<\/em> <\/span>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.<\/p>\r\n\r\n

\r\n

Takeaways<\/p>\r\n\r\n<\/header>\r\n

\r\n

In the late 20th century, Excel was in a race with other spreadsheets that you have never heard of because Excel completely overwhelmed them. In 1993, Excel 5.0 debuted with VBA, and Microsoft crushed the competition.<\/p>\r\n

VBA is an implementation of Visual Basic that runs within Excel. Macros are written in VBA in modules and then assigned to controls (such as buttons) on the spreadsheet.<\/p>\r\n

If you want to continue learning about VBA, Reed Jacobson\u2019s Microsoft Office Excel <\/em>2007 Visual Basic for Applications Step by Step<\/em><\/span> is a great place to start. The files needed were originally on a CD (an older technology that could store \u201chuge\u201d amounts of data), but they are available for download at dub.sh\/hbvba<\/a>.<\/p>\r\n

You can also look at open-source VBA code. For example, the Comparative Statics Wizard and Monte Carlo simulation add-ins are freely accessible. So are the macro-enabled workbooks we have used. You can open their modules and inspect the code to learn VBA.<\/p>\r\n\r\n<\/div>\r\n<\/div>\r\n

\r\n

References<\/p>\r\n\r\n<\/header>\r\n

\r\n

The epigraph is from the first chapter of Reed Jacobson\u2019s Microsoft Office Excel 2007 Visual Basic for Applications Step by Step<\/em><\/span> (Pearson Education), Kindle Edition. This book is out of print, but there are many copies available, and it remains a great way to learn how to write macros in Excel.<\/p>\r\n\r\n<\/div>\r\n<\/div>\r\n

\r\n

Appendix<\/p>\r\n\r\n<\/header>\r\n

\r\n

STEP<\/em> <\/span>Click the Record Macro<\/span> button and enter the formula =RAND()<\/em><\/span> in a cell. Copy the cell, select another cell, and Paste Special<\/em><\/span> as Values<\/em><\/span>. Stop recording and go to VBA. The body of your recorded macro (the code between the Sub<\/em><\/span> and End<\/em><\/span> Sub<\/em><\/span> lines) should look something like this:<\/p>\r\n

'<\/p>\r\n

' Macro3 Macro<\/p>\r\n

'<\/p>\r\n

'<\/p>\r\n

Range(\u201cA3\u201d).Select<\/p>\r\n

ActiveCell.FormulaR1C1 = \u201c=RAND()\u201d<\/p>\r\n

Range(\u201cA3\u201d).Select<\/p>\r\n

Selection.Copy<\/p>\r\n

Range(\u201cA4\u201d).Select<\/p>\r\n

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _<\/p>\r\n

SkipBlanks :=False, Transpose:=False<\/p>\r\n

Range(\u201cA5\u201d).Select\\\\<\/p>\r\n

These lines are the actual commands you gave Excel. Notice that an apostrophe at the start of a line comments out that line (it is not executed) and is displayed in green-colored text (e.g., the line Macro3 Macro). The underscore character, _, continues the current statement on the next line.<\/p>\r\n

You could clean up this code so it looks like this:<\/p>\r\n

Range(\u201cA3\u201d).FormulaR1C1 = \u201c=RAND()\u201d<\/p>\r\n

Range(\u201cA3\u201d).Copy<\/p>\r\n

Range(\u201cA4\u201d).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _<\/p>\r\n

SkipBlanks:=False, Transpose:=False<\/p>\r\n

Range(\u201cA5\u201d).Select<\/p>\r\n

You could comment out the first line, and it would still work since the RAND()<\/em><\/span> function is already in cell A3. Running this macro replaces the contents of cell A3 without any warning. You do not notice this because the code writes the same formula that was there.<\/p>\r\n\r\n<\/div>\r\n<\/div>\r\n

9.2 Functions in VBA<\/h1>\r\n

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<\/em><\/span> function<\/em><\/span> (UDF).<\/p>\r\n

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).<\/p>\r\n

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).<\/p>\r\n

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.<\/p>\r\n\r\n

UDF with No Arguments<\/h2>\r\n

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)<\/em><\/span>. Alternatively, we could create a function in VBA\u2014say, FT()<\/em><\/span>\u2014that the user could enter in any cell. Ninety percent of the time it would produce a 1, and the remaining 10% would be 0.<\/p>\r\n

STEP<\/em> <\/span>Open your IntroVBA.xlsm<\/em><\/span> macro-enabled workbook and press Alt<\/em>-F11<\/em><\/span> to go to Visual Basic. Insert a new module sheet in this workbook. Type the text function<\/em><\/span> FT()<\/em><\/span> (uppercase FT) and hit enter.<\/p>\r\n

Excel\u2019s Visual Basic Editor capitalizes the F in function<\/em><\/span>, adds an End<\/em><\/span> Function<\/em><\/span> for you, and colors the text blue for the beginning and ending lines of the code.<\/p>\r\n

Instead of the Excel function =RAND()<\/em><\/span>, VBA has its own random number generator Rnd<\/em><\/span>. It produces random numbers in the interval from 0 to 1. It is supereasy to make our UDF output a random number.<\/p>\r\n

STEP<\/em> <\/span>Enter the text FT<\/em><\/span> =<\/em><\/span> Rnd<\/em><\/span> and press Enter<\/em><\/span>. Return to Excel (Alt<\/em>-F11<\/em><\/span> is a toggle) and click on cell H1. Enter the function =FT()<\/em><\/span> and press Enter<\/em><\/span>.<\/p>\r\n

Congratulations! You just wrote your first function in Excel and accessed it from Excel.<\/p>\r\n\r\n

\r\n

EXCEL TIP <\/em><\/strong><\/span>For UDFs, Excel remembers how you first entered the function. If you enter =ft()<\/em><\/span> (lowercase), it will keep using lowercase (even if you enter FT<\/em><\/span> in a different cell). This is not true for native functions: Enter =rand()<\/em><\/span> and Excel converts it to =RAND()<\/em><\/span>.<\/p>\r\n\r\n<\/div>\r\n

There is, however, a problem with our UDF.<\/p>\r\n

STEP<\/em> <\/span>Press F9<\/em><\/span> a few times. Nothing happens to cell H1. It is not bouncing like RAND()<\/em><\/span>, generating a new random number each time we recalculate the sheet.<\/p>\r\n

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<\/em><\/span> is pressed.<\/p>\r\n

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

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<\/em><\/span> statement to separate made from missed free throws.<\/p>\r\n

STEP<\/em> <\/span>Go to the FT<\/em><\/span> code. Click after True<\/em><\/span> and press Enter<\/em><\/span> to create a new line after the volatile statement. Enter the text if <\/em>rnd<\/em> < 0.9 then<\/em><\/span> and press Enter<\/em><\/span>.<\/p>\r\n

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.<\/p>\r\n

STEP<\/em> <\/span>Press the Tab<\/em><\/span> key to indent, type the text FT<\/em><\/span> =<\/em><\/span> 1<\/em><\/span>, and press enter<\/em><\/span>. Type the word Else<\/em><\/span> and press enter<\/em><\/span>. Press Tab<\/em><\/span>, type FT<\/em><\/span> =<\/em><\/span> 0<\/em><\/span>, and press enter<\/em><\/span>. Type end if<\/em><\/span> and press enter<\/em><\/span>. Put a straight single quote (') in front of the FT=<\/em>Rnd<\/em><\/span> line to comment it out (so it does not get executed).<\/p>\r\n

Your masterpiece of code should look like this (with color added to keywords and lines):<\/p>\r\n

Function FT()<\/p>\r\n

Application.Volatile True<\/p>\r\n

If Rnd < 0.9 Then<\/p>\r\n

FT = 1<\/p>\r\n

Else<\/p>\r\n

FT = 0<\/p>\r\n

End If<\/p>\r\n

'FT = Rnd<\/p>\r\n

End Function<\/p>\r\n

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

The code is easy to read, but does it actually work? Let\u2019s find out.<\/p>\r\n

STEP<\/em> <\/span>Return to Excel, fill cell H1 down to cell H10, and press F9<\/em><\/span> a few times.<\/p>\r\n

That is pretty cool, but what if we wanted a more generalized version, where the user tells us the chances of success?<\/p>\r\n\r\n

UDF with an Argument<\/h2>\r\n

In native Excel functions\u2014like SUM<\/em><\/span>, for example\u2014the arguments are passed in the parentheses: SUM(A1:A3)<\/em><\/span>. 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.<\/p>\r\n

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

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.<\/p>\r\n

We can make the function even more flexible by having it accept a value in another cell.<\/p>\r\n

STEP<\/em> <\/span>Copy the FTARG<\/em><\/span> code and paste it below the End<\/em><\/span> Function<\/em><\/span> line. Change the name of the newly pasted function to FTARGCELL<\/em><\/span> (for argument from another cell). In the parentheses, change the text to myShootCell<\/em><\/span> as<\/em><\/span> Range<\/em><\/span>. Replace Shoot<\/em><\/span> in the If<\/em><\/span> statement line with myShootCell.Value<\/em><\/span>. Change the FTARG<\/em><\/span> =<\/em><\/span> 1<\/em><\/span> and FTARG<\/em><\/span> =<\/em><\/span> 0<\/em><\/span> lines to FTARGCELL<\/em><\/span> =<\/em><\/span> 1<\/em><\/span> and FTARGCELL<\/em><\/span> =<\/em><\/span> 0<\/em><\/span>. To see how this works, return to Excel and enter the formula =FTARGCELL($K$1)<\/em><\/span> 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<\/em><\/span> a few times.<\/p>\r\n

With a 25% chance of success, your 10 numbers in column J are bouncing around every time you press F9<\/em><\/span>, and you usually get 2 or 3 ones.<\/p>\r\n

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\u2019s familiarity with Excel. In fact, if you do not need to change the success rate, our original UDF, FT()<\/em><\/span>, might be the best choice.<\/p>\r\n\r\n

\r\n

Takeaways<\/p>\r\n\r\n<\/header>\r\n

\r\n

VBA code can be accessed by users from Excel\u2019s front end by attaching macros to buttons and other controls.<\/p>\r\n

Another option is a UDF. The user enters a formula that runs the UDF code to do computations or other manipulations that are not available in native Excel functions.<\/p>\r\n

Like native Excel functions, UDFs usually require arguments. These variables are declared in the Function<\/em><\/span> statement, inside the parentheses.<\/p>\r\n

Writing code is science and art. Deciding whether arguments are needed and, if so, how to pass them (numbers or cell addresses, for example) requires knowledge of what needs to be done and who is going to do it. You need to know your audience.<\/p>\r\n

Most people think of Excel as some kind of sophisticated adding machine or calculator. Excel can certainly do arithmetic and other mathematical operations, but its back end or kitchen opens up a whole new world of opportunities and possibilities.<\/p>\r\n\r\n<\/div>\r\n<\/div>\r\n

\r\n

Appendix<\/p>\r\n\r\n<\/header>\r\n

\r\n

The FTARG<\/em><\/span> function fails when you change the name of the function to FTARG<\/em><\/span> but do not likewise update the name of the function in the code. You must change the FT<\/em><\/span> =<\/em><\/span> 1<\/em><\/span> line to FTARG<\/em><\/span> =<\/em><\/span> 1<\/em><\/span> and the FT<\/em><\/span> =<\/em><\/span> 0<\/em><\/span> line to FTARG<\/em><\/span> =<\/em><\/span> 0<\/em><\/span>.<\/p>\r\n

When you write a UDF, you always use the function name to output a result. If you need more than one cell to output results, you can use an array function.<\/p>\r\n\r\n<\/div>\r\n<\/div>","rendered":"

\n

Once you become acquainted with VBA, you\u2019ll wonder how you ever got along without it.<\/p>\n

Reed Jacobson<\/p>\n<\/div>\n

9.1 My First Macro in Excel<\/h1>\n

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.<\/p>\n

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.<\/p>\n

The back end of a restaurant is the kitchen, where the meal is actually made. Excel\u2019s 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\u2014or VBA for short.<\/p>\n

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<\/em><\/span>. The resulting Excel workbook is a combination of spreadsheets and modules that must be saved as a macro-enabled workbook with the extension .<\/em>xlsm<\/em><\/span>. Without macros, an Excel spreadsheet has the file extension .xlsx<\/em><\/span>.<\/p>\n

\n

EXCEL TIP <\/em><\/strong><\/span>Excel warns you if you save a workbook with macros as a simple Excel workbook (.xlsx). If you ignore the warning, it will simply save the workbook without the macros, and your code will be lost.<\/p>\n<\/div>\n

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\u2019s settings, adding another layer of difficulty, just to open the file.<\/p>\n

\n

EXCEL TIP\u00a0<\/em><\/strong><\/span>If you can accomplish a task without macros, always<\/em><\/span> do so. Sometimes, however, VBA is the only solution.<\/p>\n<\/div>\n

Hello, World!<\/h2>\n

It is a tradition in computer science to introduce a new language by outputting \u201cHello, World!\u201d Let\u2019s do it.<\/p>\n

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

You have a new window on your screen with a lot of things you have never seen before. Welcome to Excel\u2019s kitchen! You are in the Visual Basic Editor (VBE).<\/p>\n

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

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

You now see a blank window. This is where you will write your code. Notice also that your workbook in the Project<\/em><\/span> Explorer<\/em><\/span> panel now has a new component, the module you just inserted.<\/p>\n

STEP<\/em> <\/span>The cursor should be blinking in the blank window, but if not, click in the window. Enter the text sub<\/em><\/span> myfirstmacro<\/em><\/span> and press Enter<\/em><\/span>.<\/p>\n

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

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

STEP<\/em> <\/span>In the middle line (where the cursor is blinking), enter the text msgbox<\/em><\/span> \u201cHello<\/em><\/span>, World!\u201d<\/em><\/span> and press Enter<\/em><\/span>.<\/p>\n

As you entered the text, you undoubtedly noticed the yellow pop-up showing the various options for the MsgBox<\/em><\/span> 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.<\/p>\n

We have finished our first macro and are ready to run it.<\/p>\n

STEP<\/em> <\/span>Click Run<\/em><\/span> in the top menu and select Run<\/em><\/span> Sub\/User<\/em><\/span> Firm<\/em><\/span> (or press F5<\/em><\/span>).<\/p>\n

You did it! You are returned to the Excel spreadsheet, and it displays a message box with \u201cHello<\/em><\/span>, World!\u201d<\/em><\/span> on it.<\/p>\n

This is exciting, but how can we run the macro from the spreadsheet?<\/p>\n

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

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.<\/p>\n

STEP<\/em> <\/span>Click the button to see the message box pop up.<\/p>\n

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.<\/p>\n

\n

EXCEL TIP\u00a0<\/em><\/strong><\/span>Regular (left) clicks enable you to use objects in Excel. Right-clicks select objects so that you can modify them.<\/p>\n<\/div>\n

STEP<\/em> <\/span>Right-click your button and replace the Button 1<\/em><\/span> text with Click Me<\/em><\/span>. Click the button to see that it works.<\/p>\n

This shows that the caption of the button can be different from the name of the macro.<\/p>\n

We used the S<\/em>croll <\/em>B<\/em>ar<\/em><\/span> and C<\/em>ombo <\/em>B<\/em>ox<\/em><\/span> controls earlier, but they were not macro-enabled. By assigning macros to controls, we greatly expand the power of Excel.<\/p>\n

Recording Macros<\/h2>\n

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.<\/p>\n

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.<\/p>\n

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

There is a new module sheet in your VBA project.<\/p>\n

STEP<\/em> <\/span>Double-click Module2<\/em><\/span> in the Project<\/em><\/span> Explorer<\/em><\/span> panel.<\/p>\n

You are looking at the code needed to do the steps you did in Excel. You now know that Selection.Style<\/em><\/span> =<\/em><\/span> \u201cCurrency\u201d<\/em><\/span> applies currency formatting to the selected cell.<\/p>\n

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

Your macro changed A1 to 0.1 and formatted it as percent! This demonstrates that you can definitely control Excel from VBA.<\/p>\n

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.<\/p>\n

STEP<\/em> <\/span>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.<\/p>\n

\n
\n

Takeaways<\/p>\n<\/header>\n

\n

In the late 20th century, Excel was in a race with other spreadsheets that you have never heard of because Excel completely overwhelmed them. In 1993, Excel 5.0 debuted with VBA, and Microsoft crushed the competition.<\/p>\n

VBA is an implementation of Visual Basic that runs within Excel. Macros are written in VBA in modules and then assigned to controls (such as buttons) on the spreadsheet.<\/p>\n

If you want to continue learning about VBA, Reed Jacobson\u2019s Microsoft Office Excel <\/em>2007 Visual Basic for Applications Step by Step<\/em><\/span> is a great place to start. The files needed were originally on a CD (an older technology that could store \u201chuge\u201d amounts of data), but they are available for download at dub.sh\/hbvba<\/a>.<\/p>\n

You can also look at open-source VBA code. For example, the Comparative Statics Wizard and Monte Carlo simulation add-ins are freely accessible. So are the macro-enabled workbooks we have used. You can open their modules and inspect the code to learn VBA.<\/p>\n<\/div>\n<\/div>\n

\n
\n

References<\/p>\n<\/header>\n

\n

The epigraph is from the first chapter of Reed Jacobson\u2019s Microsoft Office Excel 2007 Visual Basic for Applications Step by Step<\/em><\/span> (Pearson Education), Kindle Edition. This book is out of print, but there are many copies available, and it remains a great way to learn how to write macros in Excel.<\/p>\n<\/div>\n<\/div>\n

\n
\n

Appendix<\/p>\n<\/header>\n

\n

STEP<\/em> <\/span>Click the Record Macro<\/span> button and enter the formula =RAND()<\/em><\/span> in a cell. Copy the cell, select another cell, and Paste Special<\/em><\/span> as Values<\/em><\/span>. Stop recording and go to VBA. The body of your recorded macro (the code between the Sub<\/em><\/span> and End<\/em><\/span> Sub<\/em><\/span> lines) should look something like this:<\/p>\n

‘<\/p>\n

‘ Macro3 Macro<\/p>\n

‘<\/p>\n

‘<\/p>\n

Range(\u201cA3\u201d).Select<\/p>\n

ActiveCell.FormulaR1C1 = \u201c=RAND()\u201d<\/p>\n

Range(\u201cA3\u201d).Select<\/p>\n

Selection.Copy<\/p>\n

Range(\u201cA4\u201d).Select<\/p>\n

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _<\/p>\n

SkipBlanks :=False, Transpose:=False<\/p>\n

Range(\u201cA5\u201d).Select\\\\<\/p>\n

These lines are the actual commands you gave Excel. Notice that an apostrophe at the start of a line comments out that line (it is not executed) and is displayed in green-colored text (e.g., the line Macro3 Macro). The underscore character, _, continues the current statement on the next line.<\/p>\n

You could clean up this code so it looks like this:<\/p>\n

Range(\u201cA3\u201d).FormulaR1C1 = \u201c=RAND()\u201d<\/p>\n

Range(\u201cA3\u201d).Copy<\/p>\n

Range(\u201cA4\u201d).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _<\/p>\n

SkipBlanks:=False, Transpose:=False<\/p>\n

Range(\u201cA5\u201d).Select<\/p>\n

You could comment out the first line, and it would still work since the RAND()<\/em><\/span> function is already in cell A3. Running this macro replaces the contents of cell A3 without any warning. You do not notice this because the code writes the same formula that was there.<\/p>\n<\/div>\n<\/div>\n

9.2 Functions in VBA<\/h1>\n

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<\/em><\/span> function<\/em><\/span> (UDF).<\/p>\n

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).<\/p>\n

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).<\/p>\n

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.<\/p>\n

UDF with No Arguments<\/h2>\n

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)<\/em><\/span>. Alternatively, we could create a function in VBA\u2014say, FT()<\/em><\/span>\u2014that the user could enter in any cell. Ninety percent of the time it would produce a 1, and the remaining 10% would be 0.<\/p>\n

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

Excel\u2019s Visual Basic Editor capitalizes the F in function<\/em><\/span>, adds an End<\/em><\/span> Function<\/em><\/span> for you, and colors the text blue for the beginning and ending lines of the code.<\/p>\n

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

STEP<\/em> <\/span>Enter the text FT<\/em><\/span> =<\/em><\/span> Rnd<\/em><\/span> and press Enter<\/em><\/span>. Return to Excel (Alt<\/em>-F11<\/em><\/span> is a toggle) and click on cell H1. Enter the function =FT()<\/em><\/span> and press Enter<\/em><\/span>.<\/p>\n

Congratulations! You just wrote your first function in Excel and accessed it from Excel.<\/p>\n

\n

EXCEL TIP <\/em><\/strong><\/span>For UDFs, Excel remembers how you first entered the function. If you enter =ft()<\/em><\/span> (lowercase), it will keep using lowercase (even if you enter FT<\/em><\/span> in a different cell). This is not true for native functions: Enter =rand()<\/em><\/span> and Excel converts it to =RAND()<\/em><\/span>.<\/p>\n<\/div>\n

There is, however, a problem with our UDF.<\/p>\n

STEP<\/em> <\/span>Press F9<\/em><\/span> a few times. Nothing happens to cell H1. It is not bouncing like RAND()<\/em><\/span>, generating a new random number each time we recalculate the sheet.<\/p>\n

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<\/em><\/span> is pressed.<\/p>\n

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

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<\/em><\/span> statement to separate made from missed free throws.<\/p>\n

STEP<\/em> <\/span>Go to the FT<\/em><\/span> code. Click after True<\/em><\/span> and press Enter<\/em><\/span> to create a new line after the volatile statement. Enter the text if <\/em>rnd<\/em> < 0.9 then<\/em><\/span> and press Enter<\/em><\/span>.<\/p>\n

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.<\/p>\n

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

Your masterpiece of code should look like this (with color added to keywords and lines):<\/p>\n

Function FT()<\/p>\n

Application.Volatile True<\/p>\n

If Rnd < 0.9 Then<\/p>\n

FT = 1<\/p>\n

Else<\/p>\n

FT = 0<\/p>\n

End If<\/p>\n

‘FT = Rnd<\/p>\n

End Function<\/p>\n

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

The code is easy to read, but does it actually work? Let\u2019s find out.<\/p>\n

STEP<\/em> <\/span>Return to Excel, fill cell H1 down to cell H10, and press F9<\/em><\/span> a few times.<\/p>\n

That is pretty cool, but what if we wanted a more generalized version, where the user tells us the chances of success?<\/p>\n

UDF with an Argument<\/h2>\n

In native Excel functions\u2014like SUM<\/em><\/span>, for example\u2014the arguments are passed in the parentheses: SUM(A1:A3)<\/em><\/span>. 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.<\/p>\n

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

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.<\/p>\n

We can make the function even more flexible by having it accept a value in another cell.<\/p>\n

STEP<\/em> <\/span>Copy the FTARG<\/em><\/span> code and paste it below the End<\/em><\/span> Function<\/em><\/span> line. Change the name of the newly pasted function to FTARGCELL<\/em><\/span> (for argument from another cell). In the parentheses, change the text to myShootCell<\/em><\/span> as<\/em><\/span> Range<\/em><\/span>. Replace Shoot<\/em><\/span> in the If<\/em><\/span> statement line with myShootCell.Value<\/em><\/span>. Change the FTARG<\/em><\/span> =<\/em><\/span> 1<\/em><\/span> and FTARG<\/em><\/span> =<\/em><\/span> 0<\/em><\/span> lines to FTARGCELL<\/em><\/span> =<\/em><\/span> 1<\/em><\/span> and FTARGCELL<\/em><\/span> =<\/em><\/span> 0<\/em><\/span>. To see how this works, return to Excel and enter the formula =FTARGCELL($K$1)<\/em><\/span> 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<\/em><\/span> a few times.<\/p>\n

With a 25% chance of success, your 10 numbers in column J are bouncing around every time you press F9<\/em><\/span>, and you usually get 2 or 3 ones.<\/p>\n

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\u2019s familiarity with Excel. In fact, if you do not need to change the success rate, our original UDF, FT()<\/em><\/span>, might be the best choice.<\/p>\n

\n
\n

Takeaways<\/p>\n<\/header>\n

\n

VBA code can be accessed by users from Excel\u2019s front end by attaching macros to buttons and other controls.<\/p>\n

Another option is a UDF. The user enters a formula that runs the UDF code to do computations or other manipulations that are not available in native Excel functions.<\/p>\n

Like native Excel functions, UDFs usually require arguments. These variables are declared in the Function<\/em><\/span> statement, inside the parentheses.<\/p>\n

Writing code is science and art. Deciding whether arguments are needed and, if so, how to pass them (numbers or cell addresses, for example) requires knowledge of what needs to be done and who is going to do it. You need to know your audience.<\/p>\n

Most people think of Excel as some kind of sophisticated adding machine or calculator. Excel can certainly do arithmetic and other mathematical operations, but its back end or kitchen opens up a whole new world of opportunities and possibilities.<\/p>\n<\/div>\n<\/div>\n

\n
\n

Appendix<\/p>\n<\/header>\n

\n

The FTARG<\/em><\/span> function fails when you change the name of the function to FTARG<\/em><\/span> but do not likewise update the name of the function in the code. You must change the FT<\/em><\/span> =<\/em><\/span> 1<\/em><\/span> line to FTARG<\/em><\/span> =<\/em><\/span> 1<\/em><\/span> and the FT<\/em><\/span> =<\/em><\/span> 0<\/em><\/span> line to FTARG<\/em><\/span> =<\/em><\/span> 0<\/em><\/span>.<\/p>\n

When you write a UDF, you always use the function name to output a result. If you need more than one cell to output results, you can use an array function.<\/p>\n<\/div>\n<\/div>\n","protected":false},"author":13,"menu_order":9,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":[],"pb_section_license":""},"chapter-type":[],"contributor":[],"license":[],"part":3,"_links":{"self":[{"href":"https:\/\/pressbooks.palni.org\/gatewaytobusinessanalytics\/wp-json\/pressbooks\/v2\/chapters\/44"}],"collection":[{"href":"https:\/\/pressbooks.palni.org\/gatewaytobusinessanalytics\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/pressbooks.palni.org\/gatewaytobusinessanalytics\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/pressbooks.palni.org\/gatewaytobusinessanalytics\/wp-json\/wp\/v2\/users\/13"}],"version-history":[{"count":10,"href":"https:\/\/pressbooks.palni.org\/gatewaytobusinessanalytics\/wp-json\/pressbooks\/v2\/chapters\/44\/revisions"}],"predecessor-version":[{"id":370,"href":"https:\/\/pressbooks.palni.org\/gatewaytobusinessanalytics\/wp-json\/pressbooks\/v2\/chapters\/44\/revisions\/370"}],"part":[{"href":"https:\/\/pressbooks.palni.org\/gatewaytobusinessanalytics\/wp-json\/pressbooks\/v2\/parts\/3"}],"metadata":[{"href":"https:\/\/pressbooks.palni.org\/gatewaytobusinessanalytics\/wp-json\/pressbooks\/v2\/chapters\/44\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/pressbooks.palni.org\/gatewaytobusinessanalytics\/wp-json\/wp\/v2\/media?parent=44"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/pressbooks.palni.org\/gatewaytobusinessanalytics\/wp-json\/pressbooks\/v2\/chapter-type?post=44"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/pressbooks.palni.org\/gatewaytobusinessanalytics\/wp-json\/wp\/v2\/contributor?post=44"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/pressbooks.palni.org\/gatewaytobusinessanalytics\/wp-json\/wp\/v2\/license?post=44"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}