Lecture №7. Working with Range Objects
In This Chapter
1. Finding out why Range objects are so important
2. Understanding the various ways of referring to ranges
In this chapter, I dig a bit deeper into Excel’s dungeons and take a closer look at Range objects. Excel is all about cells, and the Range object is acontainer for cells. Why do you need to know so much about Range objects? Because much of the programming work you do in Excel focuses on Range objects. You can thank me later.
A Quick Review
A Range object represents a range contained in a Worksheet object. Range objects, like all other objects, have properties (which you can examine and sometimes change) and methods (which perform actions on the object).
A Range object can be as small as a single cell (for example, B4) or as large as every one of the 17,179,869,184 cells in a worksheet (A1:XFD1048576).
When you refer to a Range object, the address is always surrounded by double quotes, like this:
Range(“A1:C5”)
If the range consists of one cell, you still need the quotes:
Range(“K9”)
If the range happens to have a name (created by using the Formulas➪Defined Names➪Define Name command in Excel), you can use an expression like this:
Range(“PriceList”)
Unless you tell Excel otherwise by qualifying the range reference, it assumes that you’re referring to a range on the active worksheet. If anything other than a worksheet is active (such as a chart sheet), the range reference fails, and your macro displays an error message and stops running.
As shown in the following example, you can refer to a range outside the active sheet by qualifying the range reference with a worksheet name from the active workbook:
Worksheets(“Sheet1”).Range(“A1:C5”)
If you need to refer to a range in a different workbook (that is, any workbook other than the active workbook), you can use a statement like this:
Workbooks(“Budget.xlsx”).Worksheets(“Sheet1”).Range(“A1:C5”)
|
|
|
A Range object can consist of one or more entire rows or columns. You can refer to an entire row (in this case, row 3) by using syntax like this:
Range(“3:3”)
You can refer to an entire column (the fourth column in this example) like this:
Range(“D:D”)
In Excel, you select noncontiguous ranges by holding down the Ctrl key while selecting various ranges with your mouse. Figure 8-1 shows a noncontiguous range selection. You shouldn’t be surprised that VBA also lets you work with noncontiguous ranges. The following expression refers to a two-area noncon- tiguous range. Notice that a comma separates the two areas.
Range(“A1:B8,D9:G16”)
Be aware that some methods and properties cause havoc with noncontiguous ranges. You may have to process each area separately by using a loop.
Other Ways to Refer to a Range
The more you work with VBA, the more you realize that it’s a fairly well- conceived language and is usually quite logical (despite what you may be thinking right now). Often, VBA provides multiple ways to perform an action. You can choose the most appropriate solution for your problem. This section discusses some of the other ways to refer to a range.
This chapter barely scratches the surface for the Range object’s properties and methods. As you work with VBA, you’ll probably need to access other properties and methods. The Help system is the best place to find out about them, but it’s also a good idea to record your actions and examine the code Excel generates. You’re probably getting tired of hearing that advice by now, but it really is good advice.
|
|
|
The Cells property
Rather than use the VBA Range keyword, you can refer to a range via the Cells property.
Notice that I wrote Cells property, not Cells object or even Cells collection. Although Cells may seem like an object (or a collection), it’s really not. Rather, Cells is a property that VBA evaluates. VBA then returns an object (more spe- cifically, a Range object). If this seems strange, don’t worry. Even Microsoft appears to be confused about this issue. In some earlier versions of Excel, the Cells property was known as the Cells method. Regardless of what it is, just understand that Cells is a handy way to refer to a range.
The Cells property takes two arguments: a row number and a column number. Both of these arguments are numbers, even though we usually refer to columns by using letters. For example, the following expression refers to cell C2 on Sheet2:
Worksheets(“Sheet2”).Cells(2, 3)
You can also use the Cells property to refer to a multi-cell range. The follow- ing example demonstrates the syntax you use:
Range(Cells(1, 1), Cells(10, 8))
This expression refers to an 80-cell range that extends from cell A1 (row 1, column 1) to cell H10 (row 10, column 8).
The following statements both produce the same result; they enter a value of 99 into a 10-by-8 range of cells. More specifically, these statements set the Value property of the Range object:
 |
