[Posting] Summary of VBA statements

Custom module behavior
(1) Option Explicit forces the declaration of all variables in the module
Option Private module' indicates that the Module is Private and is only useful to other modules in the same project. It is not displayed in the macro dialog box.
Option Compare text' string is case insensitive
Option Base 1' specifies that the first subscript of the array is 1
(2) On Error Resume Next 'ignore errors and continue executing VBA code to avoid Error messages
(3) On Error GoTo ErrorHandler jump to a position in the process when an Error occurs.
(4) On Error GoTo 0' Error prompt
(5) Application. DisplayAlerts = false' do not display the warning box during Program Execution
(6) Application. ScreenUpdating = false' disable screen refresh
Application. ScreenUpdating = true' enable screen refresh
(7) Application. Enable. CancelKey = xlDisabled 'Disable Ctrl + Break to stop macro operation

Workbook
(8) Workbooks. Add () 'create a new workbook
(9) Workbooks(“book1.xls "). activate' Activate a workbook named book1
(10) ThisWorkbook. save' Save the workbook
(11) ThisWorkbook. close 'close the current workbook
(12) ActiveWorkbook. Sheets. Count 'to obtain the number of worksheets in the active workbook.
(13) ActiveWorkbook. name' returns the name of the activity workbook.
(14) ThisWorkbook. name' returns the Name of the current workbook.
ThisWorkbook. FullName 'returns the path and name of the current workbook.
(15) ActiveWindow. EnableResize = false' do not adjust the size of the active workbook.
(16) Application. Window. Arrange xlArrangeStyleTiled Arrange the workbook in tile mode.
(17) ActiveWorkbook. WindowState = xlMaximized 'maximizes the current workbook

Worksheet
(18) ActiveSheet. UsedRange. Rows. Count 'the number of Rows used in the current Worksheet
(19) Rows. Count 'to obtain the number of Rows in the worksheet (Note: Forward compatibility is considered)
(20) Sheets (Sheet1). Name = "Sum" 'Name Sheet1 as Sum
(21) ThisWorkbook. Sheets. Add Before: = Worksheets (1) 'Add a new worksheet Before the first Worksheet
(22) ActiveSheet. Move After: = ActiveWorkbook ._
Sheets (ActiveWorkbook. Sheets. Count) 'Move the current worksheet to the end of the worksheet
(23) Worksheets (Array ("sheet1", "sheet2"). Select both Worksheet 1 and worksheet 2
(24) Sheets ("sheet1"). Delete or Sheets (1). Delete 'Delete Worksheet 1
(25) ActiveWorkbook. Sheets (I). name' get the worksheet I Name
(26) ActiveWindow. DisplayGridlines = Not ActiveWindow. DisplayGridlines switch the display of gridlines In the worksheet. This method can also be used for switching between the two sides, that is, the switch button.
(27) ActiveWindow. DisplayHeadings = Not ActiveWindow. DisplayHeadings 'Switch the row and column border display in the worksheet.
(28) ActiveSheet. UsedRange. FormatConditions. delete' Delete all the condition formats in the current Worksheet
(29) Cells. Hyperlinks. delete' cancels all Hyperlinks to the current Worksheet
(30) ActiveSheet. PageSetup. Orientation = xlLandscape
Or ActiveSheet. PageSetup. Orientation = 2' to change the page settings to horizontal
(31) ActiveSheet. PageSetup. RightFooter = ActiveWorkbook. FullName: Enter the file path at the end of the table set on the page.
ActiveSheet. PageSetup. LeftFooter = Application. UserName: place the user name in the footer of the activity worksheet.

Cell/cell area
(32) ActiveCell. CurrentRegion. Select
Or Range (ActiveCell. End (xlUp), ActiveCell. End (xlDown). Select
'Select the range included in the current active cell, and there are no blank rows between the upper, lower, and lower ends.
(33) Cells. Select all Cells in the current Worksheet
(34) Range ("A1"). ClearContents clear the content in cell A1 on the active Worksheet
Selection. ClearContents clear content in the selected region
Range ("A1: D4"). clear' completely clears the content from cell A1 to cell D4, including the format
(35) Cells. clear' Clear the content of all Cells in the worksheet.
(36) ActiveCell. Offset (). Select 'Move the active cell down one row. Similarly, you can move one column down.
(37) Range ("A1"). Offset (ColumnOffset: = 1) or Range ("A1"). Offset (, 1) 'Offset a column
Range ("A1"). Offset (Rowoffset: =-1) or Range ("A1"). Offset (-1) 'Offset a row up
(38) Range ("A1"). Copy Range ("B1") 'Copy cell A1 and paste it to cell B1.
Range ("A1: D8"). Copy Range ("F1") "copies the cell area to the area starting with cell F1.
Range ("A1: D8"). Cut Range ("F1") 'Cut the cell area A1 to D8 and copy it to the area starting with cell F1.
Range ("A1"). CurrentRegion. Copy Sheets ("Sheet2"). Range ("A1") 'Copy the cell area containing A1 to the cell area starting with A1 in worksheet 2.
Note: The CurrentRegion attribute is equivalent to the positioning command, which consists of a rectangular cell block surrounded by one or more empty rows or columns.
(39) ActiveWindow. RangeSelection. Value = xx' enter the Value XX to the selected cell area.
(40) ActiveWindow. RangeSelection. Count 'number of cells selected in the activity window
(41) Selection. Count 'number of cells in the current selected area
(42) GetAddress = Replace (Hyperlinkcell. Hyperlinks (1). Address, mailto:, ") 'return the Address of the super-level link in the cell and assign a value
(43) TextColor = Range ("A1"). Font. colorindex' check the text color of cell A1 and return the color index.
Range ("A1"). Interior. ColorIndex 'get cell A1 background color
(44) cells. count 'returns the number of cells in the current worksheet.
(45) Selection. Range ("E4"). Select 'activates three rows under the current active cell and four rows to the right.
(46) Cells. Item (5, "C ")'
Cells. Item (5, 3) 'leads the cell C5
(47) Range ("A1"). Offset (RowOffset: = 4, ColumnOffset: = 5)
Or Range ("A1"). Offset () 'specifies the cell F5
(48) Range ("B3"). Resize (RowSize: = 11, ColumnSize: = 3)
Rnage ("B3"). Resize () 'create B3: D13 Region
(49) Range ("Data"). Resize (, 2) 'expands the Data area by two columns
(50) Union (Range ("Data1"), Range ("Data2") 'connects the Data1 and Data2 regions
(51) Intersect (Range ("Data1"), Range ("Data2") 'returns the Cross Region of Data1 and Data2
(52) Range ("Data"). Count 'number of cells in Data in the cell area
Range ("Data"). Columns. Count 'number of Columns in the Data area of the cell
Range ("Data"). Rows. Count 'number of Rows in Data in the cell Region
(53) Selection. Columns. Count 'number of Columns in the currently selected cell area
Selection. Rows. Count 'number of Rows in the currently selected cell area
(54) Selection. Areas. Count 'number of regions included in the selected cell area
(55) ActiveSheet. UsedRange. row' gets the Row number of the first Row used in the cell area
(56) Rng. Column 'gets the Column number of the cell in the upper left corner of Rng in the cell area.
(57) ActiveSheet. Cells. SpecialCells (xlCellTypeAllFormatConditions) 'returns all areas in the active worksheet that meet the condition format settings
(58) Range ("A1"). AutoFilter Field: = 3, VisibleDropDown: = false' disable the drop-down list of the 3rd fields generated by executing the automatic filter command.

Name
(59) Range ("A1: C3"). Name = "computer" 'name A1: C3 region: computer
Or Range ("D1: E6"). Name = "Sheet1! Book "'name the local variable, that is, the area D1: E6 on Sheet1 is book
Or Names ("computer"). Name = "robot" 'to rename the regional computer as a robot
(60) Names ("book"). delete' Delete name
(61) Names. Add Name: = "ContentList ",_
RefersTo: = "= OFFSET (Sheet1! A2, 0, 0, COUNTA (Sheet2! $ A: $ A) "'dynamic name column
(62) Names. Add Name: = "Company", RefersTo: = "CompanyCar" 'Name string CompanyCar
(63) Names. Add Name: = "Total", RefersTo: = 123456 'Name the number 123456 as Total. Note that numbers cannot be enclosed in quotation marks. Otherwise, they are named strings.
(64) Names. Add Name: = "MyArray", RefersTo: = ArrayNum 'Name the array ArrayNum as MyArray.
(65) Names. Add Name: = "ProduceNum", RefersTo: = "= $ B $1", Visible: = false' hide the Name
(66) ActiveWorkbook. Names ("Com"). name' returns the Name string

Formulas and functions
(67) Application. WorksheetFunction. IsNumber ("A1") 'Use the worksheet function to check whether the data in cell A1 is a number.
(68) Range ("A: A"). Find (Application. WorksheetFunction. Max (Range ("A: A"). Activate
'Activate the maximum cell in column A of the cell area
(69) Cells (8, 8 ). formulaArray = "= SUM (R2C [-1]: R [-1] C [-1] * R2C: R [-1] C) "'enter the array formula in the cell. Note that the R1C1 expression must be used.

Chart
(70) ActiveSheet. ChartObjects. Count 'to obtain the number of charts in the current Worksheet
(71) ActiveSheet. ChartObjects ("Chart1"). Select the chart Chart1 in the current Worksheet
(72) ActiveSheet. ChartObjects ("Chart1"). Activate
ActiveChart. ChartArea. Select 'Select the current chart area
(73) WorkSheets ("Sheet1"). ChartObjects ("Chart2"). Chart ._
ChartArea. Interior. ColorIndex = 2' change the color of the chart area in the worksheet
(74) Sheets ("Chart2"). ChartArea. Interior. ColorIndex = 2' change the color of the chart area in the chart Worksheet
(75) Charts. add' Add a new chart Worksheet
(76) ActiveChart. SetSourceData Source: = Sheets ("Sheet1"). Range ("A1: D5 "),_
PlotBy: = xlColumns specify the chart data source and arrange it in Columns
(77) ActiveChart. Location Where: = xlLocationAsNewSheet new chart as new chart Worksheet
(78) ActiveChart. PlotArea. Interior. ColorIndex = xlNone convert the color of the drawing area to white.
(79) WorkSheets ("Sheet1"). ChartObjects (1). Chart ._
Export FileName: = "C: MyChart.gif", FilterName: = "GIF" ', Export table 1 to drive C and name it MyChart.gif

Form
(80) MsgBox "Hello !" 'Hello message displayed in the message box
(81) Ans = MsgBox ("Continue ?", VbYesNo) 'click "yes" in the message box, And the Ans value is vbYes; click "no", and the Ans value is vbNo.
If MsgBox ("Continue ?", VbYesNo) <> If the return value of vbYes Then Exit Sub is not "yes", Exit
(82) Config = vbYesNo + vbQuestion + vbDefaultButton2 'use a combination of constants, assign values to the Config variable of the group, and set the second button as the default button
(83) MsgBox "This is the first line." & vbNewLine & "Second line." 'forces line breaks in the message box. vbCrLf can be used to replace vbNewLine.
(84) MsgBox "the average is:" & Format (Application. worksheetFunction. average (Selection), "#,## 0.00"), vbInformation, "selection count average" & Chr (13) 'use the worksheet function to return the Average value of the selected area and display it in the specified format
(85) Userform1.Show 'displays the user form
(86) Load Userform1: loads a user form, but the form is hidden.
(87) Userform1.Hide hide user forms
(88) Unload Userform1 or Unload Me 'unmount the user form
(89) (image control). Picture = LoadPicture ("image path") 'display the image in the user form
(90) UserForm1.Show 0 or UserForm1.Show vbModeless set the form to the stateless state.
(91) Me. Height = Int (0.88 * ActiveWindow. Height) 'the Height of the form is 0.88 of the Height of the current active window
Me. Width = Int (0.88 * ActiveWindow. Width) 'form Width is 0.88 of the height of the current active window

Event
(92) Application. EnableEvents = false' disable all events
Application. EnableEvents = true' enable all events
Note: It is not applicable to events triggered by user form controls.
Object
(93) Set ExcelSheet = CreateObject ("Excel. Sheet") 'create an Excel worksheet object
ExcelSheet. Application. Visible = true' sets the Application object to make Excel Visible.
ExcelSheet. Application. Cells (1, 1). Value = "Data" 'Enter text in the first unit of the table
ExcelSheet. SaveAs "C: \ TEST. XLS" 'Save the table to the C: \ test.xls directory.
ExcelSheet. Application. Quit 'Close Excel
Set ExcelSheet = Nothing 'releases the object variable
(94) 'declare and create an Excel Object Reference
Dim xlApp As Excel. Application
Dim xlBook As Excel. Workbook
Dim xlSheet As Excel. WorkSheet
Set xlApp = CreateObject ("Excel. Application ")
Set xlBook = xlApp. Workbooks. Add
Set xlSheet = xlBook. Worksheets (1)
(95) 'create and pass an Excel. Application Object Reference
Call MySub (CreateObject ("Excel. Application "))
(96) Set d = CreateObject (Scripting. Dictionary) 'create a Dictionary object variable
(97) d. Add "a", "Athens" 'Add keywords and entries for object variables

Others
(98) Application. OnKey "^ I", "macro" 'sets Ctrl + I as the shortcut key for macro process
(99) Application. CutCopyMode = false' exit the cut/copy mode.
(100) Application. Volatile true' this function is forcibly calculated whenever any cell in the worksheet is recalculated.
Application. Volatile false' the function is re-computed only when one or more parameters of the function change.