Inside™

®

Volume 12, Number 12 • December 2005 • www.elementkjournals.com

Analysis Tools

In this issue:
Are your investments too risky?
Find out with the Solver tool
Four things Diff Doc can dothat the Compare Side By Sidefeature can’t
Home in on Diff Doc’s Excel
settings for ultimate customization
Automatically convert currency
values into words—and save a tonof data entry time
Authenticate your macros socolleagues know the workbook
is safe to open

Are your investments too risky? Find out
with the Solver tool
by Frank Kuhn

AT A GLANCE
Knowing which stocks to pick and in what quantities so as to maximize yourreturns while minimizing your risk is a trick that can bafﬂe even the best of the Wall Street
wizards. You can take a giant step toward mastering this trick—even if you don’t haveyears of experience or education under your belt—by mastering Excel’s Solver tool.
To show you how to maximize investment returns while minimizing investment risk, we’ll:• Set up a worksheet that illustrates the problem facing all investors: how to increase yourgains without accruing too much risk.

• Point out the various constraints and variables present in such a problem.
• Introduce you to the Solver tool and use it to unravel this investment riddle.

Quick Tips

✎ Application: Microsoft Excel 2000/2002/2003/2004: Operating Systems: Microsoft Windows, Macintosh— Keywords: Excel, Financial Analysis, Diversification, Scenario-Testing, Investment Risk, Solver Tool
8 Download: http://download.elementkjournals.com/excel/200512/solver.zip

Editor’s
Note:
Relax—the holiday season is almost
over and soon you’ll be ringing in a
New Year! End the old year off right bychecking out the how-to techniques
I’ve included in this month’s issue. Frank
Kuhn leads you through some sticky
investment situations by showing you
how Excel can help maximize yourreturns while minimizing your risk.
I’ll demonstrate four user-defined
functions that translate numericalcurrency values into English words to
aid your accounting needs. Please feel
free to contact me, even if it’s just to
introduce yourself. I look forward tohearing from you.
Kara

ime_editor@elementk.com
Toll-free: (866)614-8715

Investors want to maximize the overall return while minimizing the overall risk, but they
only have so much money to work with. The difﬁculty lies in the number and complexity of
the related variables, constraints, and conditions involved. And, each stock in the portfolio
has its own beta risk measure as well as its own expected return. Goal Seek can only handle
scenario-testing dealing with one constraint. Luckily, here’s where the Solver tool comes tothe rescue.

Set up a portfolio worksheet

Most investors have not just one but several investments in their portfolio. Each of theseinvestments has its own respective degree of risk and expected return. Some of these investments offer greater returns than others, but they also likely bring greater risk in the bargain.
How do you adjust the weights of these investments so as to optimize and balance both risk
and reward?
To illustrate this problem, we’ve set up a sample portfolio spreadsheet, as shown in Figure
A. In our sample portfolio, we have three stocks: Stock A, B, and C. You’re investigating
Stock X, whose beta is 1.39. It has an expected return of 15.6 percent. You’re interested inthis exciting but somewhat risky stock, but you don’t want your portfolio beta to exceed themarket beta of 1.0: That is to say, you don’t want your portfolio to be any more risky than themarket as a whole. Using risk-free T-bills as a counter-weight to the risky Stock X, how muchof Stock X in relation to T-bills should you buy to maximize your portfolio return while
maintaining a portfolio beta of 1.0?

Note: You can follow along with our example
by downloading and extracting the fileinvestment.xls from the URL given at the
beginning of this article or build your own
worksheet using ours as a guide.

To answer this question, we’ll use
Excel’s Solver tool. Before explaining
how to use it, however, we’ll ﬁrst show
you how to set up this portfolio worksheet.
To set up a portfolio worksheet:
1. Enter the necessary column headings and formatting, using Figure A
as your guide.
2. Apply Percentage formatting tocolumns C, E, and G, if desired.
3. Enter your security names, theamount invested in each, and theirrespective returns and betas. Or,
use our data to follow along with
our example.
4. Enter =SUM(B2:B6) in cell B7.
Tip: Don’t forget that you can create SUM
formulas in a ﬂash with the AutoSum button, which you can ﬁnd on the Standard
toolbar. Just select a cell, click the AutoSum
button, ensure that it correctly selects the cells
you want to sum, and then press [Enter].

5. Type =B2/$B$7 in cell E2 to determine Stock A’s weight in the portfolio. Press [Enter].
6. Enter =D2*E2 in cell F2 to determine Stock A’s portfolio beta (thatstock’s beta as modiﬁed by its
portfolio weight). Press [Enter].
7. Enter =C2*E2 in cell G2 to determine Stock A’s portfolio return (itsreturn as modiﬁed by its portfolio
weight). Press [Enter].

T-bills to buy in order to reduce yourbeta while maximizing your return.
Note: To copy cells without formatting(versions 2002/2003), just click on the Smart
Tag that appears after you copy the cells andchoose Fill Without Formatting.

Utilize the Solver tool

8. Select cells E2:G2 and, using
the cells’ Fill handle, drag to cell
G6. The weights, portfolio betas
and returns should appear in
those cells.

The Solver tool belongs to Excel’s suiteof what-if analysis tools. Solver, unlike
Goal Seek, can ﬁnd an optimal spreadsheet value (the target cell), by adjusting several related formula cells while
subjecting the model to a number ofrestrictions or constraints. Let’s ﬁrst
take a closer look at the Solver Parameters dialog box.

Now you only need to enter the formulas to determine your total weights,
portfolio beta, and return.

Take a tour of the Solver
Parameters dialog box

To determine your total portfolio weights,
beta, and return:
1. Enter =SUM(E2:E6) in cell E7. The
total portfolio weight of 100%
appears.
2. Click and drag cell E7’s Fillhandle over to cell G7. The total
portfolio beta and return should
also appear.
Now you’re ready to use the Solver tool
to ﬁnd what quantities of Stock X andTo open the Solver tool (once the addin is installed), just select Tools | Solver
from the menu bar. The Solver Parameters dialog box appears, as shown in
Figure B.
The Solver Parameters dialog box is
divided into three main sections:• Set Target Cells. This cell is the target
or objective cell that you either wantto maximize, minimize, or set to a
speciﬁc value. You can specify thisby selecting one of the Max, Min,or Value Of option buttons, respec-

