book

Business Solutions VBA and Macros for Microsoft® Office Excel® 2007

by Bill Jelen, Tracy Syrstad

August 2007

Beginner to intermediate

624 pages

14h 46m

English

Que

Read now

Unlock full access

Dedications
Getting Results with VBAWhat Is in This BookGetting Up the Learning CurveExcel VBA PowerThe Techie Stuff Needed to Produce Applications for OthersDoes This Book Teach Excel?The Future of VBA and Windows Versions of ExcelVersionsSpecial Elements and Typographical ConventionsCode FilesNext Steps
The Power of ExcelBarriers to EntryThe Macro Recorder Doesn’t Work!Visual Basic Is Not Like BASICThe Good News—It Is Easy to Climb the Learning CurveThe Great News—Excel with VBA Is Worth the EffortKnowing Your Tools—The Developer RibbonMacro SecurityAdding a Trusted LocationUsing Macro Settings to Enable Macros in Workbooks Outside of Trusted LocationsUsing Disable All Macros with NotificationOverview of Recording, Storing, and Running a MacroFilling Out the Record Macro DialogRunning a MacroCreating a Macro ButtonAssigning a Macro to a Form Control, a Text Box, or a ShapeUsing New File Types in Excel 2007Understanding the Visual Basic EditorVB Editor SettingsCustomizing VB Editor Options SettingsEnabling Digital SignaturesThe Project ExplorerThe Properties WindowUnderstanding Shortcomings of the Macro RecorderExamining Code in the Programming WindowRunning the Same Macro on Another Day Produces Undesired ResultsA Possible Solution: Using Relative References When RecordingNext Steps: Learning VBA Is the Solution
I Can’t Understand This CodeUnderstanding the Parts of VBA “Speech”Is VBA Really This Hard? No!VBA Help Files—Using F1 to Find AnythingUsing Help TopicsExamining Recorded Macro Code—Using the VB Editor and HelpOptional ParametersDefined ConstantsProperties Can Return ObjectsUsing Debugging Tools to Figure Out Recorded CodeStepping Through CodeMore Debugging Options—BreakpointsBacking Up or Moving Forward in CodeNot Stepping Through Each Line of CodeQuerying Anything While Stepping Through CodeUsing the Immediate WindowQuerying by HoveringQuerying by Using a Watch WindowUsing a Watch to Set a BreakpointUsing a Watch on an ObjectThe Ultimate Reference to All Objects, Methods, PropertiesFive Easy Tips for Cleaning Up Recorded CodeTip 1: Don’t Select AnythingTip 2: Ride the Range from the Bottom to Find Last RowTip 3: Use Variables to Avoid Hard-Coding Rows and FormulasTip 4: Learn to Copy and Paste in a Single StatementTip 5: Use With...End With If You Are Performing Multiple Actions to the Same Cell or Range of CellsPutting It All Together—Fixing the Recorded CodeNext Steps
The Range ObjectUsing the Upper-Left and Lower-Right Corners of a Selection to Specify a RangeNamed RangesShortcut for Referencing RangesReferencing Ranges in Other SheetsReferencing a Range Relative to Another RangeUsing the Cells Property to Select a RangeUsing the Cells Property in the Range PropertyUsing the Offset Property to Refer to a RangeUsing the Resize Property to Change the Size of a RangeUsing the Columns and Rows Properties to Specify a RangeUsing the Union Method to Join Multiple RangesUsing the Intersect Method to Create a New Range from Overlapping RangesUsing the ISEMPTY Function to Check Whether a Cell Is EmptyUsing the CurrentRegion Property to Quickly Select a Data RangeUsing the Areas Collection to Return a Noncontiguous RangeReferencing TablesNext Steps
Creating User-Defined FunctionsSharing UDFsUseful Custom Excel FunctionsSet the Current Workbook’s Name in a CellSet the Current Workbook’s Name and File Path in a CellCheck Whether a Workbook Is OpenCheck Whether a Sheet in an Open Workbook ExistsCount the Number of Workbooks in a DirectoryRetrieve USERIDRetrieve Date and Time of Last SaveRetrieve Permanent Date and TimeValidate an Email AddressSum Cells Based on the Interior ColorCount Unique ValuesRemove Duplicates from a RangeFind the First Non-Zero-Length Cell in a RangeSubstitute Multiple CharactersRetrieve Numbers from Mixed TextConvert Week Number into DateSeparate Delimited StringSort and ConcatenateSort Numeric and Alpha CharactersSearch for a String within TextReverse the Contents of a CellMultiple MaxReturn Hyperlink AddressReturn the Column Letter of a Cell AddressStatic RandomUsing Select Case on a WorksheetNext Steps
For...Next LoopsUsing Variables in the For StatementVariations on the For...Next LoopExiting a Loop Early after a Condition Is MetNesting One Loop Inside Another LoopDo LoopsUsing the While or Until Clause in Do LoopsWhile...Wend LoopsThe VBA Loop: For EachObject VariablesFlow Control: Using If...Then...Else and Select CaseBasic Flow Control: If...Then...ElseConditionsIf...Then...End IfEither/Or Decisions: If...Then...Else...End IfUsing If...Else If...End If for Multiple ConditionsUsing Select Case...End Select for Multiple ConditionsComplex Expressions in Case StatementsNesting If StatementsNext Steps

