Excel 2019 Power Programming with VBA. Michael Alexander
Читать онлайн книгу.
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 A1—a 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:
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 Panic—You 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