VBA object Model (2)

Excel Introduction to the object Model
Before we introduce the Excel object model, let's look at a simple example. Most factories are set according to this structure: the top is the factory headquarters, the second level is divided into various workshops, under the workshop and sub-teams. Organized in this way, a factory system was formed. The Excel object model is similar in that it looks complex but essentially simple and clear.
hint that anything that looks complicated is actually made up of some simple parts, or it's actually quite simple.
The object model of Excel is logically organized by hierarchies, one object can be a container for other objects, and can contain other objects, which in turn contain other objects. At the top is the Application object, the Excel application itself, which contains other objects in Excel, such as the Workbook object, and a workbook object that contains other objects, such as the worksheet object , and a Worksheet object can contain other objects, such as a Range object, and so on. This is the object model of Excel.
For example, the Range object's position in the Excel object model is:
Application Object
Workbook object
Worksheet Object
Range Object
Knowing where an object is located in the object model hierarchy, you can easily reference the object with VBA code, manipulate the object, and organize the objects in a specific way so that Excel can automate the task as you need it. Therefore, to master Excel VBA programming, you must understand the object model of Excel.
In the VBA Help system, there is a hierarchy chart of the Excel object model that you can refer to.

The

collection
Collection is an object that is a set of objects of the same type or related objects as their container. For example, the Workbooks object is a collection of all the workbook objects that are currently open, worksheets is a collection of all worksheet objects contained in a Workbook object.

References to

objects
  use VBA to work with the entire collection of an object, or a single object in a collection.
     Syntax: collection (object name)   or   collection (object index number)
       Description: Refers to an object in the collection, that is, an object name or an object index number that represents an object
for example, worksheets ("Sheet1") refers to the worksheet Sheet1 in the collection worksheets; if Sheet1 is the first sheet object in the collection, It can also be written as worksheets (1).
in particular, the Sheets collection consists of all the worksheets in the workbook, including the chart sheet. To refer to the first worksheet in a workbook, you can use statement sheets (1).
  Referencing a member of an object by a point operation
We can use a period to join an object name to qualify a reference to a member of an object, and also to specify the location of the object's members in the object hierarchy.
     Syntax :< object name; . < object name >.  .
      Description: The latter object is a member of the previous object, qualifying a reference to the object member contained in the previous object
For example, Application.workbooks (" Book1.xls "). Worksheets ("Sheet1"). Range ("A1")   indicates a reference to cell A1 in the worksheet Sheet1 on the workbook Book1, where application represents the Excel application itself and can be omitted. In particular, if BOOK1 is the currently active workbook, the above statement can be abbreviated to  worksheets ("Sheet1"). Range ("A1")  ; if Sheet1 is the current active worksheet, it can be shortened to  range ("A1")  . Therefore, if the workbook object is omitted from the reference, the current active workbook is used, and if the sheet object is omitted, the current active sheet is used.

Setting Object Variables
An object variable is a variable that represents a complete object, such as a worksheet or range of cells. Declare an object variable with the Dim or public statement.
Grammar:Dim (or public) < variable name > as < object name >
Description: Declare the < variable name > as a < object name > object.
You can generally set an object name directly to an objects, that is, any object. But if you know the object to which the variable will work, it's best to set it to a specific object.
For example, the statement: Dim DataArea as range, declaring the variable DataArea as a Range object.
When a variable is declared as an object variable, an object is assigned to the variable using the SET statement.
Grammar:Dim (or public) < variable name > as < object name >
Set < variable name >=< an object >
Description: Declare the < variable name > as a < object name > object and assign an object to the variable.
Now, let's take a look at the following two simple examples, which are to enter the value 666 in the A1 to B10 cell range in the worksheet Sheet1 of the workbook Book1, and format them as bold and italic.
Sub not set object variable ()
WorkBooks ("Book1.xls"). Worksheets ("Sheet1"). Range ("A1:b10"). value=666
WorkBooks ("Book1.xls"). Worksheets ("Sheet1"). Range ("A1:b10"). Font.bold=true
WorkBooks ("Book1.xls"). Worksheets ("Sheet1"). Range ("A1:b10"). Font.italic=true
End Sub
***************************************
Sub sets the object variable ()
Dim DataArea as Range
Set DataArea = WorkBooks ("Book1.xls"). Worksheets ("Sheet1"). Range ("A1:B10")
dataarea.value=666
Dataarea.font.bold=true
Dataarea.font.italic=true
End Sub
Comparing these two programs, the function is the same, but it can be seen that when we set the object variable, not only can reduce the manual input of duplicate code, and make the code has been significantly simplified.
In addition, for a slightly more complex program, after setting an object variable, you can make your code run faster by reducing the number of point operators to process.
When you have set the variable to run, you should release the variable to save memory space. Its syntax is:
Syntax: Set < variable name >=nothing

