VBA Automation for Excel 2019 Cookbook

Other Books You May Enjoy

Copying Excel content into PowerPoint

Adding text to textboxes

Creating presentations and slides

Creating a new instance of PowerPoint

Technical requirements

Chapter 18: Working with PowerPoint in Excel VBA

Using templates and bookmarks

Copying data into Word

Writing and formatting text

Creating a new instance of Word

Technical requirements

Chapter 17: Creating Word Documents with Excel VBA

Displaying function and argument descriptions

Adding function arguments

Calling functions

Writing functions

Technical requirements

Chapter 16: Creating Custom Functions

Setting the tab order

Using the SpinButton control

Using the Combo Box control

Loading the form

Unloading a form

Assigning controls

Technical requirements

Chapter 15: UserForm Controls

Using Frames

Adding controls

Changing properties

Inserting the form

Technical requirements

Chapter 14: Creating UserForms

Using the Application.InputBox method

Using the InputBox function

Using the MsgBox function

Technical requirements

Chapter 13: Creating and Modifying Dialog Boxes

Using the debugger in Excel

Debugging techniques

Exploring various bugs

Technical requirements

Chapter 12: Debugging

Resuming after an error

Using the On Error statement

Testing to find errors

Correcting a runtime error

Handling different types of errors

Technical requirements

Chapter 11: Handling Errors

Creating non-object events

Creating worksheet-related events

Creating workbook-related events

Discovering events

Writing event handler procedures

Technical requirements

Chapter 10: Implementing Automation

Using loops

Using the Select Case structure

Using If and If-Then structures to make decisions

Changing the flow using the GoTo statement

Exploring program flow

Technical requirements

Chapter 9: Implementing Program Flow

Creating custom functions

Using worksheet functions

Using built-in VBA functions

Technical requirements

Chapter 8: Using Functions

Making ranges work

Adding the Range object's methods

Using the Range object properties

Referring to ranges

Technical requirements

Chapter 7: Working with Ranges

Declaring arrays

Scoping variables

Working with data types in variables and constants

Declaring constants

Declaring variables

Adding comments

Technical requirements

Chapter 6: VBA Language Elements

Recording options

Setting the macro recorder for absolute or relative reference

Technical requirements

Chapter 5: Next Level Recording

Executing Function procedures by calling from a Sub procedure

Executing Function procedures using a worksheet formula

Executing Sub procedures using a shortcut key

Executing Sub procedures using buttons

Executing Sub procedures from the Macro dialog box

Executing Sub procedures directly

Creating Sub and Function procedures

Technical requirements

Chapter 4: Working with Procedures

Using the Object Browser in the VBA Editor

Using events to activate objects

Associating methods with objects

Modifying object properties

Objects as a collection and changing the attributes of the entire collection

Understanding and identifying objects in VBA

Technical requirements

Chapter 3: The VBA Object Model

Customizing the Editor tab to suit your preferences

Creating VBA code in the code window in three different ways

Creating a basic procedure in the code window of the Editor

Creating a module for storing a Sub procedure

Identifying activating and closing components in the VBA Editor

Technical requirements

Chapter 2: Working with the VBA Editor

Creating a customized ribbon

Using the Trust Center for macro security

Saving the file with an embedded macro

Editing the code by changing cell references

Using the VBA Editor

Testing the macro

Recording a macro

Investigating VBA code

Technical requirements

Chapter 1: Getting Started with VBA

Reviews

Get in touch

Sections

Conventions used

Download the color images

Code in Action

To get the most out of this book

What this book covers

Who this book is for

Preface

Packt is searching for authors like you

About the reviewer

Contributors About the author

Why subscribe?

版权信息

封面

封面

版权信息

Why subscribe?

Contributors About the author

About the reviewer

Packt is searching for authors like you

Preface

Who this book is for

What this book covers

To get the most out of this book

Code in Action

Download the color images

Conventions used

Sections

Get in touch

Reviews

Chapter 1: Getting Started with VBA

Technical requirements

Investigating VBA code

Recording a macro

Testing the macro

Using the VBA Editor

Editing the code by changing cell references

Saving the file with an embedded macro

Using the Trust Center for macro security

Creating a customized ribbon

Chapter 2: Working with the VBA Editor

Technical requirements

Identifying activating and closing components in the VBA Editor

Creating a module for storing a Sub procedure

Creating a basic procedure in the code window of the Editor

Creating VBA code in the code window in three different ways

Customizing the Editor tab to suit your preferences

Chapter 3: The VBA Object Model

Technical requirements

Understanding and identifying objects in VBA

Objects as a collection and changing the attributes of the entire collection

Modifying object properties

Associating methods with objects

Using events to activate objects

Using the Object Browser in the VBA Editor

Chapter 4: Working with Procedures

Technical requirements

Creating Sub and Function procedures

Executing Sub procedures directly

Executing Sub procedures from the Macro dialog box

Executing Sub procedures using buttons

Executing Sub procedures using a shortcut key

Executing Function procedures using a worksheet formula

Executing Function procedures by calling from a Sub procedure

Chapter 5: Next Level Recording

Technical requirements

Setting the macro recorder for absolute or relative reference

Recording options

Chapter 6: VBA Language Elements

Technical requirements

Adding comments

Declaring variables

Declaring constants

Working with data types in variables and constants

Scoping variables

Declaring arrays

Chapter 7: Working with Ranges

Technical requirements

Referring to ranges

Using the Range object properties

Adding the Range object's methods

Making ranges work

Chapter 8: Using Functions

Technical requirements

Using built-in VBA functions

Using worksheet functions

Creating custom functions

Chapter 9: Implementing Program Flow

Technical requirements

Exploring program flow

Changing the flow using the GoTo statement

Using If and If-Then structures to make decisions

Using the Select Case structure

Using loops

Chapter 10: Implementing Automation

Technical requirements

Writing event handler procedures

Discovering events

Creating workbook-related events

Creating worksheet-related events

Creating non-object events

Chapter 11: Handling Errors

Technical requirements

Handling different types of errors

Correcting a runtime error

Testing to find errors

Using the On Error statement

Resuming after an error

Chapter 12: Debugging

Technical requirements

Exploring various bugs

Debugging techniques

Using the debugger in Excel

Chapter 13: Creating and Modifying Dialog Boxes

Technical requirements

Using the MsgBox function

Using the InputBox function

Using the Application.InputBox method

Chapter 14: Creating UserForms

Technical requirements

Inserting the form

Changing properties

Adding controls

Using Frames

Chapter 15: UserForm Controls

Technical requirements

Assigning controls

Unloading a form

Loading the form

Using the Combo Box control

Using the SpinButton control

Setting the tab order

Chapter 16: Creating Custom Functions

Technical requirements

Writing functions

Calling functions

Adding function arguments

Displaying function and argument descriptions

Chapter 17: Creating Word Documents with Excel VBA

Technical requirements

Creating a new instance of Word

Writing and formatting text

Copying data into Word

Using templates and bookmarks

Chapter 18: Working with PowerPoint in Excel VBA

Technical requirements

Creating a new instance of PowerPoint

Creating presentations and slides

Adding text to textboxes

Copying Excel content into PowerPoint

Other Books You May Enjoy