Transcription
Microsoft Application SeriesExcel VBAIntroduction-IntermediateBest STL Courses never cancelled: guaranteed Last minute rescheduling 24 months access to Microsoft trainers 12 months schedule UK wide deliverywww.microsofttraining.netVersion 2
E&OEBest Training reserves the right to revise this publication and make changes from time to time inits content without notice. Best STL 2014www.microsofttraining.netTel: 0845 519 4797
Like this training manual?Why not learn even more on one of ourindustry-leading training courses?These training manuals are just a sample of the top-class training provided byBest STL. We are a London based company who provide courses throughout the UK either in our own training centres or directly at our client’s offices.We partner with companies of all sizes, from international blue chip organisations to startups and freelancers, to provide comprehensive training on everything from Microsoft Officeto management, finance and key work skills.We’ve helped over 40,000 individuals in countless companies save hundreds of thousandsof hours, through increased productivity and improved workflows. Not to mention that theyare now the envy of their peers with their new found expertise!Why Best STL? Expert TrainersWe are proud to have the mostexperienced and qualified MicrosoftTrainers in the industry.A sample of our courses: Fantastic Customer Satisfaction98% of our clients would recommend us.You can see all 42,781 (and counting)reviews here, completely uncensored.Microsoft Word 24 months training supportWe provide you with unlimited support for24 months via our forums where we haveexperts online ready to answer all yourquestions. Courses never cancelled: GuaranteedWhen you book one of our Microsoft desktopor management skills courses we guaranteethat the course will never be cancelledmeaning no risk of disruption to yourschedule.Excel VBAMicrosoft ExcelMicrosoft PowerPointMicrosoft SharePointTime Management SkillsPresentation SkillsIntroduction to ManagementCiscoOracleTo view our full range of coursesvisit www.microsofttraining.net/Just a few of our satisfied customersContact us for more information on any of our training services by ining.net Best STL 2014Tel: 0845 519 4797
ContentsUnit 1 The VBA Environment1Introducing Visual Basic for ApplicationsRecording and Running MacrosUsing the Visual Basic Toolbar (2003 Only)Adding a Macro/Procedure to the Quick Access Toolbar (2007/2010 Only)Editing Macros in Visual Basic EditorUnderstanding the Development EnvironmentProtect/Lock Excel VBA CodeUsing HelpClosing the Visual Basic EditorUnit 1 Practice ActivityUnit 2 Developing with Procedures and Functions13Understanding and Creating ModulesDefining ProceduresNaming ProceduresCreating a Sub-ProcedureCreating a Function ProcedureCalling ProceduresUsing the Immediate Window to Call ProceduresWorking Using the Code Editor1314141517181920Unit 3 Understanding Objects23Defining ObjectsExamining the Excel Object HierarchyDefining CollectionsUsing the Object BrowserWorking with PropertiesThe With StatementWorking With MethodsEvent ProceduresUnit 3 Practice Activity232426272929303132Unit 4 Using Intrinsic Functions, Variables and ExpressionsDefining Expressions And StatementsHow to Declare VariablesDetermining Data TypesProgramming with Variable ScopeHarnessing Intrinsic FunctionsDefining Constants and Using Intrinsic ConstantsAdding Message BoxesUsing Input Boxeswww.microsofttraining.net Best STL 201413678910111112343436384143434549Tel: 0845 519 4797
How to Declare and Use Object VariablesUnit 4 Practice Activity5051Unit 5 Debugging the Code52Understanding ErrorsUsing Debugging ToolsIdentifying the Value of ExpressionsSetting BreakpointsHow to Step Through CodeWorking with Break Mode during Run Mode525556565758Unit 6 Handling Errors59Defining VBA's Error Trapping OptionsCapturing Errors with the On Error StatementDetermining the Err ObjectCoding an Error-Handling RoutineUsing Inline Error Handling5960616264Unit 7 Managing Program Execution65Defining Control-Of-Flow structuresUsing Boolean ExpressionsUsing the If.End If Decision StructuresUsing the Select Case.End Select StructureUsing the Do.Loop StructureUsing The For.Next StructureUsing the For Each.Next StructureGuidelines for Use Of Control-Of-Flow StructuresUnit 7 Practice Activity 1Unit 7 Practice Activity 265656769717272737475Unit 8 Harnessing Forms And Controls76Defining UserFormsUtilising the ToolboxUsing UserForm Properties, Events And MethodsUnderstanding ControlsSetting Control Properties in the Properties WindowUsing the Label ControlUsing the Text Box ControlUsing the Command Button ControlUsing the Combo Box ControlUsing the Frame ControlUsing Option Button ControlsUsing Control AppearanceSetting the Tab OrderFilling a ControlAdding Code to Controlswww.microsofttraining.net Best STL 2014767778808283838484858585868787Tel: 0845 519 4797
How to Launch a Form in CodeUnit 8 Practice Activity8788APPENDIX I: Using the PivotTable Object89Understanding PivotTablesCreating A PivotTable2003 Pivot Wizard Procedure2007/2010 ProcedureUsing the PivotTable Wizard MethodUsing PivotFields898989909192APPENDIX II: Excel VBA – Quick Reference Guide94Consolidation Exercise 1100Consolidation Exercise 2101www.microsofttraining.net Best STL 2014Tel: 0845 519 4797
Unit 1 The VBA EnvironmentIntroducing Visual Basic for ApplicationsVisual Basic for Applications or VBA is a development environment built into theMicrosoft Office Suite of products.VBA is an Object Oriented Programming (OOP) language. It works bymanipulating objects. In Microsoft Office the programs are objects. In Excelworksheets, charts and dialog boxes are also objects.In VBA the object is written firstI’m fixing the Yellow House Fix.verbVBA.object.property.methodWhen working in VBA tell Excel exactly what to do. Don’t assume anything.Some General tipsDo not hesitate to use the macro recorder to avoid typos in your code.Write your code in lower case letters. If the spelling is RIGHT, the Visual BasicEditor will capitalize the necessary letters. If it doesn't. check your spelling.All VBA sentences must be on a single line. When you need to write longsentences of code and you want to force a line break to make it easier to readyou must add a space and an underscore at the end of each line and then pressReturn. Here is an example of a single sentence broken into 3 lines:Range("A1:E9").Sort Key: Range("C2"), Order1: xlAscending,MatchCase: False, Orientation: xlTopToBottom,DataOption1: xlSortTextAsNumberswww.microsofttraining.net Best STL 2014Tel: 0845 519 4797Page 1
Flickering ScreenRunning a macro or VBA code may cause the screen to flicker as the monitor isthe slowest part of the program and cannot keep up with the very fast changestaking place. To switch off the screen until the program is run enter thefollowing code line:Application.ScreenUpdating FalseScreen comes on automatically on completion of the program.CutCopyModeAfter each Copy/Paste operation, you should empty the clipboard with thefollowing line of code to make sure that the computer memory doesn't overload:ActiveSheet.PasteApplication.CutCopyMode FalseDisplayAlertsIf you don't want Excel to ask you things like "Do you want to delete this file."you can use the following line of code at the beginning of the relevant VBAprocedure.Application.DisplayAlerts FalseThen at the end make sure you use the following code to reactivate DisplayAlerts.Application.DisplayAlerts TrueCompare TextIf you try to compare two strings in VBA the system compares the Binaryinformation of the strings so that“My Name” Is Not Equal To “my name”.To make the computer compare the words in the string, rather than the Binaryyou need to enter the code:Option Compare TextIn the Declarations area of the moduleQuitThe following line of code closes Excel et Best STL 2014Tel: 0845 519 4797Page 2
Recording and Running MacrosA macro is a series of commands in Visual Basic, also known as a SubProcedure. Macros allow you to automate tedious or complicated tasks,particularly those that are prone to error.You can record a sequence of commands and replay the actions by running themacro. Examining the code of a recorded macro can give you insight into howVisual Basic works.Macros can be stored on the current worksheet or made available globally bysaving them in the Personal.xlsm workbook. This is a hidden workbook thatautomatically opens when you open Excel.Recording a Macro2003:Tools Menu Macros Record Macro2007/2010View Ribbon Macro Section Macro Record MacroNote: 2007/2010 will require the Developer Ribbon to be available for most VBArelated tasks.2007: Office Button Options Display Tick Show Developer2010: File Ribbon Options Customise Ribbon Tick Show DeveloperThe Record Macro dialog box appears. Type the macro’s name in the Macroname box (cannot contain spaces) Select where the macro is to bestored Add a shortcut key, if desired Type a description, if desired (thiswill appear in the VB editor ascommented code) Click OK.Perform the actions to be recorded.www.microsofttraining.net Best STL 2014Tel: 0845 519 4797Page 3
To end the recording in 2003: Click the Stop Recording button.To end recording in 2007/2010 Click Stop button in bottom left ofStatus barOr from the Developer ribbonRunning a MacroA macro can be run by using a keystroke combination, a menu, a toolbar or theMacro dialog box. This provides a list of all available macros in the openworkbooks. To open this:2003: Open the Tools menu Select Macro Choose Macros.2007/2010 Developer Ribbon Code Section Macros ButtonThe Macro dialog box appears. Select the desired macrofrom the Macro Name list Click Run.Macros without a workbook name in front indicate that they belong to the activeworkbook.Click the Step Into button in the Macro dialog box to run the macro one line at atime. Once the VB editor displays, press F8.Keep pressing F8 to step through the code. Display both the Excel and VBEditor windows in order to see the results of the code execution.www.microsofttraining.net Best STL 2014Tel: 0845 519 4797Page 4
Adding a Macro/Procedure to a Custom Toolbar (2003 Only)Macros and Sub Procedures can be executed from the Macro dialog box andfrom within other procedures. You can also execute procedures from toolbarsand menus.To assign a procedure to a custom toolbar: Open Tools menuOR Right–click in the toolbars area Select Customize.The Toolbars dialog box appears. Click the Toolbars tab Click New Name the new toolbar Click OK.A new toolbar appears ready for buttons to be added. To do this:www.microsofttraining.net Best STL 2014 Click the Commands tab Select Macros from theCategories list. Drag the custom Button icononto the new toolbar Click Modify Selection Click Assign Macro Select the required macro andclick OK Click Close.Tel: 0845 519 4797Page 5
Using the Visual Basic Toolbar (2003 Only)As an alternative to this you can use the Visual Basic Toolbar to record andmanage macros. To do this: Open the View Menu Select Toolbars Choose Visual Basic.The Visual basic toolbar appears.The most used buttons are described below:Run a Macro. A list of available macros appearsRecord a Macro. The Record Macro toolbar appearsOpens the Security dialog box allowing the user to set security levels.Open the Visual Basic Editor.Open the Control Toolbox to access a variety of Form ControlsSwitch design mode On and Offwww.microsofttraining.net Best STL 2014Tel: 0845 519 4797Page 6
Adding a Macro/Procedure to the Quick Access Toolbar (2007/2010Only)To add the recorded Macro as a button on the Quick Access Toolbar, top left ofthe Excel window follow these steps:Quick Access Toolbar Select the small drop menu button (shown above) From the menu select “More Commands” This will display the Excel Options dialog Click menu called “Choose Commands From”Select Macros www.microsofttraining.net Best STL 2014Tel: 0845 519 4797Locate the name ofthe macro and selectClick the Add buttonClick OKPage 7
Editing Macros in Visual Basic EditorWhen you record a macro, the recorded instructions are inserted into aProcedure whose beginning and end are denoted with the key words Sub andEnd Sub. This is stored within a Module. A module can contain manyprocedures.Code generated when a macro is recorded can be modified to provide a morecustomised function. To do this:2003 Version Open the Tools menu Select Macro , Choose Macros Select the desired macro from the Macro Name list Click Edit.2007/2010 Version Developer Ribbon Code Section Macros Select the desired macro from the Macro Name list Click EditThe Visual Basic Editor appears. Make the desired changesSave the macroClose the Visual Basic Editor window.Important NoteYou can usually figure out how to code any action in Excel by recording it in amacro and viewing the resulting macro code.www.microsofttraining.net Best STL 2014Tel: 0845 519 4797Page 8
Understanding the Development pertiesWindowTitle bar, Menu barand StandardtoolbarThe centre of the Visual basic environment. Themenu bar and toolbar can be hidden of customized.Closing this window closes the program.Project ExplorerProvides an organized view of the files andcomponents belonging to the project.If hidden the Project Explorer can be displayed bypressing Ctrl RProperties WindowProvides a way to change attributes of forms andcontrols (e.g. name, colour, etc). If hidden press F4to display.Code WindowUsed to edit the Visual basic code. Press F7 and itwill open an object selected in Project Explorer.Close the window with the Close button thatappears on the menu bar.www.microsofttraining.net Best STL 2014Tel: 0845 519 4797Page 9
Protect/Lock Excel VBA CodeWhen we write VBA code it is often desirable to have the VBA Macro code notvisible to end-users. This is to protect your intellectual property and/or stopusers messing about with your code.To protect your code, from within the Visual Basic Editor Open the Tools Menu Select VBA Project PropertiesThe Project Properties dialog boxappears. Click the Protection page tab Check "Lock project forviewing" Enter your password and againto confirm it. Click OKAfter doing this you must Save and Close the Workbook for the protection totake effect.The safest password to use is one that uses a combination of upper, lower casetext and numbers. Be sure not to forget it.Noteswww.microsofttraining.net Best STL 2014Tel: 0845 519 4797Page 10
Using HelpIf the Visual Basic Help files are installed, by pressing F1, a help screendisplays explaining the feature that is currently active:Alternatively use the Ask a Question box on the menu bar to as a quick way tofind help on a topic.Closing the Visual Basic EditorTo close the Visual Basic Editor use one of the following: Open the File menu; selectClose and Return to MicrosoftExcelOR Press Alt QOR www.microsofttraining.net Best STL 2014ClickTel: 0845 519 4797Close in the title bar.Page 11
Unit 1 Practice Activity1. Open Macros practice.xlsm in the Practice Files folder2. This file contains only one worksheet called Macros. The worksheet has twoscenarios: Original and Cost of sales.3. Create a macro named Display cost of sales that has Ctrl Shift C as itsshortcut key. This macro should show the Cost of sales scenario.4. Create a macro named Display original that has Ctrl Shift O as its shortcutkey. This macro should show the Original scenario.5. Run the Display cost of sales macro. Run the Display original macro.6. Change the name of the Display cost of sales scenario to Decreased costof sales.7. Run the edited macro.8. Save the Workbook as My macros practice.xlsm and close.Online support forum and knowledge basewww.microsofttraining.net/forumVisit our forum to have your questions answered by our Microsoft qualified trainers.www.microsofttraining.net Best STL 2014Tel: 0845 519 4797Page 12
Unit 2 Developing with Procedures and FunctionsProcedure is a term that refers to a unit of code created to perform a specifictask. In Excel, procedures are stored in objects called Modules.In this unit we will look at both Modules and Procedures.Understanding and Creating ModulesStandard modules can be used to store procedures that are available to allforms, worksheets and other modules. These procedures are usually genericand can be called by another procedure while the workbook is open.Within a project you can create as many standard modules as required. Youshould store related procedures together within the same module.Standard modules are also used to declare global variables and constants. Tocreate a standard module in the VB Editor: Open the Insert menu Select Module.A new Module appears:NewModuleRenameModule Display the Properties window if necessaryIn the Properties window change the name of the modulewww.microsofttraining.net Best STL 2014Tel: 0845 519 4797Page 13
Defining ProceduresA procedure is a named set of instructions that does something within theapplication.To execute the code in a procedure you refer to it by name from within anotherprocedure. This is known as Calling a procedure. When a procedure hasfinished executing it returns control to the procedure from which it was called.There are two general types of procedures:Sub proceduresperform a task and return control to the callingprocedureFunction proceduresperform a task and return a value, as well ascontrol, to the calling procedureIf you require 10 stages to solve a problem write 10 sub procedures. It is easierto find errors in smaller procedures than in a large one.The procedures can then be called, in order, from another procedure.Naming ProceduresThere are rules and conventions that must be followed when namingprocedures in Visual Basic.While rules must be followed or an error will result, conventions are there as aguideline to make your code easier to follow and understand.The following rules must be adhered to when naming procedures: Maximum length of the name is 255 characters The first character must be a letter Must be unique within a given module Cannot contain spaces or any of the following characters: . , @ & # ( )!www.microsofttraining.net Best STL 2014Tel: 0845 519 4797Page 14
You should consider these naming conventions when naming procedures: As procedures carry out actions, begin names with a verb Use the proper case for the word within the procedure name If procedures are related try and place the words that vary at the end of thenameFollowing these conventions, here is an example of procedure ting a Sub-ProcedureMost Excel tasks can be automated by creating procedures. This can be doneby either recording a macro or entering the code directly into the VB Editor’sCode window.Sub procedures have the following syntax:[Public/Private] Sub ProcedureName ([argument list])Statement blockEnd SubPublic indicates procedure can be called from within other modules. It is thedefault settingPrivate indicates the procedure is only available to other procedures in thesame module.The Sub End Sub structure can be typed directly into the code window orinserted using the Add Procedure dialog box.To create a sub procedure: Create or display the module to contain the new sub procedure Click in the Code window Type in the Sub procedure using the relevant syntaxType in the word Sub, followed by a space and the Procedure namePress Enter and VB inserts the parenthesis after the name and the End Subline.OR Use Add Procedure.www.microsofttraining.net Best STL 2014Tel: 0845 519 4797Page 15
To display the Add Procedure dialog box: Open the Insert menu Select Procedure.The Add Procedure dialog box appears: Type the name of the procedure in theName text box Select Sub under Type, if necessary Make the desired selection underScope Click OK.Below is an example of a basic sub procedure:Noteswww.microsofttraining.net Best STL 2014Tel: 0845 519 4797Page 16
Creating a Function ProcedureFunction procedures are similar to built-in functions such as Sum(). They aresometimes called user-defined function.A function returns a value to the procedure that calls it. The value the functiongenerates is assigned to the name of the function.Function procedures have the following syntax:[Public/Private] Function FunctionName ([argument list]) [As Type ][Statement block][FunctionName expression ]End FunctionPublic indicates procedure can be called from within other modules. It is thedefault settingPrivate indicates the procedure is only available to other procedures in thesame module.The As clause sets the data type of the function’s return value.To create a function procedure: Create or display the module to contain the new Function procedure Click in the Code window Type in the Function procedure using the relevant syntax or use AddProcedureType in the word Function followed by a space and the Function namePress Enter and VB places the parenthesis after the name and inserts theEnd Function line.Display the Add Procedure dialog box (as in Creating a Sub Procedure): Open the Insert menu Select Procedure.Noteswww.microsofttraining.net Best STL 2014Tel: 0845 519 4797Page 17
The Add Procedure dialog box appears (as seen in Creating a SubProcedure): Type the name of the procedure in the Name text box Select Function under Type Make the desired selection under Scope Click OK.Below is an example of a basic function procedure:Calling ProceduresA sub procedure or function is called from the point in another procedure whereyou want the code to execute. The procedure being called must be accessibleto the calling procedure. This means it must be in the same module or bedeclared public.Below is an example of calls to Sub and Function procedures:Sub procedureFunction procedureWhen passing multiple arguments (as in the function procedure above) alwaysseparate them with commas and pass them in the same order as they are listedin the syntax.Auto Quick Info is a feature of the Visual Basic that displays a syntax boxwhen you type a procedure or function name.The example below shows the tip for the Message Box function:Arguments in square brackets are optional.Values passed to procedures are sometimes referred to as parameters.Noteswww.microsofttraining.net Best STL 2014Tel: 0845 519 4797Page 18
Using the Immediate Window to Call ProceduresThe Immediate window is a debugging feature of Visual Basic. It can be usedto enter commands and evaluate expressions.Code stored in a sub or function procedure can be executed by calling theprocedure from the Immediate window.To open the Immediate window: Open the View menu Select Immediate windowOR Press Ctrl G.The Immediate window appears.To execute a sub procedure: Type SubProcedureName ([Argument list]) Press Enter.To execute a function and print the return value in the window: Type ? FunctionName ([Argument list]) Press Enter.To evaluate an expression: Type ? Expression Press Enter.Within the code, especially in loops, use the Debug.Print statement to displayvalues in the Immediate window while the code is executing. The Immediatewindow must be open for this.Noteswww.microsofttraining.net Best STL 2014Tel: 0845 519 4797Page 19
Working Using the Code EditorThe Code editor window is used to edit Visual Basic code. The two drop downlists can be used to display different procedures within a standard module orobjects’ event procedures within a class module.Below is an illustration of the code window:Object listProcedure View:Displays proceduresone at a time.Procedure listProcedureseparatorFull Module View:Displays all the procedures inthe module one after the otherObject ListDisplays a list of objects contained in the current module.Procedure ListDisplays a list of general procedures in the current modulewhen General is selected in the Object list.When an object is selected in the Object list it displays a listof events associated with the object.Noteswww.microsofttraining.net Best STL 2014Tel: 0845 519 4797Page 20
Setting Code Editor OptionsThe settings for the Code Editor can be changed. To do this: Open the Tools menu in the VB Editor Select Options.The Options dialog box appears:The following are explanations of the Code Setting selections:Auto SyntaxCheckAutomatically displays a Help message when a syntax error isdetected. Message appears when you move off the code linecontaining the errorRequire VariableDeclarationAdds the line Option Explicit to all newly created modules,requiring all variables to be explicitly declared before they areused in a statement.Auto ListMembersDisplays a list box under your insertion point after you type anidentifiable object. The list shows all members of the object class.An item selected from the list can be inserted into your code bypressing the Tab keyAuto Quick InfoDisplays a syntax box showing a list of arguments when amethod, procedure or function name is typedAuto Data TipsDisplays the value of a variable when you point to it with a mouseduring break mode. Useful for debugging.Auto IndentIndent the specified amount when Tab is pressed and indents allsubsequent lines at the same level.www.microsofttraining.net Best STL 2014Tel: 0845 519 4797Page 21
The Windows Settings selections are explained below:Drag-and-Drop Text EditingAllows you to drag and drop code around the Codewindow and into other windows like the Immediatewindow.Default to Full Module ViewDisplays all module procedures in one list withoptional separator lines between each procedure.The alternative is to show one procedure at a time,as selected through the Procedure list.Procedure SeparatorDisplays a grey separator line between procedures ifModule view is selectedEditing GuidelinesBelow are some useful guidelines to follow when editing code: If a statement is too long carry it over to the next line by typing a space andunderscore ( ) character at the end of the line. This also works forcomments.Strings that are continued require a closing quote, an ampersand (&), and aspace before the underscore. This is called Command Line Continuation. Indent text within control structures for readability. To do this: Select one or more lines Press the Tab keyOR Press Shift Tab to remove the indent. Complete statements by pressing Enter or by moving focus off the code lineby clicking somewhere else with the mouse or pressing an arrow key.When focus is moved off the code line, the code formatter automaticallyplaces key words in the proper case, adjusts spacing, adds punctuation andstandardizes variable capitalization.It is also a good idea to comment your code to document what is happening inyour project. Good practice is to comment what is not obvious.Start the line with an apostrophe ( ‘ ) or by typing the key word Rem (forremark). When using an apostrophe to create a comment, you can place thecomment at the end of a line containing a code statement without causing asyntax error.Noteswww.microsofttraining.net Best STL 2014Tel: 0845 519 4797Page 22
Unit 3 Understanding ObjectsAn object is an element of an application that can be accessed and manipulatedusing Visual Basic. Examples of objects in Excel are worksheets, charts andranges.Defining ObjectsObjects are defined by lists of Properties, and Methods. Many also allow forcustom sub-procedures to be executed in response to Events.The term Class refers to the general structure of an object. The class is atemplate that defines the elements that all objects within that class share.PropertiesProperties are the characteristics of an object. The data values assigned toproperties describe a specific instance of an object.A new workbook in Excel is an instance of a Workbook object, created by you,based on the Workbook class. Properties that define an instance of aWorkbook object would include its name, path, password, etc.MethodsMethods represent procedures that perform actions.Printing a worksheet, saving a workbook selecting a range are all examples ofactions that can be executed using a method.EventsMany objects can recognize and respond to events. For each event the objectrecognizes you can write a sub procedure that will execute when the specificevent occurs.A workbook recognizes the Open event. Code inserted into the Open eventprocedure of the workbook will run whenever the workbook is opened.Events may be initiated by users, other objects, or code statements. Manyobjects are designed to respond to multiple events.Noteswww.microsofttraining.net Best STL 2014Tel: 0845 519 4797Page 23
Examining the Excel Object HierarchyThe Excel Object Module is a set of objects that Excel exposes to thedevelopment environment. Many objects are contained within other objects.This indicates a hierarchy or parent-child relationship between the objects.The Application object represents the application itself. All other objects arebelow it and accessible through it. It is by referencing these objects, in code,that we are able to control Excel.Objects, their properties and methods are referred to in code using the “dot”operator as illustrated below:Application.ActiveWorkbook.SaveAs “Employees.xls”Parent ObjectChild ObjectMethod of the Child Object Argument of the MethodSome objects in Excel are considered global. This means they are on top of thehierarchy and can be referenced directly. The Workbook object is a child objectof the Excel Application object. But since the Workbook object is global youdon’t need to specify the Application object when referring to it.Therefore the following statements are equal:Application.ActiveWorkbook.SaveAs “Employees.xlsActiveWorkbook.SaveAs “Employees.xls”Some objects in the Excel Object model
Choose Macros. 2007/2010 Developer Ribbon Code Section Macros Button The Macro dialog box appears. Select the desired macro from the Macro Name list Click Run. Macros without a workbook name in front indicate that they belong to the active workbook. Click the Step Into button in the Macro dialog box to run the macro one line at a