Before Office2000, office system software such as Word, Excel and Access all used VBA, but it was unified by Office 2000.
Excel is the first application that contains VBA. Since Office2000, Exel, Word, PowerPoint and Access all have unified macro language VBA, among which Excel and Access are the most mature.
In Excel, VBA programs can be stored in. Xls or a separate text form. To learn VBA, users must first have a certain understanding of VB (but they don't have to be very professional. After all, users who often use Excel are generally office workers and can't be professional programmers), and then learn with a macro recorder. Because the macro recorder can record macros, users can know a lot about VBA very clearly. In addition, if it is not a powerful VBA program, it is enough to use a macro recorder. To open the password recorder, click Tools-> Macros-> Record a new macro. In addition, users can use Tools-> Macros-> Macros ... to manage macros. You can use Tools-> Macro -> VB Editor or Alt+F 1 1 to open the VB editor. In VB programmers, you can easily manage the whole VB project and write VBA code. In addition, users can click Alt+F 1 1 to switch to the Excel window.
The use process of VBA:
1. Create an Excel file.
2. Create a control, then double-click the control or right-click the control, and click "View Code" to view an event of the control, such as click, keydown, etc.
Write execution code. To create a control, click View->. Toolbar-> Control Toolbox, open the Control Toolbox column, and then you can drag the control directly into the Excel cell. In the Excel window, the added control is usually in the execution state. If you want to edit this control, you can edit its properties when you first create it, or open the control toolbox, or leave the control in an editing state. If not, you can add another control, and then the existing control will definitely be in the editing state. Of course, after modifying the control, don't forget to delete the newly added useless control. In the editing state, you can right-click the control and click "Properties", then you can open the properties window, in which you can easily edit the name, title, size and other properties of the control.
3. Manually or use macro recorder to encode the events of the control. Macro recorder is simple and easy to use, but powerful functions still need to be handwritten in VBA.
Introduction of VBA objects:
The key to writing VBA code is to use VBA objects flexibly and manipulate the properties or methods of these VBA objects. In addition, you can create custom functions to encapsulate certain operations. Since it is VBA, the coding style is of course the same as VB. If you know VB, you can write a powerful VBA program as long as you have a better understanding of VBA objects. Similarly, if you don't know VB, then you only need to know the object of VBA, because it is relatively simple and easy to learn.
The following respectively discuss the properties and methods of VBA objects:
In some window objects that can contain other objects, there are several special attributes whose attribute values are the currently active objects, and they can be directly referenced.
Some Special Properties of Table 1
Object name meaning
ActiveWorkbook current workbook, that is, if you open several Excel files, then the Excel file you are editing is of course ActiveWorkbook.
ActiveSheet The current worksheet in the current workbook is the worksheet being edited in the currently edited Excel file.
ActiveCell The active cell in the current worksheet.
ActiveChart the active chart in the current workbook.
Select the currently selected object.
These objects, their properties and methods are described in detail below.
I. Applicants
This object refers to the working environment of Excel application.
< 1 > attribute
1. Title attribute
Meaning: the text displayed in the title bar of an Excel application.
Example: Application.caption= "Crew Management System". If the specification is blank, the default title bar of Excel will be restored.
2. Cursor properties
Meaning: the shape of the mouse in Excel.
Table 2 Cursor properties
Attribute value mouse shape
XlDefault is missing a value and the mouse is missing a shape.
XlWait waits for a value, and the mouse is hourglass-shaped and keeps turning.
XlNorthwestArrow value, the mouse is in the shape of a standard arrow.
XlIBeam text value, the mouse is in an "I" shape waiting for the user to input text.
Action: Apply. Cursor=xlWait, etc.
3. Display alarm properties
Meaning: Used to decide whether to display application tips and warning messages of the system. If not, set some properties to false, and vice versa.
4. Display formula bar properties
Meaning: used to determine whether the formula edit bar is displayed.
5. Show scroll bar properties
Meaning: Used to decide whether to display the scroll bar of Excel.
6.DisplayStatusBar property
Meaning: used to decide whether to display the status bar of Excel.
7.EnableCancelkey attribute
Meaning: It is used to determine whether the user is allowed to interrupt the VBA program being executed. By default, during the execution of VBA program, users can interrupt the execution of VBA program by pressing Ctrl+Break. At this point, the value of this property is xlInterrupt. If you want to interrupt the disabled VBA program, assign it to xlDisabled by; If VBA encounters an error and needs to stop during execution, it will be designated as xlErrorHandler.
8. Screen Update Properties
Meaning: used to decide whether to display the intermediate operation result of VBA program.
9. Status bar properties
Meaning: Sets the text of the status bar.
10, ActiveWorkbook property
Meaning: as stated in table 1
1 1, activity table
Meaning: as stated in table 1
12, ActiveCell property
Meaning: as stated in table 1
13, ThisWorkBook property
Meaning: used to return the workbook object that is executing VBA program. This is not the current workbook. For example, when multiple Workbooks are opened, the ActiveWorkbook property returns the current workbook object, and ThisWorkbook returns the workbook object that runs VBA programs.
14, PathSeparator property
Meaning: Returns the file path separator "\" of the file, which is a read-only property.
Example: Dim strPath as a string
StrPath = application. ThisWorkbook. Path & application. Path operator
Workbooks. Open strPath & amp" myExcel.xls "
15, user name attribute
Meaning: Set the current user name. The default user is determined by the user name in the General tab of the Excel Options dialog box. Users can select Options from the Tools menu to open Options and reset the default user name in the User Name text box on the General tab.
< 2 > method
1, calculation method
Meaning: Recalculate all formulas in the workbook opened by Excel.
2.Goto method
Meaning: select a Range object or a VBA procedure in the workbook, and the format is as follows:
Apply. Go to Reference, Scroll
The Reference parameter indicates the destination of the Goto method. If the Scroll parameter is True, the scroll bar will scroll so that the Range object appears in the upper left corner of the workbook window. If it is False, the current workbook window will not change its properties. The default value is Galse
For example: applications. Gotland ("C3"), really.
3.InputBox method
Meaning: used to open an input dialog box, allowing users to input data. The format is as follows:
Apply. InputBox (prompt, title, left, top, help file, help context ID, type)
The prompt parameter is used to set the prompt information of the input dialog box.
The title parameter is used to set the title text of the input dialog box.
The default parameter is used to set the default input value of the input dialog box.
The left and top parameters are used to set the coordinate values of the upper left corner of the input dialog box.
The helpFile parameter is used to set the name of the online help of the input dialog box.
HelpContextID parameter is used to set the context identification number of the online help topic of the input dialog box.
The type parameter is used to set the type of input data in the input dialog box. By default, it is a text type.
Example: UserValue=Application. InputBox ("Please enter data", "Data input", type: =7).
4.Onkey method
Meaning: When a key or key combination is pressed, VBA code is executed. Try it yourself, or you can look at the prompt of VB editor (that is, the drop-down prompt that pops up after typing applicationo in VB editor). Onkey)。
Second, the Workbooks object set.
A collection of all currently open workbook objects.
& ltI > properties
1, Count property, read-only, returns the number of workbooks opened by Excel.
< second > way
1, Add method, set a new workbook for the workbook object, which is equivalent to "file->; New "command. The format is as follows:
Workbooks. Add template
2. The 2.Open method opens the specified workbook. Just look at the formatting tips in the VBA editor. I'm too tired to finish writing.
3. The 3.Close method closes the workbook file.
Third, the workbook object
Represents a standalone workbook file.
& ltI > properties
1, ActiveSheet property, returns the active Sheet object in the current workbook.
2.Author property, which returns or sets the name of the author of the workbook, thus determining the owner of the workbook.
3.Saved property is used to check whether the workbook file has been modified.
< second > way
1, Activate method, used to freeze a workbook as the current active workbook, such as workbooks ("myoffice.xls "). Activate.
2. The close method is used to close the specified workbook.
3. The save method is used to save the specified workbook.
4.SaveAs method, which is used to save as the specified workbook.
Fourth, the worksheet object set.
Represents a collection of all worksheets in the current workbook.
& ltI > properties
1, the Count property returns the number of worksheets in the current workbook.
< second > way
1, Add method, add a new worksheet object to the worksheet object collection, and the format is as follows:
Worksheet. Add Before, After, Count, Type
The Before and After parameters refer to the worksheet before or after which a new worksheet is inserted. The value is the worksheet.
For example: worksheet. Add before: = worksheets ("Sheet2 "), which is equivalent to inserting a new worksheet before the Sheet2 worksheet.
Verb (short for verb) worksheet object
Represents a separate worksheet of a workbook.
To get a worksheet, you can use a worksheet ("office expenses") or a report, such as worksheet (3).
& ltI > properties
The 1, Name property sets or returns the name of the worksheet object.
2. The 2.Visible property determines whether the worksheet is hidden.
< second > way
1, Calculate method, recalculate all formulas in the specified worksheet.
2. The copy method is used to copy the specified worksheet and place it in the specified location, which is actually a function of paying equal attention to copying and pasting.
Format: worksheet -object.copybefore, after
For example, a worksheet ("Worksheet 2"). Copy after: = Worksheets ("office expenses") is equivalent to copying the sheet2 worksheet and putting it after the worksheet named "Office Expenses".
3.Move method, which moves the specified worksheet and places it in the specified location. Users and copying methods are the same, and the difference is only the difference between copying and cutting.
4.Delete method, which deletes the specified worksheet without parameters, such as Worksheet(" sheet 2 ")delete.
5. Method to get the cells in the specified rows and columns of the specified worksheet.
Format: Worksheet-Object. Cells (row, col), where row and col are integers, and the values of rows and columns start from 1
For example, a worksheet ("Worksheet 2"). Cell (1, 1) is equivalent to getting the cell in the first row and the first column of the sheet2 worksheet.
Verb (abbreviation for verb) range object
Represents a half-bridge area, which can be composed of multiple half-bridges or a unit.
There are two ways to refer to cells:
One is to use cell references, such as range ("a1") and range ("a1:C3").
The second is to use the cell range name, such as Range(“my Range ").
& ltI > properties
1, general 1C 1 property, which is used to establish and store the formula of the specified cell or cell range.
Such as the range ("C5"). Regular1c1= "= sum (r [-4] c: r [-1] c)"
2.Value attribute, which assigns a value to the specified cell or cell range. If there are multiple cells, they are all given the same value.
3. Cell attribute, which describes the reference cell and the specified cell as struggles, and regards the specified cell as the 1 row and the 1 column.
Format: Range-Object. Cells (rows, columns)
4.Offset, which describes the struggle between the reference cell and the specified cell, and treats the specified cell as row 0 and column 0.
Format: Range-Object. Cells (rows, columns)
5.Name property, which returns or sets the name of the specified cell or range of cells. Then in VBA, you can call it by name directly.
6.Count property, which returns the number of cells in the specified cell range. Read only.
7.CurrentRegion property, which returns a linked cell area that stores data based on cells.
< second > way
The 1.Select method is used to select the specified cell range. After execution, the first cell in the upper left corner of the cell range becomes the active cell.
Such as range ("a1:c3"). Select the range ("A 1: C3, D5: E6, A8: E8"). choose
2.Activate method, which is used to select the specified cell and designate it as the active cell. Of course, only the first cell in the upper left corner of the cell range becomes the active cell.
3.AutoFit method: adjust the row height and column width of the row and column where the selected cell range is located, so that the row height and column width are automatically set to the optimal height and width.
For example: range ("A4"). currentregion.select
choose Ok. Automatic adjustment
4.Clear method, which clears the content, format and comments of the specified cell area, equivalent to "edit-> clear-> all"
For example: range ("A4: C5"). clear
5. the 5.ClearContents method is equivalent to "edit-> clear-> content".
6.ClearFormats method, which is equivalent to "Edit-> Clear-> Format".
7. the 7.ClearComments and ClearNotes methods are used to clear the contents of the specified cell range, which is equivalent to "edit-> clear-> comment".
8. Copy method: copy the database in the specified cell area to the clipboard or the specified destination cell area.
9. Cut method: cut the database in the specified cell area to the clipboard or the specified target cell area.
10, PasteSpecial method, which is used to selectively paste the database of copied or cut cell area in the clipboard into the specified target cell area.
Format: range-object. paste special paste, operation, ship blanks, transpose.
Paste parameters specify the selection of volume type data to be pasted, such as formula, content, comments, etc. Please refer to the prompt in VB editor for specific values.
What kinds of plastic surgery can be divided into?
Plastic surgery mainly deals with skin, muscle and bone traum