VBA practical development guide (1)

VBA Development Guide
 

I. VBA Basics
1. What is VBA?

In office, you can use macros to perform complicated and repetitive operations. "Macro" is a command set composed of a series of commands and commands. It is essentially a program composed of VBA code. VBA is a universal automation language shared by Microsoft in its applications.

2. Recording/running macros

In the initial development of VBA, you can obtain the code by recording the "macro" operation. In office, almost all operations can be obtained by recording the "macro. Here, we use a simple "macro" Recording instance to learn how to replace all "computers" with "computers" in word and insert a table in four rows and two columns.

Recording Macros in the office, you can click the "Tools → macros → recording new macros..." menu to achieve the implementation, click this command, the "Recording macros" dialog box appears, as shown in 1.

 

The "macro name" box is used to set the recording macro name. The default "macro1" can be used ". The buttons "toolbar" and "keyboard" are used to set the macro trigger position respectively. In the "Save macro" box, you can set the macro storage location, which generally includes "normal. dot "(shared template) and the current document. We recommend that you save it in the current document to avoid any impact on all documents. In the description box, enter a description of the macro. Click OK to start recording macros. The macro recording toolbar is displayed on the document editing page. This toolbar has two buttons that are very similar to the recorder. The first button is "stop recording", and the second button is "Pause recording ". You can use these two buttons to control the recording process at any time.

Then follow the regular operations to implement the aforementioned tasks. After the operation is complete, click the "stop recording" button to End recording.

Note: When recording a "macro", you can use the mouse to click the menu and button, but you cannot record the mouse movement in the document window. Therefore, you must use the keyboard to perform these operations. In addition, for all recorded "macros" in the office, the name must start with letters, Chinese characters, numbers, and underscores (the name can be up to 255 characters ), the macro name cannot contain spaces.

Run the recorded "macro", and click the "Tools> macro" menu command. In the displayed "macro" dialog box, select the macro to be run in the macro name drop-down box, and click "run. Then word will automatically reproduce the aforementioned operations to avoid repeated office operations.

3. Macro limitations

Although most office operations can be saved by recording "macros", the recorded "macros" only faithfully reproduce specific operations, for operations that require logical judgment and process control, it seems powerless. For example, when multiple different contents are automatically replaced, they are operated in the recording "macro" mode, you need to record multiple "macros" based on the number of replaced content, and each "macro" can only be used to replace a specific content, but it adds trouble to your work.

Recording "macro" has many limitations. In addition to the inability to perform logical judgment and process control operations, there are also many limitations. Such as poor interaction capabilities, inability to display built-in office dialogs, failure to Display User-defined forms, and inability to create complex toolbar or modify menus.

4. VBA editing Environment

Although "macro" has many limitations, after obtaining the VBA code through recording "macro", you can use the VBA editor to add logical control for the code and design the process. Take Word 2002 as an example. After "macro" is recorded, you can select the recording macro in the run macro dialog box and click "edit" to display the VBA editing environment, as shown in 2.

 

The VBA editing environment consists of the project resource manager, the property window, and the code editing/Form Design window. List all open VBA projects in the Project Resource Manager. The Properties window is used to set properties of related objects, in the code editing/Form Design window, you can enter the code of the module or edit the form and class modules.

Ii. Details on common objects for VBA Development
VBA is similar to other object-oriented development languages. It also has many objects, and different office members provide a large number of development objects, such as the toolbar, office assistant, built-in dialog box, and form.

(1) toolbar and its control objects
In office, you need to use commandbar, commandbarbutton, commandbarcombobox, and other objects provided by office to develop toolbar and menu through VBA. These objects represent the toolbar, toolbar button, and toolbar drop-down box, these objects are used to generate various complex toolbar or menus.

1. commandbars set

This set represents all the toolbar of the office. You can specify a menu bar or toolbar by name or index number, but only one menu, shortcut menu, or sub-menu by name. If two or more custom menus or sub-menus have the same name, the first object with the same name is returned.

