Introduction to Macros and Visual Basic in Excel . S. Malpe11i, Real !state 9.: Dra"t, November ;, ,--8. Sub<ect to Revision.

= :>>;, ,--:, ,--8 Stephen Malpe11i

Recording bo is chec%ed. 7lso notice that #ou can/t clic% on the Stop button while #ou are editing a cell. 7s in ,--8, once the macro is recorded, #ou r

7 macro is a wa# o" automating a procedure in a spreadsheet. ?ou ma# have used macros be"ore, the# also e ist in other spreadsheet such as @otusA:,. or Buattro Cro. Denerall#, macros var# substantiall# "rom spreadsheet language to spreadsheet language, and o"ten do not translate automaticall# across t#pes o" spreadsheets. Macros can be e tremel# simple or e tremel# comple E that depends upon the tas% at hand. The purpose o" this handout is to give an introduction to macros in the ! cel spreadsheet environment. This handout onl# touches on the basics. *or more details, see #our ! cel manual, or specialt# manuals. $omputer manuals have a short Fhal" li"eG because ever# "ew #ears there/s a new version o" ! cel or other so"tware. +n creating the earl# dra"ts o" this handout +/ve used two boo%s 2now a bit out o" date6: Microsoft Excel/Visual Basic Programmers Guide 2Redmond, 0ashington: Microso"t Cress, :>>36 Microsoft Excel/Visual Basic Reference 2Redmond, 0ashington: Microso"t Cress, Second !dition, :>>36 Together these two boo%s give a ver# detailed loo% at macros in ! cel, but are not particularl# good as introductions. + usuall# avoid the &Dummies& or +ntro boo%s, but "or ! cel macros + can recommend: !lisabeth 5oonin, "User Friendly" Using Excel Visual Basic for Applications 2+ndianapolis, +N: Bue, :>>96.

+ have drawn on all these re"erences in constructing this handout, but + have drawn especiall# heavil# "rom 5ooninHs boo%. 5oo%stores li%e 5orders and 5arnes and Noble carr# lots o" these manuals, "rom introductor# to advanced levels. + recommend browsing e tensivel# be"ore #ou bu#, to see which manuals seem to address #our own level o" e pertise. There have been some changes in 457 as implemented in ! cel ,--- 2since these boo%s were written6 but, other than the loo% and "eel o" the newer 457 editor 2i.e. the environment in which we write the actual 457 code6, so "ar + havenHt "ound them to be too dramatic. +Hll tr# to indicate some o" these changes where relevant.

Visual Basic Microso"t ! cel uses a computer language called 4isual 5asic as its language "or writing macros. +" #ou have written macros be"ore in other spreadsheets, #ou ma# have used, "or e ample, the technique o" writing out a series o" letters which represent spreadsheet %e# stro%es in an obscure corner o" #our spreadsheetE naming the range containing these instructions with a name li%e I E then invo%ing the macro b# hitting the $trl J %e#s. ! cel eschews these %inds o" crude macro commands "or a "ullAblown programming language called 4isual 5asic. Those o" #ou with some e perience in computer programming %now that 5asic is itsel" a "ullA"ledged computer language, and is the computer language most associated with C$s. 2! amples o" other similar highAlevel languages include *ortran, Cascal, or C@K:6. 4isual 5asic is a soAcalled Fob<ect orientedG programming language. 0hat this means need not concern us here, e cept that we will see later how various o !ects de"ined in our

language have properties which can be manipulated in wa#s which are simultaneousl# power"ul and straight"orward. 4isual 5asic is o"ten re"erred to b# the acron#m 457. 'ne o" the power"ul things about the implementation o" 457 in ! cel is that #ou can use it to write macros, but #ou can also use it to write #our own "unctions, as well as other %inds o" subroutines 2to be discussed below6. ?ou can also integrate #our macros with #our wor%sheets b# adding what #ou create to menus and tool bars. ?ou can also create dialog bo es which interact with users and run the subroutines. 7n important e ternalit#: 4isual 5asic 2versions not e actl# identical in all respects to the MS '""ice 457, but similar6 is also a widel# used standalone programming language. 7lso, Microso"t uses 457 as the macro environment "or all its products 20ord, ! cel, 7ccess, etc.6. 7 number o" other so"tware out"its are "ollowing suit. So some o" the s%ills we learn in 457 in the spreadsheet environment can be trans"erred to other environments.

