formsc

NumbersandSymbolscharacter,usinginImmediatewindow,255!
(bang)characteraddingtocustomobjects,321!
Northwind2ExcelObject,321–322AAccess2000,newNorthwindversionin,44Accessdata,importingDAOusingJet,55–59Accessdataimportcodevs.
textdataimportcode,52Accessdatabase,importingdatafrom,43–46accessinformationpage,addingcontrolsto,159–160accesstabcontrolsettings,tableof,159–160ACE(AccessEngine),54ActiveCell.
CurrentRegionproperty,236ActiveChart.
PlotByproperty,202ActiveWorkbookobject,100–101ActiveXcustomfunctionalitywith,316–323inExcel2007programming,315and.
NETinExcelprogramming,315–350ActiveXcomponentscDataclassfrom,318cExcelNwindclassfrom,319usinginExcel2007projects,315–323ActiveXDataObjects(ADO),SeeADO(ActiveXDataObjects)AddReferencedialogbox,displaying,335AddWatchcommand,selecting,273AddWatchdialogbox,273AddChartmethodoptionalarguments,198placingandaligningchartswith,213–215addressinformationpage,addingcontrolsto,156–157addresstabcontrolsettings,tableof,156–157ADO(ActiveXDataObjects),usinginExcel2007,67–87ADO2.
8library,addingreferenceto,67–68ADOConnectionandCommandobjects,settingup,83–84ADOdatatypeenums,70–71ADOexamplesimportingSQLdatabasedonaselection,75–80importingSQLdata,67–74updatingSQLdata,80–87ADOrecordset,gettingdataintoandplacingintoworksheet,88ADODB.
Parameterobjects,fillingcolParamscollectionwith,84–85ADOTestmacro,VBAcode,316–318AdventureWorkssampledatabase,installing,67AppendXMLData()method,addingtostandardmodule,106applicationfolder,forExcelfile,123Applicationobject,ActiveWorkbookobjectaspropertyof,100–101AutoFillmethod,18AutoFitcommand,formattingworksheetwith,88,338AutoSumbutton,onHomeribbon,17AxisLabelsdialogboxopening,209settinglabelrangein,209–210Bbang(!
)characteraddingtocustomobjects,32basManagersmodule,addinginVBE,95Beveragecategorysalesinformation,modifyingMakePieChartmacrofor,212–213Beveragesaleschart,196withrowsandcolumnsswitched,197BindListToCollectionmethod,169BindListToRangemethod,169BirthYearfunction,254–255modifyingtouseDebug.
Print,258withMsgBoxdebugging,256blankdatarecords,effectonPivotTablereport,238–242breaklinetext,atbreakpoint,263–267breakmode,checkingvariablesin,267–275breakpoint,insertingincodefordebugging,263Index351CcAccessclassmoduleaddingtoproject,161codefor,168cAddressclassmoduleaddingtoproject,161codefor,165callstackfeature,270–271CallStackwindow,opening,270Cancelbutton,coding,348Cancelcommandbutton,codeforUserform1,147Candychart,testingcodefor,220–221cCustSurveyclassmodule,140–142codingintoUserForm,143–150creating,139–143creatingSavemethodin,142–143validationresults,148cDataclass,codefromActiveXcomponent,318–319cDataclassmodulecreating,90–95creatingGetDatafunctionfor,91creatinginVBE,88initializationandterminationmethods,92cDataobjects,using,95–96cds.
xmlfile,openingandaddingnewtitleto,105cEmployeeclassaddingPropertyLetandGetfunctions,31–32assigningpropertyvalues,34–35creating,31–33using,33–36cEquipmentclassmoduleaddingtoproject,161codefor,167cExcelNwindclass,codefromActiveXcomponent,319cExcelSetupclassmoduleaddingsetupandcleanupfunctionality,89codefor,92creatinginVBE,88settingandretrievingpropertyvalues,89–90workingwith,89–90cExcelSetupobjects,using,95–96cExcelUtilsclassexportingfilewithnewaddition,150exportingtoUserForm.
xlsmproject,138–139chartcreation,gettingstartedwith,193–202Chartobject,placingintoPowerPointslidetemplate,309chartplaceholder,gettinglocationof,309ChartToolscontextribbon,196Chart01.
xlsm,savinginmacro-enabledformat,193ChartByRowmacro,201–202charting,inExcel2007,193–221ChartObjectobjects,201ChartObjects.
Activatemethod,activatingachartwith,201ChartObjects.
Countproperty,218ChartTypeproperty,settingcharttypeusing,201CheckBoxcontrol,inToolboxwindow,38cHRDataclass,designingforHRWizardapplication,169–172classlibraryproject,creatingnew,328–335classmodules,29–36classname,291class-basedcode,benefitsofwriting,35–36classes,sampleandusage,31–36Classeslist,inObjectBrowser,26Class_Initializemethod,forEmployeeclass,33Class_Terminatemethod,forEmployeeclass,33cleanupcodeaddingtocCustSurveyclassmodule,141addingtoCreateChartSlidesTextsubroutine,310addingtoGetManagerssubroutine,96addingtoMakeWordDocsubroutine,293addingtoThisAddInShutdownmethod,337addingtoUserForm1,144ClearFormprocedure,144–145clientcode,addingto!
Northwind2ExcelObject,321–322cListManagerclass,addingmethods,169Closemethod,addingtobtnSave_Clickevent,346cmdCancelbutton'sClickevent,addingcodeto,188cmdPreviousbutton'sClickevent,186cmdSave_Clickevent,savingemployeerecordwith,188codemodulesaddingtoprojects,20–21standard,27–29codewindowfixingerrorin,272inVBE,6–9variablevaluesshownin,271–272withsplitpanes,10colParamscollection,fillingwithADODB.
Parameterobjects,84–85colReturncollection,addingprmvariableto,85–86Columncharttype,choosing,195INDEX352columnheadings,addingtoworksheet,337comboboxes,initializing,180–180ComboBoxcontrol,inToolboxwindow,38commandbuttons,settingsfor,155–156Command.
Executemethod,calling,86–87CommandButtoncontrol,inToolboxwindow,38contenttypes,inOfficedocuments,123–125[Content_Types]xmlfilecheckingnewpartsin,125–128forExcelfile,123controls,addingtoforms,154–160copyandpastemethod,19Copycommand,copyingformulaswith,18CopytoClipboardbutton,inObjectBrowser,26CopyFromRecordsetmethod,60ADOerrormessage,74countervariable,determiningformulalocationwith,24cPersonclassmoduleaddingcalltoIDPropertyLetfunction,162–163addingread-onlyFullNamepropertyto,164–165addingtoproject,161addingvariabledeclarationsto,161–162finishing,163–164initializingandsettingdefaults,162CreatePivotTabledialogbox,sectionsin,224–225CreateChartSlidessubroutine,creating,302–304CreateChartSlidesTextsubroutineaddingcleanupcodeto,310addingvariabledeclarationsto,308–309coding,308–313CreateObjectfunction,291–292CreatePivotTablemethod,230–231CreateTitleSlidesubroutine,creating,300–302cStepclassmodule,172–173cStepManagerclassmoduleaddingpropertiesto,174–175designingtomanagesteps,173–178propertiestable,175Ctrl-drag,usingfillhandlewith,18CurrentMapName()method,addingtocXMLclassmodule,112CurrentRegionproperty,237currReturnvariable,checkingvalueof,283CustomListsdialogbox,206custommacros,addingribbontorun,128–129CustomerSurveydatabase,savingdatato,133–139CustomerSurveyform,launching,147customUI.
xmlfile,creating,129–131cXMLclassaddingclientcodetotest,113–117addingpropertiesto,109–110buildingfunctionsfor,110–119puttingdataonworksheets,110DDAO.
SeeDataAccessObjects(DAO)DAOexamplesaddingreferencetoDAOlibrary,55–56importingAccessdatausingJet,55–59importingAccessdatausingODBC,60–65importingSQLdatausingODBC,65–67DAOJetobjectmodel,54DAOlibrary,addingreferenceto,55–56DAOobjects,common,54–55DAOODBCobjectmodel,60resultfromNorthwindCustomerstable,65dataaccesscode,macrogenerated,44–45dataaccesscomponent,creating,328–335DataAccessObjects(DAO),usinginExcel2007,54–67dataentryformcreatingsimple,133–150creatingwizard-styleUserForms,150–191dataimporttools,inExcel2007,43–54dataorientation,switchingfromcolumntorow,196–197DataPreviewwindow,withTextdatatypeapplied,51datarangeandlegendinformation,definingandsetting,218–220Dataribbon,Sortcommandon,203Datatab,SwitchRow/Columncommandon,197datatable,formatting,16–17DataAccessSample02.
xlsmworkbook,creating,48–50DataAccessSample05.
xlsmworkbook,creating,80–82DataBindings.
LoadSettingsmethod,106DataRegionStartproperty,using,89–90Debugmenu,249andtoolbarcommands,250–251optionsforsteppingthroughcode,261–267Debugobject,253toolbar,249,252using,257–260Debug.
Assertmethod,samplesubroutineusing,260Debug.
Printmethod,258–260INDEX353DebugExample01.
xlsmfilecopyingDebug.
Assertsubroutineinto,260downloadingandopening,254trappingtypemismatcherrorin,278–279Debugger'stoolkit,249–275debugginganderrorhandling,249–285insertingbreakpointincode,263loops,258samplecodefor,262VBEtoolsforsimple,253–260Departmentcombobox,boundtonamedrange,182Detailspane,inObjectBrowser,27Developerribbon,codeoptionson,3Developertab,displaying,3DisplayNameproperty,fixingerrorfiredby,47–48DLL,usinginproject,321DoClearSheet()subroutine,creating,95docPropsfolder,forExcelfile,125DoHeadingsmethod,addingtitlestoworksheetwith,346–347DynamicPivotTable,creationcodefor,234EEditSeriesdialogbox,208–209EmpDatadatabaseworksheetsampleinputvaluesandsaveddata,189–191sectionsin,151–152employeedata,addingtoworksheet,338Employeedatatype,36employeerecord,saving,187–188encapsulation,30equipmentinformationpage,addingcontrolsto,158equipmenttabcontrolsettings,tableof,158errorhandling,275–285.
Seealsodebugginganderrorhandling;exceptionhandlingbreakpointaddedin,282debuggingerrorhandler,282enablingforGetSalesTotalfunction,279rulestoliveby,278settingupprocedurefor,279trappingspecificerrors,278–282typemismatcherror,280–281errormessages,FileNotFound,278Excel,checkingversionof,74Excel2007addingcustomribbontoworkbook,119–131addingribbontoruncustommacros,128–129andADOrecordsets,74changingcodetoclassesin,87–96chartingin,193–221dataimporttools,43–54defaultchartingbehavior,196importingXMLinto,99–106inthe.
NETworld,323–349insidetheXMLfileformat,119–131OOPsolutionsin,87–96PivotTablesfeaturein,223–247simplifyingcodegeneratedby,46–48UserFormsin,133–191usingADOin,67–87usingDAOin,54–67usingXMLin,99–132ExcelOptionsdialogbox,Add-inslistin,341Excelproject,managedcodein,327–349ExcelTrustCenter.
SeeTrustCenterExcelUserForm,creating344–348ExcelVisualBasicEditor.
SeeVisualBasicEditor(VBE)Excelworkbook,howitfindsdata,340–341Excelworksheet,formatting,338ExcelVersionShortproperty,checkingExcelversionwith,74exceptionhandling,forGetDatamethod,338executionlinetext,atbreakpoint,263–267ExternalProcess,movingexecutionpointbackto,265FFileNameproperty,changing,331–332FileExistsfunction,addinginVBE,277FileNotFoundException,codetotrapfor,338fillhandle,usingwithCtrl-drag,18FindEmptyRowfunction,139For.
.
.
Nextloop,forCreateChartSlidesTextsubroutine,309–310FormatCellsdialogbox,242FormatAtNumbersCommasubroutine,running,242FormatFormmethod,addingcodefor,346Framecontrol,inToolboxwindow,38FullNameproperty,addingtocPersonclass,164–165function,28–29GGetAccessDatamacro,creating,43–46GetAccessData2function,creating,46–47GetChartInfo()subroutine,216–217GetDAOAccessJetmethod,57–59GetDatamethodcompletecodefor,334–335creating,95,337–341exceptionhandlingfor,338GetDatasubroutine,completecodefor,338–339INDEX354GetEmpDeptprocedure,addingtocXMLclassmodule,113–117GetEmpListfunction,creating,81–82GetInitialCellSelectionproperty,89–90GetManagerEmployeeListSQLmethod,testingcodewith,73GetManagerListcompletecodefor,75–76resultofrunningcode,77GetManagerssubroutineaddingmethodcallsandcleanupcodeto,96creating,95GetNewXMLDatamethodaddingtocXMLclassmodule,111modifyingtouseCurrentMapName,112–113GetNextIDmethod,addingtocCustSurveyclassmodule,141–141GetNorthwindDatamacro,running,322–323GetObjectfunction,syntaxforusing,292GetRowsmethod,74GetSalesTotalfunctionaddingawatch,273–275addingerrorhandler,279–282addingExit_Functionlinelabelto,280addingExit_Functionto,279codewitherrorhandling,281–282usingErrorResumeNext,284–285usingtocompleteloop,284variablelistfor,279GetSelectedManagerEmployeeListSQLsubroutine,77–80GetSubjectBodyfunctioncreating,290–291insertingdescriptivetextfrom,309GetTitlefunction,creating,290GetTitleBodyfunction,creating,290GetXMLDatafunction,addingtocXMLclassmodule,111–112copyingintonewworkbook,105creating,99–100GetXMLDatasubroutine,276–278GetXMLForExistingMapmethod,111–112GoBackbutton,inObjectBrowser,26GoForwardbutton,inObjectBrowser,26HHasMapsproperty,addingtocXMLclass,109Helpbutton,inObjectBrowser,26helperfunctions,creatingforWordreport,290–291HRworkbookcreatingobjectsfromcXMLclassin,117–119testingcodefor,119HRWizardaddingvariablestocStepManager,173classmoduleinProjectExplorerClassModulesfolder,178classmodulestable,161classes,160–161designingbusinessobjects,169managing,172–178testing,188–191HRWizardUserFormaddingadditionalpagesto,153–154addingcontrolsto,154–160addingnavigationto,183–186addingvariabledeclarationsto,178cleaningup,188coding,178–191controlstable,154–155initiallayoutfor,153initializing,178–182layingout,152opening,188running,181–182HRWizard.
xlsmfile,EmpDatadatabaseworksheet,150HumanResources.
uspUpdateEmployeePersonalInfo,82IIDproperty,addingtobusinessobjectclasses,161IDPropertyLetfunction,addingcallto,162–163Imagecontrol,inToolboxwindow,39Immediatewindow,10–11checkingvalueofvariablesin,268testingcEmployeeclassin,33InfoPathFormTemplateproject,addedinVSTOSE,325InitFormsubroutine,creating,180–182initializationandcleanupcode,addingtocCustSurveyclassmodule,141InitListssubroutine,adding,180–182InitWizardsubroutine,addingtoUserFormcode,179–180Insertribbon,selectingcharttypefrom,207InsertChartprocedure,addingtoStandardModule1,296InsertTextprocedure,addingtoStandardModule1,296IntelliSense,100iWherevariable,236JJetengine,54–59INDEX355KLLabelcontrol,inToolboxwindow,37Lenfunction,usingforUserForm1,146Librarydrop-downlistbox,inObjectBrowser,25ListBoxcontrol,inToolboxwindow,38ListMgrworksheet,contentsof,151ListObjects.
Addmethod,changingSourcepropertyof,47lists,managing,169Localswindow,11–12atwork,269–271Typemismatcherror13in,269loops,testingwithinImmediatewindow,268Mmacrorecording,14–20writinginVBE,20–24MacroRecorder,andCodeModules,1–41CreatePivotTablemethodcalledby,230macrosecuritycreatingmore,21–24settingsfor,1-4macro-enabledfiletypes,22macros,addingribbontoruncustom,128–129maillist.
csvdataimportedfrom,51withCommaselectedasdelimiter,49withTabselectedasdelimiter,49MakeBeverageSalesChartmacrolookingatcode,198–202modifiedversion,201recording,193–194MakeDynamicPivotTablesubroutine,creating,233–235MakePieChartmacroforcreatingpiechart,206modifyingforBeveragecategorysalesinfo,212–213MakePieChart2macro,creatingandrunning,212–213MakePivotTablemacroaddingnewworksheettoworkbookin,230codefor,229–235errorgeneratedby,232–233savingasmacro-enabledworkbook,232MakePowerPointPresentationsubroutineaddingtexttochartslides,308–313completing,304–306creating,300modifyingandrunningcallingprocedure,312–313runningthecode,306–308MakeWordDocsubroutineaddingchartstothereport,295–298creating,291–295finishedcodefor,296–297managedcode,inanExcelproject,327–349messageboxes,displayinginformationwith,253–257"Membersof"list,inObjectBrowser,26MicrosoftOfficeobjecttypes,291–292Microsofttools,forcreatingExcelprojectswithinVS2005,323–327MicrosoftWindowsVista,runningexampleson,43MonthlyTotalSalesAmountworksheet,activating,193–194MsgBoxdebugging,BirthYearfunctionwith,256–256MsgBoxfunctionargumentslist,255creatingmessageboxeswith,255–257MultiPagecontroladdingcommandbuttonsto,155determiningwhatnextpageshouldbe,185hidingallpagesexceptforfirst,181inToolboxwindow,38insertingnewpagein,153–154settingValueproperty,180–181MultiPage1control'sChangeevent,186Nnavigation,addingtoHRWizardUserForm,183–186.
NETadvantagesofvs.
ActiveX,323inExcel2007programming,315usingtoretrievedata,327–328.
NETcomponents,usinginExcel,323–349NewcommandbuttoncodeforUserForm1,146testing,149NewEmployeeUserForm,creating,344–348NewProjectdialogbox,VS2005forMSOffice2003projects,324!
Northwind2ExcelObject,321–322numberformat,changing,242NumberFormatproperty,242Nwind2Excel.
dllregisteringinWindowsXP,2000,orVista,319–320websiteaddressfordownloading,319NWindDataclassmodule,333–334INDEX356NWindData.
vbaddingcodetoclass,332–335changingclassnameto,331–332NWindDataAccessproject,addingtoSolutionExplorer,335NWindDataAddInprojectcreating,327–328NWindDataAddInSetupdeploymentproject,335NwindEmps01.
xlsxfile,downloadingandrenaming,125Oobject,definitionof,30Objectbox,incodewindow,8–9ObjectBrowserdisplaying,24–25windowelements,25–27Wordobjectsdisplayedin,298object-orientedprogramming(OOP)overview,39–41solutionsinExcel,87–96WikipediaObject_databasefor,172ODBCimportingAccessdatausing,60–65importingSQLdatausing,65–67retrievingAccess2007codevia,62–63runningthecode,63–65Office2007Add-inprojects,addedinVSTOSEinWindowsVista,326–327officeintegration,287–314OLEDB(ObjectLinkingandEmbeddingDatabase),successortoODBC,67OLEDBlibrary,referencing,333OnErrorGoTostatementaddingtoturnonerrortrapping,285syntaxforerrorhandling,275OpenXMLformat,filecontainer,120OptionExplicit,252–253OptionButton,inToolboxwindow,38Optionsdialogbox,showingVBEcoloroptions,264Overwritearguments,forappendingXMLdata,106–107PPageSettingsproperty,addingtocStepManagerclass,175–176ParameterobjectsappendingtoParameterscollection,86creatingADOandaddingtoCommandobject,70instantiatingandsettingproperties,85Parameterscollection,appendingParameterobjectsto,86personalinformationpage,addingcontrolsto,154–156piechartscreating,206–211dynamicallyplacing,216–221lookingatthecode,210–211makingselectionfor,206–207movingonworksheets,211–212selecting,208settingnameofdataseriesin,211storingdatacorrectlyfor,202–206summarizingwith,202–221updated,210PivotCaches.
Createmethod,arguments,230PivotField.
Captionproperty,changingcaptionswith,244PivotField.
NumberFormatproperty,242PivotTableFieldListpaneaddingfieldsto,231inExcel2007,228PivotTablereportchangingfieldnamesin,243–244changinglookof,245–247changingnumberformatin,242contentsof,225creatingusingdrag-and-drop,225–227defaultview,225effectofblankrecordson,238–242exampleofcompleted,227formattingto,238–242,245–247gettingcurrentsourcedatafor,236puttingdatainto,223–247refreshingdatain,235–238salessummarybycitywithinstate,227–228showingSumofQtyandSumofAmountfields,244updating,236PivotTableToolsribbon,Optionsribbonshownon,238PivotTable01.
xlsx,downloadingandopening,223/PivotTable02_Formatting.
xlsm,downloading,238PivotTables,223–247creatingandrecordingmacrofor,224–225newrowsaddedtosourcedata,237newsheetandstartingrangefor,231Positionpropertyfor,232whentouse,223PlaceChartsubroutine,creating,213–215PlaceChartDynamicsubroutineaddingvariabledeclarationsto,217–218completing,217–221creating,216INDEX357datarangeandlegendinformation,218–220gettingcoordinatesfromexistingchart,218readyformodifications,217testing,220–221PlaceDatamethod,addingcodefor,346PlaceDatasubroutine,addingtoThisAddinapplication,347Positionproperty,forPivotTables,232PowerPoint12.
0ObjectModel,addingareferenceto,299PowerPointDOM,coding,299–300PowerPointhelperfunctions,300–304PowerPointpresentation,creating,298–313PowerPointslidetemplate,fortextandchart,308ppLayoutTextAndChart,309PreviousButtonandNextButtonproperties,declaringWithEvents,177prmvariable,addingtocolReturncollection,85–86Procedure/Eventsbox,incodewindow,8–9ProjectExplorer,inVBE,5Project/Librarybox,inObjectBrowser,25PropertyGetandLet,addingtobusinessobjectclasses,161PropertyGetmethod,31PropertyLetmethod,31PropertySetmethod,31PropertySheet,inVBE,6–7QQuantityandSalesTotalvalues,makingblank,238–242QueryTableobject,members,45–46RR1C1notation,19–20Rangeobject,settingformulawith,23–24Range,rngDatavariable,assigningCurrentRegionofcellA1with,236RecordMacrodialogbox,14–20RefEditcontrol,inToolboxwindow,39ReferencesdialogboxaddingreferencetoPowerPoint12.
0ObjectModel,299addingreferencetoWordin,288–289Refreshcommand,236–237RefreshPivotTableFromWorksheetsubroutinecreatinginVBE,236–238Rochesterdatadisplayedafterrunning,238RefreshXMLmethod(function)addingtocXMLclassmodule,117addingtostandardmodule,105relationshipsforExceldocuments,125–128typesofinOfficedocuments,122–123_relsfolder,forExcelfile,121–123Removebutton,removingcertificateswith,3report,creatinginWord,287–298Resetbutton,stoppingcodeexecutionwith,271ResumeNext,usingtocompleteloop,284Resumestatement,283–285ribbon,addingtoruncustommacros,128–129ribbonextensibilitycustomizationfile,creating,128–129Runcommand,exampletoregisteraDLL,320RunSub/UserFormtoolbarbutton,181RunToCursorcommand,267SSalesByCategoryworksheet,202runningPlaceChartprocedureon,215salesdataandpiecharts,288Savebuttoninputvalidations,145performingDoAfterSavecleanup,146Userform1,145–146SavemethodsaddingtocHRDataclassmodule,171–172creatingincCustSurveyclassmodule,142–143SaveAsXMLDatamethod,107–108SaveEmployeemethod,HRWizardapplication,170SaveToFilemethod,addingtocXMLclassmodule,117ScrollBarcontrol,inToolboxwindow,39sDatavariablemodifying,237inRefreshPivotTableFromWorksheetsubroutine,236Searchbutton,inObjectBrowser,26SearchResultslist,inObjectBrowser,26SearchTextbox,inObjectBrowser,26SelectDatacommand,choosing,208SelectDataSourcedialogbox,208–210Selectmethod,198Selectobjects,inToolboxwindow,37Seriesnamerangereference,addedtoEditSeriesdialog,209SetNextStatementcommand,forresettingexecutionpoint,266–267SetSourceDatamethod,198,201,210,221SetupWorksheetsubroutine,creating,90sFormulavariable,23Show/HideSearchResultsbutton,inObjectBrowser,26INDEX358ShowFormmacro,runninginExcel,147–148ShowNextPagemethod,calling,185slides,buildingseriesof,299–313Sortdialogbox,203–204choosingCustomListin,205–206using"Thenby"drop-downlist,205Sourcepropertyassigningselectedrangeofdatato,200changingListObjects.
Addmethods,47spacervariable,213SpinButtoncontrol,inToolboxwindow,39Splitbars,inObjectBrowser,27SQLdataimportingusingADO,67–73updating,80–87SQLServer2005ManagementStudioExpress,installing,67–68StandardModule1creatingGetChartInfo()functionon,216opening,254StartWizardmacro,running,189StaticMacroRecorder-GeneratedPivotTableCreation,codefor,234StepIntocommand,264–265StepOutcommand,265StepOvercommand,265–266StoreDatamethod,functionof,183–185subprocedures(subroutines),28addingcleanupcodetoMakeWordDoc,293addingInitLists,180–182CreateChartSlidesText,308–313creatingCreateChartSlides,302–304creatingCreateTitleSlide,300–302creatingDoClearSheet,95creatingInitForms,180–182creatingTotalSales,21FormatAtNumbersComma,242GetChartInfo,216–217GetData,338–339GetManagers,95–96GetSelectedManagerEmployeeListSQL,77–80GetXMLData,276InitWizard,179–180MakeDynamicPivotTable,233–235MakePowerPointPresentation,300,304–306,308–313MakeWordDoc,291–298PlaceChart,213–215PlaceChartDynamic,217-220PlaceData,347RefreshPivotTableFromWorksheet,236–238SetupWorksheet,90–91TotalSales,21Summaryfieldheadings,modified,245Ttabledefaultversion,definedbyCreatePivotTablemethod,230tablename,definedbyCreatePivotTablemethod,230tableplacement,definedbyCreatePivotTablemethod,230TabStripcontrol,inToolboxwindow,38taskpane,creatingcustomusing.
NET,342–344TestLoopfunctionfordebuggingcode,262–267movingexecutionpointbackinto,265textdataimportcode,51–52importing,48–50textimportcode,macrorecorder-generated,51–54TextImportwizard,importingdatain,48TextBoxcontrol,inToolboxwindow,38TextFileColumnDataTypesEnums,53–54TextFileColumnDataTypesproperty,setting,53–54ThisAddInapplicationcodingCancelbutton,348running,348–349ThisAddIn.
vbfile,335–336ThisAddIn_Shutdownmethod,addingcleanupcodeto,337ThisAddIn_Startupmethod,instantiatingandcallingGetDatafunction,336titleandbodytext,insertingintoMakeWordDocsubroutine,292–293titleandsubtitle,puttingonworksheet,346–347ToggleButton,inToolboxwindow,38Toolboxwindowaddingcontrolstoformsin,36customizationoptions,37elements,37–39ToggleButtonin,38tools.
SeeMicrosofttoolstotals,macroforadding,17–20TotalSalesmacro,completed,23TotalSalessubroutine,creating,21Toursalesdata,inPivotTable01.
xlsx,223–224TransposeDim()function,74TrustCentermacrosecuritysettingsin,1–2MacroSettingsoptions,4trustedlocations,2–3trustedpublishers,2Try.
.
.
Catchblock,catchingexceptionswith,334INDEX359Typemismatcherror13,debugging,269–271Typeproperty,198typestatements,29UUFormConfig,inHRWizard.
xlmsworkbook,173UpdateEmpPersonalInfostoredprocedure,writing,83–87UserForm,SeealsoHRWizardUserForm;UserForm1;UserFormslayingout,152Toolboxcontrols,135–136UserForm.
xlsmproject,importingcExcelUtilsclassto,138–139UserForm1coding,143–150settingsandcontrols,136–137UserForms,36–39,133–191addingtoprojects,134–135coding,143–150creatingwizard-styledataentry,150–191examplewithcontrolsadded,39Excelnamingof,36insertingintoprojects,36UserForm_Initializeevent,144addingcodeto,178–179UserForm_Terminateevent,144addingcodeto,188uspGetManagerEmployeesstoredprocedure,parameterstakenby,68Vvalidationcode,addingtocCustSurveyclassmodule,141–142ValueFieldSettingsdialogbox,changingfieldnamesin,243Value2property,currentcell'svaluestoredin,269variablevalues,viewing,267–275variables,checkinginbreakmode,267–275VBcode,benefitsofvs.
VBAcode,316VBA(VisualBasicApplication),callingparameterizedSQLin,71–73VBAcode,benefitsofvs.
VBcode,316VBAcodewindowObjectbox,choosingClassfrom,32VBEcodewindow.
SeecodewindowViewDefinitionbutton,inObjectBrowser,26VisualBasicdevelopmentenvironment,4–14VisualBasicEditor(VBE),5,14addingFileExistsfunctionin,277opening,254,288Optionsdialogboxshowingcoloroptions,264panesin,5updatingPivotTablein,236–238whenbreakpointisreached,264writingamacroin,20–24VisualStudio2005startpage,recentprojectssectionof,327VisualStudioToolsforOfficeSecondEdition(VSTOSE).
SeeVSTOSEVSTOandVSTOSEprojecttemplates,323toolsforcreatingExcelprojects,323–327VSTOSEExcel2003andExcel2007Add-ins,325newprojecttypesincludedin,324websiteaddressforfreedownload,327WWatchwindowelements,13–14handlingwatchedvaluesin,272–275watchtypes,272–273websiteaddressexamplefilesandsourcecode,20forfreeVSTOSEdownload,327forrunningexamplesonVista,43WikipediaObject_database,172WindowsVistaregisteringNwind2Excel.
dllin,320runningexampleson,43With.
.
.
EndWithblockaddingtoFor.
.
.
Nextloop,309settingarrayelementswithin,214wizard-styleUserForms,creating,150–191Wordcreatinganinstanceof,291–295creatingreportin,287–298openingandloadingnewdocumentinto,291Word2007DOM,creatingsummaryreportusing,287–298Wordobjects,displayedintheObjectBrowser,298workbookaddingcustomribbonto,119–131importingdatainto,47–48macrocodegeneratederror,47–48Workbookobject,XmlImportmethodof,99workbook.
xml.
relsfile,contentsof,125Worksheetproperty,HRWizardapplication,170XXIXmlImportResultmembers,inObjectBrowserwindow,102xlfoldercontents,123\xl\connections.
xmlfile,126–127INDEX360xl\queryTables\queryTable1.
xmlfile,127–128xlAutoFillTypeenumerations,tableof,18–19xlChartTypedatatype,enumerations,198–200XlConsolidationFunctionenumeration,choicesforFunctionproperty,241–242xlPivotFieldOrientationenumerations,tableof,231XlRowCol,enumerations,202XML,usinginExcel2007,99–132XMLdataappendingoroverwritinginworkbook,106–107saving,107–108XMLdataclass,building,108–119XML_data.
xlsmdownloadingandopening,276DebuggingandErrorHandling,249–286XMLfilecreatingtomodifytheUI,129–131imported,103XMLmaps,102–106XMLSchemadialogbox,102XMLschemas.
SeeXMLmaps,132XMLSourcewindow,withartistelementselected,104XmlImportfunctionandarguments,101–103XmlImportmethodappendingXMLdatawith,106–107ofWorkbookobject,99XMLMapscollection,DataBindingpropertyRefreshmethod,105–125INDEX361