ActiveSheet.Cells.ClearContents
Getting information from a cell
If you need to get the contents of a cell, VBA provides several properties. The following are the most commonly used properties:
The Formula property returns the formula in a single cell, if the cell has one. If the cell doesn't contain a formula, it returns the value in the cell. The Formula property is a read/write property. Variations on this property include FormulaR1C1, FormulaLocal, and FormulaArray. (Consult the Help system for details.)
The Value property returns the raw, unformatted value in the cell. This property is a read/write property.
The Text property returns the text that is displayed in the cell. If the cell contains a numeric value, this property includes all the formatting, such as commas and currency symbols. The Text property is a read-only property.
The Value2 property is just like the Value property, except that it doesn't use the Date and Currency data types. Rather, this property converts Date and Currency data types to Variants containing Doubles. If a cell contains the date 5/1/2019, the Value property returns it as a Date, while the Value2 property returns it as a double (for example, 43586).
The Offset property
The Offset property, like the Range and Cells properties, also returns a Range object. But unlike the other two methods discussed, the Offset property applies only to a Range object and no other class. Its syntax is as follows:
object.Offset(rowOffset, columnOffset)
The Offset property takes two arguments that correspond to the relative position from the upper-left cell of the specified Range object. The arguments can be positive (down or to the right), negative (up or to the left), or 0. The example that follows enters a value of 12 into the cell directly below the active cell:
ActiveCell.Offset(1,0).Value = 12
The next example enters a value of 15 in the cell directly above the active cell:
ActiveCell.Offset(-1,0).Value = 15
If the active cell is in row 1, the Offset property in the preceding example generates an error because it can't return a Range object that doesn't exist.
The Offset property is useful, especially when you use variables in looping procedures. We discuss these topics in the next chapter.
When you record a macro using the relative reference mode, Excel uses the Offset property to reference cells relative to the starting position (that is, the active cell when macro recording begins). For example, we used the macro recorder to generate the following code. We started with the cell pointer in cell B1, entered values into B1:B3, and then returned to B1.
Sub Macro1() ActiveCell.FormulaR1C1 = "1" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "2" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "3" ActiveCell.Offset(-2, 0).Range("A1").Select End Sub
The macro recorder uses the FormulaR1C1 property. Normally, you want to use the Value property to enter a value in a cell. However, using FormulaR1C1 or even Formula produces the same result. Also, the generated code references cell A1a cell that wasn't even involved in the macro. This notation is a quirk in the macro recording procedure that makes the code more complex than necessary. You can delete all references to Range ("A1"), and the macro still works perfectly.
Sub Modified_Macro1() ActiveCell.FormulaR1C1 = "1" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "2" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "3" ActiveCell.Offset(-2, 0).Select End Sub
In fact, you can enter this much more efficient version of the macro. In this version, you don't do any selecting.
Sub Macro1() ActiveCell = 1 ActiveCell.Offset(1, 0) = 2 ActiveCell.Offset(2, 0) = 3 End Sub
Essential Concepts to Remember
In this section, we cover some additional essential concepts for would-be VBA gurus. These concepts will become clearer when you work with VBA and read subsequent chapters:
Objects have unique properties and methods. Each object has its own set of properties and methods. Some properties and methods are common to various objects. For example, many objects in Excel have a Name property and a Delete method.
You can manipulate objects without selecting them. This idea may be contrary to how you normally think about manipulating objects in Excel. After all, to work with an object in Excel, you have to select that object manually first, right?Well, this is not so when using VBA. It's usually more efficient to perform actions on objects without selecting them first.However, when you record a macro, Excel records every step you take, including selecting objects before you work with them. These are unnecessary steps that may make your macro run more slowly. You can generally remove the lines of code in your recorded macro that selects objects.
It's important that you understand the concept of collections. Most of the time, you refer to an object indirectly by referring to the collection in which it's located. For example, to access a Workbook object named Myfile, reference the Workbooks collection as follows: Workbooks("Myfile.xlsx")
This reference returns an object, which is the workbook with which you're concerned.
Properties can return a reference to another object. For example, in the following statement, the Font property returns a Font object contained in a Range object. Bold is a property of the Font object, not the Range object.
Range("A1").Font.Bold = True
You can refer to the same object in many ways. Assume that you have a workbook named Sales, and it's the only workbook open. Then assume that this workbook has one worksheet, named Summary. You can refer to the sheet in any of the following ways:
Workbooks("Sales.xlsx").Worksheets("Summary") Workbooks(1).Worksheets(1) Workbooks(1).Sheets(1) Application.ActiveWorkbook.ActiveSheet ActiveWorkbook.ActiveSheet ActiveSheet
The method that you use is usually determined by how much you know about the workspace. For example, if more than one workbook is open, the second and third methods aren't reliable. If you want to work with the active sheet (whatever it may be), any of the last three methods would work. To be absolutely sure that you're referring to a specific sheet on a specific workbook, the first method is your best choice.
About the code examples
Throughout this book, we present many small snippets of VBA code to make a point or to provide an example. In some cases, this code consists of a single statement, or only an expression, which isn't a valid instruction by itself.
For example, the following is an expression:
Range("A1").Value
To test an expression, you must evaluate it. The MsgBox function is a handy tool for this:
For example, the following is an expression:
Range("A1").Value
To test an expression, you must evaluate it. The MsgBox function is a handy tool for this:
MsgBox Range("A1").Value
To try these examples, put the statement in a procedure in a VBA module, like this:
Sub Test() ' statement goes here End Sub
Then put the cursor anywhere in the procedure and press F5 to execute it. Also, make sure that the code is being executed in the proper context. For example, if a statement refers to Sheet1, make sure that the active workbook has a sheet named Sheet1.
If the code is just a single statement, you can use the VBE Immediate window. The Immediate window is useful for executing a statement immediately, without having to create a procedure. If the Immediate window isn't displayed, press Ctrl+G in the VBE.
Just type the VBA statement in the Immediate window and press Enter. To evaluate an expression in the Immediate window, precede the expression with a question mark (?), which is a shortcut for Print. For example, you can type the following in the Immediate window:
? Range("A1").Value
The result of this expression is displayed in the next line of the Immediate window.
Don't PanicYou Are Not Alone
If this is your first exposure to VBA, you're probably a bit overwhelmed by objects, properties, and methods. That's normal. No one is going to be a VBA expert in one day. VBA is a journey of time and practice. The good news is that you won't be alone on this journey. There are plenty of resources out there that can help you on your path. This section highlights a few resources you can leverage when you need a push in the right direction.
Read the rest of the book
Don't forget, the name of this chapter is Introducing Visual Basic for Applications. The remainder of this book covers many additional details and provides many useful and informative examples.
Let Excel help write your macro
One of the best places to get macro help is the macro recorder in Excel. When you record a macro with the macro recorder, Excel writes the underlying VBA for you. After recording, you can review the code, see what the recorder is doing, and then try to turn the code it creates into something more suited to your needs. For example, let's say you need a macro that refreshes all the pivot tables in your workbook and clears all of the filters in each pivot table. Writing this macro from a blank canvas would be a daunting task. Instead, you can start the macro recorder and record yourself refreshing all the pivot tables and clearing all the filters. Once you've stopped recording, you can review the macro and make any changes that you deem necessary.
Use the Help system
To a new Excel user, the Help system may seem like a clunky mechanism that returns a perplexing list of topics that has nothing to do with the topic you're searching. The truth is, however, once you learn how to use the Excel Help system effectively, it's often the fastest and easiest way to get extra help on a topic.
You just need to remember two basic tenets of the Excel Help system: location matters when asking for help, and you need to be connected to the Internet to use Excel's Help system.
Location matters when asking for help
In Excel, there are actually two Help systems: one providing help on Excel features and another providing help on VBA programming topics. Instead of doing a global search with your criteria, Excel throws your search criteria only against the Help system that is relevant to your current location. This essentially means that the help you get is determined by the area of Excel in which you're working. So, if you need help on a topic that involves macros and VBA programming, you'll need to be in the Visual Basic Editor while performing your search. This will ensure that your keyword search is performed on the correct Help system.
You need to be connected to the Internet
When you search for help on a topic, Excel checks to see if you're connected to the Internet. If you are, Excel takes you to the MSDN website where you can search for the topic on which you need help. If you aren't connected to the Internet, Excel gives you a message telling you that you need to be online to use Help.
Use the Object Browser
The Object Browser is a handy tool that lists every property and method available for every object. When you are in the VBE, you can bring up Object Browser in any of the following three ways:
Press F2.
Choose View Object Browser.
Click the Object Browser button on the Standard toolbar.
The Object Browser is shown in Figure 2.17.
FIGURE 2.17 The Object Browser is a great reference source.
The drop-down list in the upper-left corner of Object Browser includes a list of all object libraries to which you have access:
Excel itself
MSForms (if user forms are utilized in your workbook)
Office (objects common to all Microsoft Office applications)
Stdole (OLE automation objects)
VBA
The current project (the project that's selected in Project Explorer) and any workbooks referenced by that project
Your selection in this upper-left drop-down list determines what is displayed in the Classes window, and your selection in the Classes window determines what is visible in the Members Of panel.
After you select a library, you can search for a particular text string to get a list of properties and methods that contain the text. You do so by entering the text in the second drop-down list and then clicking the binoculars (Search) icon.
1 Select the library of interest.If you're not sure which object library is appropriate, you can select <All Libraries>.
2 Enter the object you're seeking in the drop-down list below the library list.
3 Click the binoculars icon to begin the text search.
The Search Results window displays the matching text. Select an object to display its classes in the Classes window. Select a class to display its members (properties, methods, and constants). Pay attention to the bottom pane, which shows more information about the object. You can press F1 to go directly to the appropriate help topic.
Object Browser may seem complex at first, but its usefulness to you will increase over time.
Pilfer code from the Internet
All the macro syntax you will ever need has likely been documented somewhere on the Internet. In many ways, programming has become less about the code one creates from scratch and more about how to take existing code and apply it creatively to a particular scenario.