Adding To Your Spreadsheet

ADDING GETFORMULA TO YOUR SPREADSHEET *
Created by: Simon Benninga, [email protected]
Table of Contents
Overview ------------------------------------------------------------------------------------------------------------------------------------------------- 1
Procedure ------------------------------------------------------------------------------------------------------------------------------------------------ 1
Saving the Excel workbook: Windows------------------------------------------------------------------------------------------------------------ 4
Saving the Excel workbook: Mac ------------------------------------------------------------------------------------------------------------------ 4
Do you have to put Getformula into each Excel workbook? -------------------------------------------------------------------------------- 5
A shortcut to use Getformula ----------------------------------------------------------------------------------------------------------------------- 5
Automating the procedure ----------------------------------------------------------------------------------------------------------------------- 5
Recording Getformula: The Windows case ----------------------------------------------------------------------------------------------------- 5
Activate the Developer tab ----------------------------------------------------------------------------------------------------------------------- 5
Use Record Macro ---------------------------------------------------------------------------------------------------------------------------------- 6
Using the macro ------------------------------------------------------------------------------------------------------------------------------------- 7
Recording Getformula: The Mac case ------------------------------------------------------------------------------------------------------------ 7
Overview
The Excel notebooks in Financial Modeling, 4th edition contain a function called Getformula that aids in annotating your
spreadsheets. In the example below cell C5 shows the formula contained in cell B5; the formula in question computes the
annual repayment of a loan of 165,000 for 7 years at 8%. Cell C5 contains the function =Getformula(B5).
A B C
2 Principal 165,000
3 Interest 8%
4 Term 7 <-- years
5 Annual payment 31,691.95 <-- =PMT(B3,B4,-B2)
In this short document, we describe how to add this formula to your Excel notebook.
Mac users: This works only in Excel 2011.
Procedure
1. Open the Excel workbook in which you want the formula to work.
2. Open the VBA editor
On Windows computers: Push [Alt]+F11.
On Mac (Excel 2011): Tools|Macro|Visual Basic Editor
3. This will open the VBA editor.
*
If you are using a Mac, you will need Excel 2011. Otherwise the procedure described here will not work.
FM4, Adding Getformula to your spreadsheet 1
Windows screen Mac screen

4. Hit Insert|Module at the top of the screen.
Windows screen
Mac screen
5. Now insert the following text into the Module window (where it says General). Just copy/paste the text below.
'8/5/2006 Thanks to Maja Sliwinski and Beni Czaczkes
Function getformula(r As Range) As String
Application.Volatile
If r.HasArray Then
getformula = "<-- " & " {" & r.FormulaLocal & "}"
Else
getformula = "<-- " & " " & r.FormulaLocal
End If
End Function
In Windows close VBA window (no need to save). In the Mac, just continue to work on the spreadsheet. The formula is
now part of the spreadsheet and will be saved along with it.

Saving the Excel workbook: Windows
In order to save the notebook with the Getformula macro in VBA, you will have to save it as a Macro-enabled workbook.
Macro-enabled workbooks have extension .xlsm, whereas regular Excel workbooks have extension .xlsx. Your users will
never know the difference. We have changed our Excel settings (File|Options|Save ) to make the macro-enabled
workbook our default:
Saving the Excel workbook: Mac

The Mac screen for saving as a Macro-enabled workbook looks like this:

Do you have to put Getformula into each Excel workbook?
The short answer is yes. You could create an add-in to Excel (see Chapter 39) that contains Getformula, but this will
make it more difficult for you to share your workbooks. We prefer to put Getformula in each new spreadsheet we create.
A shortcut to use Getformula

Once you have put Getformula into your Excel workbook, you will have to use it! 90% of our uses of this function point
to the cell to the left of the formula itself:
Weve put a short macro into our Personal notebook that automates this procedure. The remainder of this section
describes how to automate the Getformula procedure.
Automating the procedure

We want to automate this procedure:
Turn it into a macro.
Attach a key sequence (in our case [Ctrl]+t ) to the macro.
Make the macro and key sequence available in your Excel spreadsheets.
We will save the macro to our Personal.xlsb file. This file activates each time you start Excel. Its yours onlyother
readers of your spreadsheets wont see it. Below we describe the steps, for both Windows and the Mac.
Recording Getformula: The Windows case

Here are the steps to recording the macro in Windows:
Activate the Developer tab on the menu bar
Use Record Macro to save a macro as a personal notebook
Activate the Developer tab

Go to File|Options|Customize Ribbon and activate the Developer tab as shown below:

Use Record Macro
The Developer tab allows you to record a macro and save it as part of the Personal.xlsb notebook. We will illustrate with
the copy as picture feature.
1. Open a blank Excel notebook and click on the Developer tab and then on Record macro:
Excel will ask for details of the recording. Heres what I wrote. We will save this as a Personal Macro Workbook and that
use the shortcut [Ctrl] + t:
2. Now go to your spreadsheet and use Getformula, pointing to the cell to the left of where you want Getformula to
appear. In the spreadsheet below, we have typed =Getformula(A3) into cell B3:

3. Go back to the Developer tab and stop the recording:
4. Close down Excel. Excel will ask you if you want to save the Personal notebook. The answer is, of course, positive:
This creates the following file (simon benninga is of course my user name on my computeryou will substitute your
user name).
C:\Users\simon benninga\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB
Using the macro

From now on, whenever you open a file on your computer, you can use Ctrl+t to copy a region as a picture. Cool!
Recording Getformula: The Mac case

To record a macro in Excel use Tools|Macro|Record New Macro:

To stop the recording:
As in the Windows case, you will be prompted to name the macro and assign it a control key sequence. When you save
the spreadsheet you will be asked if you want to save the Personal Notebook. Done!

Adding To Your Spreadsheet

Uploaded by

Copyright:

Available Formats

Adding To Your Spreadsheet

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Adding To Your Spreadsheet

Uploaded by

Copyright:

Available Formats

ADDING GETFORMULA TO YOUR SPREADSHEET *

Created by: Simon Benninga, [email protected]

3. This will open the VBA editor.

FM4, Adding Getformula to your spreadsheet 2

FM4, Adding Getformula to your spreadsheet 3

Saving the Excel workbook: Mac

FM4, Adding Getformula to your spreadsheet 4

A shortcut to use Getformula

Automating the procedure

Recording Getformula: The Windows case

Activate the Developer tab

FM4, Adding Getformula to your spreadsheet 5

FM4, Adding Getformula to your spreadsheet 6

Using the macro

Recording Getformula: The Mac case

FM4, Adding Getformula to your spreadsheet 7

FM4, Adding Getformula to your spreadsheet 8

You might also like