book

Microsoft® Offic Excel® 2007 Visual Basic® for Applications Step by Step

Name: Microsoft® Offic Excel® 2007 Visual Basic® for Applications Step by Step
Author: Reed Jacobson
ISBN: 9780735624023

by Reed Jacobson

May 2007

Beginner

384 pages

10h 7m

English

Microsoft Press

Read now

Unlock full access

Features and Conventions of This Book
Using the Book’s CD
What’s on the CD?Minimum System RequirementsStep-by-Step Exercises2007 Microsoft Office SystemInstalling the Practice FilesUsing the Practice FilesRemoving and Uninstalling the Practice Files
Getting Help
Errata & Book SupportMore Information
1. Make a Macro Do Simple Tasks
Chapter at a GlanceWhat’s the Difference Between VBA and a Macro?Creating a Simple MacroFormat Currency by Using a Built-In ToolRecord a Macro to Format CurrencyRun the MacroAssign a Shortcut Key to the MacroLook at the MacroSave the Macro WorkbookChanging Multiple Properties at OnceCreate Sidebar Headings with a CommandRecord a Macro to Merge Cells VerticallyEliminate Unnecessary Lines from the MacroManipulating Recorded PropertiesRecord a Macro to Remove Window ElementsRun the Macro from the Visual Basic EditorUse a Macro to Toggle the Value of a PropertyEliminate Repeated Objects in a Recorded MacroRun a Macro from the Quick Access ToolbarRecording Methods in a MacroConvert a Formula to a Value by Using Menu CommandsConvert a Formula to a Value by Using a MacroMake a Long Statement More ReadableTrusting Macro-Enabled WorkbooksDesignate a Trusted Location for MacrosDesignate a Trusted Publisher for MacrosKey Points
2. Make a Macro Do Complex Tasks
Chapter at a GlanceTask One: Opening the Report FileOpen a Text FileWatch a Macro Run by Stepping Through ItSelect a File While Running a MacroTask Two: Filling In Missing LabelsSelect Only the Blank CellsFill the Selection with ValuesRecord Filling In the Missing ValuesWatch the FillLabels Macro RunTask Three: Adding a Column of DatesAdd a Constant DateStep Through the MacroPrompt for the DateTask Four: Appending to the DatabaseAppend Data to a Master ListStep Through the AppendData MacroRecord a Relative MovementChoose Whether to Save Changes While Closing a FileTask Five: Deleting the WorksheetCreate a Macro to Delete the Active WorksheetMake the Macro Operate QuietlyAssembling the PiecesRecord a Macro That Runs Other MacrosSimplify the Subroutine StatementsKey Points
3. Explore Workbooks and Worksheets
Chapter at a GlanceWhat Is an Object?Objects Come in CollectionsObjects Have PropertiesObjects Have MethodsMethods Can Change PropertiesProperties Can Involve ActionsUnderstanding WorkbooksAdd a New WorkbookCount the WorkbooksClose the WorkbooksRefer to a Single WorkbookRefer to a Workbook by NameRefer to a Workbook by PointingChange a Workbook Property ValueUnderstanding WorksheetsAdd a New WorksheetRename and Delete a WorksheetLook at the Return Value of the Delete MethodLook at the Result of the Add MethodCopy a WorksheetManipulate Multiple WorksheetsDeclare Variables to Enable Auto ListsKey Points
4. Explore Range Objects
Chapter at a GlanceReferring to a RangeRefer to a Range by Using an AddressRefer to a Range as a Collection of CellsRefer to a Range as a Collection of Rows or ColumnsRefer to a Range Based on the Active CellRefer to Subsets of a RangeRefer to a Relative RangeEnhancing Recorded SelectionsSimplify Select…Selection PairsSimplify Select GroupsEntering Values and Formulas into a RangeRelative ReferencesAbsolute ReferencesR1C1 Reference StylePut Values and Formulas into a RangeConstruct Formulas to Fill a GridFormatting a RangeAdd Borders to a RangeFormat the Interior of a RangeKey Points
5. Explore Data Objects
Chapter at a GlanceWorking with Excel TablesCreate a New File from an Existing WorksheetCreate a Table from an Internal SourceCreate a Table from an External SourceRecord a Macro to Manipulate a TableManipulate Table ColumnsManipulate Table Totals and FiltersWorking with PivotTable ReportsCreate a PivotTable Report from an Internal SourceCreate a PivotTable Report from an External SourceRecord a Macro to Set the PivotTable StructureSet the PivotTable StructureRecord a Macro to Customize a PivotTable LayoutCustomize a PivotTable LayoutRecord a Macro to Customize a PivotTable StyleCustomize a PivotTable StyleKey Points
6. Explore Graphical Objects
Chapter at a GlanceExploring Graphical ObjectsUse Worksheet Cells as a Drawing GridAdd a Gradient Fill to a CellAdd a Gradient-Filled ShapeReference a Selected ShapeUse an AutoShape to Create a LogoUse Grouped Shapes to Create Macro ButtonsExploring Chart ObjectsCreate a ChartSynchronize Two ChartsFormat the Plot Area of a ChartKey Points
7. Control Visual Basic
Chapter at a GlanceUsing ConditionalsMake a DecisionMake a Double DecisionAsk Yourself a QuestionTest for a Valid EntryAsk with a MessageCreating LoopsLoop Through a Collection by Using a For Each LoopLoop with a Counter by Using a For LoopLoop Indefinitely by Using a Do LoopManaging Large LoopsSet a BreakpointSet a Temporary BreakpointShow Progress in a LoopKey Points

