Excel 2007 VBA Programming for Dummies

Name: Excel 2007 VBA Programming for Dummies
Price: 6.23 USD
Availability: InStock
ISBN: 9780470046746

ISBN-10: 0470046740

ISBN-13: 9780470046746

Edition: 2007

Authors: John Walkenbach, Jan Karel Pieterse

List price: $29.99

30 day, 100% satisfaction guarantee!

Marketplace

2 new & used from $6.23

what's this?

Rush Rewards U
Members Receive:

You have reached 400 XP and carrot coins. That is the daily max!

Description:

Step-by-step instructions for creating VBA macrosHarness the power of VBA and create custom Excel applicationsMake 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 toGrasp essential programming conceptsUse the Visual Basic EditorNavigate the new Excel user interfaceCommunicate with your usersDeal with errors and bugs

Book details

List price: $29.99
Copyright year: 2007
Publisher: John Wiley & Sons, Incorporated
Publication date: 2/20/2007
Binding: Paperback
Pages: 384
Size: 7.44" wide x 9.69" long x 0.92" tall
Weight: 1.188
Language: English



Introduction


Is This the Right Book?


So You Want to Be a Programmer


Why Bother?


What I Assume about You


Obligatory Typographical Conventions Section


Check Your Security Settings


How This Book Is Organized



Introducing VBA



How VBA Works with Excel



Programming Concepts



Communicating with Your Users



Putting It All Together



The Part of Tens


Marginal Icons


Get the Sample Files


Now What?



Introducing VBA



What Is VBA?


Okay, So What Is VBA?


What Can You Do with VBA?


Inserting a bunch of text


Automating a task you perform frequently


Automating repetitive operations


Creating a custom command


Creating a custom button


Developing new worksheet functions


Creating complete, macro-driven applications


Creating custom add-ins for Excel


Advantages and Disadvantages of VBA


VBA advantages


VBA disadvantages


VBA in a Nutshell


An Excursion into Versions



Jumping Right In


First Things First


What You'll Be Doing


Taking the First Steps


Recording the Macro


Testing the Macro


Examining the Macro


Modifying the Macro


Saving Workbooks that Contain Macros


Understanding Macro Security


More about the NameAndTime Macro



How VBA Works with Excel



Working In the Visual Basic Editor


What Is the Visual Basic Editor?


Activating the VBE


Understanding VBE components


Working with the Project Explorer


Adding a new VBA module


Removing a VBA module


Exporting and importing objects


Working with a Code Window


Minimizing and maximizing windows


Creating a module


Getting VBA code into a module


Entering code directly


Using the macro recorder


Copying VBA code


Customizing the VBA Environment


Using the Editor tab


Using the Editor Format tab


Using the General tab


Using the Docking tab



Introducing the Excel Object Model


Excel Is an Object?


Climbing the Object Hierarchy


Wrapping Your Mind around Collections


Referring to Objects


Navigating through the hierarchy


Simplifying object references


Diving into Object Properties and Methods


Object properties


Object methods


Object events


Finding Out More


Using VBA's Help system


Using the Object Browser



VBA Sub and Function Procedures


Subs versus Functions


Looking at Sub procedures


Looking at Function procedures


Naming Subs and Functions


Executing Sub procedures


Executing the Sub procedure directly


Executing the procedure from the Macro dialog box


Executing a macro by using a shortcut key


Executing the procedure from a button or shape


Executing the procedure from another procedure


Executing Function procedures


Calling the function from a Sub procedure


Calling a function from a worksheet formula



Using the Excel Macro Recorder


Is It Live or Is It VBA?


Recording Basics


Preparing to Record


Relative or Absolute?


Recording in absolute mode


Recording in relative mode


What Gets Recorded?


Recording Options


Macro name


Shortcut key


Store Macro In


Description


Is This Thing Efficient?



Programming Concepts



Essential VBA Language Elements


Using Comments in Your VBA Code


Using Variables, Constants, and Data Types


Understanding variables


What are VBA's data types?


Declaring and scoping variables


Working with constants


Working with strings


Working with dates


Using Assignment Statements


Assignment statement examples


About that equal sign


Other operators


Working with Arrays


Declaring arrays


Multidimensional arrays


Dynamic arrays


Using Labels



Working with Range Objects


A Quick Review


Other Ways to Refer to a Range


The Cells property


The Offset property


Referring to entire columns and rows


Some Useful Range Object Properties


The Value property


The Text property


The Count property


The Column and Row properties


The Address property


The HasFormula property


The Font property


The Interior property


The Formula property


The NumberFormat property


Some Useful Range Object Methods


The Select method


The Copy and Paste methods


The Clear method


The Delete method



Using VBA and Worksheet Functions


What Is a Function?


Using Built-in VBA Functions


VBA function examples


VBA functions that do more than return a value


Discovering VBA functions


Using Worksheet Functions in VBA


Worksheet function examples


Entering worksheet functions


More about Using Worksheet Functions


Using Custom Functions



Controlling Program Flow and Making Decisions


Going with the Flow, Dude


The GoTo Statement


Decisions, decisions


The If-Then structure


The Select Case structure


Knocking Your Code for a Loop


For-Next loops


Do-While loop


