Taking a Brief Look at Variables
Another concept you will see throughout the macros in this book is the concept of variables. We need to
dedicate a few words to this concept because it plays a big part in most of the macros you will encounter
here.
You can think of variables as memory containers that you can use in your procedures. There are
different types of variables, each tasked with holding a specific type of data.
Some of the common types of variables you will see in this book are
➤ String: Holds textual data
➤ Integer: Holds numeric data ranging from –32,768 to 32,767
➤ Long: Holds numeric data ranging from –2,147,483,648 to 2,147,483,647
➤ Double: Holds floating point numeric data
➤ Variant: Holds any kind of data
➤ Boolean: Holds binary data that returns True or False
➤ Object: Holds an actual object from the Excel object model
The term used for creating a variable in a macro is declaring a variable. You do so by entering Dim
(an abbreviation for dimension), the name of your variable, and then the type. For instance:
Dim MyText as String
Dim MyNumber as Integer
Dim MyWorksheet as Worksheet
After you create your variable, you can fill it with data. Here are a few simple examples of how you
would create a variable and assign a value to it:
Dim MyText as String
Mytext = Range(“A1”).Value
Dim MyNumber as Integer
MyNumber = Range(“B1”).Value * 25
Dim MyObject as Worksheet
Set MyWorksheet = Sheets(“Sheet1”)
The values you assign to your variables often come from data stored in your cells. However, the
values may also be information that you yourself create. It all depends on the task at hand. This
notion becomes clearer as you go through the macros in the book.
About the Macros in This Book
As we mention in the Introduction, the macros in this book are designed to get you up and running
with VBA in the quickest way possible. Each macro tackles a common task that benefits from automa-
tion. The idea here is learning through application. This book is designed so that you can implement
the macro, while getting a clear understanding of what the macro does and how it works.
Getting the sample files
Each macro in this book has an associated sample file. These sample files give you the ability to see
the macro working, as well as the ability to review the code. You can also use the sample files to copy
and paste the code into your environment (as opposed to typing each macro in from scratch). See
the “About the Companion Website” section in this book’s Introduction for complete instructions on
how to download the sample code.
Using the sample files
Each macro in this book has detailed instructions on where to copy and paste the code. You should
open the sample file associated with the macro, go to the Visual Basic Editor (by pressing Alt+F11),
and then copy the code. After you’ve copied the code, you can go to your workbook, go into the
Visual Basic Editor, and paste the code into the appropriate location.
Note that in some of the Macro examples in this book, you need to change some aspect of the macro
to suit your situation. For instance, Macro 12 in Part II demonstrates how to open all the Excel files in
a directory. In that example, we point to the C:\Temp\ directory. Before you use this particular
macro, you need to edit that portion of the macro to point to your target directory.
If a macro is not working for you, it’s probably because you need to change some component of the
macro. Pay special attention to Range addresses, directory names, and any other hard-coded names.
We built these hard-coded values into the macro for demonstration purposes; with the full intent
that you, the reader, would edit the macro to alter these hard-coded names to fit your scenario.
Things to keep in mind
Here are some final things to keep in mind while working with the macros in this book:
➤ Macro-enabled file extensions: Remember that any file that contains a macro must have
the .xlsm file extenstion. See the section called “Macro-enabled file extensions” in this Part
for more details.
➤ Macro security: Keep in mind that Excel will not run macros until they are Enabled. As you
implement these macros, it’s important to understand the steps that you and your custom- ers
will need to take to comply with Excel’s macro security measures. The section in this Part called
“Macro security in Excel 2010” highlights these steps.
➤ You cannot undo macro actions: When working in Excel, you can often undo the actions
you have taken. This is because Excel keeps a log (called the undo stack) that records the last
100 actions you have taken. However, running a macro automatically destroys the undo
stack, clearing the log of the actions you have taken. You must keep this in mind as you start
writing and running your own macros. You cannot undo the action you take in a macro.
➤ Where to go from here: As mentioned before, these macros were designed to get you started with
VBA. If you find a developing passion for Excel VBA, you may want to know where to get a more
detailed reference on Excel VBA in general. Allow us to recommend Excel 2010 Power Programming
with VBA by John Walkenbach (Wiley) as the next step in your learning. This reference is a
comprehensive guide to VBA, diving deeper into the Excel Object model.