Related Articles
For a closer look at T-bills andinvestment betas, check out thesearticles:• Take the guesswork out of yourinvesting with the Regression
Analysis tool (November 2005)

A: Our portfolio spreadsheet takes into account both risk and reward.

• Make better “cents” of U.S.
Treasury bills using Excel’s T-bill
functions (October 2005)• Easily answer what-if questions
using Excel’s Goal Seek feature(October 2005)
These articles and more are available to EKJ Online subscribers at
www.elementkjournals.com/premier/journal.asp.

2

Inside Microsoft Excel

B: The Solver
Parameters dialog
box allows you greater
range and ﬂexibility inwhat-if scenario testing
than Goal Seek.

tively, in the Equal To area. The
target cell must contain a formula.
• By Changing Cells. These cells are theones you’ll be changing to reachyour target in the Target Cell. Use acomma to separate multiple changing cells.
• Subject To The Constraints. In thesecells you’ll set constraints on yourchanging cells.
After you’ve entered data in your target
cell and its goal, your changing cells,
and constraints, click the Solve button
to ﬁnd your optimal solution. Or, click
the Close button to close the Solver
tool without giving a solution.
For advanced options: Click the Options
button to access the Solver Options dialog box,
which allows you to set the max time, iterations,precision, tolerance, and/or convergence ofyour optimization modeling. You also havethe option to save or load saved optimization
scenarios, as well as set other conditions foradvanced modeling.

your current portfolio beta. You’d liketo reduce this to 1.0 so your portfolio
is no riskier than the overall market.
This will be one of your constraints.
Cell B7 contains your total investment
so far, $34,800: you only have another$15,200 left to invest. This meansthat the total investment ($50,000)
is another constraint. Your ﬁnal constraint is Stock X itself; you’d like tobuy at least some of this stock, so we’ll
set this as greater than or equal to one.
The cells that you’ll be changing,of course, are B5 and B6, the amountinvested in Stock X and T-bills. Allother cells, your previous investments,
should remain unchanged. Only
the cells you designate in the Solver
Parameter dialog box’s Set Target Cell
text box, By Changing Cells text box,
and Subject To The Constraints list
box will change.
To ﬁnd your optimal investment mix
using Solver:

Now that you’re familiar with theSolver Parameters dialog box, let’s
apply it to our investment conundrum.

Find your optimal portfolio
investment mix
Looking over your portfolio spreadsheet, you see that cell G7, 10.89%,
your total portfolio return, is the cell
you want to maximize. Cell F7, 1.13, is

1. Select cell G7, your total portfolio
return, to enter it in the Set Target
Cell text box.
2. Choose the Max option button if
it isn’t already selected.
3. Click in the By Changing Cells
text box to activate it, select cell
B5, enter a comma (,) and thenselect B6 to make the Stock X
and T-bill investments yourchanging cells.

4. Click in the Subject To The Constraints list box to activate it andclick the Add button to add yourﬁrst constraint. The Add Constraint dialog box opens, as shownin Figure C.
5. Refer to Table A to add threeconstraints, clicking the Add button after each one. When you’redone, click OK instead of Add toreturn to the Solver Parameters
dialog box.
Tip: You can click the Guess button in the
Solver Parameters dialog box if you’d like
Solver to automatically place the references of
all nonformula cells referred to in the formulain the Set Target Cell box, as well as theirreferences, in the By Changing Cells text box.

6. Upon clicking OK, the Solver
Parameters dialog box appears, as
shown in Figure D if you’re following along with our example.

Install the Solver add-in
If you haven’t already done so, youneed to install the Solver tool onto
your system via Excel’s Add-in utility.
To load the Solver tool:
1. Open a new workbook in Excel.
2. Choose Tools | Add-Ins fromthe menu bar.
3. In the Add-Ins dialog box, selectthe Solver Add-in check box in
the Add-Ins Available list box,
and then click OK.

Constraint relationship

C: Use the Add Constraint dialog box to add up to
100 constraints to your optimization model.

Table A: Solver tool constraints
Cell reference

Constraint relationship

Constraint

B5

>=

1

B7

<=

50000

F7

=

1.0

www.elementkjournals.com

D: Our optimization model has one target cell, two changing cells,
and three constraints.

December 2005

3

7. Click the Solve button to ﬁnd the
optimal investment mix solution.
The Solver Results dialog box
ﬁrst appears, as shown in Figure E.
Make sure the Keep Solver Solution option button is selected andclick OK.

The optimal solution of 10.52%
appears in G7. You can buy up to$7,741 in Stock X and $7,459 inT-bills and other risk-free assets
without spending more than $50,000
while maintaining a portfolio betaof 1.0.

E: Your results show up in a separate dialog box so you canAlternative uses for the
Solver tool

In addition to ﬁnding optimal
investment mixes, the Solver tool canhandle nearly any optimization modeling that has multiple variables orconstraints. For example, you might
determine a company’s ideal productmix, price, or place of production.
You may even need to plan a project.
In fact, the list of possible uses isnearly endless and includes any situation where you’re interested in ﬁnding
the most efﬁcient means of achieving
a given objective.

decide whether you want to keep Excel’s suggested solution.

Excel Enhancements

Four things Diff Doc can do that the Compare Side By Sidefeature can’t
by Kara Soos

AT A GLANCE
Excel has increased its capability for multi-workbook analysis with
Excel 2003’s Compare Side By Side feature, but there are several reasons
why it just isn’t enough to adequately compare two workbooks. Diff Doc is autility that takes document comparison to a whole new level, giving greater
ﬂexibility to Excel users who frequently compare worksheet data.
To illustrate four reasons why Diff Doc can be a good alternative to Excel’s
built-in comparison features, we’ll:• Discuss the limitations of having an older version of Excel when it comes to✎ Application:
Microsoft Excel 2000/2002/2003: Operating Systems:
Microsoft Windows— Keywords:
Excel, Compare, Side By Side, Utility, Diff Doc

8 Download:
http://download.elementkjournals.com/excel/200512/diffdoc.zip

document comparison.

• Illustrate Diff Doc’s reporting capabilities so you can analyze the differences
between documents.

• Point out other ﬁle types that you can compare with an Excel ﬁle to cut
down on needlessly converting documents.

• Demonstrate how Diff Doc can compare the ﬁles in a folder, instead of comparing the individual ﬁles themselves.