Do-Until loop


Looping through a Collection



Automatic Procedures and Events


Preparing for the Big Event


Are events useful?


Programming event-handler procedures


Where Does the VBA Code Go?


Writing an Event-Handler Procedure


Introductory Examples


The Open event for a workbook


The BeforeClose event for a workbook


The BeforeSave event for a workbook


Examples of Activation Events


Activate and deactivate events in a sheet


Activate and deactivate events in a workbook


Workbook activation events


Other Worksheet-Related Events


The BeforeDoubleClick event


The BeforeRightClick event


The Change event


Events Not Associated with Objects


The OnTime event


Keypress events



Error-Handling Techniques


Types of Errors


An Erroneous Example


The macro's not quite perfect


The macro is still not perfect


Is the macro perfect yet?


Giving up on perfection


Handling Errors Another Way


Revisiting the EnterSquareRoot procedure


About the On Error statement


Handling Errors: The Details


Resuming after an error


Error handling in a nutshell


Knowing when to ignore errors


Identifying specific errors


An Intentional Error



Bug Extermination Techniques


Species of Bugs


Identifying Bugs


Debugging Techniques


Examining your code


Using the MsgBox function


Inserting Debug.Print statements


Using the VBA debugger


About the Debugger


Setting breakpoints in your code


Using the Watch window


Using the Locals Window


Bug Reduction Tips



VBA Programming Examples


Working with Ranges


Copying a range


Copying a variable-sized range


Selecting to the end of a row or column


Selecting a row or column


Moving a range


Looping through a range efficiently


Prompting for a cell value


Determining the selection type


Identifying a multiple selection


Changing Excel Settings


Changing Boolean settings


Changing non-Boolean settings


Working with Charts


Modifying the chart type


Looping through the ChartObjects collection


Modifying chart properties


Applying chart formatting


VBA Speed Tips


Turning off screen updating


Turning off automatic calculation


Eliminating those pesky alert messages


Simplifying object references


Declaring variable types


Using the With-End With structure



Communicating with Your Users



Simple Dialog Boxes


Why Create UserForms?


The MsgBox Function


Displaying a simple message box


Getting a response from a message box


Customizing message boxes


The InputBox Function


InputBox syntax


An InputBox example


The GetOpenFilename Method


The syntax


A GetOpenFilename example


Selecting multiple files


The GetSaveAsFilename Method


Getting a Folder Name


Displaying Excel's Built-in Dialog Boxes



UserForm Basics


Knowing When to Use a UserForm


Creating UserForms: An Overview


Working with UserForms


Inserting a new UserForm


Adding controls to a UserForm


Changing properties for a UserForm control


Viewing the UserForm Code window


Displaying a UserForm


Using information from a UserForm


A UserForm Example


Creating the UserForm


Adding the CommandButtons


Adding the OptionButtons


Adding event-handler procedures


Creating a macro to display the dialog box


Making the macro available


Testing the macro



Using UserForm Controls


Getting Started with Dialog Box Controls


Adding controls


Introducing control properties


Dialog Box Controls: The Details


CheckBox control


ComboBox control


CommandButton control


Frame control


Image control


Label control


ListBox control


MultiPage control


OptionButton control


RefEdit control


ScrollBar control


SpinButton control


TabStrip control


TextBox control


ToggleButton control


Working with Dialog Box Controls


Moving and resizing controls


Aligning and spacing controls


Accommodating keyboard users


Testing a UserForm


Dialog Box Aesthetics



UserForm Techniques and Tricks


Using Dialog Boxes


A UserForm Example


Creating the dialog box


Writing code to display the dialog box


Making the macro available


Trying out your dialog box


Adding event-handler procedures


Validating the data


Now the dialog box works


More UserForm Examples


A ListBox example


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



Accessing Your Macros Through the User Interface


CommandBars and Excel 2007


Excel 2007 Ribbon Customization


Working with CommandBars


Commanding the CommandBars collection


Listing all shortcut menus


Referring to CommandBars


Referring to controls in a CommandBar


Properties of CommandBar controls


VBA Shortcut Menu Examples


Resetting all built-in right-click menus


Adding a new item to the Cell shortcut menu


Disabling a shortcut menu


Creating a Custom Toolbar



Putting It Alt Together



Creating Worksheet Functions and Living to Tell about It


Why Create Custom Functions?


Understanding VBA Function Basics


Writing Functions


Working with Function Arguments


Function Examples


A function with no argument


A function with one argument


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 an array of month names


Returning a sorted list


Using the Insert Function Dialog Box


Displaying the function's description


Argument descriptions



Creating Excel Add-Ins


Okay...So What's an Add-In?


Why Create Add-Ins?


Working with Add-Ins


Add-in Basics


An Add-in Example


Setting up the workbook


Testing the workbook


Adding descriptive information


Protecting the VBA code


Creating the add-in


Opening the add-in


Distributing the add-in


Modifying the add-in



The Part of Tens



Ten VBA Questions (And Answers)


The Top Ten Questions about VBA



(Almost) Ten Excel Resources


The VBA Help System


Microsoft Product Support


Internet Newsgroups


Internet Web Sites


Excel Blogs


Google


Local User Groups


My Other Book


Index