The advantage of using the Cells property to refer to ranges becomes appar- ent when you use variables rather than actual numbers as the Cells argu- ments. And things really start to click when you understand looping, which I cover in Chapter 10.
Literature
1. Джон Уокенбах, Excel VBA Programming For Dummies 5 августа 1999 г.
2. Джон Мюллер, VBA for dummies 2003 г.
|
|
|
3. Ричард Мансфилд, Mastering VBA for Microsoft Office 2007 2008 г.
4. Билл Джелен, Трейси Сирстад, Excel 2013 VBA and Macros 2013 г
5. Ричард Мансфилд, Mastering VBA for Office 2010 2010 г
6. Ричард Шеперд, Excel 2007 VBA Macro Programming 2009 г
7. Майк Мак-Грат, EXCEL VBA IN EASY STEPS. 2017 г.
Lecture № 8. Using VBA and Worksheet Functions
In This Chapter
1. Using functions to make your VBA expressions more powerful
2. Using the VBA built-in functions
In previous chapters, I allude to the fact that you can use functions in your VBA expressions. There are three flavors of functions: those built into VBA (vanilla), those built into Excel (strawberry), and other functions writ- ten in VBA (chocolate). I provide a full and tasty explanation in this chapter. Functions can make your VBA code perform some powerful feats, with little or no programming effort required. If you like that idea, this chapter’s for you.
What Is a Function?
Except for a few people who think Excel is a word processor, all Excel users incorporate worksheet functions in their formulas. The most common work- sheet function is the SUM function, and you have hundreds of others at your disposal.
A function essentially performs a calculation and returns a single value. The SUM function, of course, returns the sum of a range of values. The same holds true for functions used in your VBA expressions: Each function does its thing and returns a single value.
VBA function examples
In this section, I present a few examples of using VBA functions in code. In many of these examples, I use the MsgBox function to display a value in a message box. Yes, MsgBox is a VBA function — a rather unusual one, but a function nonetheless. This useful function displays a message in a pop-up dialog box and also returns a value. For more details about the MsgBox func- tion, see Chapter 15.
A workbook that contains all the examples is available at this book’s website.
|
|
|
Displaying the system date or time
The first example uses VBA’s Date function to display the current system date in a message box:
 |
Notice that the Date function doesn’t use an argument. Unlike worksheet functions, a VBA function with no argument doesn’t require an empty set of parentheses. In fact, if you type an empty set of parentheses, the VBE will promptly remove them.
To get the system time, use the Time function. And if you want it all, use the Now function to return both the date and the time.
Here, the current date is passed as an argument to the Month function, which returns a value that is passed as an argument to the MonthName function.
Determining a file size
The following Sub procedure displays the size, in bytes, of the Excel execut- able file. It finds this value by using the FileLen function.
 |
Notice that this routine hard codes the filename (that is, it explicitly states the path). This isn’t a good idea. The file might not be on the C drive, or the Excel folder may have a different name. The following statement shows a better approach:
TheFile = Application.Path & “\EXCEL.EXE”
Path is a property of the Application object. It simply returns the name of the folder in which the application (that is, Excel) is installed (without a trailing backslash).
Identifying the type of a selected object
The following procedure uses the TypeName function, which returns the type of the selection on the worksheet (as a string):
 |
