219
217 220
218 221
219 222
220 223
221 224
222 225
223 226
224 227
225 228
226 229
227 230
228 231
229 232
230 233
231 234
232 235
233 236
234 237
235 238
236 239
237 240
238 241
239 242
240 243
241 244
242 245
243 246
244 247
245 248
246 249
247 250
248 251
249 252
250 253
251 254
252 255
253 256
254 257
255 258
256 259
257 260
258 261
259 263
260 264
261 265
262 266
263 267
264 268
265 269
266 270
267 271
268 272
269 273
270 274
271 275
272 276
273 277
274 278
275 283
276 284
277 286
278 287
Introduction
In its broadest sense, a macro is a sequence of instructions that automates some aspect of Excel so that you can work more efficiently and with fewer errors. You might create a macro, for example, to format and print a month-end sales report. After you develop the macro, you can execute it to perform many time-consuming procedures automatically.
Macros are written in VBA, which stands for Visual Basic for Applications. VBA is a programming language developed by Microsoft and a tool used to develop programs that control Excel.
Excel programming terminology can be a bit confusing. For example, VBA is a programming language but also serves as a macro language. What do you call something written in VBA and executed in Excel? Is it a macro or is it a program? Excel’s Help system often refers to VBA procedures as macros, so this is the terminology used in this book.
You also see the term automate throughout this book. This word means that a series of steps are completed automatically. For example, if you write a macro that adds color to some cells, prints the worksheet, and then removes the color, you have automated those three steps.
You’re probably aware that people use Excel for thousands of different tasks. Here are just a few examples:
Keeping lists of things, such as customer names and transactions
Budgeting and forecasting
Analyzing scientific data
Creating invoices and other forms
Developing charts from data
The list could go on and on. The point is simply that Excel is used for a wide variety of tasks, and everyone reading this book has different needs and expectations regarding Excel. One thing most readers have in common, however, is the need to automate some aspect of Excel, which is what macros (and this book) are all about.
About This Book
This book approaches the topic of Excel macros with the recognition that programming VBA takes time and practice — time that you may not have right now. In fact, many analysts don’t have the luxury of taking a few weeks to become expert at VBA. So instead of the same general overview of VBA topics, this book provides some of the most commonly used real-world Excel macros.
Each section in the book outlines a common problem and provides an Excel macro to solve the problem — along with a detailed explanation of how the macro works and where to use it.
Each section presents the following:
The problem
The