methods and properties of the object
After referencing an object or setting an object variable, we can perform the desired action or setting on the object. This requires the use of the object's methods and properties.
Methods of the Object
Objects have methods, and one way is to perform an action on the object. When you specify a method for an object, the objects and methods should be grouped together, separated by periods.
Grammar:< Objects >.< methods > < parameters >
Description: Specifies a method for an object. If the method has parameters or needs to specify parameters for a method with parameters, specify the parameters to perform further actions, and if the parameter returns a value, enclose the parameter with parentheses.
For example, the statement worksheets ("Sheet1"). Range ("A1:b2"). ClearContents, executes the ClearContents method of the Range object, clears the contents of the A1 to B2 cell range, but retains the formatting of the range, while the statement worksheets ("Sheet1"). Range ("A1:b2"). Clear, executes the clear method of the Range object, clears the contents of the A1 to B2 range, and removes all formatting.
Properties of the Object
Objects have properties that describe or set the characteristics of an object. You can use VBA to set properties on an object, to modify some properties of an object, to define the object, and to reference the property values of an object. When using attributes, you should combine objects and attributes, separated by periods.
Grammar:< Objects >.< properties > < parameters >
Description: Sets or references the properties of an object. If the attribute has parameters or if a parameter needs to be specified for an attribute with parameters, the parameter is specified to further describe the object, and if the parameter returns a value, parentheses are applied to both sides of the argument.
Grammar:< variables >=< objects >.< properties >
Description: Assigns the property value of an object to a variable so that it can be used in a program.
For example, a Range object has a Value property that can reference the property value of the object in VBA code, or you can modify the property, such as the following statement:
Worksheets ("Sheet1"). Range ("A1"). Value that refers to the value of cell A1 in worksheet Sheet1 on the current workbook.
Worksheets ("Sheet1"). Range ("A1"). value=666 the statement to change the value of cell A1 in the worksheet Sheet1 the current workbook to 666.
Dim Var as Variant
Var=myform.caption
The above two sentences assign the Caption property of the MyForm object to the variable var.
Tip (1) Most objects have a default property, such as the Value property of the Range object's default property. For default properties, you can omit the write of the attribute designator, which is range ("A1"). Value is the same as the meaning expressed in range ("A1"). Even so, it is still advisable to write the attribute designator to improve the readability of the program.
(2) An error is returned when accessing a property that does not exist for an object.
Parameters for methods and properties
Most methods have parameters to further define the action. For example, the copy method of a Range object has a parameter that defines where to copy the contents of the cell range. Statement
Worksheets ("Sheet1"). Range ("A1"). Copy Worksheets ("Sheet2"). Range ("A1") copies the contents of cells A1 in the worksheet Sheet1 the current workbook to cell A1 in the worksheet Sheet2 the current workbook.
In some cases, the method has one or more optional parameters. If the method uses optional parameters, you should insert a blank placeholder for those parameters. For example, the Protect method for a Workbook object has three parameters, the password, structure, and window, corresponding to the appropriate option in the Protect Workbook dialog box, with the following syntax:
Syntax:< Workbook object;. Protect (Password,structure,windows)
Description: Protects the workbook from being modified. Three parameters are optional parameters, where password specifies a password, if omitted, you can unprotect the workbook without a password; The structure parameter specifies whether to protect the workbook structure; The Windows parameter specifies whether the workbook window is protected.
To protect the workbook "Book1.xls", you can use the statement:
Workbooks ("Book1.xls"). Protect "Xyzrq", True,false
Where the first parameter specifies the password of the insured workbook, note that the password is case sensitive, the second argument is true to indicate that the workbook structure is protected, and the third parameter is false to indicate that the window is not protected.
If you do not want to specify a password, use the statement:
Workbooks ("Book1.xls"). Protect, True,false
The statement omits the first argument, indicating that no protection password is specified, but that the parameter must be represented by a comma placeholder where the parameter appears.
Let's look at the following statement:
Workbooks ("Book1.xls"). Protect Structure:=true,windows:=false
The statement has the same functionality as the above statement, that is, the workbook does not specify a password to protect, the workbook structure is protected, but its window is not protected. The difference is that the statement uses a named parameter and does not use blank placeholders for omitted arguments. Therefore, when a method has more than one optional parameter, but only some of the parameters in the VBA statement, using named arguments, you can use a blank placeholder for the omitted argument and make the code more readable. Note that a ": =" connection is used between the parameter name and the value of the argument.
In another case, the properties (and methods) of the object may return a value. For properties (and methods) that return a value, you must enclose the argument in parentheses. For example, the Address property of a Range object returns a value that is a reference to a range of cells with 5 optional parameters. If the following statement is written:
Range ("A1"). Address false, an error occurs because the parameter is missing parentheses. The correct expression is as follows:
Range ("A1"). Address (False)
Or use a named parameter range ("A1"). Address (Rowabsolute:=false).
Methods and properties of a collection
A collection object typically has special properties and methods that you can use to manage the object. Typically, the collection object has the Add method, the Item method, and the Remove method, and there is always a Count property to return the number of objects in the collection.