Most data analysis entails comparing one workbook with another. But
when you’re comparing several workbooks (or worksheets), it isn’t efﬁcientto switch back and forth between
them. And, the Compare Side By Sidefeature is new to Excel; if you use an

4

Inside Microsoft Excel

older version, it may not even be an
option. SoftInterface, Inc.’s Diff Doc
utility provides a ﬂexible interface
for comparing Excel documents that
may be a great alternative to Excel
2003’s built-in Compare Side By Sidefeature.

Reason #1: You don’t use
Excel 2003

In the December 2004 issue of Inside
Microsoft Excel, our article “Simplify
comparison tasks by synchronizing
worksheet windows” outlined thenew Compare Side By Side feature in
Excel 2003. However, if you haven’t
upgraded to 2003, you don’t have that
option for ﬂexible document comparison. Diff Doc breaks down the version
obstacle by accepting any ﬁle with an.xls ﬁlename extension.
However, Mac and Unix users areout of luck since Diff Doc only supports the Windows operating system.
Note: Even though Diff Doc is a free utility,
you’re encouraged to purchase it with a popup reminder. Click Done to dismiss it, but bewarned that it appears every 15 minutes until
you purchase the software.

Reason #2: It has reporting
capabilities

Diff Doc’s most important feature is its
ability to not only compare two workbooks, but also to quickly report andemail the differences. Let’s illustrate
how it all works.

Compare two workbooks
First, you need to download Diff Doc
from SoftInterface Inc.’s website. Then,a quick wizard takes you through the
installation.
To download and install Diff Doc:
1. Launch your web browser and go
to www.softinterface.com.

Home in on Diff Doc’s Excel settings for ultimate
customization
by Kara Soos

✎ Application: Microsoft Excel 2000/2002/2003: Operating System: Microsoft Windows— Keywords: Excel, Diff Doc, Compare, Settings, Preferences
In addition to the general settings for
Diff Doc, there are also settings speciﬁc to Excel workbooks. Diff Doc’s
ﬂexibility is primarily a result of these
preferences.

tion. You can change the following
settings:

To change the Excel workbook settings:
1. Click on the Compare Options
tab in the Diff Doc window.

• Which worksheet(s) you compare
in both the Original and the Modiﬁed workbooks.

2. Select the General tab, if
necessary.

• Whether you want to compare formulas in addition to static data.

3. Click the Excel button in the
Compare Options Speciﬁc To
File Type panel.

• How much padding, or empty
space, you want to appear between
worksheet columns.

In the resulting Excel Comparison
Options window, as shown in Figure
A, you get down to the essentials ofyour document comparison.
Most of the settings in the Excel
Comparison Options window are
self-explanatory, but it’s important toknow the possibilities for customiza-• Which rows and/or columns you
compare in both the Original andModiﬁed workbooks.

• Whether the worksheet nameappears above its data so you candistinguish where one worksheetends and the next begins.
• Passwords necessary to open theOriginal and/or Modiﬁed workbooks.

2. Click the Download button in the
Diff Doc section of the website.
3. Type your email address in theE-mail text box and click theContinue To Download button.
4. Click the Download button on
the next page and then click Run
in the File Download – Security
Warning dialog box if necessary.
5. Follow the instructions for the
Diff Doc Setup Wizard, selecting
the program’s location and folder
name as you desire.
Once you ﬁnish the wizard, you canlaunch Diff Doc from Windows’ Startmenu (or from the desktop if you choseto add an icon to it during installation). When you ﬁrst launch Diff Doc,www.elementkjournals.com

A: You can change the settings for comparing Excel workbooks to eliminate
unnecessary information and make the differences easier to read.

December 20055

the program’s interface displays, as
shown in Figure A.

To designate two workbooks forcomparison:

Note: You can follow along with our example
by downloading and extracting the sample
ﬁles from diffdoc.zip found at the URL given
at the beginning of this article.

1. Click the Original button on theleft side of the Diff Doc window,
navigate to the sample ﬁle January
2005.xls in the Load File dialog
box, and click Open.

2. Click the Modiﬁed button on theright side of the Diff Doc window,
navigate to the sample ﬁle February
2005.xls in the Load File dialog
box, and click Open.
3. Press [F5] to show the differences
between the two workbooks in thelower window, where the Quick
Start text originally displays.
4. Click Yes, and then click OK
twice, when Diff Doc prompts you
to turn off the Word Wrap feature.

A: When you ﬁrst launch Diff Doc, the Quick Start text appears in the lower half of thewindow to help you get started.

Your Diff Doc window should now
look similar to the one shown on the
top in Figure B. If you prefer to view
your differences side by side, as shownon the bottom in Figure B, just click theView Side By Side button. Currently,
Diff Doc’s defaults are all in place andthe results look confusing. To customize the display to suit your Excel needs,
check out the accompanying article,“Home in on Diff Doc’s Excel settings
for ultimate customization.”
Tip: We found that Excel data tends totruncate after a comma in a number. To avoid
this, just click on the Compare Options tab
and click the Excel button that appears on the
General tab. Then, deselect the Quick Excel
Data Extraction (MS Excel Is Used If Not
Checked) check box and click Apply.

To create a report of your documentchanges:
1. Click on the Report Options tabin the Diff Doc window.
2. Select the option button that corresponds to the format in which
you want the report. We’ll selectthe MS Word option button.
3. Press [F2] to view the report.
Microsoft Word launches andthe report displays, as shown in
Figure C.

B: You can view the document’s differences in one large window, or you can comparethem side by side.

6

Inside Microsoft Excel

The format in which you report thedifferences between your workbooks
and/or worksheets depends on how
you plan to use the report. Refer toTable A to determine which format isbest for your situation.

Reason #3: Compare withother ﬁle types

Diff Doc supports more than justExcel workbooks. You can also compare ﬁles from other applications,such as Word, PowerPoint, and WordPerfect, not to mention PDF and RTF
ﬁles. No more importing text ﬁlesfrom clients outside the company into
Excel workbooks just so you can do aside-by-side comparison of the data.
You can keep each ﬁle type intact toavoid accidental changes.
Remember when you’re comparing an Excel workbook to a ﬁle fromanother application that there areapplication-speciﬁc settings. You cancustomize the comparison by accessingthese settings through the Compare
Options tab.

Now you need to see which workbooks
she modiﬁed. Instead of opening each
workbook individually, you can use
Diff Doc to compare two folders.

Reason #4: Compare folders
instead of ﬁles

