Excel 2007 VBA Programming For Dummies (Google eBook)

Front Cover
John Wiley & Sons, Feb 8, 2011 - Computers - 384 pages
2 Reviews
Step-by-step instructions for creating VBA macros

Harness the power of VBA and create custom Excel applications

Make Excel 2007 work for you! This clear, nonintimidating guide shows you how to use VBA to create Excel apps that look and work the way you want. Packed with plenty of sample programs, it explains how to work with range objects, control program flow, develop custom dialog boxes, create custom toolbars and menus, and much more.

Discover how to

  • Grasp essential programming concepts
  • Use the Visual Basic Editor
  • Navigate the new Excel user interface
  • Communicate with your users
  • Deal with errors and bugs
  

What people are saying - Write a review

User Review - Flag as inappropriate

Wonderfull Book. This book made excel using simple

Review: Excel 2007 VBA Programming for Dummies

User Review  - Chand Khan - Goodreads

good Read full review

Contents

The Details
Error handling in a nutshell
Identifying specific errors
An Intentional Error
Chapter 13
Identifying Bugs
Debugging Techniques
Using the MsgBox function

Part I
Chapter 1
What Can You Do with VBA?
Inserting a bunch of text
Creating a custom button
VBA disadvantages
An Excursion into Versions
Chapter 2
What Youll Be Doing
Recording the Macro
Testing the Macro
Modifying the Macro
Saving Workbooks that Contain Macros
More about the NameAndTime Macro
Part II
Chapter 3
Understanding VBE components
Working with the Project Explorer
Adding a new VBA module
Removing a VBA module
Working with a Code Window
Creating a module
Entering code directly
Using the macro recorder
Copying VBA code
Using the Editor Format tab
Using the General tab
Chapter 4
Climbing the Object Hierarchy
Wrapping Your Mind around Collections
Referring to Objects
Simplifying object references
Diving into Object Properties and Methods
Object methods
Object events
Using the Object Browser
Chapter 5
Looking at Sub procedures
Naming Subs and Functions
Executing the Sub procedure directly
Executing the procedure from the Macro dialog box
Executing the procedure from a button or shape
Executing the procedure from another procedure
Executing Function procedures
Calling a function from a worksheet formula
Chapter 6
Recording Basics
Preparing to Record
Relative or Absolute?
Recording in relative mode
What Gets Recorded?
Recording Options
Macro name
Store Macro In
Part III
Chapter 7
Using Variables Constants and Data Types
What are VBAs data types?
Declaring and scoping variables
Working with constants
Working with strings
Working with dates
Using Assignment Statements
Other operators
Working with Arrays
Multidimensional arrays
Using Labels
Chapter 8
Other Ways to Refer to a Range
The Cells property
The Offset property
Some Useful Range Object Properties
The Text property
The Address property
The Font property
The Formula property
Some Useful Range Object Methods
The Clear method
Chapter 9
Using Builtin VBA Functions
VBA functions that do more than return a value
Discovering VBA functions
Using Worksheet Functions in VBA
Worksheet function examples
Entering worksheet functions
Using Custom Functions
Chapter 10
The GoTo Statement
Decisions decisions
The Select Case structure
Knocking Your Code for a Loop
ForNext loops
DoWhile loop
Looping through a Collection
Chapter 11
Are events useful?
Programming eventhandler procedures
Writing an EventHandler Procedure
Introductory Examples
The BeforeClose event for a workbook
Examples of Activation Events
Activate and deactivate events in a workbook
Workbook activation events
Other WorksheetRelated Events
The Change event
Events Not Associated with Objects
The OnTime event
Keypress events
Chapter 12
An Erroneous Example
The macros not quite perfect
The macro is still not perfect
Giving up on perfection
Handling Errors Another Way
About the On Error statement
Inserting DebugPrint statements
About the Debugger
Using the Watch window
Using the Locals Window
Chapter 14
Copying a range
Copying a variablesized range
Selecting to the end of a row or column
Selecting a row or column
Looping through a range efficiently
Prompting for a cell value
Determining the selection type
Identifying a multiple selection
Changing Boolean settings
Working with Charts
Looping through the ChartObjects collection
Applying chart formatting
VBA Speed Tips
Turning off automatic calculation
Simplifying object references
Using the WithEnd With structure
Part IV
Chapter 15
The MsgBox Function
Getting a response from a message box
Customizing message boxes
The InputBox Function
An InputBox example
The GetOpenFilename Method
Selecting multiple files
The GetSaveAsFilename Method
Getting a Folder Name
Chapter 16
An Overview
Working with UserForms
Adding controls to a UserForm
Changing properties for a UserForm control
Viewing the UserForm Code window
Using information from a UserForm
Creating the UserForm
Adding the OptionButtons
Adding eventhandler procedures
Creating a macro to display the dialog box
Making the macro available
Chapter 17
Introducing control properties
The Details
CheckBox control
ComboBox control
CommandButton control
Frame control
Image control
Label control
MultiPage control
OptionButton control
RefEdit control
SpinButton control
TabStrip control
ToggleButton control
Aligning and spacing controls
Testing a UserForm
Dialog Box Aesthetics
Chapter 18
A UserForm Example
Writing code to display the dialog box
Making the macro available
Adding eventhandler procedures
Validating the data
Now the dialog box works
Selecting a range
Using multiple sets of OptionButtons
Using a SpinButton and a TextBox
Using a UserForm as a progress indicator
Creating a tabbed dialog box
Displaying a chart in a dialog box
A Dialog Box Checklist
Chapter 19
Excel 2007 Ribbon Customization
Working with CommandBars
Commanding the CommandBars collection
Referring to CommandBars
Referring to controls in a CommandBar
Properties of CommandBar controls
VBA Shortcut Menu Examples
Resetting all builtin rightclick menus
Disabling a shortcut menu
Part V
Chapter 20
Understanding VBA Function Basics
Writing Functions
Function Examples
A function with two arguments
A function with a range argument
A function with an optional argument
A function with an indefinite number of arguments
Functions That Return an Array
Returning a sorted list
Using the Insert Function Dialog Box
Argument descriptions
Chapter 21
Why Create AddIns?
Working with AddIns
Addin Basics
An Addin Example
Testing the workbook
Adding descriptive information
Creating the addin
Distributing the addin
Part VI
Chapter 22
Chapter 23
Internet Newsgroups
Excel Blogs
Copyright

Common terms and phrases

About the author (2011)

John Walkenbach is a leading authority on spreadsheet software and creator of the award-winning Power Utility Pak.

Bibliographic information