Summary of objects
Below, give a brief summary of the knowledge described earlier.
In Excel, the Application object represents the Excel application itself, and all other objects begin with it. Each object has its own methods and properties, and some methods and properties of some objects are the same.
In general, we think that we need to select objects before we work on the selected objects, which is the case with the macro recorder. In fact, it is more efficient and fast to perform actions directly on an object without making a selection.
In most cases, you need to refer to an object indirectly by referencing the collection where the object resides. For example, Workbooks ("Book1.xls") references a Workbook object named Book1 in the workbook collection.
property to return a reference to another object, be sure to recognize this. For example, statement range ("A1"). Font.bold=true, the Font property returns a Font object contained in the Range object.
To refer to an object, you can use a number of different methods. Depending on the actual environment in which the program is run, you can determine which method to use, with ease and ease of understanding as the principle.

Event for Object
The Excel object model is characterized by object-oriented programming, but VBA is also committed to the event-driven programming model. When an event occurs on an object, the corresponding program runs. An event can be triggered by an application, or it can occur when an operation is in progress.
For example, in the Project window of the VBE editor, double-click the ThisWorkbook object under the Microsoft Excel object model, with two drop-down list boxes at the top of the code window on the right, with a list of objects on the left and a list of procedures on the right. Select an object from the list of objects on the left, and the event that corresponds to the object is listed in the list on the right.
You can customize the application with the object's events. For example, when you open a workbook, the Welcome window appears, which requires selecting the Open event for the ThisWorkbook object and calling the program that displays the Welcome window in the private Sub workbook_open () procedure.