To compare all of the ﬁles in two
different folders:

In addition to comparing the data
and text in an Excel workbook, you
can use Diff Doc to look at the bigger
picture. For example, say you put threeworkbooks—January 2005.xls, February 2005.xls, and March 2005.xls—in
the Q1 List Acquisitions folder. A colleague reviews the workbooks in thefolder and saves it in her own folder.

Table A: Reporting formats and their best uses
Format typeBest to use when ...

MS Word

You want to print results; you need a versatile ﬁle format
that most users can open; you want to specify viewing only
deletions, only additions, or only changes

Text

You aren’t concerned with formatting; you want to import
and/or export to other programs with ease

Comma
Delimited

You want to create a report that can easily be converted to
Excel dataHTML

You want to view the report in a web browser; you want tosave, print, or email the report from the browser; you want
the option of viewing the report all at once or side by side

1. Click on the Compare Files tab, if
necessary.
2. Choose File | Compare Folders
from the menu bar to switch to theCompare Files window.
3. Click the Original button on theleft side of the window, navigate
to the Q1 List Acquisitions folder,
and then click OK.
4. Open the modiﬁed folder byclicking the Modiﬁed buttonon the right side of the window,
navigating to the Copy Of Q1

Related article
For more information on Excel’s
Compare Side By Side feature, checkout this article:• Simplify comparison tasks bysynchronizing worksheet windows(December 2004)
This article and more are available
to EKJ Online subscribers at www.elementkjournals.com/premier/journal.asp.

Change the wacky colors
and strikethroughs
If the colors and font formatting
are too busy for your tastes, you canchange how Diff Doc displays additions, deletions, and changes. Just
follow these quick steps:
1. Select the Compare Options tabin the Diff Doc window.
2. Click on the View tab and modify the settings in the Additions,
Deletions, and Changes panels.

C: You can get the results of your workbook comparison as a Word document, anapplication with which most users are comfortable.

www.elementkjournals.com

3. Select the Compare Files tab toreturn to the Diff Doc window
and press [F5] to refresh thewindow with your new settings.

December 2005

7

List Acquisitions folder, and thenclicking OK.
5. Press [F5] to compare the twofolders.

D: Diff Doc provides a legend at the bottom of the window so you know exactly what the colors
indicate.

As shown in Figure D, Diff Doc uses
color coding to show you whichdocuments in the folder have changed,
which documents only appear in theoriginal folder, and which ones only
appear in the modiﬁed folder. In addition, if a ﬁle has changed, you can see
which ﬁle (the original or the modiﬁed) is larger in size and which is more
recent.

The Excelerated Program

Automatically convert currency values into words—and savea ton of data entry timeby Kara Soos

AT A GLANCE
Business checks are no different than personal checks in thesense that you still need to display the amount in two forms: numerical andtext. Instead of typing out the English text that corresponds with your checkamounts, you can create a few user-deﬁned functions to do it for you.
To save time converting a currency amount into its English text equivalent, we’ll:• Review user-deﬁned functions and discuss how they differ from the macros
you know and love.

• Enter the VBA (Visual Basic for Applications) code necessary to create four

✎ Application:
Microsoft Excel 2000/2002/2003/2004: Operating Systems:
Microsoft Windows, Macintosh— Keywords:
Excel, VBA, Macros, Accounting, Select Case,
Currency

8 Download:
http://download.elementkjournals.com/excel/200512/currencywords.zip

user-deﬁned functions.

• Step through the important, behind-the-scenes actions that help the functions work together.

• Test the main function and discuss possibilities for customizing it.
Sometimes the numerical value of
your currency data isn’t enough; youalso need to store the value as English
text. For instance, if you’re preparing
data for checks, you need to display
the check’s amount in both numericalvalue and its text equivalent. Forgettyping all of that data manually. You
can use VBA to create your own userdeﬁned functions, which work together
to convert the numbers into words
automatically.

8

Inside Microsoft Excel

Tell me in plain English

User-deﬁned functions are a crucialpart of making Excel work for yourspeciﬁc needs. You can transform
any calculation that you ﬁnd yourself
performing repetitively into an Excel
function, as long as you know how to
express it in VBA.
In our example, we need to show
the client balances in our spreadsheet
in both numerical value and English
text, as shown in Figure A. This makes
it easier when we import the data to an

Access database or check-generating
software.
Note: You can follow along with our example
by downloading and extracting the sample
files from currencywords.zip, found at the
URL given at the beginning of this article.

Deﬁne your own functions

Familiar functions, such as the SUM
or the IF functions, are part of Excel.
You don’t necessarily need to know
how they work. However, if you’recreating your own functions, youneed to understand them on a deeper
level. We’ll take you through the fourfunctions described in Table A, which
work together to convert numbers intowords.
User-deﬁned functions differ fromthe procedures you’re used to seeing.
Macros often use procedures (thefamiliar Sub…End Sub structure), but
a function works a little differently.
A function doesn’t perform a series oftasks, but it takes a value or a cell reference and passes it through a calculation. When you create a user-deﬁnedfunction, you’re essentially recording
the steps of the calculation. Then, you
can use the function just as you would
any other built-in Excel function.
You’ll see how it all comes together as
we step through this article.

Start with the main
SpellCurrency() function

Instead of boring you with a play-byplay commentary on each function,
we’ll summarize each one and highlight the important points you may
want to note.

Look closer at the SpellCurrency()
function
The SpellCurrency() function brings
it all together. It’s the skeleton for theother three functions, as they focus on
more speciﬁc tasks. This is the function
you’ll need to type into a cell to actually
perform the function, whereas the otherthree functions work behind the scenes.
Mostly, the function uses String functions (Left, Right, Mid, Len, and Trim) to
ﬁnd the decimal place in the value andassign it text based on its position. We’ll
cover String functions in more detaillater in the article. Here are two otheraspects of the SpellCurrency() functionthat are important to note:• The variables deﬁned in the beginning of the function include a Redim
statement. You use Redim statements
to deﬁne a ﬂexible array. In our
example, the Redim statement helps
us determine whether the value isin the trillions, billions, millions, or
thousands depending on the place-ment of the decimal point. You canalso add Preserve to a Redim statement, which means that only the last
dimension of the array can change.
Since our array only has one dimension, it isn’t necessary.
• The ByVal expression designatesparameters that pass a value so thatthe variable takes on this value temporarily without permanently altering the original variable. It’s as if acopy of the variable passes throughthe procedure as a value, but it
doesn’t affect the variable itself. In
this case, the ByVal expression is justtemporary information that passes
through the function without actually altering it.

