Basic statements for getting started with Excel VBA

Select the workbook for the current activity:
Thisworkbook. Activate
If you choose another job book, the book must be opened, and you do not need to forget to add the suffix ". xls", for example:
Windows ("totol.xls"). Activate
Select a worksheet:
Sheets ("balance"). Activate.
Select cells:
Range ("A1"). Select
Select a continuous cell:
Range ("A1: G8"). Select
Select discontinuous cells:
Range ("A1, B6, D9"). Select
Range ("A1, B6: B10, D9"). Select
Move between cells:
Activecell. offset (13, 14). Select
Selection. offset (-3,-4). Select
Range ("G8"). offset (-3,-4). Select
Note: You can define a variable and use offset to implement it. For example:
Varfreightrowscount = range ("A1"). currentregion. Rows. Count
Activecell. offset (varfreightrowscount, 0). Select
Select the entire Worksheet:
Cells. Select
Select the region where the current cell is located (when an empty row or column is empty ):
Range ("A1"). currentregion. Select
Select rows or columns:
Rows ("1"). Select
Columns ("A"). Select
Or:
Activecell. entirerow. Select
Activecell. entirecolumn. Select
Select multiple adjacent rows/columns:
Columns ("A: C"). Select
Rows ("1:5"). Select
Select multiple non-adjacent rows/columns:
Note: Unlike selecting adjacent multiple rows/columns, use "range" instead of "columns/rows ":
Range ("A: A, C: C, E: F"). Select
Range (","). Select
Select the current active cell and move it down to the last non-empty cell:
Range ("A1", range ("A1"). End (xldown). Select
Range (activecell, activecell. End (xldown). Select
Select the current active cell to go up to the first non-empty cell:
Range ("A32", range ("A32"). End (xlup). Select
Range (activecell, activecell. End (xlup). Select
Select the right of the current active cell to the first non-empty cell.
Note: It is "xltoright" instead of "xlright"
Range ("A1", range ("A1"). End (xltoright). Select
Range (activecell, activecell. End (xltoleft). Select
Select 10th to the right of the current active cell.
Range ("A2", range ("A2"). offset (0, 10). Select
Range (activecell, activecell. offset (0, 10). Select
Select the current active cell to the left to 10th cells.
Range ("A20", range ("A20"). offset (0,-10). Select
Range (activecell, activecell. offset (0,-10). Select
Select the current active cell to go down to the 10th cells.
Range ("A2", range ("A2"). offset (10, 0). Select
Range (activecell, activecell. offset (10, 0). Select
Select the current active cell to go up to 10th cells.
Range ("A1"). End (xldown). offset (1, 0). Select
Select the first empty cell in the row:
Range ("A1"). End (xltoright). offset (0, 1). Select
Change the area size (from A1: B5 to A1: D10): Note: The area is not expanded, but redefined. That is, "selection. Resize (10, 4). Select instead of selection. Resize (5, 2). Select

The Excel macro cannot record the action to move to the first cell of the current row (that is, the action you press the "home" key). The following statement can be done:
Activecell. offset (0,-activecell. Column + 1). Select
Move to the first cell of the current column:
Activecell. offset (-activecell. Row + 1, 0). Select
About if... then... endif
If there is only one condition and one action, you can use the following statement:
If selection. value> 10 then
Selection. offset (1, 0) = 100
End if
Or simpler:
If selection. value> 10 then selection. offset (1,0) = 100
If there are two conditions and two actions, you can use the following statement
If selection. value> 10 then
If selection. value = 12 then
Selection. offset (1, 0) = 100
End if
Selection. offset (1, 0) = 20
End if
About if... then... And... endif
When there are two conditions and an action, you can use the following statement:
If selection. value = 10 and selection. offset (0, 1). value = 20 then
Selection. offset (1, 0) = 100
End if
About if... then... or... endif
When there are two conditions and an action, you can use the following statement:
If selection. value = 10 or selection. offset (0, 1). value = 20 then
Selection. offset (1, 0) = 100
End if
About if... then... else... endif
When there is only one condition and two actions, you can use the following statement:
If selection. value> 10 then
Selection. offset (1, 0) = 100
Else
Selection. offset (1, 0) = 0
End if
About if... then... elseif... endif
When there are more than one condition and each condition follows different actions, you can use the following statement:
If selection. value = 1 then
Selection. offset (1, 0) = 10
Elseif selection. value = 2 then
Selection. offset (1, 0) = 20
Elseif selection. value = 3 then
Selection. offset (1, 0) = 30
Elseif selection. value = 4 then
Selection. offset (1, 0) = 40
Elseif selection. value = 5 then
Selection. offset (1, 0) = 50
End if
About select case
When you have to test many conditions, you can use the select case statement to replace if then... elseif. Syntax As follows:
Sub test ()
Select case selection. Value
Case is> = 85
Selection. offset (0, 1) = ""
Case is> = 75
Selection. offset (0, 1) = "B"
Case is> = 65
Selection. offset (0, 1) = "C"
Case is> = 50
Selection. offset (0, 1) = "D"
Case else
Selection. offset (0, 1) = "F"
End select
End sub
If the value of the selected cell is greater than 85, the value of the cell on the right is ""...... When the value of the selected cell is less than 50, the value of the cell on the right is "F"
Here are some examples of VBA functions lcase, now (), ucase:
Note: Many Excel functions can be used in VBA as follows:
Varanswer = application. worksheetfunction. sum (range ("A1: A32 "))
Or
Varanswer = application. sum (range ("A1: A32 "))
Lcase
When determining the input characters, we cannot know whether the characters entered by the user are in upper or lower case. The lcase function can convert them to lower case:
If lcase (selection. Value) = "Toto" then...
Or
Select case lcase (selection. value)
Or
Do While lcase (selection. Value) <> "Toto"
Now ()
Now () is both an Excel function and a VBA function.
The following statement shows the current time when cell A1 is opened:
Range ("A1"). formula = "= now ()"
The following statement grants the cell "A1" the current time after execution, which will not change unless you execute the statement again. Each time you open a workbook, the cell "A1" value does not change.
Range ("A1"). value = now ()
Ucase
When determining the input characters, we cannot know whether the characters entered by the user are in upper or lower case. The lcase function can convert them into upper case letters:
If ucase (selection. Value) = "Toto" then...
Or
Select case ucase (selection. value)
Or
Do While ucase (selection. Value) <> "Toto"