The Macro Recorder There are two wa#s to create a macro. +" the macro #ou wish to create is simpl# a shortcut wa# o" collecting routine tas%s in a macro, the easiest wa# to create it is to use the macro recorder. ?ou start the macro recorder <ust as #ou would a tape recorder, per"orm the tas% #ou wish to automate, and ! cel records #our actions "or later pla#bac%. ?ou can also write a macro using the 457 language as #ou would write an# computer program. ?ou can also combine the two methodsE "or e ample #ou can create a dra"t macro b# using the macro recorder and then edit the resulting code which ! cel creates "or #ou.

0e used the macro recorder in class to create a Fto#G macro. The steps involved were: Select the Developer tab in the Ribbon, then "ocus on the $ode section o" the tab, on the le"t. $lic% on Record Macro. The Record Macro dialogue bo will appear. !nter the name o" the macro, t#ped as one word, and the description o" the macro in the dialogue bo . Ca# attention to the option &Store macros in:& window o" the dialogue bo . Ma%e sure that "or this class #ou use the option &This 0or%boo%&. This will ensure that #our macro is recorded into the wor%boo% #ou are wor%ing in. There are some other options which we will be discussing later in the course. $lic% &'(&. The Record Macro dialogue bo will close. 7t the ver# bottom le"t o" the ! cel displa# #ou/ll see a little rectangular Stop button. The recorder is now recording ever# action that #ou ta%e with the computer. )nderta%e whichever actions #ou wish to have recorded. Remember that the recorder will quite literall# cop# ever# action #ou ta%e until #ou shut the recorder o"". Thus, i" #ou ma%e a mista%e and correct it, ever# time #ou @7T!R run the macro ! cel will ma%e the same mista%e and then correct it 2if possi le6. Thus + recommend that be"ore using the macro recorder #ou practice #our action or actions several times to ma%e sure #ou are doing them correctl# so that #our recorded macro is Fclean.G +" the macro to be recorded is long and comple , ma%e some shorthand notes on a sheet o" paper to remind #ou o" the steps to be ta%en and the order in which to ta%e them. *inall#, when #ou are "inished clic% on the Stop button to stop recording the macro.

'nce the macro is recorded, #ou run or pla# the macro b# using the Macros button in the Code section o" the Developer tab. 7 dialogue bo will pop up which lists an# macros which #ou have available to #ou. To run a macro #ou have created, clic% on the macro in question and then clic% the Run button.

?ou can also assign a macro a shortcut %e#, which is a combination o" %e#s that run the macro and wor% "aster than the dialogue bo . + do not recommend using shortcut %e#s in the classroom and lab environment, since we are o"ten not sure i" some particular %e# combination has alread# been set to per"orm some other action. ?ou can also ma%e a "requentl# used macro as a menu item. 7gain + do not recommend this as long as #ou are wor%ing in a lab environment. 0hen #ou have recorded a macro, where it is stored depends on the version o" ! cel #ouHre using. +n ! cel >8 and ,---, macros are stored &out o" sight& even though the# are 2usuall#, at least in our class6 saved as part o" the spreadsheet. To view a macro #ou recorded, clic% on the Visual Basic icon in the Code section o" the Developer tab 2in ! cel ,--.: Tools, Macro, Visual Basic Editor). The 457 editor 2a separate program6 starts, and three windows pop up. Do to the &Cro<ect& window, and clic% on the &Modules& "older. +" #ou have recorded or written an# macros, one or more modules appear, o" the "orm Module:, Module,, etc. Double clic% on a Module and it opens in the larger window to the right o" the Cro<ect window. The de"ault is that ! cel will create a new module ever# time #ou record a new macroE but it is possible to place multiple macros in a single module. Notice that the module sheet does not have rows or columns. +t wor%s more or less li%e a crude word processing program. +" there are no module sheets, #ou can easil# add one. Do to the Project Explorer window o" the 457 !ditor, select #our 457 Cro<ect 2the name o" the ! cel wor%boo% #ou are using6, and then "rom top menu, Insert a Module. 'ne will appear, li%e a blan% sheet o" white paper, in the large window to the right.

