ñòð. 59 |

of your own choosing instead of Arg1.

Now go to the worksheet part, select a cell anywhere, and

write. Within the parentheses in the worksheet, enter the number

you want to convert or the cell address holding the number to

convert:

=ConvertFToC(68)

or

=ConvertFtoC(B1)

where the cell B1 holds the number 68.

When you do this, the cell returns the value of 20. Yes, 68

degrees Farenheit has just been converted to 20 degrees Celsius.

You write the function in the worksheet in exactly the same way

it is defined in the function macro. VBA makes the automatic

connection between the value in the parentheses in the worksheet

function and the function macroâ€™s argument Arg1 and makes the

function work.

TLFeBOOK

Chapter 19

326

You can create a function macro that reads more inputs.

Here is one that multiplies the three inputs:

Function Mult3 (Arg1,Arg2,Arg3)

Mult3=Arg1*Agr2*Arg3

End Function

When you write the formula

=Mult3(B1,B2,B3)

it will return the results of multiplying the contents of the three

cells on the worksheet.

OTHER IMPORTANT TOPICS TO COVER

Here is a brief road map of the subjects to cover as you begin

your own exploration of VBA. I leave you with these:

Variables

u

u Declaring them using the Dim statement for the

appropriate data type.

u Declaring them at the local, module, or projectwide

level

Ways to control the execution of the code

u

u If-Then, the VBA equivalent of the worksheet IF

function.

u Ways to repeat a block of instructions, e.g., the For-

Next loop, and the Exit For statement to stop the loop

when a condition is met.

u Error trapping

How to make procedures work with each other

u

User forms: How to create your own dialog boxes for data

u

input and display.

Custom toolbars and custom menus: How to create your

u

own toolbar for access to the features that you have in

your model.

TLFeBOOK

Writing a Macro in Visual Basic for Applications 327

VBA AND BEYOND

I hope I have given you a useful tour of modeling and Excelâ€™s

features in support of making user-friendly models. Knowing

VBA adds to your ability to create even more powerful and

functional models, and there are tremendous resources available

now in bookstores and online for more advanced information

about this programming language. This book covers only the

beginning steps for learning VBA, and I invite you to turn to

these other sources for continuing your learning.

TLFeBOOK

This page intentionally left blank.

TLFeBOOK

INDEX

> >, 165 Assets:

>

$ (dollar sign), 49â€“50 in accounting equation, 112â€“113

Â¼ (equal sign), 64 on balance sheet, 149â€“150

#DIV/0! error, 97 in balance-sheet-only cash sweep,

#NAME? error, 98 244

#REF! error, 98 in cash flow variation cash sweep, 262

Â¼TEXT function, 103 core, 219

#VALUE! error, 98 fixed, 150

net fixed, 150

other current, 150

A other long-term, 151

ABS function, 101 static totals for, 240, 244

Accounting, 109â€“110 Audit toolbar, 24â€“26

Accounting credits, 200 Autofilter, 311, 313

Accounting debits, 200 Available cash flow:

Accounting equation, 112â€“113 applying, to cash sweep, 265â€“266

Accounting format, 306 finding, 264â€“265

Accounts payable, 151â€“152 AVERAGE function, 83â€“84, 252â€“254

forecasting, 233â€“235

formulas for, 181â€“182

B

Accounts payable/cost of goods

sold * 365 (ratio), 215â€“216 Backups, 20â€“21

Accounts receivable, 149, 219 Balance sheet, 111, 119â€“143, 147â€“153

forecasting, 233 assets on, 149â€“150

formulas for, 176â€“177 attaching income statement to, 250â€“256

Accounts receivable/sales * 365 (ratio), balance sheet method of balancing, 122,

214â€“215 124â€“136

Accreting debt, 222 basic form of, 148â€“149

Accrued expenses, 152 cash flow statement method of

Accumulated depreciation, 150 balancing, 122â€“124

Adding, functions for, 77â€“81 common-size, 223, 224, 226

Additional paid-in capital (APIC), current vs. noncurrent items on, 148

152 excess cash on, 121

Alignment settings, 30â€“32 financial model, inputs for,

Amortization of intangibles, formula for, 162â€“165

169, 170 and income statement, 118, 136â€“143

Anchoring key, 56 liabilities on, 151â€“153

AND function, 76â€“77 NTF on, 121â€“122

APIC (additional paid-in capital), 153 output sheet for, 196â€“197

Apostrophe (for comments), 285 use of â€˜â€˜plugâ€™â€™ numbers in, 119â€“121

Arguments (in functions), 64â€“65 Balance sheet accounts:

Asset intensity ratio, 217 forecasting, 232â€“235

Asset turnover ratio, 218 reconciliation tables for, 202â€“203

329

Copyright Â© 2004 by John S. Tjia. Click here for terms of use.

TLFeBOOK

Index

330

Balance sheet method, 122, 124â€“136 Cash sweep (balance sheet only variation),

improved variation of, 132â€“136 237â€“256

static and dynamic numbers in, attaching income statement to, 250â€“256

130â€“132 for building balance sheet, 243â€“250

summary of, 125 connecting NTF to balance sheet, 250

Bank debt, cash sweep for, 238 connecting remaining surplus funds to

Bank debt/EBITDA (ratio), 221 balance sheet, 249

Banks, projection models for, 3â€“4 and difference between (total liabilities Ã¾

Basis points, 230 equity) and total assets, 246

Benchmarks, ratio, 210 error-trapping formula, adding,

â€˜â€˜Black boxâ€™â€™ models, 17 255â€“256

Bold type, 15, 29 function of, 237â€“238

Bookkeeping, double-entry, 113â€“115 interest, calculating, 252â€“254

Boolean logic, 68â€“69 layout for, 238â€“239

Borders, cell, 33 and linking income statement to

retained earnings, 254â€“255

and NTF plug, 247

C and post-sweep debt numbers, 250

Calculation settings, 40â€“42 static total for assets side, finding, 244

Calculations: static total for equity, finding, 246

following flow of, 16â€“17 static total for liabilities, finding,

forced, 57 244â€“246

visibility of, 17 structuring, 239â€“243

Capital, working, 213 and surplus funds available for sweep,

Capital expenditures (capex), 8, 199 247â€“248

Caption property: surplus funds for payment of debt

for check boxes, 289 tranches, applying, 248â€“249

for command buttons, 290 Cash sweep (cash flow variation), 257â€“269

for option buttons, 291 assets side, laying out, 262

for toggle buttons, 294 available cash flow, finding, 264â€“265

Cash, 121, 150, 174â€“176 available cash flow to sweep, applying,

Cash accounts, forecasting, 232â€“233 265â€“266

Cash equivalents, 220 cumulative NTF, calculating,

Cash fixed charge coverage, 223 267â€“268

Cash flow statement, 111 cumulative plug lines to balance sheet,

flows in, 200â€“201 connecting, 268

output sheet for, 199â€“207 cumulative surplus funds, calculating,

purpose of, 199 266â€“267

reconciliations in, 202â€“207 debt amounts after sweep and reference

Cash flow statement method to balance sheet, calculating,

(of balancing balance sheet), 268â€“269

122â€“124 error-trapping formula, adding, 269

Cash flows, 4â€“5 laying out income statement and

available, 264â€“266 calculating net income, 263â€“264

in cash sweep calculations, 259 layout for, 259â€“261

from financing, 201 liabilities and equity, laying out,

from investments, 201 262â€“263

from operations, 201 structuring, 258â€“259

sources of, 200 CEILING function, 101

uses of, 200 â€˜â€˜Cell Value Isâ€™â€™ formatting, 308â€“309

TLFeBOOK

Index 331

Cells: scroll bars, 295

addresses of, 49 spin buttons, 295

borders of, 33 text boxes, 290

with conditional formats, finding, toggle buttons, 294â€“295

Convergence, 141

309â€“310

Copying:

hiding, 34, 35

code for, 284

locking, 34, 35

of financial statement sheets, 224

patterns, cell, 34

Core assets, 219

shading of, 37

Cost of goods sold (COGS), formulas for,

Check boxes, 289â€“290

Chief financial officer (CFO), 2 167â€“168

CHOOSE function, 74, 76 Cost of goods sold/accounts payable

Circular references, 43, 99â€“100 (ratio), 216

Code, macro: Cost of goods sold/inventory (ratio),

definition of, 315 215

editing, 279â€“285 COUNT function, 81â€“82

reading, 279â€“282 COUNTA function, 82

writing, 322â€“323 COUNTIF function, 82â€“83

Code Editor window, 317 Counting, functions for, 81â€“84

COGS (see Cost of goods sold) Coverage, ratios for, 221â€“223

Color: Create Chart key, 58

in number formatting, 307 Credit analysis, 4â€“5

settings for, 37â€“40 Credits, 115â€“118, 200

Column-width setting, 36 Currency format, 306

Combo boxes, 293â€“294 Current assets:

Command buttons, 290â€“291 financial model, input for, 162

Comments, 285 formulas for, 176, 178

ñòð. 59 |