The selection could be a Range, a Picture, a Rectangle, a ChartArea, or any other type of object that can be selected.
The TypeName function is very versatile. You can also use this function to determine the data type of a variable.
Using Worksheet Functions in VBA
Although VBA offers a decent assortment of built-in functions, you might not always find exactly what you need. Fortunately, you can also use most of Excel’s worksheet functions in your VBA procedures. The only worksheet functions that you cannot use are those that have an equivalent VBA function.
VBA makes Excel’s worksheet functions available through the WorksheetFunction object, which is contained in the Application object. Here’s an example of how you can use Excel’s SUM function in a VBA statement:
Total = Application.WorksheetFunction.Sum(Range(“A1:A12”))
You can omit the Application part or the WorksheetFunction part of the expression. In either case, VBA will figure out what you’re doing. In other words, these three expressions all work exactly the same:
Total = Application.WorksheetFunction.Sum(Range(“A1:A12”))
Total = WorksheetFunction.Sum(Range(“A1:A12”))
Total = Application.Sum(Range(“A1:A12”))
My personal preference is to use the WorksheetFunction part just to make it perfectly clear that the code is using an Excel function.
Worksheet function examples
In this section, I demonstrate how to use worksheet functions in your VBA expressions.
Finding the maximum value in a range
Here’s an example that shows how to use Excel’s MAX worksheet function in a VBA procedure. This procedure displays the maximum value in column A on the active worksheet (see Figure 9-3):
 |
Literature
1. Джон Уокенбах, Excel VBA Programming For Dummies 5 августа 1999 г.
2. Джон Мюллер, VBA for dummies 2003 г.
3. Ричард Мансфилд, Mastering VBA for Microsoft Office 2007 2008 г.
4. Билл Джелен, Трейси Сирстад, Excel 2013 VBA and Macros 2013 г
5. Ричард Мансфилд, Mastering VBA for Office 2010 2010 г
6. Ричард Шеперд, Excel 2007 VBA Macro Programming 2009 г
7. Майк Мак-Грат, EXCEL VBA IN EASY STEPS. 2017 г.
Lecture № 9. Automatic Procedures and Events
In This Chapter
1. Knowing the event types that can trigger an execution
2. Finding out where to place your event-handler VBA code
You have a number of ways to execute a VBA Sub procedure. One way is to arrange for the Sub to be executed automatically. In this chapter, I cover the ins and outs of this potentially useful feature, explaining how to set things up so that a macro is executed automatically when a particular event occurs. (No, this chapter is not about capital punishment.)
Preparing for the Big Event
What types of events am I talking about here? Good question. An event is basically something that happens in Excel. Following are a few examples of the types of events that Excel can recognize:
✓ A workbook is opened or closed.
✓ A window is activated or deactivated.
✓ A worksheet is activated or deactivated.
✓ Data is entered into a cell or the cell is edited.
✓ A workbook is saved.
✓ An object, such as a button, is clicked.
✓ A particular key or key combination is pressed.
✓ A particular time of day occurs.
✓ An error occurs.
Most Excel programmers never need to worry about most of the events in this list. You should, however, at least know that these events exist because
Are events useful?
At this point, you may be wondering how these events can be useful. Here’s a quick example.
Suppose you have a workbook in which you enter values in column A. Your boss, a very compulsive person, tells you that he needs to know exactly when each number was entered. Entering data is an event: a WorksheetChange event. You can write a macro that responds to this event. That macro kicks in when- ever the worksheet is changed. If the change was made in column A, the macro puts the date and time in column B, next to the data point that was entered.
In case you’re curious, here’s what such a macro would look like. Probably a lot simpler than you thought it would be, eh?
By the way, macros that respond to events are very picky about where they are stored. For example, this Worksheet_Change macro must be in the Code module for that worksheet. Put it somewhere else, and it won’t work. More about this later (see “Where Does the VBA Code Go?”).
Just because your workbook contains procedures that respond to events doesn’t guarantee that those procedures will actually run. As you know, it’s possible to open a workbook with macros disabled. In such a case, all macros (even procedures that respond to events) are turned off. Keep this fact in mind when you create workbooks that rely on event-handler procedures.
Programming event-handler procedures
A VBA procedure that executes in response to an event is called an event- handler procedure. These are always Sub procedures (as opposed to Function procedures). Writing these event-handlers is relatively straightforward after you understand how the process works.
Creating event-handler procedures boils down to a few steps:
The procedure checks the day of the week; if it’s Friday, the code displays a reminder message for you.
To create the procedure that is executed whenever the Workbook Open event occurs, follow these steps:
Open the workbook.
Any workbook will do.
2. Press Alt+F11 to activate the VBE.
Дата добавления: 2019-03-09; просмотров: 219; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!