Getting Started with Excel VBA

First, file format

The Vba,excel file that you want to use must be saved as a macro-enabled workbook, the XLSM format.

Second, start the VBA editor

After opening the workbook, to start the VBA editor, there are two methods, one is to right-click on the worksheet name, select "View Code", the other is the shortcut key alt+f11

Iii. Introduction to the Project Explorer

The tree-like directory on the right side of the VBA editor is the Project Explorer, such as a project (VBAProject) and its following individual objects. A workbook is a project, the following Sheet1 represents a worksheet, double-click it to view and edit the code of this worksheet, to implement various functions, ThisWorkbook represents the entire workbook.

Iv. Immediate Window, MsgBox and Hello World program

As with all other programming languages, the first program is output in one sentence: Hello world.

First select the menu bar-view-Immediate window to open the Immediate window (shortcut key ctrl+g), the Immediate window means that the code in the entry will be executed immediately after the input, a bit like debugging JavaScript console.

In the window, enter

MsgBox " Hello World "

Then enter and you will see the Hello World dialog box.

V. Worksheets--worksheets (1), Sheets (1) and Sheet1

The following three lines of code will output the name of worksheet 1:

MsgBox Worksheets (1). NameMsgBox Sheets (1). NameMsgBox Sheet1.name

Worksheets represents a collection of all the worksheets in the active workbook, and Worksheets (1) represents the 1th sheet in the collection, which is Sheet1

Sheets represents the collection of all charts (Charts) and Worksheets (worksheets) in the currently active workbook, that is, sheets contains the worksheets above

Sheet1 directly represents the first table in the currently active workbook

Vi. Regional--range

For example, range ("A1") represents A1 cells, and range ("A1:b2") represents a range of four cells, such as A1, A2, B1, B2, and so on, and if you want to represent discontinuous regions, separate multiple discontinuous regions with commas, such as Range ("A1:b2, C3:d4 ")

The following code enters the address of the zone:

MsgBox Sheet1.range ("a1:b2"). Address

Run results

Seven, Cell--cells

Cells--the cell that represents the 2nd column of row 1th, which is cell B1.

Cells = "I am a Cell"--Indicates that the text "I am a cell" is filled in the 2nd column of line 1th

Eight, Process Control statements

If condition Then

...

End if

---------------------------------------

If condition Then

...

Else

...

End if

----------------------------------------

If condition Then

...

ElseIf condition Then

...

Else

...

End If

-----------------------------------------

For I=0 to 100

...

Next

-----------------------------------------

While condition

...

Wend

-----------------------------------------

Do

...

Loop while condition

-----------------------------------------

Nine, copy

Cell replication:

Cells (1,2). Copy Cells (1,3

The above code copies the data from column 2nd of row 1th to the 1th row 3rd column

---------------------------------------------------------

Zone replication:

Range ("a1:b2"). Copy Range ("C3"

The above code copies the contents of the A1:B2 zone to the C3 area

----------------------------------------------------------

Row or column replication:

Rows (1). Copy Rows (2) Columns (3). Copy Columns (4)

Copy the data from line 1th to the bottom 2 rows

Copy data from row 3rd to line 4th

----------------------------------------------------------

X. Removal of--clear and clearcontent

Range (A1:B2). ClearContent  ' clears area content range (A1:B2). Clear  ' erase area, including content and formatting

Xi. row and column Adaptive High (wide) degrees

Rows.AutoFitColumns.AutoFit

12. Jump to a worksheet

Jumping to a worksheet activates a worksheet so that it becomes active:

Sheet2.activate

13. Events

For example, to achieve double-click Sheet1 cell A1 when the pop-up prompt good, then in the Project Explorer double-click Sheet1, open the Sheet1 Code window, the window at the top of the first drop-down menu select worksheet, The second drop-down menu selects BeforeDoubleClick, where the code window appears:

Private Sub Worksheet_beforedoubleclick (ByValas asBoolean)End Sub

This code indicates that the SHEET1 worksheet can be double-clicked when the action is performed, but we want to A1 cell is double-clicked when the pop-up window, then also to be double-click the place to judge, the target is double-clicked, we determine whether the object is A1, is the pop-up window prompt, No action is taken:

Private Sub Worksheet_beforedoubleclick (ByValas asBoolean)If"  $A"then    MsgBox"good" End If End Sub

Getting Started with Excel VBA