Excel 2019 Bible

1011

C H A P T E R

47

Seeing Some VBA Examples

IN THIS CHAPTER

Working with ranges

Working with charts

Modifying properties

Speeding up your VBA code

O

ur philosophy about learning to write Excel macros places heavy emphasis on examples.

Often, a well-thought-out example communicates a concept much better than a lengthy

description of the underlying theory. In this book, space limitations don’t allow us to

describe every nuance of VBA, so we prepared many examples. Don’t overlook the VBA Help system

for specific details. To get help while working in the Visual Basic Editor (VBE), press F1. For context-

sensitive help, select a VBA keyword, object name, property, or method before you press F1.

This chapter consists of several examples that demonstrate common VBA techniques. You may be

able to use some examples directly, but in most cases, you must adapt them to your own needs.

Working with Ranges

Most of what you do in VBA probably involves worksheet ranges. When you work with range objects,

keep the following points in mind:

■ Your VBA code doesn’t need to select a range to work with the range.

■ (^) If your code does select a range, its worksheet must be active.
■ The macro recorder doesn’t always generate the most efficient code. Often, you can use the
recorder to create your macro and then edit the code to make it more efficient.
■ We recommend you use named ranges in your VBA code. For example, a reference such as
Range("Total") is better than Range("D45"). In the latter case, you need to modify
the macro if you add a row above row 45.
■ (^) When you record macros that select ranges, pay close attention to Relative versus Absolute
recording mode. The recording mode that you choose can determine whether your macro
works correctly.
Excel® 2019 Bible, First Edition. Michael Alexander, Dick Kusleika and John Walkenbach.
© 2019 John Wiley & Sons, Inc. Published 2019 by John Wiley & Sons, Inc.