two important statements for working with objects and collections
If you want to use VBA to work effectively with the Excel object model, you need to use the with ... frequently. End with statement and for each ... Next statements, which simplify the processing of objects and collections.
With ... End with statement
With ... The END with statement can perform a series of operations on an object without having to repeatedly indicate the name of the object. Its syntax is:
With<Objects >
[Statement Code]
End with
Where the,< object > represents the specific object to which the WITH statement performs an action, [statement code] is one or more statements that perform an operation on an object, preceded by a dot operator.
Consider the following program, which operates on the cell range A1:B10 on worksheet Sheet1 in the current workbook, setting the font style, font size, underline, and font color properties for the range.
Sub setting Format 1 ()
Worksheets ("Sheet1"). Range ("A1:b10"). Font.Name = "Arial"
Worksheets ("Sheet1"). Range ("A1:b10"). Font.fontstyle = "Bold Italic"
Worksheets ("Sheet1"). Range ("A1:b10"). Font.Size = 10
Worksheets ("Sheet1"). Range ("A1:b10"). Font.underline = xlUnderlineStyleSingle
Worksheets ("Sheet1"). Range ("A1:b10"). Font.ColorIndex = 3
End Sub
The process can use the with ... End with statement, as the following program performs exactly the same as the above program:
Sub setting Format 2 ()
With Worksheets ("Sheet1"). Range ("A1:b10"). Font
. Name = "Arial"
. FontStyle = "Bold Italic"
. Size = 10
. Underline = xlUnderlineStyleSingle
. ColorIndex = 3
End with
End Sub
Cannot use a with ... The END with statement to set several different objects, but you can place the with block in another and produce a nested with statement, using the full object reference in the inner layer with a block to indicate the members of the object in the outer layer with block, for example:
With MyObject ' MyObject is the name of an object
. Height = 100 ' and myobject.height statements function the same
. Caption = "Hello World" and myobject.caption statement function the same
With. Font
. Color = Red ' and MyObject.Font.Color statements work the same
. Bold = True ' and MyObject.Font.Bold statements function the same
End with
End with
Note: This example and the two examples below are from the VBA Help system.
TipsIn general, it is recommended that you do not jump in or out of the With block. If the statement in the With block is executed, but the with or end With statement is not executed, a temporary variable containing a reference to the object will remain in memory until you exit the procedure.
Using the WITH statement will not only prevent you from repeatedly entering the same code, making your program code more concise and, more importantly, enabling your program to run faster. In the "format" program above, you can feel a slight difference in speed between the two programs, if the amount of data is increased, the difference between this speed is more obvious.
Give two more examples to deepen the ... End with statement.
The following example fills in the value 30 for a cell in a cell range A1 to C10 in a worksheet Sheet1 the current workbook, the font in the cell is in bold format, and the inner cell color is set to yellow.
Sub FormatRange ()
With Worksheets ("Sheet1"). Range ("A1:C10")
. Value = 30
. Font.Bold = True
. Interior.Color = RGB (255, 255, 0)
End with
End Sub
The following is an example of nested with statements, which is more efficient when nested with statements. The example inserts a formula in cell A1 in worksheet Sheet1 on Workbook Book1, and then formats the font in that cell.
Sub Myinput ()
With Workbooks ("Book1"). Worksheets ("Sheet1"). Cells (1, 1)
. Formula = "=sqrt (50)"
With. Font
. Name = "Arial"
. Bold = True
. Size = 8
End with
End with
End Sub
For Each ... Next statement
For Each ... The next statement iterates through each element in the collection or array and executes the code in the statement repeatedly. Its syntax is:
For each<Elements >Inch<Collections/Arrays >
[Statement Code]
[Exit for]
[Statement Code]
Next [element]
Where the,< element > is a required parameter that represents the variable;< collection/array > representing the collection or array of objects that is used to traverse the collection or array, and [statement code] is an optional parameter for the code that performs an operation on each element of the collection or array; [Exit for] The statement exits the loop in the middle, and the [element] after next is omitted.
When there is at least one element in the collection or array, you should enter for each ... Next statement. The first element in the collection or array is executed for each ... Next statement, and then executes the statement code for the second element, which exits the loop when all elements in the collection or array have finished executing. If the statement code in the loop places the exit For statement, it exits the loop when executed to the statement, and the exit For statement is usually placed in a conditional judgment statement.
You can put a for each ... Next statement is placed in another for each ... A nested loop is formed in the next statement. In each for each ... The < elements > variables in the next statement should be different.
Here are a few examples to illustrate the ... The usage of the next statement.
The following example is quoted from the VBA Help system. It uses the For each ... The next statement searches for the Text property of all members in the collection, looking for a "Hello" string. In the example, MyObject is a text-oriented object and is a member of the MyCollection collection, and these two names are common names used for demonstration purposes and can be debugged in the VBE editor after the actual objects are assigned.
Sub Sample ()
Dim Found, MyObject, mycollection
Found = False ' Sets the variable initial value.
For every MyObject in mycollection ' to cycle through each member
If Myobject.text = "Hello" Then ' determines whether the value of the Text property equals "Hello"
Found = True ' If the value of the Text property equals ' Hello ', set the value of the variable Found to True
Exit for ' Quit Loop '
End If
Next
End Sub
The following example displays the names of all worksheets in the current workbook and is displayed with the MsgBox function. If there are 3 worksheets in the current workbook, Loop 3 times and call the MsgBox function 3 times accordingly.
Sub Display sheet name ()
Dim ws as Worksheet
For each ws in Activeworkbook.worksheets
MsgBox ws. Name
Next ws
End Sub
The following example closes all workbooks except the current workbook, using the If ... in the code. Then statement to determine whether the workbook is the current workbook, or if it is not the current workbook, close the workbook.
Sub Close Workbook ()
Dim WB as Workbook
For each WB in Workbooks
If WB. Name <> Activeworkbook.name then WB. Close
Next WB
End Sub
The following example requires that you select a range of cells in the worksheet before you run the code. The program loops through the cells in the selected range of cells and converts each cell's value to uppercase using the VBA UCase function.
Sub converted to uppercase ()
Dim Cell as Range
For each Cell in Selection
Cell.value = UCase (cell.value)
Next Cell
End Sub

