VBA Development Practical Guide ??? Tang Dazhong Appendix Article
VBA (Visual Basic for Application) is the secondary development tool that is available from Office, which can bring great convenience to our daily office. This article describes the common objects developed by Office of Office, and its related properties, methods, and events (including Word, Excel, PowerPoint, etc.) and give relevant instance code.
First, VBA foundation 1. What is VBA
In Office For some cumbersome, repetitive operations, users can implement it through "macro". "Macro" is a command set composed of a series of commands and instructions, and its essence is a program consisting of VBA code. VBA is a universal automation language that Microsoft is shared in its developed application.
2. Recording / running macro
The initial development of VBA can obtain code by recording the "macro" operation, almost all operations in Office can obtain the code by recording "macro". Here, we learn from a simple "macro" recording: replace all "Computer" in Word to "Computers" and insert a 4-row 2 list.
In the Office, you can click "Tools → Macro → Record New Macro ..." menu command implementation, click the "Recording Macro" dialog, as shown in Figure 1.
?
Where the "Macro Name" box is used to set the name of the recorded macro, the default "macro1" can be used. "Toolbars" and "Keyboard" two buttons are used to set the macro trigger position. "Put the Macro Save" box can set the macro's storage location, which generally includes two types of "Normal.dot" and current documentation. It is recommended to save in the current document to avoid affecting all documents. In the "Description" box, you can enter the description of the macro. Click the "OK" button to start recording macros. The Macro Record toolbar will appear in the Document Editing interface. This toolbar has 2 buttons with a very similar recorder, the first button is "stop recording", the second button is "suspend recording". Control can be performed at any time during recording.
Then, the aforementioned tasks are then implemented, and the "Stop Recording" button is clicked after the operation is completed, and the recording is ended.
Note: You can use the mouse when you record the macro, but you cannot record the movement of the mouse in the document window, so you must implement these actions through the keyboard. In addition, all recorded "macro" in office, the first character of its name must be letters or Chinese characters, numbers and underscores (the name can be 255 characters), and the "macro" name is not allowed to contain spaces.
Run recorded Macro, click the Tool → Macro → Macro menu command. Select the macro you want to run in the "Macro" drop-down box that appears, and then click the "Run" button. Then Word will automatically reproduce the operations described above and avoid duplicate office operations.
3. Limitations of macros
Although most of the operation of Office can be saved with a "macro" method, the recorded "macro" reproduces a specific operation, and for some operations that need to be logical judgment and process control, it seems that it is not strong. If you automatically replace multiple different content, press the "macro" manner, you need to record multiple "macros" by replacing the amount, and each "macro" can only be used for a certain content, but Add trouble.
Recording "macro" has many limitations, in addition to the logical judgment and flow control operations, there are many. If the interaction ability is poor, the Office's built-in dialog cannot be displayed, and the user-defined form cannot be displayed and the complex toolbar or modification menu cannot be displayed.
4.VBA editing environment
Although the "macro" has many limitations, it can be added to the code to add logical control and design flow through the VBA editor after obtaining the VBA code by recording the Macro. Take Word 2002 as an example, "Macro" recording is completed, you can select the recorded macro in the Run Macro dialog and click the Edit button to display the VBA editing environment, as shown in Figure 2.
?
The VBA editing environment consists of a part of the engineering resource manager, an attribute window, and a code editing / form design window. List all currently open VBA projects in Engineering Explorer, the Properties window is used to set the properties of the related objects, and the code editing / form design window can enter the module's code or edit the form and class module. Second, VBA Development Shared Objects Detailed VBA and other object-oriented development languages, and there are very many objects, and different Office members provide a lot of development objects, such as toolbars, office assistants, built-in dialogs and windows. Body, etc.
(1) Toolbars and their control objects In Office, through the VBA development toolbar and menu, you need to use the COMMANDBAR, CommandBarbutton, CommandBarComboBox provided by Office, which is the toolbar, toolbar buttons, and toolbar down. These objects can produce a variety of complex toolbars or menus.
1.Commandbars collection
This collection represents all the toolbars of Office, which specifies the menu bar or toolbar with the name or index number, but only the name is specified for a menu, shortcut menu or submenu. If two or more custom menus or submenu names are the same, the first object having the name is returned.
Its add method is used to create a toolbar and return to the CommandBar object.
Syntax: Expression.Add (Name, Position, Menubar, Temporary)
Parameter Description:
Name is an optional Variant type that represents the name of the new toolbar. If omitted, use the default name; position is the optional Variant type, represents the location of the new toolbar. This parameter value can be set via VBA constant, such as MSOBARLEFT, MSOBARTOP, MSOBARRIGHT, MSOBARBOTTOM constant (setting new toolbar located in the software toolbar); MSOBARFLOATING constant (Mo can be moved by the new toolbar); MSOBARPOPUP constant (Representative New Toolbar) For shortcuts), etc .; Menubar is an optional Variant type for setting whether to replace the active toolbar with a new toolbar; TemPorary is an optional Variant type, which is used to set whether the new toolbar is temporarily valid.
2.Commandbar object
This object represents the toolbar in the application, and the control of the new toolbar is used as a carrier.
(1) Controls Properties: Returns the CommandBarControls object to represent all controls in the specified toolbar.
(2) NameLocal Properties: Returns the toolbar name set by the application version, such as the built-in toolbar setting for the software.
(3) Position attribute: Return or set the location of the toolbar, values can be set via VBA constants, such as Msobarleft, MsobArtop, Msobarright, Msobarbottom, Msobarfloating, MSOBARPOPUP, or MSOBARMENU, etc.
(4) TYPE Properties: Return or set the type of toolbar, the value can be set via the VBA constant, such as the MSObartypenorMal (Toolbar is a normal type), msobartypemenubar (toolbar is menu type), msobartypepopup (Toolbar is a pop-up menu type) Wait.
(5) Reset method: Reset the built-in toolbar to the default settings, which is very useful when recovering the original toolbar or menu. Resetting the built-in toolbar will delete the custom controls and restore its built-in controls.
3.CommandbarControls collection
This collection represents all the toolbar controls in the toolbar.
Its Add method is used to add a toolbar control in a CommandBarControls collection.
4.CommandbarControl object
The object represents the toolbar control, which can be defined using the CommandBarbutton, CommandBarComboBox, and CommandBarpopUp objects, and the control can be used by the above three objects, and COMMANDBARCONTROL can be used. Object. (1) BeGingROUP attribute: Set whether the toolbar control is set.
(2) CAPTION attribute: Used to set the title text of the toolbar control, and can be prompted as the default control screen.
(3) ID attribute: The function of setting the CommandBarbutton, CommandBarComboBox, and CommandBarControl objects, which can be directly set to the ID of the built-in toolbar control, so that the control has the corresponding function of the software, the custom control ID attribute It is necessary to set to 1.
(4) COPY method: Copy the toolbar control into the existing toolbar.
Syntax: Expression.copy (bar, before)
Parameter Description:
BAR is an optional Variant type, represents the target toolbar, if omitted, the control will be copied to the toolbar where it is, and the option is an optional Variant type, which represents the location of the new control in the specified toolbar, that is, the new control will be added. Before the control of this location, if omitted, the control will be copied to the end of the toolbar.
(5) Type Properties: Returns the type of toolbar control, can be referenced by VBA constants, and the common toolbar control type is shown in Table:
Constant Name Meaning
MSOCONTROLBUTTON Control button
MsoControlButtondropdown button with drop-down list
MsoControlButtonPopup button with pop-up menu
MsoControlComboX drop-down combination control box
MSOCONTROLDROPDOWN drop-down list control box
MSOCONTROLEDIT text box
MSOCONTROLEXPANDGRID Scalable form
MSOCONTROLGRAPHICCOMBO Image drop-down combination box
MSOCONTROLGRAPHICDROPDOWN Image drop-down list box
MSOCONTROLGRID form
MSOCONTROLPOPUP pop-up menu
?
?
5.commandbarbutton object
This object represents the button control in the toolbar.
(1) Onaction attribute: Return or set the Visual Basic code process name (the code process is not available), the process will run after clicking the button.
(2) STYLE Attribute: Return or set the display method of the toolbar button. Value can be set via VBA constants, and the common toolbar button control type is shown in the following table:
Constant Name Meaning
MSOBUTTONICON contains buttons with icons
MSOBUTTONCAPTION button contains the title
ButtonIndCaption contains buttons with icons and titles
MsobuttoniconAndCaptionBelow contains icons and titles, and the title is on the bottom button
MSOBUTTONICONAANDWRAPCAPTION contains icons and headings, and the title of the title automatic wrap
MSOBUTTONWRAPCAPTION contains the title, and the title is automatically wrapped.
?
?
?
(3) Tooltiptext properties: Return or set the screen prompt of the toolbar button control.
(4) FaceID Properties: Return or set the icon number of the toolbar button, set the appearance of the toolbar button instead of its function, the toolbar button of the custom icon, the FaceId property value is 0.
(5) CopyFace method: Copy the icon of the toolbar button to the clipboard.
(6) Pasteface method: Paste the icon content of the clipboard to the toolbar button control. (7) EXECUTE method: Run the process or built-in command corresponding to the toolbar control.
6.commandbarComboBox object
This object represents the combo control in the toolbar.
(1) DROPDOWNLINES attribute: Return or set the number of rows of the combo box control, if set to 0, the number of control rows will depend on the number of entries in the list.
(2) List properties: Return or set the value of a list item in the combo box control, read only the built-in combination box control.
(3) Onaction attribute: Return or set the Visual Basic code process name (this code process is not available), which is run when you click or change the combo box control value.
(4) TYPE Properties: Returns or sets the display mode of the combo box control.
(5) AddITEM method: Used to add a list item in the combo box control, and the combo box control must be a custom control.
(6) REMOVEITEM method: Used to remove a list item from the combo box control.
(2) Assistant Objects Office Assistant is a set of cartoon animation characters that provide friendly information prompts and can achieve simple interaction through custom dialogs (also known as "balloon").
1.Assistant object
The object represents the Office Assistant to return Assistant objects through the ASSISTANT attribute of the Application object. The default assistant (ie "CLIPPIT"), the user can click the Select Assistant menu item in the shortcut menu that appears back to the Assistant, and select in the "Office Assistant" dialog box that pops up. Different assistants.
(1) Animation attribute: An animation effect for returning or setting an Office assistant. If applied to the Balloon object, the "Assistant" will only occur when the balloon is displayed, and can be assigned by constant, and the constant constants and its meaning are shown in the following table:
?
Action of constant name represented
?
MsoanimationAppear assistant
?
MsoanimationBeginspeaking Assistant begins to talk
?
MsoanimationCheckingsomething Assistant Check Action
?
MSoAnimationDisappear assistant disappeared
?
MsoanimationGoodby Assistant says "Goodby"
?
MsoanimationGReeting Assistant Welcome Action
?
MsoanimationIdle assistant resting action (for the default animation effect type)
?
MsoanimationPrinting Assistant Print Action
?
MsoanimationSaving Assistant Save Action
?
MsoanimationSearching Assistant starts looking up
?
MsoaniMationthinking assistant considers the action
?
?
?
(2) AssistWithlerts attribute: Used to set whether the assistant balloon is sent to the application warning message.
(3) AssistWithhelp properties: Used to set whether the assistant appears when the user presses the F1 key to display the help.
(4) AssistWithWizards Properties: Used to set up the assistant to provide an online help.
(5) FeatureTips properties: Use to set whether the assistant provides more effectively provides information about application functionality.
(6) GUESSHELP attribute: Used to set up the Assistant to display the list of help topics.
(7) FileName Properties: Used to set the file name used by the Assistant, the extension of the assistant file is "ACS", which is generally located in the installation directory of Office. "Sun Wukong" is mnkyking.acs; "big eye holder" is clippit.acs; "love" is OFFCAT.ACS; "Cong" is Rocky.ACS; "Juvenile Put" is logo.acs; "Tony" DOT. ACS; "Beautiful Home" is mnature.acs; "f1" is F1.acs. (8) Newballoon properties: Used to create a balloon balloon and return to the Balloon object.
(9) ON attribute: Used to set whether the assistant is used.
(10) SOUNDS attribute: The sound used to set whether the assistant has a sound corresponding to the animation.
(11) TipofDay properties: The assistant displays a dedicated prompt when setting each time the Office is started.
2.Balloon object
This object represents a balloon, the assistant can display the title, text and some controls in the balloon, and returns a balloon object through the Newballoon property, and only one balloon is visible at any time.
(1) BalloontYPE Properties: The balloon type used to set the assistant can be assigned by constant. When you create a Balloon object, the initial value is MSOBALLOONTYPEBUTTONS constant.
(2) Button property: Used to set the bash balloon bottom button type. When you create a Balloon object, the initial value is MSOBUTTONSETOK.
(3) ICON attribute: The type of icon used to set the upper left corner of the assistant balloon can be assigned via constant.
(4) Checkboxes properties: Return to the Ballooncheckboxes collection, all check boxes in the balloon.
(5) Heading attribute: Used to set the title in the assistant balloon. Balloon headings and text support text containing 16 colors of underscore and system palette.
To display the text containing the underscore, you can start the underscore and "{UL 0}" syntax by "{ul}", "{UL 1}" syntax; if you need to change the color, you can pass "{cf number } "Syntax, where Number constants are 16 colors supported by the system palette.
(6) Labels properties: Return to the BalloonLabels collection, that is, all the tags in the balloon.
(7) MODE attribute: Used to set the model of the balloon, that is, whether the user is allowed to continue working in the program while displaying the balloon.
(8) Text attribute: Used to set the text in the balloon label, check box, or direct display of the part.
(9) Close method: Non-modal balloon for closing the activity, only can be used during the callback process.
(10) SHOW method: Used to display the specified balloon object.
3.BalloonCheckbox object
This object represents the checkbox control in the balloon.
Checked Attribute: Returns whether the specified check box in the balloon has been selected.
(3) Built-in dialog object Office uses a large number of built-in dialogs, providing development interfaces in Word and Excel, enabling developers to make full use of the built-in dialog implementing a lot of operation, especially in Word, using multiple methods to display built-in Dialog, implement different functional requirements, here, we explain as an example with the Word's built-in dialog.
1.Dialogs collection object
The collection object represents a collection of Dialog objects in Word or Excel, where each Dialog object represents an built-in dialog box, which cannot create a new built-in dialog in the Dialogs collection, and cannot add dialogs.
2.Dialog object
This object represents the built-in dialog.
(1) CommandName Properties: Used to return the process name corresponding to the specified built-in dialog.
(2) DEFAULTTAB Properties: The tab that is activated when the display specified dialog is displayed. (3) Display method: Used to display the specified built-in dialog, until the user closes the dialog or timeout, use the display built-in dialog box (ie, only to display), but can return to the user to close The button code clicked when the dialog box.
Where, return -2, represents the "Close" button; return -1, represent "determination" button; return 0, represent "cancel" button; return greater than 0 value, then represent the first button, 2 represents the second The button is pushed in this class.
(4) Execute method: Used to apply the current settings of the built-in dialog.
(5) SHOW method: Used to display and execute the built-in dialog, that is, if the user opens the dialog box through the menu or toolbar, the user closes the button code clicked when the user closes the dialog box.
(6) UPDATE method: Used to update the parameter value of the built-in dialog.
3. Display the built-in dialog
The display of the built-in dialog can be controlled via the VBA code. To invoke the specified built-in dialog box, you can assign a Dialogs property through a constant, such as "Dialogs (WDDialogFileOpen) .Show Displays the" Open "dialog of Word. In the aforementioned code, if the return value of the statement is given the Dialog variable, the option to return or set the dialog box, as follows:
SET TESTDIALOG = Dialogs (WDDIALOGFILEOPEN)
Reasonable use of the built-in dialog to achieve a lot of functions in Word and Excel, there are many built-in dialog boxes, limited to space, this article does not introduce, readers can refer to VBA's online help.
(4) Forms and related control objects VBA's editor and VB are very similar, so forms and related controls are also provided, but there are some differences in the form, controls, and VB in VBA due to the characteristics of Office.
1. User form knowledge point
Forms are interfaces for program implementation and user interaction, but also improve the interface friendliness of the program through the form, but also further enhanced, and improve the functionality of the program. You can create a user form in the VBA editor. Right-click on the project in the Engineering Explorer, select the Insert → User Form command in the menu that appears, then the default form and control tool will be displayed in the design interface. The tank, the control toolbox and the VB control toolbox are similar, as shown in Figure 3.
?
2. User Forms Common Control
Although the controls used by VBA forms are similar, their properties and methods and VB have certain differences:
(1) Tag control: This control is used to display an invigible text, the default property is the CAPTION property, the default event is a Click event.
(2) Text Box Control: This control is used to display editable text information, which is the most common editing control in VBA development. The default property is Value property, the default event is a Change event.
(3) Composite box control: This control combines the list box and text box, the user can make an input and list box selection operation, the default attribute is the value attribute, the default event is a Change event.
(4) List box control: This control is used to display a list of values, and the user can select one or more list items. The list box in VBA can be used in the form of the list form and option buttons or check boxes. The default property is Value property. The default event is a Click event.
(5) Check box control: This control is used to display the selected state, ie, allow the user to select one from two values (such as TRUE or FALSE). If you choose, the tag is displayed, the default property is the value property, and the default event is a Click event. (6) Options button control: This control is used to display the selection status of each of the multi-selection, the default property is the value attribute, the default event is the Click event.
(7) Switch button control: This control is used to display the selected state, the default property is the value property, the default event is the Click event.
(8) Frame control: This control is used to create a control group for functional or visual perspective, and the default event is a Click event.
(9) Command button Control: This control is used to start, end, or interrupt operations. The Click event is the most commonly used event code in the form program, and the default attribute is the value property, the default event is a Click event.
(10) Head control: This control is used to display a series of related controls as a multitarative collection, the default attribute is the SelectEdItem property, and the default event is a Change event.
(11) Multi-page control: This control is used to implement multi-page content in a single control, useful when handling a lot of information for different categories, and the default event is a Change event.
(12) Rolling Bar Control: This control is used to press the scroll block position, return or set the variable value, the default attribute is the value attribute, the default event is a Change event. To create a horizontal or longitudinal scroll bar, drag the scroll bar control point laterally or longitudinally when the form is designed.
(13) Rotate button control: This control is used to increase and reduce variable values, the default attribute is the value attribute, and the default event is a Change event.
(14) Image Control: This control is used to display pictures, and its supported image file formats include: BMP, CUR, GIF, ICO, JPG, and WMF, etc., the default event is a Click event.
In the VBA form design, you can select all the controls used in the development. Drag to the toolbox directly, the toolbox will add a "control group", in the form design, you can directly place the control group on the window. Body, greatly increased efficiency (this feature is unique to VBA).
If the developer needs to use the other controls not provided by the VBA standard control box, right-click the toolbox, select the "Additional Control" command in the menu that appears, and selects in the dialog box that appears.
3. User Forms Common Events
The usual events of the user form and the VB form have a certain difference.
(1) Initialize event: This event occurs after loading the object and before the display object is usually initialized in the event, or the properties of the setup control are set.
(2) QueryClose event: This event occurs before the user form is closed, usually check the unfinished operation in the user form.
CANCEL parameters: Integer, if the parameter is set to not zero, it blocks the shutdown user form.
Closemode Parameters: This parameter is used to get the reason why the QueryClose event is triggered.
(3) Terminate event: This event sets all reference object variables to Nothing constants, that is, delete the object's reference. This event occurs after the uninstall object. If it is not normal to exit, it will not trigger it.
(5) Class module VBA development also provides a class module structure that enables developers to enable common operations to make reasonable encapsulation, implement code multiplexing and program structure.
1. Create a class module
The Create Class Module in VBA is very simple. Right-click the project in the VBA Editor's Engineering Explorer, select the "Insert → Class Module" command in the menu, and then add it in the Project Explorer Class module, the default name is "class 1", click this class module to set its properties, which generally need to set its "name" attribute to make it easy to use. Double-click this class module to enter the code according to the form of operation. 2. Class module common event
Initialize and Terminate are included in the class module.
(1) Initialize event: This event is used to initialize the data used by the class module. This event is triggered when you create a class module in the program.
(2) Terminate event: When the class instance is deleted from memory, the event is triggered at the end of the program.
Third, the Office XP Development Object Knowledge Detailed VBA program is mainly composed of a large number of objects. Here, we selected the knowledge of commonly used Office VBA development common objects to facilitate readers.
(1) Word 2002 Development Object 1.Application object
This object represents a Word application that accesses all other objects in Word through this object.
(1) ActiveDocument property: Return to the Document object, represent the activity document.
(2) ActivePrinter attribute: Returns or set the name of the current printer.
(3) ActiveWindow Properties: Return to the Window object, represent the active window.
(4) Documents Properties: Returns the Documents collection that represents all open documents.
(5) Selection property: Returns the Selection object, represents the selected range or insertion point.
(6) Windows properties: Return to the Windows collection, represent 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 a document opened in Word. The Document object can be obtained by "Documents" syntax, where the index parameter represents the name or index number of the document, and the ActiveDocument property can also be used to obtain the current edited documentation.
(1) AttachedTemplate properties: Returns the template object to the template associated with the active document.
(2) Built IndocumentProperties Properties: Returns the DocumentProperties collection, representing all built-in properties of the specified document, such as author, topics, or keywords.
(3) Characters Properties: Returns the Characters collection that represents characters in the document.
(4) Tables properties: Return to Tables collection, representing all the forms in the document.
(5) Checkgrammar method: Check if the string has a syntax error.
(6) CHECKSPELLING method: Used to check if the string is spelling error.
(7) ComputeStatistics method: Used to obtain statistics for the specified document, such as words, number of pages, etc.
(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 revoke the last operation of the document.
3.Bookmark object
This object is an element in the Bookmarks collection represents a bookmark in the document. The Bookmark object can be obtained through the "Bookmarks (INDEX) syntax, where the index parameter represents the bookmark name or index number.
(1) SELECT method: Used to select the specified bookmark. (2) DELETE method: Used to delete the specified bookmark.
4.field object
This object is the element in the field of fields, represents the domain in the document. Get the field object with the syntax of "Fields (Index).
5.Template object
This object is an element in the Templates collection represents the document template. Template objects can be obtained through the syntax of Templates (Index).
6.FontNames object
This object represents a list of valid font names. Get the FontNames object by FontNames, LandscapeFontNames or PortraitFontNames properties.
7.Options object
This object represents Word's application and document options.
8.autotextentries set objects and autotextentry objects
The AutotExtenceTries collection object consists of autotextentry objects, representing all the AutoTexts entry in the template. AutotExtentry object represents a single "Auto Graphic" entry.
Insert method: Used to replace the original in the specified range by inserting the "Automatic Graphic" entry.
9.Selection object
The object represents the selection of the current document, which can be a zone or insertion point. Each document has only one Selection object, and only one Selection object can only be activated, and the Selection object can be obtained through the Selection property.
(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 contents of the clipboard to the selection area.
(4) TYPARAGRAPH method: Used to insert a new paragraph, equivalent to pressing the Enter key when editing.
(5) Typetext method: Used to insert the specified text.
(6) WHOLESTORY method: Used to extend the selection content and include the entire text section.
(7) GOTO method: Move the insertion point to the specified location.
(8) HOMEKEY method: equivalent to pressing the "home" button.
(9) Endkey method: equivalent to pressing the "END" button.
(2) Excel 2002 Development Object 1.Application object
This object represents the Excel application, which can access all other objects in Excel through this object.
(1) ActiveCell Properties: Returns the RANGE object, represents the active window of the active window or the specified window.
(2) ActiveChart Attribute: Return to a Chart object, representing the activity chart.
(3) ActiveSheet Properties: Returns the Worksheet object to represent the active worksheet in the event workbook.
(4) ActiveWorkbook properties: Return to Workbook objects, representing a workbook representing the event.
(5) Cells properties: Returns the RANGE object, representing all cells in the active workbook.
(6) Charts attribute: Return the Sheets collection, representing all charts in the active workbook.
(7) RANGE Properties: Returns the RANGE object, represents a cell or cell area.
(8) Sheets Properties: Return the Sheets collection, representing all the worksheets of the current workbook.
(9) STATUSBAR attribute: Returns the text of the status bar.
(10) Workbooks Properties: Returns the Workbooks collection that represents all open workbooks.
(11) Worksheets Properties: Returns the Sheets collection, representing all worksheets for the active workbook.
(12) Calculate method: Used to calculate cells in the designated area.
(13) Evaluate method: Used to perform an operation of the value of the string to solve the operation of the arithmetic represented by a string. (14) InputBox method: Used to display the dialog that receives the input and return information entered in the dialog.
(15) QUIT method: Used to exit the Excel application.
2. ADIN object
This object represents the load macro, and the AddIn object can be returned by "Addins" syntax, where the index parameter is loaded macro or numbers.
FullName Properties: Returns the file name of the loaded macro.
3.Chart object
This object represents a chart in a workbook, including embedded charts and individual charts.
(1) Chartarea Attribute: Returns a Chartarea object that represents all chart areas in the specified chart.
(2) ChartTitle Attribute: Returns a ChartTitle object that represents the title of the specified chart.
(3) ChartTYPE Properties: Returns or sets the type of chart.
(4) DATATABLE Attribute: Returns the DataTable object, represents the chart data sheet.
(5) Walls properties: Return to Walls object, representing the background wall of the three-dimensional chart.
4.Workbook object
This object represents a workbook.
(1) Styles Attribute: Returns the Styles collection that represents all the styles of the specified workbook.
(2) ADD method: Used to create a workbook and set it to an active workbook.
(3) OPEN method: Used to open a workbook.
(4) OpenText method: Used to load text files and subsequently handle it, then inserted text data through the column processed in the worksheet.
5.Worksheet object
The object represents a worksheet, and the Worksheet object can be obtained through the "Worksheets (INDEX)" syntax, where the index parameter works table index number or name.
(1) AutoFilter property: Used to set whether to filter.
(2) UsedRange Properties: Returns the RANGE object that represents the used area in the specified worksheet.
(3) Printout method: Used to print the specified object.
(4) PRINTPREVIEW method: Preview the view of the handover.
(5) SELECT method: Used to select the specified object.
6.Range object
The object represents a selection area, which is a commonly used object in Excel development.
(1) Address properties: Returns the reference name for the specified area.
(2) AREAS attribute: Returns the AREAS collection that represents all the regions in the multi-selection area.
(3) Column attribute: Returns the serial number of the first column in the first block of the first block.
(4) Column attribute: Returns the RANGE object to represent all columns in the specified area.
(5) ColumnWidth properties: Return or set the column width of all columns in the specified area.
(6) Formula Properties: The formula used to set the RANGE object.
(7) Mergearea Attribute: Returns the RANGE object, represents the merge scope of the specified cell.
(8) Mergecells Properties: Whether the merge unit is included in the return area.
(9) NumberFormat Attribute: Return or set the format code for the specified object.
(10) ROW attribute: Returns the serial number of the first row in the first block.
(11) ROWS attribute: Returns the RANGE object to represent all rows in the specified area.
(12) ACTIVATE method: Used to activate the object.
(13) ApplyNames method: Used to apply the name to the specified cell.
(14) AutoFill method: Used to automatically fill the specified cell.
(15) Autofit method: Used to adjust the column width and row height to an appropriate value. (16) Find method: Used to find specific information in the area and return to the first cell that contains the found information.
(17) MERGE method: Used to create a merge unit from the specified RANGE object.
(18) Replace method: Used to find and replace in the specified area.
(19) Subtotal method: Used to create a classification summary in the designated area.
(3) PowerPoint 2002 Development Object 1.Application object
This object represents the PowerPoint application, through which all other objects in PowerPoint are accessed.
(1) Active property: Returns whether the specified pane is activated.
(2) ActivePresentation Properties: Returns the Presentation object to the presentation on behalf of the active window.
(3) ActiveWindow Properties: Returns the DocumentWindow object to represent the current document window.
(4) Presentations properties: Returns the Presentations collection that represents all open presentations.
(5) SlideshowWindows properties: Returns the SlideshowWindows collection that represents all open slide show windows.
(6) QUIT method: Used to exit the PowerPoint program.
2. DocumentWindow object
This object represents a document window. Use the "Windows" syntax to return the DocumentWindow object.
(1) ActivePane Properties: Returns the PANE object to represent the active pane in the document window.
(2) PANES properties: Return to the PANES collection, represent all panes in the document window.
(3) ViewType properties: Returns the view type in the specified document window.
3.Presentation object
This object represents a presentation and returns a Presentation object by "Presentations (Index) syntax.
(1) Built IndocumentProperties Properties: Returns the DocumentProperties collection, representing all document properties of the presentation.
(2) Colorschemes properties: Returns the Colorschemes collection, representing the color scheme of the presentation.
(3) Pagesetup properties: Returns the Pagesetup object to control the slide page setting properties of the presentation.
(4) SlideMaster properties: Returns the slide master object.
(5) SlideShowSettings Properties: Returns the slideShowSettings object, which represents the slide show settings for the presentation.
(6) SlideshowWindow properties: Returns the slide show window object.
(7) AddtitleMaster method: Add a title master to the presentation.
(8) ApplyTemplate method: design template for presentation application application.
4.SlideshowWindow object
This object represents a slide show window.
IsFullScreen properties: Used to set whether the slide show window is displayed full screen.
5.master object
This object represents a slide mother version, title master, a masterpiece or a mother version.
TextStyles Attribute: Returns the TextStyles collection for the slide master, represents the title text, the text, and the default text.
6.slide object
This object represents a slide.
(1) SLIDEID Properties: Returns the unique identifier of the slide.
(2) SlideIndex Properties: Returns the Slide Slide in the Slides collection.
7.SlideshowView object
This object represents the view in the slide show window.
(1) AcceleratorSenableD Properties: Used to set whether it is allowed to use shortcuts when the slide show is allowed. (2) CurrentShowPosition Attribute: Returns the location of the current slide in the show.
(3) Drawline method: Draw a straight line in the specified slide screen view.
(4) ERASEDRAWING method: It is used to clear the straight line drawn in the show through the Drawline method or the drawing tool.
(5) GOTOSLIDE method: Used to switch the specified slide.
Fourth, VBA Comprehensive Development Example Warning Greeting Card Wizard (1) Decomposition This routine is a simple greeting card guide that can run directly and can assist in generating a simple greeting card. The main interface of the program is similar to the Word standard to the magic program, click the "Previous" and "Next" button or directly click the switch button on the left side of the form to the respective steps. Click the "Cancel" button to close the wizard window, as shown in Figure 4.
?
Click the "Next" button to switch to the next page, select the holiday of the greeting card in this page and enter the name of the sender and the recipient, as shown in Figure 5.
?
Click the "Next" button to switch to the next page, you can enter a congratulatory message in this page, as shown in Figure 6.
?
Click the "Next" button to switch to the last page of the wizard program, click the "Finish" button to generate a document. The generated document will contain information that fills in the user in the aforementioned steps, generates a simple greeting card.
(2) Program knowledge points 1. Principle of wizard
The essence of the wizard program is the aforementioned Word Template file (DOT file), the only difference is that its extension is "WIZ", so the preparation of the template first needs to be prepared and debug code, change the extension to "WIZ" after debugging I.e. The conventional steps prepared by the wizard are as follows:
(1) Creating the automatic graphic set required for the Wizard: The automatic graphic set required by the guide is the automatic graphic set of bookmarks, and finally calls the pre-saved graphic when generating a document, and used in Word for Located bookmark objects, can help probilities to be positioned and replaced at the location where you need to fill in the input content. This step is the key to the preparation of the wizard.
(2) Editing Forms and Modules: The wizard program needs to implement interactive functionality through the form, so through reasonable use of controls, you can fully design a beautiful and easy-to-use form, and then perform event programming for the form, implement process control. . After the form is complete, you need to save the universal feature through the module, and the commonly used features include generating documents, replacing bookmarks, and deleting bookmarks.
(3) Set the wizard program entry: The wizard is triggered by a specific event. It is recommended to enter the code of the call wizard program entry in the "New" event of the "thisDocument" object. The event of the Document object includes a New event (triggering when creating a template-based document. Press the function analysis, the wizard program entry is executed by this event), OPEN event (triggering when opening a document) and a Close event (triggering when the document is turned off) .
2. Interface design knowledge points
(1) The wizard program needs to be stepped by step, so it is recommended to implement interface switching using a multi-page control (MultiPage).
(2) The VBA's form is not provided with a VB control array function, so you need to access the Controls collection object to control some of the appearance and functionality.
(3) Since the knowledge point can be obtained from the aforementioned knowledge point, the developer can directly open the Word's wizard (such as "Practical Word Wizard", then export its interface form via the engineering resource manager. And then on this basis for reference and modification, it can greatly improve the efficiency of the preparation.
(4) Controls Collection: The form of the Controls collection represents all the controls therebetween. Each control in the Controls collection has a unique index, or references to the control name. In the wizard program, for controls that effectively control features and appearances (such as switching buttons), you can enumerate these controls through the name reference to enumerate access to the Controls collection of access to the form.
(5) Multi-page control: Multipage is a container for the Pages collection, each multi-page control contains one or more Page objects, the usual properties and methods are as follows: Value Properties: The default properties of the multi-page control, Returns the index number of the current event page, starting from "0", and push it accordingly.
STYLE Attribute: The display style for setting the multi-page control tag, is recommended to "2" (ie, not displaying the header) in the wizard.
(6) Page object: This object represents the page of the multi-page control. Each Page object can place different controls to achieve multi-interface switching, the first Page object default name "Page1", its common attributes and methods are as follows:
Picture Properties: Background images for settings page.
PictureTiling Properties: Used to set whether or not pair of pictures.
PictureSizeMode Properties: Use to set the way the display background image is displayed.
(3) Program Implementation Steps 1. Creating the automatic graphs required for the wizard
The automatic graphic set required by the aforementioned content can be created is a key step. Therefore, first enter the relevant text in the editing interface, then add a bookmark in the position that needs to be inserted into the content, and finally select all the content, named "HK" And saved in the template's automatic graphic set. As shown in Figure 7:
?
2. Preparation form
Add a form to the VBA Editor's Engineering Explorer, place a multi-page control, label, text box, list box, and other controls (also using the above import and modifying methods to design the form), because the form is used There are more controls, limited to space, not in one by one, can be designed according to the renderings, this article only introduces some controls that need to be used in the code.
First switch to the second page of the multi-page control, the location and name you need to program in this page are shown in Figure 8:
?
As shown in Figure 8, "FRMHKWIZ" is the user form, "Page1" is a page object of the multi-page control, and "TXTFSZ" and "TXTJSZ" are text box controls, "SHPMAP0" to "SHPMAP3", "LBLMAP0". Between "LBLMAP3" is the Label control, "LSTJR" is a list box control. Switch to the third page of the multi-page control, the location and name you want to program in this page are shown in Figure 9:
?
As shown in Figure 9, "Page2" is a page object of a multi-page control, "SHPCHARTPATH" is the tag control, "TXTHC" is text box control, "cmdcancel", "cmdback", "cmdnext", and "cmdfinish" are all Command button control.
After the form interface is designed, switch to the code editing window, first enter a constant statement, the code as follows:
'Representative page number
Const p_count = "3"
'Constant of the name of the label
Const const_lbl = "lblmap"
'Constant of the name of the label
Const const_SHP = "SHPMAP"
Then enter a common variable, the code as follows:
'Use to save the current page number
DIM IndexPanel AS Integer
Enter the custom process code as shown below:
'The process of initialization control
Private subinit_controls ()
With Lstjr
.Additem "Christmas"
.Additem "Mid-Autumn Festival"
.Additem "National Day"
End with
End Sub
'Switching the page process
Private subwaypage (inewpanel as integer)
IF indexpanel = inewpanel or fwizardcallback the
EXIT SUB
END IF
'Implement page switching effects through the Controls collection object
FRMHKWIZ.CONTROLS (const_SHP & indexPanel) .BackColor = VBWHITE
FRMHKWIZ.CONTROLS (const_lbl & indexpanel) .fontbold = false
Indexpanel = inewpanel
FRMHKWIZ.CONTROLS (const_shp & indexpanel) .Backcolor = VBGreen
FRMHKWIZ.CONTROLS (const_lbl & indexpanel) .fontbold = true
MPGWIZARDPAGE.VALUE = IndexPanel
End Sub
Finally, enter the event code of each control, as shown below:
'Tags for simulating switch buttons Click event
Private sub lblmap0_click ()
Changepage (0)
End Sub
Private sub lblmap1_click ()
Changepage (1)
End Sub
Private sub lblmap2_click ()
Changepage (2)
End Sub
Private sub lblmap3_click ()
Changepage (3)
End Sub
Private sub shpmap0_click ()
Changepage (0)
End Sub
Private sub shpmap1_click ()
Changepage (1)
End Sub
Private sub shpmap2_click ()
Changepage (2)
End Sub
Private sub shpmap3_click ()
Changepage (3)
End Sub
Private sub shpmap4_click ()
Changepage (4)
End Sub
'"Previous" button Click Event
Private subcmdback_click ()
IF IndexPanel> 0 THEN
ChangePage (IndexPanel - 1)
END IF
End Sub
'"Next" button Click Event
Private subduDNext_click ()
IF indexpanel ChangePage (IndexPanel 1) END IF End Sub '"Cancel" button Click Event Private subcmdcancel_click () 'Close the form Unload me End Sub '"Finish" button Click Event Private sub cmdfinish_click () Application.screenupdating = false 'Create a document in the call module, that is, the interface process of the form and module CreateNewDoc (TRUE) End Sub 'Form Initialization Event Private sub Userform_initialize () IndexPanel = 0 MPGWIZARDPAGE.VALUE = 0 Changepage (0) Init_Controls End Sub 3. Preparation module The module is used to save the public code in the wizard, add a standard module in the Engineering Explorer, named "Common", then enter the code (also imitated the import operation of the form, import the word provided by the Word) , Refer to and use many of these processes), as shown below: 'Start the wizard, the entrance to the wizard Public Sub StartWizard () 'Display Form FRMHKWIZ.SHOW End Sub 'The process of creating a new document, allowing the PUBLIC type process without appearing in the "macro" list of Word. Public Sub CreateNewDoc (FDummy as Boolean) Application.screenupdating = false 'Set the mouse as "Waiting" shape System.cursor = wdcursorwait 'Get the template for the wizard program Set objwiztemplate = activeDocument.attachedTemplate Application.displayAutoCompleTips = TRUE 'Insert the created automatic graphic ActiveDocument.attachedTemplate.autotextentries ("HK"). Insert Selection.Range, True ActiveDocument.select.select 'Call the replacement bookmark process, replace the content entered in the form in the document ReplaceBookmark "JR", frmhkwiz.lstjr.text ReplaceBookmark "FSZ", frmhkwiz.txtfsz.text ReplaceBookmark "JSZ", frmhkwiz.txtjsz.text ReplaceBookmark "HC", frmhkwiz.txthc.text 'Set the properties of the new document With activedocument .SpellingChecked = TRUE .Gramarchecked = true .Undoclear End with Application.displayAutoCompleTips = TRUE Selection.homekey WDStory 'Restore the default mouse shape System.cursor = wdcursornormal Application.screenupdating = true 'Close the form Unload frmhkwiz 'Call the process of deleting all bookmarks DeleteallBookmark End Sub 'Process of replacing the bookmark content Private Sub ReplaceBookmark (Which As String, What As String) IF len (what) = 0 THEN What = "" END IF 'Select the specified bookmark Selection.goto What: = WDGOTOBOOKMARK, NAME: = Which 'Replace content Selection.Typetext What End Sub 'Delete all bookmarks Private sub deleteallbookmark () DIM BM as Bookmark 'Traverse all bookmarks For Each BM in ActiveDocument.bookmarks 'Delete bookmarks Bm.delete NEXT End Sub 4. Set to the introduction port After the code is written, switch to the "thisDocument" object in the Engineering Explorer, enter the event code, as shown below: Private subdoment_new () The process of starting the wizard in the Call Module (Common) Common.startwizard End Sub 5. Subsequent work The wizard program is prepared, and after the debug is correct, it can be changed to "WIZ", then search for the folder where the "NORMAL.DOT" file is located, and the wizard program is placed in this directory, then start Word XP, The Icon of the Wizard is displayed in the "Common" column of the Template Form.
