VBA practical development guide (2)

(4) forms and related control objects
The VBA editor is very similar to VB, so it also provides forms and related controls. However, due to the characteristics of office, there are some differences between forms, controls and VB in VBA.

1. User form knowledge point

A form is an interface for implementing program and user interaction. It not only improves the interface friendliness of the program, but also further enhances and improves the functions of the program. In the VBA editor, you can create a user form. In the project resource manager, right-click the project and choose "Insert> User form" from the menu that appears, the default form and control toolbox are displayed on the design interface. The control toolbox is similar to the control toolbox of VB, as shown in 3.

 

2. Common controls for user forms

Although the controls used for VBA form development are similar to those used for Vb, their attributes and methods differ from those used for VB:

(1) label control: this control is used to display uneditable text. The default attribute is the caption attribute and the default event is the click event.

(2) text box control: this control is used to display editable text information. It is the most common editing control in VBA development. The default attribute is the value attribute, and the default event is the change event.

(3) Composite Box control: This control combines the list box with the text box. You can enter and select the list box. The default attribute is the value attribute, and the default event is the change event.

(4) list box control: this control is used to display the value list. You can select one or more list items. The list box in VBA can be used in the form of list and option buttons or check boxes, the default attribute is the value attribute, and the default event is the click event.

(5) check box control: this control is used to display the selected status, that is, allow users to select one from two values (such as true or false. If selected, the tag is displayed. The default attribute is the value attribute, and the default event is the click event.

(6) Option Button Control: this control is used to display the selection status of each item in multiple options. The default attribute is the value attribute, and the default event is the click event.

(7) switch button control: this control is used to display the selection status. The default attribute is the value attribute, and the default event is the click event.

(8) frame control: this control is used to create a control group with a function or visual angle. The default event is a click event.

(9) command button control: this control is used to start, end, or interrupt operations. Its click event is the most common event code in form programming. The default attribute is the value attribute, the default event is a click event.

(10) Table header control: this control is used to display a series of related controls as a multi-table set. The default attribute is the selecteditem attribute, and the default event is the change event.

(11) Multi-page control: this control is used to implement multi-page content in the form of a single control. It is useful when processing a large amount of information in different categories. The default event is a change event.

(12) scroll bar control: this control is used to return or set the variable value based on the position of the Rolling block. The default attribute is the value attribute, and the default event is the change event. To create a horizontal or vertical scroll bar, you can drag the control points horizontally or vertically when designing the form.

(13) Rotating button control: this control is used to increase or decrease variable values. The default attribute is the value attribute, and the default event is the change event.

(14) image control: this control is used to display images. Supported image file formats include BMP, cur, GIF, ICO, JPG, and WMF. The default event is a click event.

In the VBA form design, you can select all the controls used in development and drag them directly to the toolbox. A "control group" is added to the toolbox ", in the form design, you can directly place the control group on the form, greatly improving the efficiency (this feature is unique to VBA ).

If you want to use other widgets not provided in the VBA standard control box, right-click the toolbox and select the "additional control" command in the displayed menu. In the displayed dialog box, select.

3. Common events of user forms

Common events of user forms are different from those of VB forms.

(1) initialize event: This event occurs after the object is loaded and before the object is displayed. It usually initializes the variable value or sets the properties of the control in this event.

(2) queryclose event: This event occurs before the user form is closed. It usually checks the incomplete operations in the user form.

Cancel: an integer. If this parameter is set to a non-zero value, the user form cannot be closed.

Closemode: used to obtain the cause for triggering the queryclose event.

(3) terminate event: This event sets all referenced object variables as nothing constants, that is, deleting object references. This event occurs after the object is detached. If you exit the program abnormally, it will not be triggered.

(5) category modules
VBA Development also provides a class module structure, allowing developers to reasonably encapsulate common operations through classes to achieve code reuse and program structuring.

1. Create a class module

It is very easy to create a class module in VBA. In the project resource manager of the VBA Editor, right-click the project and choose "insert → class module" from the menu that appears, the added class module is displayed in "Project Resource Manager". The default name is "Class 1". Click this class module to set its attributes, generally, you need to set the "name" attribute for ease of use. Double-click this type of module to enter the code according to the form operation method.

2. Common class module events

The class module contains two events: Initialize and terminate.

(1) initialize event: this event is used to initialize the data used by the class module. This event is triggered when a class module is created in the program.

(2) terminate event: this event is triggered when the class instance in the program is deleted from the memory, and this event is only triggered when the program ends normally.

Iii. Details about Office XP development objects
VBA program development mainly consists of a large number of objects. Here, we have selected the knowledge of common office VBA development objects to facilitate quick query.

(1) Development objects of Word 2002
1. Application Object

This object represents the word application, through which all other objects in word can be accessed.

(1) activedocument attribute: return the document object, which represents the activity document.

(2) activeprinter attribute: return or set the name of the current printer.

(3) activewindow property: return the window object, representing the activity window.

(4) Documents attributes: return the documents set, which indicates all open documents.

(5) Selection attribute: return the selection object, which indicates the selected range or insertion point.

(6) Windows properties: Return to the Windows collection, representing all document windows.

(7) Activate method: Used to activate the specified object.

(8) Printout method: print all or part of the document.

(9) Quit method: Used to exit the word program.

2. Document Object

This object represents the document opened in word. You can obtain the document object through the syntax of "Documents (INDEX)". The index parameter indicates the document name or index number. In addition, you can use the activedocument attribute to obtain the currently edited document.

(1) attachedtemplate attribute: return the template object, representing the template associated with the activity document.

(2) builtindocumentproperties: return the documentproperties set, which represents all the built-in attributes of the specified document, such as the author, topic, or keyword.

(3) characters attribute: returns the characters set, which represents the characters in the document.

(4) tables attribute: return the tables set, which represents all tables in the document.

(5) checkgrammar method: Check whether the string has a syntax error.

(6) checkspelling method: used to check whether a character string has a spelling error.

(7) computestatistics: used to obtain statistical data of a specified document, such as the number of words and pages.

(8) printpreview method: switch between print preview and view mode.

(9) Save method: used to save a document or template.

(10) undo method: Used to cancel the last operation of the document.

3. Bookmark object

This object is an element in the bookmarks set, representing the bookmarks in the document. You can use the "bookmarks (INDEX)" syntax to obtain the bookmark object. The index parameter indicates the name or index number of the bookmark.

(1) Select method: used to select the specified bookmarks.

(2) Delete method: Used to delete the specified bookmarks.

4. Field object

This object is an element in the fields set and represents the fields in the document. You can use the syntax of "fields (INDEX)" to obtain the Field object.

5. template object

This object is an element in the templates set and represents a document template. You can use the "templates (INDEX)" syntax to obtain the template object.

6. fontnames object

This object represents a list of valid font names. The fontnames object can be obtained through the fontnames, landscapefontnames, or portraitfontnames attributes.

7. Options object

This object represents the word application and document options.

8. autotextentries collection object and autotextentry object

The autotextentries collection object is composed of autotextentry objects, representing all the entries in the template. The autotextentry object represents a single "" entry.

Insert method: used to replace the original content by inserting the "" entry in the specified range.

9. Selection object

This object represents the selected content of the current document. It can be a region or insertion point. Each document has only one selection object, and only one selection object can be activated at any time. The selection object can be obtained through the selection attribute.

(1) text attribute: return or set the text in the selected content.

(2) copy method: copy the specified object to the clipboard.

(3) paste method: paste the clipboard content to the selected area.

(4) typeparagraph: used to insert a new paragraph. It is equivalent to pressing the Enter key during editing.

(5) typetext: used to insert the specified text.

(6) wholestory method: Used to expand the selected content and contain the entire text part.

(7) goto method: Move the insert point to the specified position.

(8) homekey method: equivalent to pressing the "home" key.

(9) endkey method: equivalent to pressing the "end" key.

(2) Excel 2002 development object
1. Application Object

This object represents an Excel application. You can use this object to access all other objects in Excel.

(1) activecell attribute: return the range object, which represents the activity cells of the activity window or specified window.

(2) activechart attribute: return the chart object, representing the activity chart.

(3) activesheet attribute: return the worksheet object, representing the activity worksheet in the activity workbook.

(4) activeworkbook attribute: return the workbook object, representing the active workbook.

(5) cells property: returns a range object, representing all cells in the active workbook.

(6) Charts attribute: Return to the sheets set, representing all charts in the activity workbook.

(7) range property: returns the range object, representing the cell or cell area.

(8) sheets attribute: return the sheets set, which represents all worksheets of the current workbook.

(9) statusbar attribute: return the text of the status bar.

(10) workbooks attributes: return the workbooks set, which represents all opened workbooks.

(11) worksheets attributes: return the sheets set, which represents all worksheets of the active workbook.

(12) Calculate Method: used to calculate cells in a specified area.

(13) Evaluate Method: This method is used to escape the value of a string, and can be used to calculate the value in the string format.

(14) inputbox method: used to display the dialog box for receiving input and return the information entered in the dialog box.

(15) Quit method: Used to exit the Excel application.

2. addin object

This object is used to load macros. The addin object can be returned through the "addins (INDEX)" syntax. The index parameter is the loaded macro question or number.

Fullname attribute: returns the macro file name.

3. Chart object

This object represents charts in the workbook, including embedded charts and separate charts.

(1) chartarea attribute: returns the chartarea object, representing all chart areas in the specified chart.

(2) charttitle attribute: returns the charttitle object, which indicates the title of the specified chart.

(3) charttype attribute: return or set the chart type.

(4) datatable attribute: return the datatable object, representing the chart data table.

(5) properties: return the wall object of a 3D chart.

4. Workbook object

This object represents the workbook.

(1) styles attribute: returns the styles set, indicating all styles of the specified workbook.

(2) Add method: used to create a workbook and set it as an active workbook.

(3) open method: used to open a workbook.

(4) opentext method: This method is used to load text files, process them in a separate manner, and insert the processed text data in the worksheet.

5. worksheet object

This object represents a worksheet. You can use the "worksheets (INDEX)" syntax to obtain the worksheet object. The index parameter indicates the worksheet index number or name.

(1) autofilter attribute: Used to set whether to filter.

(2) usedrange attribute: return the range object, which indicates the used area in the specified worksheet.

(3) Printout method: used to print the specified object.

(4) printpreview: you can switch to the print preview view.

(5) Select method: used to select the specified object.

6. Range object

This object represents the selected region and is a common object in Excel Development.

(1) Address attribute: return the reference name for the specified region.

(2) areas attribute: returns the areas set, which indicates all regions in multiple selection areas.

(3) column attribute: return the sequence number of the first column in the specified area of the first block.

(4) columns attribute: returns the range object, which represents all columns in the specified area.

(5) columnwidth attribute: return or set the column width of all columns in the specified area.

(6) formula attribute: used to set the formula used by the range object.

(7) mergearea property: returns the range object, which indicates the merging range of the specified cell.

(8) mergecells attribute: whether the returned area contains merged cells.

(9) numberformat attribute: return or set the format code of the specified object.

(10) Row attribute: return the sequence number of the first row in the specified region of the first block.

(11) rows attribute: return the range object, which represents all rows in the specified area.

(12) Activate method: Used to activate an object.

(13) applynames method: used to apply a name to a specified cell.

(14) AutoFill: used to automatically fill a specified cell.

(15) autofit method: used to adjust the column width and Row Height to an appropriate value.

(16) Find method: This method is used to search for specific information in a region and returns the first cell containing the searched information.

(17) Merge method: used to create and merge cells from the specified range object.

(18) replace method: used for searching and replacing within a specified region.

(19) Subtotal method: used to create a Category summary for a specified region.

(3) Development object of PowerPoint 2002
1. Application Object

This object represents the PowerPoint application, through which all other objects in PowerPoint can be accessed.

(1) Active attribute: return whether the specified pane is activated.

(2) activepresentation attribute: return the presentation object, representing the presentation opened in the activity window.

(3) activewindow property: return the documentwindow object, representing the current document window.

(4) Presentations attributes: return the presentations set, which represents all opened presentations.

(5) slideshowwindows properties: Return to the slideshowwindows collection, representing all open slide show windows.

(6) Quit method: Used to exit the PowerPoint program.

2. documentwindow object

This object represents the document window. You can use the "windows (INDEX)" syntax to return the documentwindow object.

(1) activepane property: returns the pane object, representing the activity pane in the document window.

(2) panes property: returns the panes set, representing all the panes in the document window.

(3) viewtype attribute: return the view type in the specified document window.

3. Presentation object

This object represents the presentation, and the presentation object can be returned through the "presentations (INDEX)" syntax.

(1) builtindocumentproperties properties: return the documentproperties set, representing all document properties of the presentation.

(2) colorschemes property: returns the colorschemes set, representing the color scheme of the presentation.

(3) pagesetup attribute: returns the pagesetup object, which is used to control the slide page setting attribute of the presentation.

(4) slidemaster attribute: the slide master object is returned.

(5) slideshowsettings properties: return the slideshowsettings object, which represents the slide show settings of the presentation.

(6) slideshowwindow property: return the slide show window object.

(7) addtitlemaster method: Add a title master for the presentation.

(8) applytemplate method: design a template for the presentation application.

4. slideshowwindow object

This object represents the slide show window.

Isfullscreen attribute: Used to set whether to display the slide show window in full screen.

5. Master object
This object represents the slide master, title master, handout master, or remark master.

Textstyles property: return the textstyles set for the slide master, representing the title text, body text, and default text.

6. slide object

This object represents a slide.

(1) slideid attribute: the unique identifier of the returned slide.

(2) slideindex attribute: returns the index number of the slide in the slides set.

7. slideshowview object

This object represents the view in the slide show window.

(1) acceleratorsenabled attribute: Used to set whether to allow shortcut keys during slide show.

(2) currentshowposition property: returns the position of the current slide in the show.

(3) drawline method: draw a straight line in the specified slide show view.

(4) erasedrawing method: used to clear the straight lines drawn in the screening using the drawline method or drawing pen tool.

(5) gotoslide method: used to switch between specified slides.