Book Image

VBA Automation for Excel 2019 Cookbook

By : Mike Van Niekerk

Book Image

VBA Automation for Excel 2019 Cookbook

By: Mike Van Niekerk

Overview of this book

Visual Basic for Applications (VBA) is a programming language developed by Microsoft to automate tasks in MS Office applications. This book will help you to focus on the essential aspects of your role by automating mundane tasks in Excel and other Office applications. With comprehensive coverage of VBA delivered in the form of practice problems and bite-sized recipes, this book will help you to hit the ground running. Unlike most books that assume prior programming experience, this book starts with the fundamentals and gradually progresses to solving bigger problems. You’ll start by becoming familiar with VBA so that you can start recording macros right away. With this foundation in place, you’ll advance to using the full capabilities of the language as you apply loops, functions, and custom dialog boxes to design your own automation programs. You'll also get to grips with embedded macros and other advanced tools to enhance productivity and explore topics relating to app performance and security. Throughout this VBA book, you’ll cover multiple practice projects in Excel, Word, and PowerPoint while exploring tips and best practices to hone your skills. By the end of this book, you’ll have developed the skills you need to use VBA to create your own programs that control MS Office applications.

Preface

Who this book is for

What this book covers

To get the most out of this book

Download the color images

Conventions used

Chapter 1: Getting Started with VBA

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

Free Chapter

Chapter 2: Working with the VBA Editor

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

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

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

Chapter 5: Next Level Recording

Technical requirements

Setting the macro recorder for absolute or relative reference

Recording options

Chapter 6: VBA Language Elements

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

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

Chapter 8: Using Functions

Technical requirements

Using built-in VBA functions

Using worksheet functions

Creating custom functions

Chapter 9: Implementing Program Flow

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

Chapter 10: Implementing Automation

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

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

Chapter 12: Debugging

Technical requirements

Exploring various bugs

Debugging techniques

Using the debugger in Excel

Chapter 13: Creating and Modifying Dialog Boxes

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

Chapter 14: Creating UserForms

Technical requirements

Inserting the form

Changing properties

Adding controls

Chapter 15: UserForm Controls

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

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

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

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

Other Books You May Enjoy

Leave a review - let other readers know what you think

Customer Reviews

5 star

0

4 star

0

3 star

0

2 star

0

1 star

0

Using the Range object properties

In this recipe, we will be working with the Range object's properties. Ranges are objects, and all VBA objects have properties – some are read-only, while others can be edited.

A Range object has many properties, much more than we normally use. Once you understand the principle, you can apply it to all objects.

Getting ready

Open Excel and activate Book1. Make sure Sheet1 is active. Save the file as Ranges.xlsm, and then press Alt + F11 to activate the VBA Editor. Insert a new module in the Explorer.

How to do it…

In the code window, create the following Sub procedure:
```
Sub RangeProperties()
End Sub
```

Add the following lines of code:

Sub RangeProperties()
    MsgBox Range("A1:B4").Address
    Range("A1").Value = 10
    MsgBox Range("A1:C1").Count
    Range("A1").Font.Bold = True
End Sub

Press...