Convert the hundreds place

The ConvertHundreds() function works
by utilizing String functions alongwith an If...Then...Else statement. The
same ByVal value is passed through thefunction, which is the temporary text
string representing the currency value.

To create the main SpellCurrency()
function:
1. Launch Excel and open the ﬁlein which you want to use thefunction. If you’re following our
example, open the sample ﬁlecheckdata.xls.
2. Choose Tools | Macro | Visual
Basic Editor to open the VBE(Visual Basic Editor). Or, in Windows, you can press [Alt][F11].
3. Select Insert | Module from the
VBE’s menu bar to open a newmodule.

A: You don’t need to manually type this cumbersome text when you deﬁne functions to create it
automatically.

Table A: Four user-deﬁned functions and their tasks
Function name

Listing

PurposeSpellCurrency()A

Main function; brings together the other threefunctions; adds the “dollars” and “cents” text.

ConvertHundreds()

B

Finds the hundreds place, if applicable, andadds the “hundred” text to it; works with theConvertTens() and ConvertDigit() functions.

ConvertTens()

C

Replaces the tens place with text to represent
either the numbers 1-19 or 10-99.

ConvertDigit()

D

Replaces single digits with the text for numbers
1-9, if applicable.

4. Enter the code shown in Listing A.
Note: The code for each of the listings in thisarticle is part of the download ﬁle. Just open
the sample ﬁle vba.txt and then copy and paste
it into the VBE. This should save you from reentering the data and possibly making typos.

www.elementkjournals.com

December 2005

9

You can include the code for theConvertHundreds() function by leaving

a blank line in the VBE’s code window,
and then entering the code in Listing
B. Or, if you’re using the TXT ﬁle we
provide, you can simply copy and paste
it into the code window.

There are two If...Then...Elsestatements in the ConvertHundreds()
function. The ﬁrst one has only one
condition, and therefore it omits theoptional Else argument. If the digitin the hundreds place isn’t zero, thenthe ConvertDigit() function is called toreplace the digit with its text equivalent,
and then add the text hundred to it.
The second If...Then...Else statement has two conditions. If the digit
to the right of the hundreds place (thetens place) isn’t zero, then the ConvertTens() function is called to generate the

Listing A: The SpellCurrency() function
Option Explicit
Function SpellCurrency(ByVal MyText)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = “ Thousand “
Place(3) = “ Million “
Place(4) = “ Billion “
Place(5) = “ Trillion “
MyText = Trim(Str(MyText))
DecimalPlace = InStr(MyText, “.”)If DecimalPlace > 0 Then
Cents = ConvertTens(Left(Mid(MyText, _DecimalPlace + 1) & “00”, 2))
MyText = Trim(Left(MyText, DecimalPlace - 1))
End IfCount = 1
Do While MyText <> “”Temp = ConvertHundreds(Right(MyText, 3))
If Temp <> “” Then

Dollars = Temp & Place(Count) & Dollars
If Len(MyText) > 3 Then
MyText = Left(MyText, Len(MyText) - 3)
Else
MyText = “”End IfCount = Count + 1
Loop
Select Case Dollars
Case “”: Dollars = “No Dollars”
Case “One”: Dollars = “One Dollar”
Case Else: Dollars = Dollars & “ Dollars”
End SelectSelect Case Cents
Case “”: Cents = “ and No Cents”
Case “One”: Cents = “ and One Cent”
Case Else: Cents = “ and “ & Cents & “ Cents”
End SelectSpellCurrency = Dollars & Cents
End Function

Related articles

Listing B: The ConvertHundreds() function• Avoid major business errors:
Double-check totals with a UDF(February 2002)

Function ConvertHundreds(ByVal MyText)
Dim Result As String• Simplify worksheet formulaswith user-deﬁned functions(June 2001)
These articles and more are available to EKJ Online subscribers at
www.elementkjournals.com/premier/journal.asp.

If Val(MyText) = 0 Then Exit Function
MyText = Right(“000” & MyText, 3)
If Mid(MyText, 1, 1) <> “0” Then
Result = ConvertDigit(Mid _(MyText, 1, 1)) & “ Hundred “
End IfIf Mid(MyText, 2, 1) <> “0” Then
Result = Result & ConvertTens(Mid(MyText, 2))
Else
Result = Result & ConvertDigit(Mid(MyText, 3))
End IfConvertHundreds = Result
End Function

10

Inside Microsoft Excel

appropriate text. Otherwise, the ConvertDigit() function is called.
As these functions begin to cometogether, you can see how they intertwine and feed off one another to work
toward the goal of translating digits
into words. A vital part of this task isaccomplished through String functions, which we’ll go over next.

Identify decimal placementwith String functions
String functions are really the backbone of these four functions. By using
the Left, Right, Mid, Len, and Trim functions, our four functions can identify
where digits are in relation to thedecimal point, and therefore assign
the correct text to reﬂect the numerical value. For a quick review of theseString functions and how they work,
check out Table B.

Use Select Case to
substitute words

The ﬁrst two functions rely on theseﬁnal functions: ConvertTens() andConvertDigit() . You can enter the codefor these functions in the VBE code
window by referring to Listings C and
D. Or, you can copy and paste the codefrom our sample TXT ﬁle.

These two functions are composedof mainly a Select Case statement.
These statements set up several conditions, each one with a corresponding
result. In our case, each numericalvalue is assigned a text string. The
ConvertDigit() function is the simplest
example, since it merely converts the
single digits (1-9) into their English
text equivalent. The ConvertTens()
function is a bit more complicated.
It uses Select Case in addition to an
If...Then...Else statement. It converts
the numbers 10 through 19 into text
if the value of the tens place is one.
Otherwise, it converts the values 2-9

into their tens place equivalents, 20
through 90.

Select Case vs. If…Then…
Else statements
You’ve probably noticed that these twostatements accomplish very similartasks—and it’s true. Both If...Then...
Else statements and Select Case statements set up conditions and what
result to return based on those conditions. However, Select Case is more
efﬁcient when:• the same variable or expression
appears on the left side

Table B: String functions and their uses
Function

SyntaxUse it when you need to ...

Left

Left(string, number)

Find the characters to the left of aparticular placement in the text string

Right

