Excel VBA programming for dummies / by Michael Alexander and John Walkenbach.

Format:

Book

Contributor:

Walkenbach, John.

Series:

--For dummies.

For dummies

Language:

English

Subjects (All):

Microsoft Excel (Computer file).

Visual Basic for Applications (Computer program language).

Electronic spreadsheets.

Physical Description:

1 online resource (xiv, 398 p.) : ill.

Edition:

5th ed.

Place of Publication:

Hoboken, N.J. : John Wiley & Sons, 2019.

Summary:

Take your Excel programming skills to the next level. To take Excel to the next level, you need to understand and implement the power of Visual Basic for Applications (VBA). Excel VBA Programming For Dummies introduces you to a wide array of new Excel options, beginning with the most important tools and operations for the Visual Basic Editor. Inside, you’ll find an overview of the essential elements and concepts for programming with Excel. In no time, you’ll discover techniques for handling errors and exterminating bugs, working with range objects and controlling program flow, and much more. With friendly advice on the easiest ways to develop custom dialog boxes, toolbars, and menus, readers will be creating Excel applications custom fit to their unique needs! Fully updated for the new Excel 2019; Step-by-step instructions for creating VBA macros to maximize productivity; Guidance on customizing your applications so they work the way you want; All sample programs, VBA code, and worksheets are available at dummies.com. Beginning VBA programmers rejoice! This easy-to-follow book makes it easier than ever to excel at Excel VBA!

Contents:

Intro

Title Page

Copyright Page

Table of Contents

Introduction

About This Book

Obligatory Typographical Conventions Section

Check Your Security Settings

Foolish Assumptions

Icons Used in This Book

Sample Files Online

Where to Go from Here

Part 1 Getting Started with Excel VBA Programming

Chapter 1 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 custom add-ins for Excel

Advantages and Disadvantages of VBA

VBA advantages

VBA disadvantages

VBA in a Nutshell

Excel Compatibility

Chapter 2 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

Revealing More about the NameAndTime Macro

Part 2 How VBA Works with Excel

Chapter 3 Working in the Visual Basic Editor

What Is the Visual Basic Editor?

Activating the VBE

Understanding VBE components

Working with the Project Window

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

Chapter 4 Introducing the Excel Object Model

Excel Is an Object?

Climbing Down 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

Automatically listing properties and methods

Chapter 5 VBA Sub and Function Procedures

Understanding 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

Chapter 6 Using the Excel Macro Recorder

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 option

Description

Is This Thing Efficient?

Part 3 Programming Concepts

Chapter 7 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

Premade constants

Working with strings

Working with dates

Using Assignment Statements

Assignment statement examples

About that equal sign

Smooth operators

Working with Arrays

Declaring arrays

Multidimensional arrays

Dynamic arrays

Using Labels

Chapter 8 Working with Range Objects

A Quick Review

Other Ways to Refer to a Range

The Cells property.

The Offset property

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

Chapter 9 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

Chapter 10 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

Using For Each-Next Loops with Collections

Chapter 11 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

Chapter 12 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

Chapter 13 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 Watches window

Using the Locals window

Bug Reduction Tips

Chapter 14 VBA Programming Examples

Working with Ranges

Copying a range

Copying a variable-size range

Selecting to the end of a row or column

Selecting a row or column

Moving a range

Looping through a range efficiently

Looping through a range efficiently (Part II)

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

AddChart versus AddChart2

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

Declaring variable types

Using the With-End With structure

Part 4 Communicating with Your Users

Chapter 15 Simple Dialog Boxes

UserForm Alternatives

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

Another type of InputBox

The GetOpenFilename Method

The syntax for the GetOpenFilename method

A GetOpenFilename example

The GetSaveAsFilename Method

Getting a Folder Name

Displaying Excel's Built-in Dialog Boxes

Chapter 16 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

Chapter 17 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

Chapter 18 UserForm Techniques and Tricks

Using Dialog Boxes

Creating the dialog box

Writing code to display the dialog box

Trying out your dialog box

Validating the data

Now the dialog box works

A ListBox Example.

Filling a ListBox.

Notes:

Includes index.

Description based on publisher supplied metadata and other sources.

ISBN:

1-119-51824-5

1-119-51823-7

OCLC:

1063982883