Referring to Cells: A1 Versus R1C1 ReferencesSwitching Excel to Display R1C1 Style ReferencesThe Miracle of Excel FormulasEnter a Formula Once and Copy 1,000 TimesThe Secret—It Is Not That AmazingExplanation of R1C1 Reference StyleUsing R1C1 with Relative ReferencesUsing R1C1 with Absolute ReferencesUsing R1C1 with Mixed ReferencesReferring to Entire Columns or Rows with R1C1 StyleReplacing Many A1 Formulas with a Single R1C1 FormulaBuilding the TableAn Interesting TwistRemembering Column Numbers Associated with Column LettersConditional Formatting—R1C1 RequiredSetting Up Conditional Formatting in the User InterfaceSetting Up Conditional Formats in VBAArray Formulas Require R1C1 FormulasNext Steps
If It’s Changed in the Front End, It’s Changed in VBAThe RibbonChartsPivot TablesConditional FormattingTablesSortingSmartArtThe Macro Recorder Won’t Record Actions That It Did Record in Earlier Excel VersionsLearning the New Objects and MethodsCompatibility ModeVersionExcel8CompatibilityModeNext Steps
Excel NamesGlobal Versus Local NamesAdding NamesDeleting NamesAdding CommentsTypes of NamesFormulasStringsNumbersTablesUsing Arrays in NamesReserved NamesHiding NamesChecking for the Existence of a NameNext Steps
Levels of EventsUsing EventsEvent ParametersEnabling EventsWorkbook EventsWorkbook_Activate()Workbook_Deactivate()Workbook_Open()Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)Workbook_BeforePrint(Cancel As Boolean)Workbook_BeforeClose(Cancel As Boolean)Workbook_NewSheet(ByVal Sh As Object)Workbook_WindowResize(ByVal Wn As Window)Workbook_WindowActivate(ByVal Wn As Window)Workbook_WindowDeactivate(ByVal Wn As Window)Workbook_AddInInstall()Workbook_AddInUninstallWorkbook_SheetActivate(ByVal Sh As Object)Workbook_SheetBeforeDoubleClick (ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)Workbook_SheetCalculate(ByVal Sh As Object)Workbook_SheetChange (ByVal Sh As Object, ByVal Target As Range)Workbook_Sync(ByVal SyncEventType As Office.MsoSyncEventType)Workbook_SheetDeactivate (ByVal Sh As Object)Workbook_SheetFollowHyperlink (ByVal Sh As Object, ByVal Target As Hyperlink)Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)Workbook_PivotTableCloseConnection(ByVal Target As PivotTable)Workbook_PivotTableOpenConnection(ByVal Target As PivotTable)Workbook_RowsetComplete(ByVal Description As String, ByVal Sheet As String, ByVal Success As Boolean)Worksheet EventsWorksheet_Activate()Worksheet_Deactivate()Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)Worksheet_Calculate()Worksheet_Change(ByVal Target As Range)Worksheet_SelectionChange(ByVal Target As Range)Worksheet_FollowHyperlink(ByVal Target As Hyperlink)Chart Sheet EventsEmbedded ChartsChart_Activate()Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)Chart_BeforeRightClick(Cancel As Boolean)Chart_Calculate()Chart_Deactivate()Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)Chart_Resize()Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)Chart_SeriesChange(ByVal SeriesIndex As Long, ByVal PointIndex As Long)Chart_DragOver()Chart_DragPlot()Application-Level EventsAppEvent_AfterCalculate()AppEvent_NewWorkbook(ByVal Wb As Workbook)AppEvent_SheetActivate (ByVal Sh As Object)AppEvent_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)AppEvent_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)AppEvent_SheetCalculate(ByVal Sh As Object)AppEvent_SheetChange(ByVal Sh As Object, ByVal Target As Range)AppEvent_SheetDeactivate(ByVal Sh As Object)AppEvent_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)AppEvent_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)AppEvent_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)AppEvent_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window)AppEvent_WindowResize(ByVal Wb As Workbook, ByVal Wn As Window)AppEvent_WorkbookActivate(ByVal Wb As Workbook)AppEvent_WorkbookAddinInstall(ByVal Wb As Workbook)AppEvent_WorkbookAddinUninstall(ByVal Wb As Workbook)AppEvent_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)AppEvent_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)AppEvent_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)AppEvent_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)AppEvent_WorkbookOpen(ByVal Wb As Workbook)AppEvent_WorkbookPivotTableCloseConnection(ByVal Wb As Workbook, ByVal Target As PivotTable)AppEvent_WorkbookPivotTableOpenConnection(ByVal Wb As Workbook, ByVal Target As PivotTable)AppEvent_WorkbookRowsetComplete(ByVal Wb As Workbook, ByVal Description As String, ByVal Sheet As String, ByVal Success As Boolean)AppEvent_WorkbookSync(ByVal Wb As Workbook, ByVal SyncEventType As Office.MsoSyncEventType)Next Steps
User Interaction MethodsInput BoxesMessage BoxesCreating a UserformCalling and Hiding a UserformProgramming the UserformUserform EventsProgramming ControlsUsing Basic Form ControlsUsing Labels, Text Boxes, and Command ButtonsDeciding Whether to Use List Boxes or Combo Boxes in FormsUsing the MultiSelect Property of a List BoxAdding Option Buttons to a UserformAdding Graphics to a UserformUsing a Spin Button on a UserformUsing the MultiPage Control to Combine FormsVerifying Field EntryIllegal Window ClosingGetting a FilenameNext Steps
Charting in Excel 2007Coding for New Charting Features in Excel 2007Referencing Charts and Chart Objects in VBA CodeCreating a ChartSpecifying the Size and Location of a ChartLater Referring to a Specific ChartRecording Commands from the Layout or Design RibbonsSpecifying a Built-in Chart TypeSpecifying a Template Chart TypeChanging a Chart’s Layout or StyleUsing SetElement to Emulate Changes on the Layout RibbonChanging a Chart Title Using VBAEmulating Changes on the Format RibbonUsing the Format Method to Access New Formatting OptionsChanging an Object’s FillFormatting Line SettingsFormatting Glow SettingsFormatting Shadow SettingsFormatting Reflection SettingsFormatting Soft EdgesFormatting 3-D Rotation SettingsChanging the Bevel and 3-D FormatUsing the Watch Window to Discover Object SettingsCreating Advanced ChartsCreating True Open-High-Low-Close Stock ChartsCreating Bins for a Frequency ChartCreating a Stacked Area ChartExporting a Chart as a GraphicCreating a Dynamic Chart in a UserformCreating Pivot ChartsNext Steps
Advanced Filter Is Easier in VBA Than in ExcelUsing Advanced Filter to Extract a Unique List of ValuesExtracting a Unique List of Values with the User InterfaceChange the List Range to a Single ColumnChange the List Range to a Single ColumnExtracting a Unique List of Values with VBA CodeGetting Unique Combinations of Two or More FieldsUsing Advanced Filter with Criteria RangesJoining Multiple Criteria with a Logical ORJoining Two Criteria with a Logical ANDOther Slightly Complex Criteria RangesThe Most Complex Criteria—Replacing the List of Values with a Condition Created as the Result of a FormulaSetting Up a Condition as the Result of a FormulaUsing Formula-Based Conditions with VBAUsing Formula-Based Conditions to Return Above-Average RecordsUsing Filter in Place in Advanced FilterCatching No Records When Using Filter in PlaceShowing All Records after Filter in PlaceUsing Filter in Place with Unique Records OnlyThe Real Workhorse: xlFilterCopy with All Records Rather than Unique Records OnlyCopying All ColumnsCopying a Subset of Columns and ReorderingUsing AutoFilterEnabling AutoFilter with CodeTurning Off a Few Drop-Downs in the AutoFilterFiltering a Column Using AutoFiltersSelecting Multiple Values from a FilterSelecting a Dynamic Date Range Using AutoFiltersFiltering Based on Color or IconNext Steps
Introducing Pivot TablesUnderstanding VersionsNew in Excel 2007Creating a Vanilla Pivot Table in the Excel InterfaceUnderstanding New Features in Excel 2007 Pivot TablesBuilding a Pivot Table in Excel VBADefining the Pivot CacheCreating and Configuring the Pivot TableGetting a Sum Rather Than a CountLearning Why You Cannot Move or Change Part of a Pivot ReportDetermining Size of a Finished Pivot TableCreating a Report Showing Revenue by ProductEliminating Blank Cells in the Values AreaEnsuring Table Layout Is UtilizedControlling the Sort Order with AutoSortChanging Default Number FormatSuppressing Subtotals for Multiple Row FieldsSuppressing Grand Total for RowsHandling Additional Annoyances When Creating Your Final ReportCreating a New Workbook to Hold the ReportCreating a Summary on a Blank Report WorksheetFilling the Outline ViewHandling Final FormattingAdding SubtotalsPutting It All TogetherAddressing Issues with Two or More Data FieldsCalculated Data FieldsSummarizing Date Fields with GroupingUnderstanding the Group Method in VBAGroup by WeekMeasuring Order Lead Time by Grouping Two Date FieldsUsing Advanced Pivot Table TechniquesUsing AutoShow to Produce Executive OverviewsUsing ShowDetail to Filter a RecordsetCreating Reports for Each Region or ModelManually Filtering Two or More Items in a Pivot FieldControlling the Sort Order ManuallyUsing Sum, Average, Count, Min, Max, and MoreCreating Report PercentagesPercentage of TotalPercentage Growth from Previous MonthPercentage of a Specific ItemRunning TotalUsing New Pivot Table Features in Excel 2007Using the New FiltersApplying a Table StyleChanging the Layout From the Design RibbonNext Steps
File OperationsList Files in a DirectoryImport CSVRead Entire CSV to Memory and ParseCombining and Separating WorkbooksSeparate Worksheets into WorkbooksCombine WorkbooksFilter and Copy Data to Separate WorksheetsExport Data to WordWorking with Cell CommentsList CommentsResize CommentsResize Comments with CenteringPlace a Chart in a CommentUtilities to Wow Your ClientsUsing Conditional Formatting to Highlight Selected CellHighlight Selected Cell Without Using Conditional FormattingCustom Transpose DataSelect/Deselect Noncontiguous CellsTechniques for VBA ProsPivot Table Drill-DownSpeedy Page SetupCalculating Time to Execute CodeCustom Sort OrderCell Progress IndicatorProtected Password BoxChange CaseSelecting with SpecialCellsActiveX Right-Click MenuCool ApplicationsHistorical Stock/Fund QuotesUsing VBA Extensibility to Add Code to New WorkbooksNext Steps
Introduction to Data VisualizationsNew VBA Methods and Properties for Data VisualizationsAdding Data Bars to a RangeAdding Color Scales to a RangeAdding Icon Sets to a RangeSpecifying an Icon SetSpecifying Ranges for Each IconUsing Visualization TricksCreating an Icon Set for a Subset of a RangeUsing Two Colors of Data Bars in a RangeUsing Other Conditional Formatting MethodsFormatting Cells That Are Above or Below AverageFormatting Cells in the Top 10 or Bottom 5Formatting Unique or Duplicate CellsFormatting Cells Based on Their ValueFormatting Cells That Contain TextFormatting Cells That Contain DatesFormatting Cells That Contain Blanks or ErrorsUsing a Formula to Determine Which Cells to FormatHighlight the First Unique Occurrence of Each Value in a RangeHighlight the Entire Row for the Largest Sales ValueUsing the New NumberFormat PropertyNext Steps
Getting Data from the WebManually Creating a Web Query and Refreshing with VBAUsing VBA to Update an Existing Web QueryBuilding a New Web Query with VBAUsing Streaming DataUsing Application.OnTime to Periodically Analyze DataScheduled Procedures Require Ready ModeSpecifying a Window of Time for an UpdateCanceling a Previously Scheduled MacroClosing Excel Cancels All Pending Scheduled MacrosScheduling a Macro to Run x Minutes in the FutureScheduling a Verbal ReminderScheduling a Macro to Run Every Two MinutesPublishing Data to a Web PageUsing VBA to Create Custom Web PagesUsing Excel as a Content Management SystemBonus: FTP from ExcelTrusting Web ContentNext Steps
What Is XML?Simple XML RulesUniversal File FormatXML as the New Universal File FormatThe Alphabet Soup of XMLMicrosoft’s Use of XML as a File TypeHow Excel 2007 Stores Workbooks with XMLNext Steps
Early BindingCompile Error: Can’t Find Object or LibraryLate BindingCreating and Referencing ObjectsKeyword NewCreateObject FunctionGetObject FunctionUsing Constant ValuesUsing the Watch Window to Retrieve the Real Value of a ConstantUsing the Object Browser to Retrieve the Real Value of a ConstantUnderstanding Word’s ObjectsDocument ObjectCreate a New Blank DocumentOpen an Existing DocumentSave Changes to a DocumentClose an Open DocumentPrint a DocumentSelection ObjectHomeKey/EndKeyTypeTextRange ObjectDefine a RangeFormat a RangeBookmarksControlling Word’s Form FieldsNext Steps
Declare an ArrayMultidimensional ArraysFill an ArrayEmpty an ArrayArrays Can Make It Easier to Manipulate Data, But Is That All?Dynamic ArraysPassing an ArrayNext Steps
Importing from Text FilesImporting Text Files with Fewer Than 1,084,576 RowsOpening a Fixed-Width FileOpening a Delimited FileReading Text Files with More Than 1,084,576 RowsReading Text Files One Row at a TimeReading Text Files with More Than 1,084,576 RowsWriting Text FilesNext Steps
ADO Versus DAOThe Tools of ADOAdding a Record to the DatabaseRetrieving Records from the DatabaseUpdating an Existing RecordDeleting Records via ADOSummarizing Records via ADOOther Utilities via ADOChecking for Existence of TablesChecking for Existence of a FieldAdding a Table On the FlyAdding a Field On the FlyNext Steps
Inserting a Class ModuleTrapping Application and Embedded Chart EventsApplication EventsEmbedded Chart EventsCreating a Custom ObjectUsing a Custom ObjectUsing Property Let and Property Get to Control How Users Utilize Custom ObjectsCollectionsCreating a Collection in a Standard ModuleCreating a Collection in a Class ModuleUser-Defined Types (UDTs)Next Steps
Using the UserForm Toolbar in the Design of Controls on UserformsMore Userform ControlsCheck BoxesTab StripsRefEditToggle ButtonsUsing a Scrollbar as a Slider to Select ValuesControls and CollectionsModeless UserformsUsing Hyperlinks in UserformsAdding Controls at RuntimeResizing the Userform On-the-FlyAdding a Control On-the-FlySizing On-the-FlyAdding Other ControlsAdding an Image On-the-FlyPutting It All TogetherAdding Help to the UserformShowing Accelerator KeysAdding Control Tip TextCreating the Tab OrderColoring the Active ControlTransparent FormsNext Steps
What Is the Windows API?Understanding an API DeclarationUsing an API DeclarationAPI ExamplesRetrieve the Computer NameCheck Whether an Excel File Is Open on a NetworkRetrieve Display-Resolution InformationCustom About DialogDisable the X for Closing a UserformRunning TimerPlaying SoundsRetrieving a File PathFinding More API DeclarationsNext Steps
What Happens When an Error OccursDebug Error Inside Userform Code Is MisleadingBasic Error Handling with the On Error GoTo SyntaxGeneric Error HandlersHandling Errors by Choosing to Ignore ThemSuppressing Excel WarningsEncountering Errors on PurposeTrain Your ClientsErrors While Developing Versus Errors Months LaterRuntime Error 9: Subscript Out of RangeRunTime Error 1004: Method Range of Object Global FailedThe Ills of Protecting CodeMore Problems with PasswordsErrors Caused by Different VersionsNext Steps
Out with the Old, In with the NewWhere to Add Your Code: customui Folder and FileCreating the Tab and GroupAdding a Control to Your RibbonAccessing the File StructureUnderstanding the RELS FileRenaming the Excel File and Opening the WorkbookRibbonCustomizerUsing Images on ButtonsMicrosoft Office iconsCustom Icon ImagesTroubleshooting Error MessagesThe Attribute “Attribute Name” on the Element “customui Ribbon” Is Not Defined in the DTD/SchemaIllegal Qualified Name CharacterElement “customui Tag Name” Is Unexpected According to Content Model of Parent Element “customui Tag Name”Excel Found Unreadable ContentWrong Number of Arguments or Invalid Property AssignmentNothing HappensOther Ways to Run a MacroKeyboard ShortcutAttach a Macro to a Command ButtonAttach a Macro to an ActiveX ControlRunning a Macro from a HyperlinkNext Steps
Characteristics of Standard Add-InsConverting an Excel Workbook to an Add-InUsing Save As to Convert a File to an Add-InUsing the VB Editor to Convert a File to an Add-InHaving Your Client Install the Add-InStandard Add-Ins Are Not SecureClosing Add-InsRemoving Add-InsUsing a Hidden Workbook as an Alternative to an Add-InNext Steps

Content preview from Business Solutions VBA and Macros for Microsoft® Office Excel® 2007

Chapter 12. Data Mining with Advanced Filter

IN THIS CHAPTER

Advanced Filter Is Easier in VBA Than in Excel	249
Using Advanced Filter to Extract a Unique List of Values	250
Using Advanced Filter with Criteria Ranges	257
Using Filter in Place in Advanced Filter	265
The Real Workhorse: `xlFilterCopy` with All Records Rather than Unique Records Only	267
Using AutoFilter	274
Next Steps	280

Advanced Filter Is Easier in VBA Than in Excel

Using the arcane Advanced Filter command is so hard in the Excel user interface that it is pretty rare to find someone who enjoys using it regularly. In versions of Excel prior to 2007, the AutoFilter command was probably used by most people rather than the Advanced Filter. In Excel 2007, Microsoft renamed the AutoFilter to Filter and ...