8. Extend Excel and Visual Basic
Chapter at a GlanceCreating Custom FunctionsUse a Custom Function from a WorksheetAdd Arguments to a Custom FunctionMake a Function VolatileMake Arguments OptionalUse a Custom Function from a MacroHandling ErrorsSyntax ErrorsCompiler ErrorsLogic ErrorsRun-Time ErrorsIgnore an ErrorIgnore an Error Safely by Using a SubroutineAdd Arguments to Generalize a SubroutineCheck for an ErrorLoop Until an Error Goes AwayTrap an ErrorKey Points
9. launch Macros with Events
Chapter at a GlanceCreating Custom Command ButtonsTry the ZoomIn and ZoomOut MacrosEnable the Developer Tab in the RibbonCreate a Custom Command ButtonLink a Command Button to a MacroCreate an Event Handler on Your OwnMake a Button Respond to Mouse MovementsExplore the Visual Basic ProjectHandling Worksheet and Workbook EventsRun a Procedure When the Selection ChangesHandle an Event on Any WorksheetSuppress a Workbook EventCancel an EventKey Points
10. Use Dialog Box Controls on a Worksheet
Chapter at a GlanceUsing a Loan Payment CalculatorCreate a Loan Payment ModelUse the Loan Payment ModelCreating an Error-Resistant Loan Payment CalculatorRestrict the Years to a Valid RangeRestrict the Down Payment to Valid ValuesRestrict the Interest Rate to Valid ValuesRetrieving a Value from a ListPrepare a List of CarsRetrieve the Price from the ListSet the Column WidthsProtecting the WorksheetCreate an Event Handler for the Combo BoxProtect the WorksheetKey Points
11. Create a Custom Form
Chapter at a GlanceCreating a Form’s User InterfaceCreate the FormAdd Option ButtonsAdd a Check Box with a Related Text BoxInitialize the Text BoxAdd Command ButtonsSet the Tab Order for ControlsPreparing a Form’s FunctionalityCreate Custom Views on a WorksheetCreate a Macro to Switch ViewsDynamically Hide ColumnsImplementing a FormImplement Option ButtonsImplement a Check BoxCheck for Errors in an Edit BoxPrint the ReportLaunch the FormKey Points
A. A Complete Enterprise Information System
Index
About the Author
Copyright

Content preview from Microsoft® Offic Excel® 2007 Visual Basic® for Applications Step by Step

Using the Book’s CD

The companion CD included with this book contains the practice files you’ll use as you work through the book’s exercises, as well as other electronic resources that will help you learn how to use VBA macros with Microsoft Office Excel 2007.

What’s on the CD?

The following table lists the practice files supplied on the book’s CD. Note that some practice files are used in more than one chapter.

Tip

The ExcelVBA07SBS folder contains a subfolder named Finished. This folder contains the finished version of each chapter’s workbook. The Finished folder is never explicitly referred to in the text, but it is there for your reference. If you have trouble getting a macro to work properly, you can look at the macros in the Finished folder ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Microsoft Office Access 2007 - Das Handbuch

Publisher Resources

ISBN: 9780735624023Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Microsoft® Offic Excel® 2007 Visual Basic® for Applications Step by Step

by Reed Jacobson

Using the Book’s CD

What’s on the CD?

Tip

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.