Obj100

The ne t page shows a "ew annotated screen shots o" the 457 e plorer, to help #ou learn how to navigate.

Obj101

VB Pro!ra""in! Since recording is so eas#, wh# use the other method o" creating macrosL Sometimes #ou ma# want to write or cop# someone else/s program. 7nother reason is that there are man# things that can be done in the 4isual 5asic language that cannot be done using ! cel commands per se, so the# can/t be recorded. The 457 language is not eas# to trul# master in a short period o" time. The best approach is probabl# not to tr# to memori1e all the elements o" the language, but rather to start o"" b# recording some macros that underta%e "amiliar tas%s, then loo% to see how the macro recorder has translated #our actions into 4isual 5asic. @etHs write an e tremel# simple macro as a little practice. *irst we need a module sheet to wor% with. +n the 457 editor, go to the top menu bar and +nsert a Module. This will create a new macro sheet. ?ou should have a blin%ing cursor at the top le"t corner o" the module sheet. +" not, use the mouse to put the cursor there. Now #ou can t#pe <ust as in a simple word processor. T#pe the "ollowing: Sub Sill#macro 26 5eep !nd Sub That/s all there is to it. The "irst line o" the macro AA Sub Sill#macro26 AA is the title o" the macro. The word Sub tells 457 that this is a subroutine. There are several %inds o" subroutines in 457, including macros. Sill#macro is the name and the wa# we would re"er to this macro later. The parentheses are not utili1ed in macros, but are utili1ed in other t#pes o" subroutines and in "unctions. 2To <ump ahead, i" we write a nonAmacro subroutine, or a "unction, we use the

parentheses to enclose arguments which are past bac% and "orth between ! cel and the subroutine or "unction that we have created.6 The second line contains the &meat& o" the subroutine 2such as it is6. The word F5eepG is the action that the action underta%esE that is to sa#, the computer beeps. 0e can place man# lines representing man# actions, some o" them actuall# use"ul 2believe it or not, there are occasions where &5eep& is quite use"ulM6 The "inal line, !nd Sub, tells 457 that the subroutine has ended. ?ou run this macro in the same wa# in which #ou run a macro which #ou have recorded. T#ping in module sheets is similar to t#ping in a simple word processor. ?ou can also Cop# and Paste "rom elsewhere in the spreadsheet, or even other programs. The 457 editor does ma%e some changes to #our te t i" #our te t has a special meaning to 457. 0ords which are "unctions or have other special meanings are automaticall# capitali1ed even i" #ou t#pe them in lower case letters, "or e ample. Some %e# words are assigned colors, so #ou will notice some words turn blue or red as #ou t#pe them. 5lue means that what #ou have t#ped is a restricted %e# word. This is a word which has to be used "or a special purpose and cannot be used, "or e ample, as the name o" a macro. Dreen te t is "or comments. $omments are lines which start with an apostrophe. +t/s a good idea to get in the habit o" writing lots o" comments about #our macros within #our macros. $omments can also be hand# i" #ou thin% #ou ma# want to delete a line o" code but aren/t sure. 2This happens a lot when the macro recorder writes possibl# e traneous lines6. Nust put comments 2apostrophes6 in "ront o" the lines #ou want to consider deleting, and then run the macro. +" it wor%s, then delete the lines. +" it doesn/t wor%, delete the apostrophes and the lines

