1、Extended Learning Module MProgramming in Excel with VBAINTRODUCTIONVBA,which stands for Visual Basic for Applications,is a programming language developed by MicrosoftExcel,along with the other members of Microsoft Office,includes the VBA language INTRODUCTIONExcel VBA is a programming application th
2、at allows Visual Basic code to customize your Excel applicationsUnits of VBA code are often referred to as macros INTRODUCTIONOne advantage of Excel VBA is the macro recorderThe macro recorder is a software tool that will let you record a sequence of commands in Excel and save them as a macro WHY VB
3、A?VBA is a programming language,but it also serves as a macro languageA macro language is a programming language that includes built-in commands that mimic the functionality available from menus and dialog boxes within an applicationA macro is a set of actions recorded or written by a user WHY VBA?C
4、reate a VBA macro to format and print a month-end sales reportExecute the macro with a single commandTrigger Excel to automatically perform many time-consuming tasks WHY VBA?If you are able to perform an operation manually,you can use the macro recorder to capture that operationYou can use VBA to cr
5、eate your own worksheet functions WHY VBA?Common uses for VBA macros:Inserting text Automating a task Automating repetitive tasksCreating a custom command WHY VBA?Common uses for VBA macros:Creating a custom toolbar buttonCreating a custom menu command Creating a simplified front endDeveloping new w
6、orksheet functions Creating complete,macro-driven applications VBA in a NutshellYou perform actions in VBA by writing(or recording)code in a VBA macroYou view and edit VBA macros using the Visual Basic Editor(VBE)VBA in a NutshellA VBA macro consists of Sub proceduresA Sub procedure is computer code
7、 that performs some action on or with objects VBA in a NutshellSub procedure example:Sub Demo()Sum=1+1MsgBox“The answer is”&Sum End Sub VBA in a NutshellA VBA macro can also have Function proceduresA Function procedure is a VBA macro that returns a single valueYou can call it from another VBA macro
8、or even use it as a function in a worksheet formula VBA in a NutshellFunction example:Function AddTwo(arg1,arg2)AddTwo=arg1+arg2 End Function VBA in a NutshellVBA manipulates objectsAn object in VBA is an item available for you to control in your codeExcel provides more than 100 objects that you can
9、 manipulate VBA in a NutshellYou can assign values to variablesA variable is a place to store a piece of informationYou can use variables in your VBA macro to store such things as values,text,or property settings VBA in a NutshellVBA FUNCTIONEXAMPLEObjectArranged in a hierarchyExcel:Workbook:Workshe
10、etApplication.Workbooks(“Book1.xls”).Worksheets(“Sheet1”)MethodsMethod is an action such as ClearContentsWorksheets(“Sheet1”).Range(“A1”).ClearContentsTHE VISUAL BASIC EDITORThe Visual Basic Editor(VBE)is a separate application where you write and edit your Visual Basic macrosYou cant run the VBE se
11、parately;Excel must be running in order for the VBE to operate THE VISUAL BASIC EDITORThe quickest way to activate the VBE is to press Alt+F11 when Excel is activeTo return to Excel,press Alt+F11 againAlt+F11 acts as a toggle between the Excel application interface and the VBE You can also activate
12、the VBE by using the menus within ExcelChoose Tools,then Macro,and then choose Visual Basic Editor The VBE ToolsMenu BarToolbarProject Explorer WindowCode WindowThe Properties WindowThe Immediate Window The VBE ToolsWorking with the Project ExplorerWhen working in the VBE,each Excel workbook thats o
13、pen is a projectA project is a collection of objects arranged as an outlineExpand a project by clicking the plus sign(+)To contract a project click the minus sign(-)Adding a New VBA ModuleFollow these steps to add a new VBA module to a project:1.Create a new workbook in Excel2.Press Alt+F11 to activ
14、ate the VBE3.Select the projects name in the Project Explorer window4.Choose Insert and then Module or you can use the shortcut,by using the right-mouse click,choosing Insert,and then Module Removing a VBA ModuleTo remove a VBA module from a project,follow these steps:1.Select the modules name in th
15、e Project Explorer window2.Choose File,and then Remove ModuleName Creating a ModuleIn general,a VBA module can hold several types of code:Sub procedures-A set of programming instructions that performs some action Function procedures-A set of programming instructions that returns a single valueDeclar
16、ations-One or more information statements that you provide to VBA VBA Module CodeBefore you can do anything meaningful,you must have some VBA code in the VBA moduleYou can get VBA code into a VBA macro in two ways:1.Entering the code directly by typing it2.Using the Excel macro recorder to record yo
17、ur actions and convert them to VBA code Entering Code DirectlyYou can type code directly into the moduleYou can select,copy,cut,paste,and do other things to the textWhen you are entering your code directly,you use the Tab key to indent some of the lines to make your code easier to read Entering Code
18、 DirectlyEntering Code DirectlyUsing the Macro Recorder1.Activate a worksheet in the workbook2.Choose Tools,then Macro,and then Record New Macro3.Excel displays its Record Macro dialog box4.Click OK to accept the defaults5.Excel automatically inserts a new VBA module into the project6.From this poin
19、t on,Excel converts your actions into VBA code-while recording,Excel displays the word Recording in the status bar Using the Macro Recorder7.Excel displays a miniature floating toolbar that con-tains two toolbar buttons:Stop Recording and Relative Reference 8.Choose Tools,then Options9.Click the Vie
20、w tab10.Remove the check mark from the Gridlines option11.Click OK to close the dialog box12.Click the Stop Recording button Using the Macro RecorderUsing the Macro RecorderUsing the Macro RecorderTry the macro:1.Activate a worksheet that has gridlines displayed2.Choose Tools,then Macro,and then cho
21、ose Macros,or press Alt+F83.Select Macro14.Click the Run button5.Excel executes the macro,and the gridlines disappear Using the Macro RecorderUsing the Macro RecorderAnother way to execute a macro is to press its shortcut key1.Choose Tools,then Macro,and then Macros2.Select the Macro1 Sub procedure
22、name from the list box3.Click the Options button4.Click the Shortcut Key option and enter a letter in the box labeled Ctrl+5.Click OK to close the Macro Options dialog box Using the Macro RecorderVBA BUILDING BLOCKSThere are many ways to write a macro using Excel VBAWrite or record macros using modu
23、les or proceduresDevelop user-defined functions Code ModulesAll macros reside in code modules like the one on the right of the VBE windowThere are two types of code modules 1.Standard modules2.Class modules ProceduresIn VBA,macros are referred to as proceduresThere are two types of procedures1.Sub p
24、rocedures 2.Function proceduresThe macro recorder can only produce Sub procedures Sub ProceduresSub procedures(sometimes referred to as subroutines)start with the keyword Sub followed by the name of the procedure and opening and closing parenthesesThe end of a Sub procedure is marked by the keywords
25、 End Sub Sub ProceduresSub MonthNames()Range(“B1”).SelectActiveCell.FormulaR1C1=“Jan”Range(“C1”).SelectActiveCell.FormulaR1C1=“Feb”.End SubFunction ProceduresExcel has hundreds of built-in worksheet Function ProceduresChoose Insert,and then the Function command to see a list of those functionsIf the
26、 function you need is not already in Excel,you can write your own user defined function(or UDF)using VBA Function ProceduresA function procedure example:Function CentigradeToFahrenheit(Centigrade)CentigradeToFahrenheit=Centigrade*9/5+32End Function ELEMENTS OF VBA PROGRAMMINGVBA uses many elements c
27、ommon to all programming languages,such as:CommentsVariablesConstantsData types CommentsA comment is the simplest type of VBAVBA ignores these statementsExample:Sub CommentsExample()This procedure is a demonstrationx=0 x represents zero The next line of code will display the resultMsgBox x End Sub V
28、ariables and ConstantsVBAs main purpose is to manipulate dataVBA stores the data in your computers memorySome data,such as worksheet ranges,reside in objects and other data are stored in variables or constants VariablesVariable name of a storage locationExamplesx=1(assigns the value of 1 to the vari
29、able x)UserName=“Amy Phillips”(UserName is a string variable that can hold text)x=x+1(adds 1 to x and stores it in the variable x)DateStarted=#12/20/2004#(DateStarted is a date variable)ConstantsA variables value may(and usually does)change while your procedure is executingA constant is a named elem
30、ent whose value doesnt changeExample:Const NumQuarters As Integer=4 Const Rate=.0725Const Period=12 Const ModName As String=“Budget Macros”Data TypesData types are the manner in which data types are stored in memory-for example,as integers,real numbers,or stringsExamplesBooleanIntegerCurrencyDateStr
31、ingStringsExcel and VBA can work with both numbers and textText is often referred to as a stringThere are two types of strings in VBA:1.Fixed-length strings are declared with a specified number of characters(the maximum length is about 65,526 characters)2.Variable-length strings theoretically can ho
32、ld as many as 2 billion characters DatesTo store dates,use the Date data typeIf you do,you will be able to perform calculations with the dates Assignment StatementsAn assignment statement is a VBA statement that assigns the result of an expression to a variable or an objectExamples:x=1x=x+1x=(y*2)/(
33、z*2)HouseCost=375000 FileOpen=True Range(“TheYear”).Value=2005 OperatorsThe precedence order for operators in VBA is exactly the same as in Excel formulasExponentiation has the highest precedenceMultiplication and division come nextThen addition and subtraction OperatorsArithmetic operatorsAdd(+),su
34、btract(-),multiply(*),divide(/),exponentiate(),string concatenate(&)Logical operatorsNot,And,Or,XoR(exclusion),Eqv(equivalence),Imp(implication)DECISIONS,DECISIONS,DECISIONSVBA is a structured languageIt offers standard structured decision constructs such as:If-Then and Select Case structuresFor-Nex
35、t,Do-Until,and Do-While loops The If-Then StructureExecutes one or more statements based on some conditionFormatIf condition Then statements Else statementsExamplesIf Quantity=75 Then Discount=0.25If Quantity 10 Then Discount=0.1 Else Discount=0.0The Select Case StructureUseful for decisions involvi
36、ng three or more optionsOften used for decisions that involve evaluating a number in a range(e.g.,between 1 and 100,between 101 and 200,and so on)See example on page M.21 LoopingThe term looping refers to repeating a block of statements or code numerous timesThere are several looping statements to c
37、hoose from:The For-Next loopThe Do-While loopThe Do-Until loop The For-Next LoopThe looping is controlled by a counter variable,which starts at one value and stops at another valueExample:Sub FillRange()Dim Count As Integer For Count=1 To 100 ActiveCell.Offset(Count-1,0)=Rnd Next CountEnd Sub Do-Whi
38、le LoopA Do-While loop continues until a specified condition is metExample:Sub DoWhileExample()Do While ActiveCell.Value Empty ActiveCell.Value=ActiveCell.Value*2 ActiveCell.Offset(1,0).SelectLoopEnd Sub Do-Until LoopIn a Do-Until loop,the macro executes the loop until the condition is trueExample:S
39、ub DoUntilExample()Do Until IsEmpty(ActiveCell.Value)ActiveCell.Value=ActiveCell.Value*2 ActiveCell.Offset(l,0).SelectLoopEnd Sub WRAP IT UPWRAP IT UP1.Open a new workbook2.Create a worksheet 3.Press Alt+F11 to activate the VBE4.Click the new workbooks name in the Project Explorer window5.Choose Insert,then Module to insert a VBA module into the project WRAP IT UP6.Type the following code into the Code windowWRAP IT UP7.Return to the Excel spreadsheet,make cell C10 the active cell and type in the formula=InvoiceAmount(A10,B10)8.Copy the formula to the remaining cells WRAP IT UP