The add method is used to create a toolbar and return the commandbar object.

Syntax: expression. Add (name, position, menubar, temporary)

Parameter description:

Name is an optional variant type, indicating the name of the new toolbar. If this parameter is omitted, the default name is used. Position indicates the optional variant type, which indicates the position of the new toolbar. This parameter value can be set through VBA constants, such as msobarleft, msobartop, msobarright, and msobarbottom constants (the new toolbar is set to the position in the software toolbar); msobarfloating constants (representing the mobility of the new toolbar ); msobarpopup constant (indicating that the new toolbar is a shortcut menu); menubar is an optional variant type, used to set whether to replace the active toolbar with the new toolbar; temporary is an optional variant type, used to set whether the new toolbar is currently valid.

2. commandbar object

This object represents the toolbar in the application, and the control of the new toolbar takes this object as the carrier.

(1) controls property: return the commandbarcontrols object, which represents all controls in the specified toolbar.

(2) namelocal attribute: return the name of the tool bar set by the language of the application version. If you set the built-in tool bar of the software, an error occurs.

(3) Position attribute: return or set the position of the toolbar. values can be set through VBA constants, such as msobarleft, msobartop, msobarright, msobarbottom, msobarfloating, msobarpopup, or msobarmenu.

(4) type attribute: return or set the type of the toolbar. values can be set through VBA constants, such as msobartypenormal (the toolbar is of the normal type) and msobartypemenubar (the toolbar is of the menu type) and msobartypepopup (the toolbar is the pop-up menu type.

(5) reset method: resets the built-in toolbar to the default setting, which is useful when restoring the original toolbar or menu of the software. Resetting the built-in toolbar will delete the custom controls and restore their built-in controls.

3. commandbarcontrols set

This set represents all toolbar controls in the toolbar.

The add method is used to add a Toolbar Control in the commandbarcontrols set.

4. commandbarcontrol object

This object represents a toolbar control. For custom Toolbar controls, you can use commandbarbutton, commandbarcombobox, and commandbarpopup objects to define and operate the built-in controls of the software, the commandbarcontrol object can be used if the control cannot be represented by the preceding three objects.

(1) begingroup attribute: Used to set whether the toolbar controls are displayed in groups.

(2) caption attribute: used to set the title text of the Toolbar Control and can be used as the default control screen prompt.

(3) ID attribute: used to set the functions of commandbarbutton, commandbarcombobox, and commandbarcontrol objects. These controls can be directly set to the ID of the built-in toolbar control, in this way, the control has built-in functions, and the ID attribute of the custom control must be set to 1.

(4) copy method: copy the toolbar control to an existing toolbar.

Syntax: expression. Copy (Bar, before)

Parameter description:

Bar is an optional variant type, indicating the target toolbar. If it is omitted, the control is copied to its own toolbar. Before is an optional variant type, indicating the position of the new control in the specified toolbar, that is, the new control will be added to the control at this position. If it is omitted, the control will be copied to the end of the toolbar.

(5) type attribute: return the type of the Toolbar Control, which can be referenced by VBA constants. The following table lists the types of commonly used Toolbar controls:

Constant name
Description

Msocontrolbutton
Control Button

Msocontrolbuttondropdown
Button with drop-down list

Msocontrolbuttonpopup
Button with pop-up menu

Msocontrolcombobox
Drop-down control box

Msocontroldropdown
Drop-down list control box

Msocontroledit
Text Box

Msocontrolexpandinggrid
Scalable tables

Msocontrolgraphiccombo
Image drop-down box

Msocontrolgraphicdropdown
Image drop-down list box

Msocontrolgrid
Table

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 Procedure name (this Code Procedure cannot use parameters). The procedure runs after clicking the button.

(2) style attribute: return or set the display mode of toolbar buttons. Values can be set through VBA constants. The following table lists the types of commonly used toolbar button controls:

Constant name
Description

Msobuttonicon
Button Containing the icon

Msobuttoncaption
Button Containing the title

Buttoniconandcaption
Buttons containing icons and titles

Msobuttoniconandcaptionbelow
Button with icons and titles at the bottom

Msobuttoniconandwrapcaption
Button that includes icons and titles and the title is automatically wrapped

Msobuttonwrapcaption
Button that contains the title and the title is automatically wrapped

 

 

 

(3) tooltiptext property: return or set the screen prompt of the toolbar button control.

(4) faceid attribute: return or set the icon Number of the toolbar button. Set the appearance of the toolbar button rather than its function. The faceid attribute value of the toolbar button of the custom icon is 0.

(5) copyface method: copy the icon of the toolbar button control to the clipboard.

(6) pasteface method: paste the content of the clipboard icon to the toolbar button control.

(7) execute method: run the corresponding process or built-in commands of 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 control in the combo box. If it is set to 0, the number of rows of the control depends on the number of entries in the list.

(2) List attribute: return or set the value of a list item in the combox control, read-only for the built-in combox control.

(3) onaction attribute: return or set the Visual Basic Code Procedure name (this Code Procedure cannot use parameters), which runs when you click or change the value of the combo box control.

(4) type attribute: return or set the display mode of the combo box control.

(5) additem method: This method is used to add a list item to the combox control. The combox control must be a custom control.

(6) removeitem method: Used to delete a list item from the combo box control.

(2) Assistant objects
The office assistant is a group of cartoon animated figures. These assistants provide friendly Information prompts and can use custom dialog boxes (also known as "balloons ") text Format and controls to achieve simple interaction.

1. Assistant object

This object represents the Office Assistant and can return the assistant object through the Assistant attribute of the Application object. The default assistant is "eye-catching folder" (that is, "clippit"). You can right-click the shortcut menu that appears after the assistant and click the "select assistant" menu item, in the pop-up "office assistant" dialog box, you can select different assistants.

(1) animation attribute: Used to return or set the animation effect of the office assistant. For example, if it is applied to a balloon object, the "assistant" only appears when the balloon is displayed. You can assign values by using constants. The following table lists common constants and their meanings:

 

Constant name
Action

 

Msoanimationappear
Assistant appears

 

Msoanimationbeginspeaking
The assistant starts talking.

 

Msoanimationcheckingsomething
Assistant check action

 

Msoanimationdisappear
Assistant disappears

 

Msoanimationgoodbye
The assistant said "Goodbye"

 

Msoanimationgreeting
Greeting by the Assistant

 

Msoanimationidle
Helper rest action (default animation effect type)

 

Msoanimationprinting
Action printed by the Assistant

 

Msoanimationsaving
Action saved by the Assistant

 

Msoanimationsearching
Action for the assistant to start searching

 

Msoanimationthinking
Actions taken by the Assistant

 

 

 

(2) discard withalerts attribute: Used to set whether the assistant balloon sends a warning message to the application.

(3) define withhelp attribute: Used to set whether the assistant appears when the user presses the F1 key to display help.

(4) Configure withwizards attributes: Used to set whether the assistant provides wizard-type online help.

(5) featuretips attribute: Used to set whether the assistant provides more effective information about application functions.

(6) guesshelp attribute: Used to set whether the assistant displays the help topic list.

(7) filename attribute: used to set the name of the file used by the assistant. The extension of the assistant file is "ACS", which is generally located in the installation directory of the Office. "Sun Wukong" is mnkyking. ACS; "Eye widening" is clippit. ACS; "fall in love" is offcat. ACS; "Cong" is Rocky. ACS; "Jigsaw Puzzle" is the logo. ACS; "PHS" dot. ACS; "beautiful home" is mnature. ACS; "f1" is f1.acs.

(8) newballoon attribute: used to create a helper balloon and return a balloon object.

(9) On attribute: Used to set whether to use the assistant.

(10) Sounds attribute: Used to set whether the assistant sends the sound corresponding to the animation.

(11) tipofday attribute: Used to set whether the assistant displays a dedicated prompt 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. Through the newballoon attribute, the balloon object can be returned, and only one balloon is visible at any time.

(1) balloontype attribute: used to set the balloon type used by the assistant. You can assign values through constants. When creating a balloon object, the initial value is the constant msoballoontypebuttons.

(2) button attribute: used to set the button type at the bottom of the assistant balloon. When creating a balloon object, the initial value is msobuttonsetok.

(3) icon attribute: This attribute is used to set the icon type in the upper left corner of the assistant balloon. You can assign values through constants.

(4) checkboxes properties: return the ballooncheckboxes set, that is, all the check boxes in the balloon.

(5) Heading attribute: used to set the title in the assistant balloon. Both the title and text of the balloon can contain underscores and 16-color text in the system palette.

To display underlined text, you can use the "{ul}" and "{ul 1}" syntax to start the underline and "{ul 0}" syntax to clear the underline; to change the text color, you can use the "{CF number}" syntax. The number constant is the 16 colors supported by the system color palette.

(6) Labels attribute: return the balloonlabels set, that is, all labels in the balloon.

(7) mode attribute: used to set the balloon mode, that is, whether to allow the user to continue working in the program while displaying the balloon.

(8) text attribute: used to set the text of the assistant in the balloon label, check box, or directly display part.

(9) Close method: the non-modal balloon used to close the activity. It can only be used during the callback process.

(10) show method: used to display the specified balloon object.

3. ballooncheckbox object

This object represents the check box control in the balloon.

Checked attribute: return whether or not the specified check box in the selected balloon has been returned.

(3) built-in dialog box objects
Office uses a large number of built-in dialogs and provides development interfaces in Word and Excel, allowing developers to make full use of the built-in dialogs for many operations, especially in word, you can use multiple methods to display built-in dialogs, the built-in dialog box of word is used as an example to describe different functional requirements.

1. dialogs set object

This set object represents the dialog object set in word or Excel. Each dialog object represents a built-in dialog box. You cannot create a new built-in dialog box in the dialogs set or add a dialog box.

2. Dialog object

This object represents the built-in dialog box.

(1) commandname attribute: return the process name corresponding to the specified built-in dialog box.

(2) defaulttab attribute: used to set the tabs activated when the specified dialog box is displayed.

(3) display method: used to display the specified built-in dialog box, and until the user closes the dialog box or times out. When the built-in dialog box is displayed, no operation is performed (that is, only for display ), however, you can return the button code you clicked when closing the dialog box.

The return value is-2, indicating the "close" button; the return value is-1, indicating the "OK" button; the return value is 0, indicating the "cancel" button; and the return value is greater than 0, 1 indicates the first button, 2 indicates the second button, and so on.

(4) execute method: used to apply the current settings of the built-in dialog box.

(5) show method: used to display and execute the built-in dialog box, that is, if the user opens the dialog box through the menu or toolbar, and the display method is the same, return the button code clicked when the user closes the dialog box.

(6) update method: used to update the parameter values in the built-in dialog box.

3. display the built-in dialog box

You can use VBA code to control the display of the built-in dialog box. To call the specified built-in dialog box, you can assign values to the dialogs attribute through constants, such as "dialogs (wddialogfileopen ). show "can display the" open "dialog box of word. In the preceding code, if you assign the return value of this statement to the dialog variable, you can use this variable to return or set the options of the dialog box. The Code is as follows:

Set testdialog = dialogs (wddialogfileopen)

Rational use of the built-in dialog box can achieve a lot of functions, Word and Excel provides a lot of built-in dialog box, limited by space, this article does not detail, readers can refer to the VBA online help.