VBA Shh Shh

VBA Shh Shh

What is VBA ? What role does it have?

A. Implement features that are not implemented in Excel.

B. Improve the speed of operation.

C. Writing a custom function.

D. Implement automation functions.

E. Make small management software by inserting a form.

VBA where is it stored? How does it work?

A. In the module

In Excel 2010, without the "developer" item, the "file"--"options"--"Customize Ribbon"--check "development tools"-"OK" (Figure 1).

(Note: In order to improve the quality of the images inserted in word2010, "file"--"options"--"advanced"--check "Do not compress file image quality"--"OK". ）

Figure 1 "Development tools" opens the code in Figure 2, "Module 1"

B. Run a macro

Click the green triangle to the right to run.

Figure 3 Run interface Figure 4 run result

3. What is a macro? What is the relationship between Macros and VBA?

Macro, is a batch processing.

Macros can often be recorded and manually programmed, and VBA is typically done programmatically. Macros can be used in conjunction with VBA.

4. Record a macro.

A. " Development tools--"Record Macro"--macro named "Input 100"--"record"--Enter 100--"Stop" in cell A1.

B. " Macro "--check" Enter 100 "--" execute ".

Figure 5 Selecting "Record Macro" Figure 6 enter 100 Figure 7 in a cell select "Stop Recording"

Figure 8 Click on "Macro" and check "Enter 100"-"execute" Figure 9 execution result

5. Write a macro.

A. " Development Tools--Visual Basic--View--Project Explorer--Insert--module--Enter the code on the right:

--"Save".

B. Click on the green right triangle to run. or close the VBA editing window and click Macros--check "test"--"execute".

Figure 10 Click on the green Right triangle run Figure 11 Run result

6.VBA statements

A. Macro Program statement.

B. Function program Statements

C. Should be shipped the statement in the program.

D. Looping statements.

7.VBA objects

A. Workbook objects

Workbooks represents the workbook collection, all workbooks, Workbooks (n), which represents the nth workbook that has been opened

Workbooks ("Workbook name")

ActiveWorkbook workbook that is being manipulated

ThisWorkBook ' The workbook where the code resides

B. Sheet objects

Sheets ("sheet name")

Sheet1 represents the first inserted sheet, Sheet2 represents the second inserted sheet ....

Sheets (n) indicates that the nth worksheet is sorted in order

ActiveSheet represents the active sheet, the worksheet on which the cursor is located

Worksheet also represents a worksheet, but does not include chart sheets, macro worksheets, and so on.

C. Cell Object

Cells all cells

Range ("Cell address")

Cells (number of rows, columns) Range ("B3") and cells (3,2) represent the same cells

Activecell cells that are being selected or edited

Selection the cell or range of cells that is being selected or selected

8. VBA Properties

A VBA property is a feature of a VBA object that represents an object's properties as follows:

Object. Property = property Value

Sub TTT ()

Range ("A1"). Value = 100 ' fills the cell with the value 100

End Sub

Sub TTT1 ()

Sheets (1). Name = "Worksheet renamed" "Rename Workbook to" worksheet renamed "

End Sub

Sub Ttt2 ()

Sheets ("Sheet2"). Range ("A1"). Value = "ABCD" ' fills the A1 cell of the Sheet2 worksheet with the string "ABCD"

End Sub

Sub Ttt3 ()

' Inner fill color of cell '

Range ("A2"). Interior.ColorIndex = 3 ' Sets the background color of the A2 cell to red

Range ("A2"). Font.ColorIndex = 3 ' Sets the font color of the A2 cell to red

End Sub

9.VBA method

The VBA method is an action on a VBA object that represents an object that is used for VBA in a method, and can be in the following format:

Object. Method Parameter Name: = parameter value

Sub Ttt4 ()

' Range ("A1"). Copy destination:= Range ("A2")

Range ("A1"). Copy Range ("A2") copies the contents of A1 to A2

End Sub

Sub Ttt5 ()

Sheet1.move before:=sheets ("Sheet3") before moving the Sheet1 table to Sheet3

End Sub

VBA Shh Shh