Right(string, number)

Find the characters to the right of aparticular placement in the text stringMid

Mid(string, start, length)

Find the characters in the middle of atext string, starting from a particularplacement and ending after a designatednumber of characters

Len

Len(string)

Return the number of characters in a text
string (or cell reference)

Trim

Trim(string)

Eliminate any extra leading spaces froma text string (or cell reference)

Listing C: The ConvertTens() function
Function ConvertTens(TensText)
Dim Result As String
Result = “”If Val(Left(TensText, 1)) = 1 Then
Select Case Val(TensText)
Case 10: Result = “Ten”
Case 11: Result = “Eleven”
Case 12: Result = “Twelve”
Case 13: Result = “Thirteen”
Case 14: Result = “Fourteen”
Case 15: Result = “Fifteen”
Case 16: Result = “Sixteen”
Case 17: Result = Seventeen”
Case 18: Result = “Eighteen”
Case 19: Result = “Nineteen”
Case ElseEnd Selectwww.elementkjournals.com

Else
Select Case Val(Left(TensText, 1))Case 2: Result = “Twenty “Case 3: Result = “Thirty “Case 4: Result = “Forty “Case 5: Result = “Fifty “Case 6: Result = “Sixty “Case 7: Result = “Seventy “Case 8: Result = “Eighty “Case 9: Result = “Ninety “Case ElseEnd SelectResult = Result & ConvertDigit _(Right(TensText, 1))
End IfConvertTens = Result
End Function

December 200511• the conditions aren’t too complicated(no And or If operators) and2. Select cell D2 and enter=SpellCurrency(C2).

• they don’t necessitate a speciﬁc order.

3. Press [Enter] to view the results.

In general, when you have more than
three conditions to deﬁne, an If...
Then...Else statement gets cumbersomeand difﬁcult to read.

Now you can use this user-deﬁnedfunction as you would any other. Copy
it to adjacent cells by dragging the Fillhandle. Or, instead of a cell reference,
enter a numerical value, such as=SpellCurrency(“$523.21”).

To test the SpellCurrency() function:
1. Close the VBE and return to yourworkbook.

Listing D: The ConvertDigit() function
Function ConvertDigit(Digit)
Select Case Val(Digit)
Case 1: ConvertDigit = “One”
Case 2: ConvertDigit = “Two”
Case 3: ConvertDigit = “Three”
Case 4: ConvertDigit = “Four”
Case 5: ConvertDigit = “Five”
Case 6: ConvertDigit = “Six”
Case 7: ConvertDigit = “Seven”
Case 8: ConvertDigit = “Eight”
Case 9: ConvertDigit = “Nine”
Case Else: ConvertDigit = “”End SelectEnd Function

Tweak the code to matchyour needs

Although these functions don’t leave
much room for adjustment, you canalways modify the text to suit different
types of currency. For example, if yourcompany deals with British clients andyou send checks in their native currency, you can replace all “dollar” and“dollars” text in the code with “pound”
and “pounds,” and then replace the“cent” and “cents” text with “pence.”
Then, the translated text can matchthe symbols that may result fromworking with foreign currency. If you
do make these adjustments, you may
want to change the name of the function to reﬂect which type of currency
it translates. In theory, you could replicate these four functions in different
currencies, and then you’d have userdeﬁned functions to reﬂect each one.
Another way you can use thesefunctions is to translate the currency
into a different language, if you sodesire. All you’d need to do is replace
the English text with the new language, such as Spanish or French. Just
be careful of different language conventions that may change the values’translation.

6 QUICK TIPS
Let Excel format your PivotTable so you can concentrate on the data
(2000/2002/2003/2004)— Keywords: Excel, PivotTable, AutoFormat, Tips
PivotTables have a job to do; they
don’t always have to look pretty. Aslong as they’re helping you get to thebottom of your data, that’s all you
need. However, there are situations
when you want your PivotTable tolook presentable and appealing,such as when you need to show yoursupervisor the latest budget ﬁgures.
Instead of spending valuable
time trying to pretty-up your

12

Inside Microsoft Excel

PivotTable, let Excel do the workfor you.

To AutoFormat your PivotTable:
1. Launch Excel and open the ﬁlein which you have a PivotTable
that you’d like to format.
2. Select the worksheet that
contains your PivotTable andclick anywhere inside the tableto activate it.

3. Choose View | Toolbars |PivotTable to display the
PivotTable toolbar, if necessary.
4. Click the Format Report buttonon the PivotTable toolbar toopen the AutoFormat window.
5. Select a format and click OK to
apply it.
Note that you can choose to format
the PivotTable in the form of a reportor a table.

Authenticate your macros so colleagues know the workbook
is safe to open
by T. L. Thomas and Kara Soos

AT A GLANCE
Excel workbooks with macros are often an overlooked entry for viruses, especially when you don’t know who created the macro. You can verify
that macros come from trusted sources by assigning a digital signature to yourmacro projects.
To enforce a proactive security method by digitally signing our macros, we’ll:• Specify a security level to ﬁlter safe and unsafe macros.
• Obtain and install a digital certiﬁcate from a commercial certiﬁcation✎ Applications:
Microsoft Excel 2000/2002/2003, Microsoft
Internet Explorer 5.0 or later: Operating System:
Microsoft Windows— Keywords:
Excel, Macros, Security, Sign, Certificates,
Signaturesauthority.

• Create your own digital certiﬁcate for personal use.
• Add a digital signature to a macro project.
Hackers continually challenge themselves to come up with new ways tobreak through company ﬁrewalls
and wreak havoc on innocent users’
systems. Many companies employ
developers dedicated to creating macro
solutions for company-wide use. However, the more widely distributed amacro solution is and the bigger thecompany is that employs it, the lower
the likelihood is that you know who
created the macro. You can protect
your macros and the people who usethem by signing them with a digital

signature based on a digital certiﬁcate,as shown in Figure A.

Set your macro security level

There are three macro virus protection
security levels in Excel.
• Very High (2003 only). The Very Highsecurity setting only allows macros
installed in a trusted location, whether
they’re signed or unsigned. You must
manually include the source in a list oftrusted publishers, which we’ll show
you later in the article.

