Part VAdvanced Programming Techniques
Chapter 16 Developing Excel Utilities with VBA.............513
About Excel Utilities .............................513
Using VBA to Develop Utilities .....................514
What Makes a Good Utility? .......................515
Text Tools: The Anatomy of a Utility.................515
Background for Text Tools..........................516
Project goals for Text Tools .........................516
How the Text Tools utility works......................517
The Text Tools workbook...........................517
The UserForm for the Text Tools utility .................518
The ThisWorkbook code module......................520
The Module1 VBA module..........................520
The UserForm1 code module ........................522
Making the Text Tools utility efficient..................523
Saving the Text Tools utility settings...................525
Implementing Undo ..............................526
Post-mortem of the project .........................528
Understand the Text Tools utility .....................529
More about Excel Utilities.........................529
Chapter 17 Working with Pivot Tables .....................531
An Introductory Pivot Table Example ................531
Creating a pivot table.............................532
Examining the recorded code for the pivot table...........534
Cleaning up the recorded pivot table code...............534
Creating a More Complex Pivot Table................535
Data for a more complex pivot table...................535
The code that created the pivot table...................536
How the more complex pivot table works ...............538
Creating a Pivot Table from an External Database.......539
Creating Multiple Pivot Tables......................541
Modifying Pivot Tables ...........................544
Chapter 18 Working with Charts ..........................547
Chart locations..................................547
The Chart object model............................548
Recording Chart Macros ..........................550
Chart creation macro recorder output ..................550
Cleaning up the chart creation macro recorder output.......552
Common VBA Charting Techniques..................553
Using VBA to activate a chart .......................553
Using VBA to deactivate a chart......................555
Determining whether a chart is activated................555
Deleting from the ChartObjects or Charts collection.........556
Using VBA to apply chart formatting ..................557
Looping through all charts..........................558
Sizing and aligning ChartObjects .....................559
More Charting Examples..........................560
Using names in a SERIES formula.....................561
Using VBA to specify the data used by a chart............563
Using VBA to determine the ranges used in a chart.........566
Using VBA to display arbitrary data labels on a chart.......570
Displaying a chart in a UserForm.....................572
Understanding Chart Events .......................574
An example of using Chart events ....................575
Enabling events for an embedded chart.................578
Example: Using Chart events with an embedded chart.......580
VBA Charting Tricks .............................583
Printing embedded charts on a full page ................583
Creating a "dead" chart............................583
Displaying text with the MouseOver event...............585
Animated charts.................................587
Creating a hypocycloid chart........................588
Creating a "clock" chart............................589
Charting Tricks That Don't Use Macros ...............591
Controlling a data series by using Autofiltering ...........592
Storing multiple charts on a chart sheet.................593
Creating a self-expanding chart......................594
Creating an interactive chart ........................601
Chapter 19 Understanding Excel's Events...................605
Event Types That Excel Can Monitor.................606
What You Should Know about Events................607
Understanding event sequences ......................607
Where to put event handler procedures .................607
Disabling events.................................609
Entering event handler code.........................610
Event handler procedures that use arguments.............611
Workbook-Level Events...........................613
The Open event .................................614
The Activate event ...............................615
The SheetActivate event ...........................616
The NewSheet event..............................616
The BeforeSave event .............................616
The Deactivate event..............................617
The BeforePrint event.............................618
The BeforeClose event.............................619
Worksheet Events ...............................620
The Change event................................621
Monitoring a specific range for changes ................622
The SelectionChange event .........................626
The BeforeRightClick event .........................627
Chart Events ...................................627
Application Events ..............................629
Enabling Application-level events.....................631
Determining when a workbook is opened................632
Monitoring Application-level events...................633
UserForm Events ................................634
Events Not Associated with an Object................636
The OnTime event ...............................636
The OnKey event ................................638
Chapter 20 Interacting with Other Applications .............641
Starting an Application from Excel..................641
Using the VBA Shell function........................642
Using the Windows ShellExecute API function............644
Activating an Application with Excel ................645
Using AppActivate...............................645
Activating a Microsoft Office application................646
Running Control Panel Dialog Boxes.................647
Using Automation in Excel ........................648
Working with foreign objects using Automation...........649
Early versus late binding...........................649
A simple example of late binding.....................652
Controlling Word from Excel........................653
Controlling Excel from another application ..............656
Sending Personalized E-Mail via Outlook .............659
Sending E-Mail Attachments from Excel..............663
Chapter 21 Creating and Using Add-Ins ....................667
What Is an Add-In?..............................667
Comparing an add-in with a standard workbook...........668
Why create add-ins?..............................668
Understanding Excel's Add-In Manager...............670
Creating an Add-In ..............................671
Setting up the workbook for the example add-in ..........673
Testing the workbook for the example add-in.............673
Adding descriptive information for the example add-in......673
Creating an add-in...............................674
Installing an add-in ..............................675
Distributing an add-in.............................676
Modifying an add-in..............................676
Comparing XLA and XLS Files .....................678
XLS and XLA file size and structure...................678
XLA file VBA collection membership ..................678
Visibility of XLS and XLA files.......................679
Worksheets and chart sheets in XLS and XLA files .........679
Accessing VBA procedures in an add-in ................680
Manipulating Add-Ins with VBA....................683
Understanding the AddIns collection...................683
AddIn object properties............................685
AddIn object events ..............................688
Optimizing the Performance of Add-ins...............689
Maximizing code speed in add-ins ....................689
Controlling an add-in's file size ......................690
Special Problems with Add-Ins .....................691
Ensuring that an add-in is installed....................691
Referencing other files from an add-in .................692
Detecting the proper Excel version for your add-in .........693
Part VI Developing Applications
Chapter 22 Creating Custom Toolbars ......................697
Toolbar Manipulations............................698
How Excel Handles Toolbars .......................698
Storing toolbars.................................698
When toolbars don't work correctly ...................699
Manipulating Toolbars and Buttons Manually ..........700
About command bar customization mode ...............700
Distributing custom toolbars ........................704
Manipulating the CommandBars Collection............706
Command bar types ..............................706
Listing all CommandBar objects ......................707
Creating a command bar...........................708
Referring to command bars in VBA....................709
Deleting a command bar using VBA ...................710
Properties of command bars.........................710
Referring to controls in a command bar.................716
Listing the controls on a command bar .................717
Listing all controls on all toolbars.....................718
Adding a control to a command bar ...................719
Deleting a control from a command bar ................720
Properties of command bar controls ...................721
Chapter 23 Creating Custom Menus........................733
A Few Words about Excel's Menu Bar................733
What You Can Do with Excel's Menus................734
Understanding Excel menu terminology ................735
Removing Excel menu elements......................736
Adding Excel menu elements........................736
Changing Excel menu elements ......................736
Using VBA to Customize Excel Menus................738
Listing Excel menu information......................738
Adding a new menu to a menu bar....................740
Deleting a menu from a menu bar ....................743
Adding menu items to a menu.......................743
Displaying a shortcut key with a menu item..............748
Fixing a menu that has been reset ....................750
Menu Programming That Works with Events...........750
Adding and deleting menus automatically...............751
Disabling or hiding menus..........................752
Working with checked menu items....................753
Creating Custom Menus the Easy Way................756
Creating a Substitute Worksheet Menu Bar............758
Working with Shortcut Menus......................761
Adding menu items to shortcut menus..................762
Deleting menu items from shortcut menus...............763
Disabling shortcut menu items.......................764
Disabling shortcut menus ..........................764
Resetting shortcut menus...........................765
Creating new shortcut menus........................765
Chapter 24 Providing Help for Your Applications............769
Help for Your Excel Applications....................769
Help Systems That Use Excel Components.............771
Using cell comments for help........................772
Using a text box for help...........................772
Using a worksheet to display help text .................773
Displaying help in a UserForm.......................774
Using the Office Assistant to display help ...............777
Simulating What's This? Help in a UserForm...........780
Using the HTML Help System ......................781
Associating a Help File with Your Application .........783
Associating a Help Topic with a VBA Function.........783
Other Ways to Display HTML Help ..................785
Using the Help method............................785
Displaying Help from a message box...................785
Displaying Help from an input box....................786
Chapter 25 Developing User-Oriented Applications ..........787
What Is a User-Oriented Application? ................787
The Loan Amortization Wizard .....................788
Using the Loan Amortization Wizard application ..........788
The Loan Amortization Wizard workbook structure.........790
How the Loan Amortization Wizard works...............790
Potential enhancements for the Loan Amortization Wizard . . . 796 Application Development Concepts..................797
Post a comment