How to get Help
In the process of writing code using VBA, you will inevitably encounter problems, then you need help. In addition to other people, in fact, Excel has a good help system, at least in the following three ways.
Record a macro. By recording a macro, you can learn how to use the knowledge of objects, methods, and properties in a way that compares to the process of doing it.
Use the VBA Help system. Here are detailed information about the objects, methods, properties, and other knowledge of Excel. You can press ENTER after you enter a keyword in the upper-right corner of the VBE editor in the type a question to help text box, and the VBE will display all the related results that you have searched for, and you can choose which theme you want to see. When you click on the selected topic, information about the topic pops up, and you can click on the text link "See", "Sample", "Apply to", "features" to see more detailed information.
Use the Object Browser. In the VBE editor, the Object Browser window is displayed by selecting the menu view-Object Browser or pressing the F2 key or selecting the Object Browser button on the toolbar. Here, all the methods, properties, and events are listed for each available object to see the procedures and constants in the project. Where the Flying Small cube icon indicates that the member is a method, the bright lightning bolt icon indicates that the member is an event, and the index card icon indicates that the member is an attribute. You can select the object library and enter the values you want to search for in the search box. In the Search Results window, you display the matching text, select an object, display its class in the Classes window, select a class, and on the right side will display its members, including methods, properties, and constants. In the bottom window, more information about the object is displayed. You can go directly to the Help topic for the object by pressing the F1 key or clicking the sign icon.
In addition to providing a way to view the Excel object model, the Object Browser also allows you to view information about the project that you are developing, making it easy to quickly browse through all the components in the project and the details of the specific process.

VBA object Model (2)