A Practical Guide to VBA development
TANGDA Appendix Articles
VBA (Visual Basic for application) is a two-time development tool with Office, which can be a great convenience for everyday office. This article describes common objects and their associated properties, methods, and events (including Word, Excel, PowerPoint, and so on) for VBA development of Office members and gives the relevant instance code.
First, the VBA foundation
1. What is VBA
In office for some cumbersome, repetitive operations, users can be implemented through "macros." A "macro" is a set of commands combined with a series of commands and instructions, essentially a program composed of VBA code. VBA is a common Automation language that Microsoft shares in the applications it develops.
2. Record/Run macros
In the early days of VBA development, you can get code by recording macros, and almost all of the actions in office can be recorded as "macros" to get code. Here, we learn through a simple "macro" recording instance: in Word, replace all the words "computer" with "computer" and insert a table of 4 rows and 2 columns.
To record a macro in office, click the tools → macro → record a new macro ... menu command implementation, click the command and the Record Macro dialog box appears, as shown in Figure 1.
Where the Macro name box is used to set the name for the recorded macro, you can use the default "Macro1". The toolbar and keyboard two buttons are used to set the trigger position of the macro, respectively. The Save macros in box sets the location where the macros will be saved, typically including "Normal.dot" (a common template) and 2 of the current document, and is recommended to be saved in the current document to avoid having an impact on all documents. In the Description box, you can enter a description of the macro. Click the OK button to begin recording the macro. The macro Recording toolbar appears in the document editing interface. The toolbar has 2 buttons that are very similar to a tape recorder, the first button is "Stop recording" and the second button is "pause recording." These two buttons are available for control at any time during recording.
Then follow the general action to achieve the aforementioned tasks, after the operation is completed, click the Stop Recording button to finish the recording.
Note: When you record macros, you can click menus and buttons with your mouse, but you cannot record the movement of the mouse in the document window, so you must implement these actions by using the keyboard. In addition, all recorded macros in office must have the first character of a letter or kanji, a number, and an underscore (the name can be up to 255 characters), and the name of the macro will not allow spaces to be included.
To run a recorded macro, click the tools → macros → macros menu command. Select the macro that you want to run in the Macro name Drop-down box in the Macro dialog box that appears, and then click the Run button. Word will then automatically reproduce the actions described previously, avoiding repetitive office operations.
3. Limitations of macros
Although most of office operations can be saved by recording the macro method, but the recorded "macro" only "faithfully" reproduced a specific operation, for some need to make logical judgments and process control operations, it appears powerless, such as automatically replace multiple different content, as recorded "macro" mode of operation, You need to record multiple macros by the amount of replacement content, and each "macro" can be used only for one content replacement, adding to the hassle of the job.
Recording macros has many limitations, including a lot of logic judgments and process control operations. such as a less interactive ability to display Office built-in dialog boxes, inability to display user-defined forms, and inability to create complex toolbars or modify menus.
4.VBA Editing Environment
Although there are many limitations to macros, you can use the VBA editor to add logical control and design processes to your code by recording macros after you get the VBA code. In Word 2002, for example, when the macro recording is complete, you can display the VBA editing environment by selecting the recorded macro in the Run Macro dialog box and clicking the Edit button, as shown in Figure 2.
The VBA editing environment consists of the engineering Explorer, the Properties window, and the code Editing/Form Design window. Lists all the currently open VBA projects in Project Explorer, which is used to set properties for the related objects, and the code Editing/Form Design window to enter the code for the module or edit the form and class module.
Second, VBA development common object detailed
VBA is similar to other object-oriented development languages, with a very large number of objects, and different office members provide a number of development objects, such as toolbars, Office Assistants, built-in dialogs, and forms.
(i) toolbars and their control objects
Using VBA to develop toolbars and menus in office, you use the CommandBar, CommandBarButton, CommandBarComboBox, and other objects provided by Office, which represent toolbars, toolbar buttons, and toolbar Drop-down boxes. These objects can be used to produce a variety of complex toolbars or menus.
1.CommandBars Collection
This collection represents all of Office's toolbars, can specify a menu bar or toolbar with a name or index number, but only a menu, shortcut menu, or submenu can be specified with a name. If two or more custom menu or submenu names are the same, the first object with that name is returned.
The Add method is used to create a new toolbar and return 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, the default name is used; position is an optional Variant type, representing the location of the new toolbar. The value of the parameter can be set through the VBA constants, such as Msobarleft, msoBarTop, Msobarright, Msobarbottom constants (where the new toolbar is located on the Software toolbar); msoBarFloating constants (on behalf of the new toolbar to move ; msoBarPopup constant (on behalf of the new Toolbar as a shortcut menu); MenuBar is an optional variant type used to set whether or not to replace the active toolbar with a new toolbar; Temporary is an optional variant type that is used to set whether the new toolbar is temporarily valid.
2.CommandBar objects
This object represents the toolbar in your application, and the new toolbar's controls are based on that object.
(1) Controls property: Returns a CommandBarControls object that represents all the controls in the specified toolbar.
(2) NameLocal property: Returns the name of the toolbar set by the application version language, such as an error with the built-in toolbar settings for the software.
(3) Position property: Returns or sets the position of the toolbar, which can be set by a VBA constant, such as Msobarleft, msoBarTop, Msobarright, Msobarbottom, msoBarFloating, msoBarPopup or Msobarmenu.
(4) Type property: Returns or sets the type of the toolbar, which can be set by a VBA constant, such as msobartypenormal (the toolbar is a normal type), Msobartypemenubar (toolbar is a menu type), Msobartypepopup ( Toolbar is a pop-up menu type), and so on.
(5) Reset method: Resets a built-in toolbar to its default setting, which is useful when restoring software's existing toolbars or menus. Resetting a built-in toolbar deletes the custom control and restores its built-in controls.
3.CommandBarControls Collection
This collection represents all the toolbar controls in the toolbar.
The Add method is used to add a toolbar control to the CommandBarControls collection.
4.CommandBarControl objects
This object represents a toolbar control, which you can define using CommandBarButton, CommandBarComboBox, and CommandBarPopup objects for custom toolbar controls, and for controls that are built into the software. And the control cannot be represented using the three objects above, you can use the CommandBarControl object.
(1) BeginGroup property: Lets you set whether a toolbar control is displayed in groups.
(2) Caption property: Lets you set the caption text for a toolbar control and as the default control ScreenTip.
(3) ID properties: Used to set the capabilities of the CommandBarButton, CommandBarComboBox, and CommandBarControl objects, which can be set directly to the ID of a built-in toolbar control, so that the control has the appropriate functionality built into the software, The ID property of the custom control needs to be set to 1.
(4) Copy method: Copy a Toolbar control to an existing toolbar.
Syntax: expression. Copy (Bar, before)
Parameter description:
Bar is an optional Variant type that represents the target toolbar and, if omitted, the control is copied to its own toolbar; Before is an optional Variant type that represents the location of the new control at the specified toolbar, or before the new control is added to the control in that position, if omitted, The control is copied to the end of the toolbar.
(5) Type property: Returns the type of the toolbar control that can be referenced by a VBA constant, as shown in the following table for the commonly used toolbar control types:
Constant name
Meaning
msoControlButton
Control button
Msocontrolbuttondropdown
button with Drop-down list
msoControlButtonPopup
A button with a pop-up menu
msoControlComboBox
Drop-down Combo Control Box
msoControlDropdown
Drop-down List Control Box
msoControlEdit
text box
Msocontrolexpandinggrid
Expandable tables
Msocontrolgraphiccombo
Image Drop-down combo box
msoControlGraphicDropdown
Image Drop-down list box
Msocontrolgrid
Form
msoControlPopup
pop-up menu
5.CommandBarButton objects