are bac% as code. ?ou can put a comment on the same line as programming code i" #ou put the code "irst. Red te t indicates some sort o" error. Remember, i" #ouHre not ma%ing errors, #ouHre not learning an#thing new. The 457 te t editor doesn/t wrap lines. This can be anno#ing, but it/s more or less necessar# since a line o" code needs to be %ept as a separate line "or good programming practice. ?ou can write one line o" code across two ph#sical lines b# using the underscore character at the end o" one ph#sical line to indicate continuation o" a programming line on the ne t ph#sical line. 'ne thing #ou ma# o"ten want to do i" #ou use the macro recorder is to ta%e out some o" the commands that get recorded which ma# be e traneous. @et/s loo% at a simple e ample. Select a cell in a spreadsheet with some te t in it and start the macro recorder. Select $or"at, Cells. $lic% on the $ont Ta% to bring up the page o" the dialogue bo that describes the "ont. Select Bold "rom the list o" st#les, then clic% &'(&.1 Then hit the Stop button to stop the macro recorder. 7ll #ou wanted to do with this macro was to change the "ont st#le to 5old. 2 + %now #ou don/t need a macro to do this but this is <ust to illustrate a point.6 Now loo% at the module sheet and see what was recorded. +t probabl# loo%s something li%e this:

1 Don't use the bold button on your menu bar; it will work, i.e. bold the text, but it won't illustrate the principle we want to look at here.

Sub 5oldness 26 0ith Selection.*ont .Name O F7rialG .*ontst#le O F5oldG .Si1e O :.Stri%ethrough O *alse .Superscript O *alse .Subscript O *alse .'utline*ont O *alse .Shadow O *alse .)nderline O :None .$olor+nde O :7utomatic !nd 0ith !nd Sub Now, #ou have probabl# alread# "igured out what has happened. 7ll #ou reall# cared about was changing the "ont to 5old. 5ut what ! cel actuall# did was in e""ect ta%e a snap shot o" all the current properties o" #our "ont. Thus, i" later #ou had a cell where #ou had t#ped something in, sa#, Times Roman, and used this macro, it would bold the te t but also change it to 7rialM So what #ou want to do is delete all those lines that describe properties o" the "ont that #ou do not wish the macro to a""ect. ! actl# which these are depends o" course on what #ou want the macro to doE "or e ample i" #ou want the onl# thing the macro does is to bold then delete all the lines "rom .Name O &7rial& through .$olor+nde O :7utomatic, with the e ception o" .*ontst#le O &5old&.2

If you used the bold button, despite my entreaties, the macro will contain the following: Selection.Font.Bold=True

which in fact works better than our example, but doesn't illustrate the principle we're trying to get across.

This also gives us a clue to something we/ll see pop up again and againE the F0ithG code, which speci"ies the o%ject we are going to wor% with. Control Structure 7 macro or subroutine without control structure starts at the beginning and goes to the end without stopping. )sing control structure means #ou add I& statements, or other statements. These control the order in which statements are run, how man# times the# run, or whether the# run at all. Crobabl# the simplest control structure is to use a simple i" statement. *or e ample: +" 7D+P:----- Then C@@ O ,3--- A .3Q27D+ A :-----6 where 7D+ has been previousl# de"ined. Notice this +" statement within 457 is di""erent than the O+* "unction in the wor%sheet. *irst o" all, there is no equals sign in "ront o" the word &+".& Secondl#, i" the condition is true then the action to the right o" the Then statement is carried out. There is no requirement to speci"# another statement which is carried out i" the statement is not true. Rowever, this can be done with a construction called i"IthenIelse: +" 7D+P:----- Then C@@ O ,3--- A .3Q27D+ A :-----6 !lse C@@ O ,3--+" #ou have multiple lines which need to be carried out i" a statement is true, #ou can use a more general version: +" 2condition6 Then 2statements6 2statements6 2statements6 !nd i" where &condition& is a logical condition to be evaluated, and &statements& are things which run i" the condition is true.

