VBA Object Model of Excel worksheet

Worksheet class and object of Excel VBA Project

1. worksheet classes and objects when creating a workbook:

1. Create classes and objects:

2. Class and object names:

2. Modify codename:

1. codename is read-only during running, that is, during running, you cannot use code to change this attribute of the worksheet. You can only read this attribute. Otherwise, an error dialog box is displayed as follows.

2. synchronous modification:

[1] codename can be modified, but can only be modified in the attribute window;

[2] when it is modified, the corresponding class name and worksheet object name will be changed accordingly.

3. codename can be Chinese and can be easily referenced:

[1] After changing codename to "Haha", you can directly use "Haha" to represent the worksheet named "experiment". The VBA code greatly facilitates access to this worksheet.

[2] Using codename to reference a worksheet test. The code and effects are as follows:

1 sub test1 () 2 debug. print "Haha. name = "& CHR (34) & haha. name & CHR (34) 3 debug. print "Haha. codename = "& CHR (34) & haha. codename & CHR (34) 4 end sub

 

Iii. Impact of codename changes on worksheet objects:

Let's take a look at the experiment. The Code is as follows:

1. "Haha" worksheet code:

1 Public Value As Integer

2. thisworkbook module code:

1 sub Test2 () 2 for I = 1 to 33 debug. print "=" & CSTR (I) & "Times =" 4 debug. print "Before change: Haha. value = "& CSTR (haha. value) 5. value = haha. value + 26 debug. print "changed: Haha. value = "& CSTR (haha. value) 7 next8 end sub

3. Change codename from "Haha" to "sht", and then execute the following code:

1 sub test3 () 2 debug. print "after the codename is changed from" Haha "to" sht "from the Property Window: sht. value = "& CSTR (SHT. value) 4 end sub

It can be seen that when the codename is modified, the Class Name of the class representing its worksheet will be changed, indicating that the original object of its worksheet will be destroyed (inaccessible ), the Worksheet object is reinitialized with the class represented by the new class name.

Iv. Summary:

1. codename is read-only during the running of VBA code. To modify codename, you can only manually modify it in the Properties window;

2. The class names of the VBA code class associated with the codename and worksheet and the object names of the worksheet in the VBA code are synchronized;

3. When the codename is modified, the worksheet object will be reinitialized from the worksheet class represented by the new class name, and the data and status of the original worksheet object will be lost;

4. codename can be Chinese;

5. Name is only the label name of the worksheet. It cannot be referenced as a worksheet in VBA code;

6. When the workbook is opened/the codename of the worksheet is changed/the worksheet is added/the worksheet is moved into the current workbook, the worksheet object will be initialized or reinitialized from the worksheet class;

7. When the workbook is closed, the worksheet codename is changed, the worksheet is deleted, and the worksheet is moved to another workbook, the existing worksheet objects are destroyed;

8. during the period between 6 and 7, the worksheet object exists, and any VBA code operations on the worksheet object will be saved in various properties of the worksheet object, of course, some changes will also be reflected on the Excel interface;

9. the global variables declared in the worksheet class are also the attributes of the worksheet. All the modifications made in the above 6-7 process will be saved in its memory. Of course, the modifications made to these declared global variables, it will not be permanently saved as other inherent properties of the worksheet object. These modifications will be lost when the lifecycle is exceeded. The last State will no longer exist when it is initialized, if necessary, pay attention to the localization of these global variables (meaning to save the result data ).

VBA Object Model of Excel worksheet