• High. The High security setting
protects against any macro that isn’t
digitally signed by automatically disabling any unsigned macros.
• Medium. The Medium security setting prompts you to choose whether
to run macros by presenting the
Security Warning dialog box.
• Low. The Low security setting turns
off macro virus protection altogether.
To set your macros security level:
1. Launch Excel and open the workbook that contains macros you
wish to certify.
2. Choose Tools | Macro | Security
from the menu bar to access the
Security window, as shown in
Figure B.

B: Microsoft recommends
A: You can verify that the macros in your distributed
workbook are safe by assigning them your digital
certiﬁcate.

www.elementkjournals.com

that you set your macros security setting toHigh, which disables all
unsigned macros.

December 2005

13

3. Select the Security Level tab, if
necessary, and then select theoption button corresponding to
the security level you desire.
4. Click OK to apply the new security setting.

Authenticode technology
You’ve probably seen the familiar
dialog box that appears when you
open a workbook with macros.
You may click whatever it takes todismiss the window, without stopping to think about what it means
for a workbook to contain unsigned
macros.
Microsoft Ofﬁce’s enhanced
macro security protection utilizes
Authenticode technology to verify
a macro’s source before you run it.
Authenticode technology refers to
the macro’s digital certiﬁcate; itmakes sure that the program code
was signed by a veriﬁed source andwasn’t tampered with since its signing. Authenticode technology is also
used on the internet to conﬁrm thesafety of downloadable software.

Obtain a digital certiﬁcate

There are two ways to obtain a digital certiﬁcate: create one yourself orrequest one from a Certiﬁcation
Authority (CA). When you assign aself-signed certiﬁcate to a macro project, users continue to receive a security
warning that prompts them to enable
or disable macros until they specify
that you’re a reliable source. However,when you assign an authenticated certiﬁcate to a macro project, the CA—areliable source in its own right—veriﬁes you as a reliable source to users.

Request an authenticatedcertiﬁcate
You can request an authenticateddigital certiﬁcate from a Microsoftapproved CA. That way, you can sign
any of your macro projects with it toreassure users that it’s genuine and safe.
To obtain a list of Microsoft-approved
CAs, check out http://msdn.microsoft.com/library/?url=/library/en-us/dnsecure/html/rootcertprog.asp.

Create your own digital certiﬁcate
You can create a digital certiﬁcate with
Microsoft Ofﬁce’s SelfCert tool. However, the SelfCert tool creates a selfsigned digital certiﬁcate that doesn’t
verify your identity. You should only
employ it for personal use because,
hypothetically, anyone can unsign amacro project and add his own digital
signature unless you request a digital
certiﬁcate from a CA.
To create a digital signature in your name:
1. Search for the selfcert.exe ﬁle on
your hard drive, using your operating system’s Search feature anddouble-click on the ﬁle to run it. If
you don’t have this ﬁle anywhereon your hard drive, see the “Install
the SelfCert tool” shaded box.
2. Type your name in the Your Nametext box when the Create Digital
Certiﬁcate dialog box appears, as
shown in Figure C.

Commercial certiﬁcation vs.
individual certiﬁcation
You can request a digital certiﬁcate,also known as an SPC (Software
Publishing Certiﬁcate) for commercial or individual purposes. For
both kinds of requests, you need tosupply personal information so theCertiﬁcation Authority can verify
your identity and credentials. You
also need to take a pledge statingthat you won’t knowingly distribute
software infected with viruses. The
difference between the two certiﬁcations is that commercial requests
also need to meet a speciﬁc Dun &Bradstreet rating, which veriﬁes thecorporation’s ﬁnancial standing andstability. This merely helps the CA
clearly distinguish between individual and commercial certiﬁcates.

C: You can create yourown digital certiﬁcate,but it doesn’t verify youridentity and credentials
the way a Certiﬁcation
Authority does.

D: You can view further
details about a certiﬁcateby selecting it and clicking
the View Certiﬁcate button.

14

Inside Microsoft Excel

3. Click OK to create the digital certiﬁcate, and then click OK again
when Windows veriﬁes that theoperation is successful.

Assign your digital signature
to a macro

Now that you’ve created a digital
certiﬁcate, whether self-created orrequested, you can use it to make yourmark on any macro project.
To digitally sign a macro and viewthe new certiﬁcate:
1. Switch back to your Excel
workbook.
2. Press [Alt][F11] to access the VBE(Visual Basic Editor) or you canchoose Tools | Macro | Visual
Basic Editor from the menu bar.
3. Select the project you want to signin the Project Explorer.
4. Choose Tools | Digital Signaturefrom the VBE’s menu bar to open
the Digital Signature dialog box.
5. Click Choose to access the SelectCertiﬁcate dialog box, and thenselect your certiﬁcate from the list
box, as shown in Figure D.
6. Take a look at the certiﬁcate byclicking the View Certiﬁcate
button.
7. Dismiss any open dialog boxes andreturn to your Excel workbook.

Make sure your certiﬁcate is among
trusted publishers
If you view the certiﬁcate for yournewly assigned digital signature, it
may indicate that you need to store
the certiﬁcate in your Trusted Root
Certiﬁcation Authority Store. And
you’re probably thinking, “What isthat all about?” You can access the
publishers that your system automatically trusts, and then include yourpersonal certiﬁcate among them tocomplete the certiﬁcate, as shown in
Figure A.

Install the SelfCert tool
If the selfcert.exe ﬁle isn’t present,
you may need to install it. To do so,
follow these steps:
1. Access Windows Control Paneland double-click on Add Or
Remove Programs (or Add/
Remove Programs dependingon your operating system).
2. Select Microsoft Ofﬁce (orMicrosoft Excel if you use it asa standalone program) from theCurrently Installed Programs
list box.
3. Click the Change button to run
the Microsoft Ofﬁce setup.
4. Choose Add Or Remove Features option button in the
Maintenance Mode Options
window and click Next.
5. Select the Choose Advanced
Customization Of Applications
check box and click Next.

™

®

Inside Microsoft Excel (ISSN 1075-1580) is published monthly by Eli Journals, Inc., asubsidiary of Eli Research, 2222 Sedwick Road, Durham, NC 27713.

Customer Relations & Subscription Services
New Hill Services
Dept. 1380
Denver, CO 80291-1380
Email: service@medville.com
U.S. toll-free . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . (800) 508-2582
Outside U.S. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . (239)-280-2383
Fax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . (800) 508-2592