To underta%e such comparisons #ou can use the "ollowing comparison operators: O, P, S, PO, SO, or SP. These are all "amiliar with the possible e ception o" SP which means &not equal to.& ?ou can compare te t as well as numbers 2although we won/t be using these comparisons much "or a while6. ?ou can also test multiple conditions together using &7nd& and &'r& statements. ?ou can also cause the program to repeat steps. This uses a construction called *orINe t. The best wa# to see this is to loo% at an e ample: Sub Testloop26 T11 O 3 *or n O : to T11 Msg5o Fthe current number is G U n Ne t n Msg5o Fnow we/re out o" the loopG !nd Sub The variable 111 is given the value 3. The *or statement sets up a loop that will repeat until n O the value o" 111. 5# de"ault the initial value o" n is set to :. The line beginning with ne t is the signal that it is the end o" the loop. 7 word o" caution: i" #ou use the wrong logic in a loop #ou can end up in an in"inite loopE that is a loop which #ou never get out o". This use to concern programmers a lot because #ou might burn up thousands o" dollars worth o" computer o" time be"ore an# one reali1ed what was going on. '" course with C$s all #ou lose is a little bit o" #our time. +" #ou "ind #oursel" in an in"inite loop in ! cel, hit the !S$ %e# to <ump out. Then ta%e a care"ul loo% at #our logic and "i the loop.

There are some other constructs which can be used to loop. 7nother use"ul one is the do while: Do 0hile 2condition6 2statements6 @oop The "irst line starts with Do 0hile and is "ollowed b# a condition that 4isual 5asic can evaluate as true or "alse. 0henever it is true, the statements which "ollow 2up to @oop6 will be e ecuted. +" it/s "alse it will be s%ipped. 7"ter the statements have been run the @oop line sends the program bac% to do while to see i" the condition is still true.

Expressions Now that we %now how to control the program, what do we controlL 0hat %inds o" statements will we e ecuteL 'ne element we will need to understand in some detail is e pressions. 7n e pression is something that returns a value in 4isual 5asic. $ommon e pressions include arithmetic e pressions such as . V ,. 7nother %ind o" e pression is re"erring to a variable, the e pression will be evaluated as whatever value is currentl# assigned to that variable. 7 single number is an e pression. Some e pressions test "or truth or "alseness, such as S 8. 7 "unction can also be used to

create an e pression. Something that is con"using in 457 is that 457 "unctions are o"ten slightl# di""erent than equivalent "unctions in the wor%sheet. *or e ample, to ta%e the square root o" a number in an ! cel wor%sheet cell, we write: OSBRT2W6

which will return the value ,. Rowever within 457 we write: Osqr2W6 which assigns the value , to . Notice #ou can place variables on both sides o" the equal sign, "or e ample: J O JV. +n an assignment statement 457 evaluates the right hand side o" the equal sign and then assigns it to the variable on the le"t hand side.

T#pes o& VB Procedures There are three main t#pes o" 457 procedures. The "irst two are subroutines. 7 macro is a subroutine which has an empt# argument list. That is, a"ter the word Sub and the name o" the subroutine we "ind 26. Since there is nothing in the parentheses "ollowing the name o" the procedure it doesn/t loo% "or an# values to be passed to it. +n "act itHs the lac% o" arguments that defines a macro subroutine. The second t#pe o" subroutine is where one or more values are passed to the subroutine when it is invo%ed. Notice that #ou can write a macro 2a subroutine with no arguments6 in which #ou suppl# arguments interactivel# during the running o" the subroutine. Denerall# the t#pe o" subroutine that requires arguements be passed to it can onl# be called b# other lines o" 457 codeE that is #ou can/t run it b# using the macro dialogue bo or using the pla# button on the 4isual 5asic tool bar.

The third t#pe o" procedure is a "unction. 7 "unction comes up with a single value when called, %nown as the return value. This is what distinguishes "unctions "rom subroutines. *unctions require arguments be passed to them. Rere is an e ample o" a "unction: *unction Tap2a,b,c6 Tap O aVbVc !nd *unction 0ithin the spreadsheet #ou would invo%e this "unction b# t#ping OT7C2:,,,.6. '" course within the parentheses #ou could put an# three numbers or cells containing three numbers. Tap will return their sum.

Co""ent on St#le )se lots o" comments, and indent #our code appropriatel#. 457 does not re"uire that #ou indent code. Rowever + strongl# urge #ou to indent codes so that it is eas# to "ollow program control. *or e ample indent ever#thing between the beginning and ending o" a subroutineE indent twice ever#thing between +" statements and !nd +" statements, "or e ample.