Select the module’s name in the Project window.
2. Choose File➪Remove xxx, where xxx is the module name.
Or
Right-click the module’s name.
Choose Remove xxx from the shortcut menu.
Excel, always trying to keep you from doing something you’ll regret, will ask if you want to export the code in the module before you delete it.
Almost always, you don’t. (If you do want to export the module, see the next section.)
You can remove VBA modules, but there is no way to remove the other code modules — those for the Sheet objects or ThisWorkbook.
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 № 3. Introducing the Excel Object Model
In This Chapter
1. Introducing the concept of objects
2. Finding out about the Excel object hierarchy
Еveryone is familiar with the word object. Well, folks, forget the definition
you think you know. In the world of programming, the word object has a
different meaning. You often see it used as part of the expression object-ori- ented programming, or OOP for short. OOP is based on the idea that software deals with distinct objects that have attributes (or properties) and can be manipulated. These objects are not material things. Rather, they exist in the form of bits and bytes.
In this chapter, I introduce you to the Excel object model, which is a hierar- chy of objects contained in Excel. By the time you finish this chapter, you’ll have a reasonably good understanding of what OOP is all about — and why you need to understand this concept to become a VBA programmer. After all, Excel programming really boils down to manipulating the objects that make up Excel. It’s as simple as that.
The material in this chapter may be a bit overwhelming. But please take my advice and plow through it, even if you don’t fully grasp it at first. The impor- tant concepts presented here will make a lot more sense as you progress through the book.
Excel Is an Object?
|
|
|
You’ve used Excel for quite a while, but you probably never thought of it as an object. The more you work with VBA, the more you view Excel in those terms. You’ll understand that Excel is an object and that it contains other objects. Those objects, in turn, contain still more objects. In other words, VBA programming involves working with an object hierarchy.
At the top of this hierarchy is the Application object — in this case, Excel itself (the mother of all objects).
Climbing the Object Hierarchy
The Application object contains other objects. Following is a list of some of the more useful objects contained in the Excel Application:
✓ Addin
✓ Window
✓ Workbook
✓ WorksheetFunction
Each object contained in the Application object can contain other objects. For example, the following is a list of objects that can be contained in a Workbook object:
✓ Chart (which is a chart sheet)
✓ Name
✓ VBProject
✓ Window
✓ Worksheet
In turn, each of these objects can contain still other objects. Consider a Worksheet object, which is contained in a Workbook object, which is con- tained in the Application object. Some of the objects that can be contained in a Worksheet object are
✓ Comment
✓ Hyperlink
✓ Name
Wrapping Your Mind around Collections
Collections are another key concept in VBA programming. A collection is a group of objects of the same type. And to add to the confusion, a collection is itself an object.
Here are a few examples of commonly used collections:
✓ Workbooks: A collection of all currently open Workbook objects
✓ Worksheets: A collection of all Worksheet objects contained in a par- ticular Workbook object
✓ Charts: A collection of all Chart objects (chart sheets) contained in a particular Workbook object
✓ Sheets: A collection of all sheets (regardless of their type) contained in a particular Workbook object
You may notice that collection names are all plural, which makes sense (at least I think so).
|
|
|
“What are collections for?” you may rightfully ask. Well, for example, they are very useful when you want to do stuff with not just one worksheet, but with a couple of them or all of them. As you’ll see, your VBA code can loop through all members of a collection and do something to each one.
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 № 4. VBA Sub and Function Procedures
In This Chapter
1. Understanding the difference between Sub procedures and Function procedures
2. Executing Sub procedures (many ways)
Subs versus Functions
The VBA code that you write in the Visual Basic Editor is known as a proce- dure. The two most common types of procedures are Sub and Function.
✓ A Sub procedure is a group of VBA statements that performs an action (or actions) with Excel.
✓ A Function procedure is a group of VBA statements that performs a cal- culation and returns a single value (or an array).
Most of the macros you write in VBA are Sub procedures. You can think of a Sub procedure as being like a command: Execute the Sub procedure and something happens. (Of course, exactly what happens depends on the Sub procedure’s VBA code.)
A Function is also a procedure, but it’s quite different from a Sub. You’re already familiar with the concept of a function. Excel includes many work- sheet functions that you use every day (well, at least every weekday).
Examples include SUM, PMT, and VLOOKUP. You use these worksheet func- tions in formulas. Each function takes one or more arguments (although a few functions don’t use any arguments). The function does some behind-the- scenes calculations using those arguments, and then it returns a single value. The same goes for Function procedures that you develop with VBA.
|
|
|
Looking at Sub procedures
Every Sub procedure starts with the keyword Sub and ends with an End Sub statement. Here’s an example:
 |
This example shows a procedure named ShowMessage. A set of parentheses follows the procedure’s name. In most cases, these parentheses are empty. However, you may pass arguments to Sub procedures from other proce- dures. If your Sub uses arguments, list them between the parentheses.
When you record a macro with the Excel macro recorder, the result is always a Sub procedure that takes no arguments.
As you see later in this chapter, Excel provides quite a few ways to execute a VBA Sub procedure.
Naming Subs and Functions
Like humans, pets, and hurricanes, every Sub and Function procedure must have a name. Although it is perfectly acceptable to name your dog Hairball Harris, it’s usually not a good idea to use such a freewheeling attitude when naming procedures. When naming procedures, you must follow a few rules:
✓ You can use letters, numbers, and some punctuation characters, but the first character must be a letter.
✓ You can’t use any spaces or periods in the name.
✓ VBA does not distinguish between uppercase and lowercase letters.
✓ You can’t embed any of the following characters in a procedure name: #,
$, %, &, @, ^, *, or !. In other words, your procedure name can’t look like comic strip curse words.
✓ If you write a Function procedure for use in a formula, avoid using a name that looks like a cell address (for example, A1 or AK47). Actually, Excel allows such function names, but why make things more confusing than they are already?
✓ Procedure names can be no longer than 255 characters. (Of course, you would never make a procedure name this long.)
Ideally, a procedure’s name describes the routine’s purpose. A good prac- tice is to create a name by combining a verb and a noun — for example, ProcessData, PrintReport, Sort_Array, or CheckFilename.
|
|
|
Some programmers prefer using sentence-like names that provide a complete description of the procedure. Some examples include WriteReportToTextFile and Get_Print_Options_and_Print_Report. The use of such lengthy names has pros and cons. On the one hand, such names are descriptive and usually unambiguous. On the other hand, they take longer to type. Everyone devel- ops a naming style, but if your macro isn’t just a quick and dirty temporary macro, it’s a good idea to be descriptive and to avoid meaningless names such as DoIt, Update, Fix, and Macro1.
Executing the Sub procedure directly
The quickest way to execute this procedure is by doing so directly from the VBA module in which you defined it. Follow these steps:
Дата добавления: 2019-03-09; просмотров: 191; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!