Editorial
Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Kara Soos
Email: ime_editor@elementk.com
Toll-free: (866) 614-8715
Copy Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Teena Artman
Contributing Editors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Frank Kuhn
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . T. L. Thomas
Graphic Designers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Rachel Allen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Pam Nichols
Creative Director . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Chuck Willey
Managing Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Craig Watkins
Editorial Director . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Erin Lang Bonin, PhD

Publishing & Sales
Publisher . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Sherry Kappel(888) 779-3718, ext. 348
Bulk Sales. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Pam Snyder(877) 853-9938
Customer Service Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Holly Smith

PostmasterPostmaster: Send address changes to
Inside Microsoft Excel

6. Drill down to the Microsoft
Ofﬁce | Ofﬁce Shared Features
directory and click on the Digital Signature For VBA Projects
icon. (In some operating systems, you may need to look in
the Microsoft Ofﬁce | Ofﬁce
Tools directory.)
7. Select Run From My Computer from the resulting shortcut menu, and then click theUpdate button.
You may be prompted to insert yourMicrosoft Ofﬁce or Microsoft Excel
installation disc.

P.O. Box 92880
Rochester, NY 14692

Price
Domestic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . $127/yr.
Outside the U.S. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . $137/yr.
GST #: 889037958. PM #: 40031405.

Back Issues
To order a back issue, call Customer Service at (800) 874-9180. You can pay with MasterCard, VISA, Discover, or American Express.

Copyright© 2005 Eli Journals. This work is an independently produced publication of Eli Research,
the Content of which is the property of Eli Research or its afﬁliates or third-party licensors
and which is protected by copyright law in the U.S. and elsewhere. The right to copy andpublish the Content is reserved, even for Content made available for free such as sample
articles, tips, and graphics, none of which may be copied in whole or in part or further
distributed in any form or medium without the express written permission of Eli Research.
Requests for permission to copy or republish any Content may be directed to John Hall at(800) 508-2582, ext. 2325.
Inside Microsoft Excel is a trademark of Eli Journals. All product names or services identiﬁed throughout this journal are trademarks or registered trademarks of their respective
companies.
Printed in the U.S.A.

To export your personal certiﬁcate
to the Desktop:
1. Launch Internet Explorer and thenchoose Tools | Internet Options
from the menu bar.
2. Select the Content tab and click
the Certiﬁcates button in the Certiﬁcates panel.
3. Choose the Personal tab, if necessary, and select your certiﬁcate
from the list box.

www.elementkjournals.com

InsideAre you moving?
If you’ve moved recently or you’re planning to move, you can guarantee uninterrupted
service on your subscription by calling us at (800) 874-9180 and giving us your new
address. Or you can fax us your label with the appropriate changes at (585) 789-3560. Our
Customer Relations department is also available at http://www.eliresearch.com/contact.htm.
To see a list of our products, visit our websites at www.elementkjournals.com andwww.eliresearch.com.

Find Answers Fast Online!

Mastering Excel is just a click away with EKJ Online. You’ll
get unlimited searchable access to hundreds of articles
and tips from Inside Microsoft Excel—a rich, just-in-time
resource to enhance your journal subscription. Take a
FREE online guided tour at www.elementkjournals.com/ekjonline. Or call us toll-free at 1-800-508-2582 to upgrade
your current subscription today!

COMING UP…

2086• Use a pop-up calendar to control
date entry• Streamline worksheet formulaswith arrays

Please include account number from label with any correspondence.

4. Click the Export button to launch
the Certiﬁcate Export Wizard(Certiﬁcate Manager Export Wizard depending on your operating
system) and click Next to advancethrough the wizard.
5. Ensure that the No, Do Not
Export The Private Key option
button is selected as you go
through the wizard.
6. Type a meaningful name for yourcertiﬁcate in the File To Export
screen’s text box, and then click
Next and Finish.
7. Click OK to dismiss the conﬁrmation of a successful export.
Once you export the certiﬁcate, itappears on your Desktop. Now, youneed to import it to your Trusted Root
Certiﬁcation Authorities Store.
To import the certiﬁcate to designate
it as trusted:
1. Click the Import button in theCertiﬁcates dialog box to accessthe Certiﬁcate Import Wizard.
(The same certiﬁcate should still
be selected.)

2. Read through the wizard’s introduction and click Next.
3. Click the Browse button and navigate to the certiﬁcate ﬁle you justsaved on the Desktop, select it,
and click Open.
4. Advance in the wizard by clicking
Next and ensure that the PlaceAll Certiﬁcates In The Following
Store option button is selected.
5. Click Browse, select the Trusted
Root Certiﬁcation Authorities
folder, and click OK to dismiss the
Select Certiﬁcate Store dialog box.
6. Click Next and Finish to complete
the import. Click Yes or OK if
you’re prompted to conﬁrm theimport.
When you view the certiﬁcate
through the Select Certiﬁcate dialog
box, it should now list the certiﬁcate’s
intentions, as shown in Figure A. Also,
any workbook containing macros
signed with your signature won’t
trigger a security warning when you
open it.

Inside Microsoft Excel 2005 index

Know the limitations of adigital signature

Digital signatures, particularly ones
assigned by CAs, are important foranyone who wants to distribute macrosin their Excel workbooks. However,there are important limitations thatyou should understand.
• Whenever you modify the code in asigned macro project, the digital signature is removed. However, as longas you’ve installed the source’s digital
certiﬁcate on your system, the macro is
re-signed each time you save it. Be sure
to test your macro before signing it.
• To prevent users from modifying
your code and, as a result, invalidating your signature, it’s a good idea
to lock your macro before you sign
it. This way, users can still modify
a workbook that implements themacro, but they can’t modify themacro itself.
• Other developers can replace yoursignature with their own at any time.
A digital signature is no more than anhonorable guarantee that you approved
the code. However, a signature with acertiﬁcate that’s approved by a CA carries more weight and validity.

Can’t ﬁnd the article you need? Look no further. The complete 2005 index for Inside Microsoft Excel is now available
online! Use the searchable online index to ﬁnd any article, any time at www.elementkjournals.com/index_ime.asp. The online
index is arranged by month and includes a list of article titles for all issues published in Inside Microsoft Excel during 2005.
The 2005 index is a great problem-solving resource when you need answers—fast! Plus, you can locate articles of interest
in issues missing from your collection. To order back issues, call Customer Relations at (800) 874-9180. Or, to gain online
access to articles prior to 2005, please visit www.elementkjournals.com/premier/premierInfo.asp for more information.

16

Inside Microsoft Excel