Excel programming: your visual blueprint for creating interactive spreadsheets

Excel(r) Programming 2nd Edition

Welcome to the only guidebook series that takes a visual approach to professional-level computer topics. Open the book and you'll discover step-by-step screen shots that demonstrate over 120 key Excel programming techniques, including:
* Creating and launching keyboard shortcuts
* Setting macro security
* Assigning a digital signature
* Developing a function with VBA
* Displaying a built-in dialog box
* Converting a list into an array
* Using the msgbox function
* Processing a runtime error
* Saving a workbook in a specified format
* Finding the intersection of two ranges

Author Notes

Jinjer Simon has been actively involved in the computer industry for the past 18 years. Her involvement includes programming, providing software technical support, training end-users, developing written and online user documentation, creating software tutorials, and developing Web sites. She is the author of several computer books, including Excel Programming: Your visual blueprint for creating interactive spreadsheets, and Windows CE 2 For Dummies.

How to Use This Book

Getting Stared with Excel Macros

Introduction to Macros

Record a Macro

Run a Macro

Create and Launch a Keyboard Shortcut

Delete a Macro from a Workbook

Delete from the Personal Macro Workbook

Assign a Macro to a Toolbar Button

Assign a Macro to a Menu

Set Macro Security

Using the Visual Basic Editor

An Introduction to the Visual Basic Editor

Activate the Visual Basic Editor

Arrange the Visual Basic Editor Windows

Set Properties for a Project

Set Display Options for the Code Window

Add a New Module

Remove a Module

Rename a Macro

4Create a Startup Macro

Hide a Macro

Assign a Digital Signature to a Macro

Update a Recorded Macro

VBA Programming Basics

An Introduction to VBA

Create a Subroutine

Create a Function

Declare a Variable

Perform Mathematical Calculations

Create a Constant

Comment Your Code

Join Two Strings

Working with the Excel Object Model

An Introduction to the Excel Object Model

Using the Object Browser

Create an Object Variable

Change the Properties of an Object

Compare Object Variables

Using an Object Method

Display a Built-in Dialog Box

Using Variables and Arrays

Assign Values to Variables

Using Global Variables

Declare an Array

Declare a Multidimensional Array

Convert a List into an Array

Redimension an Array

Create a User-Defined Data Type

Adding Control Statements

Execute a Task While a Condition is True

2Perform Multiple Tasks Until a Condition is Met

Execute Tasks a Specific Number of Times

Using the For Each Next Loop

Conditionally Execute a Group of Statements

Execute a Statement Based upon the Value

Jump to a Specific Location in a Macro

Conditionally Call a Subroutine

Using Built-In Functions and Statements

Using Excel Worksheet Functions

Using the MsgBox Function

Using the InputBox Function

Retrieve Current Date and Time

Determine the Amount of Time between Dates

Format a Date Expression

Format a Numeric Expression

Remove Extra Spacing from a String

Return a Portion of a String

Compare Two Strings

Debugging Macros

Debugging Basics

Debug a Procedure with Inserted Break Points

Using Watch Expressions to Debug a Procedure

Step through a Procedure

Resume Execution if an Error is Encountered

Process a Runtime Error

Working with other Workbooks and Files

Open a Workbook

Open a Text File as a Workbook

Open a File Requested by the User

Save a Workbook

6Save Workbook in Format Specified by User

Determine if a Workbook is Open

0Close a Workbook

Create a New Workbook

Delete a File

Find a File

Working with Worksheets

Add a Sheet

Delete a Sheet

Move a Sheet

Copy and Paste a Sheet

Hide a Sheet

Change the Name of a Sheet

Save a Sheet to Another File

Protect a Worksheet

Protect a Chart

Print a Sheet

Sort Worksheets by Name

Defining Ranges

Using the Range Property

Using the Cells Property

Combine Multiple Ranges

Using the Offset Property

Delete a Range of Cells

Hide a Range of Cells

Specify the Name of a Range

Resize a Range

Insert a Range

Set the Width of Columns in a Range

Set the Height of Rows in a Range

Convert a Column of Text into Multiple Columns

Find the Intersection of Two Ranges

Working with Cells

Cut and Paste Ranges of Cells

Copy and Paste Ranges of Cells

0Custom Paste Values in Cells

Add Comments to a Cell

Automatically Fill a Range of Cells

Copy a Range to Multiple Sheets

Place Borders around a Range of Cells

Available:*

On Order

Summary

Summary

Author Notes

Table of Contents