[Getting started with VBA] commonly used and interpreted VBA

[Getting started with VBA] commonly used and interpreted VBA
[A65536]. End (xlup). Row 'a column last row to the first row with a value
[A1]. End (xldown). Row 'a column first row down first row with a value
[Iv1]. End (xltoleft). column' the last column of the first row has a number column to the first column on the left.
[A1]. End (xltoright). Column 'specifies the number of columns in the first column of the First row with consecutive values to the right.
Application. commandbars ("standard"). Controls (2). begingroup = true' insert a separator before the second button in the commonly used Toolbar
Cells. wraptext = false' cancel Automatic line feed
If Len (target)> 5 then', if the number of characters in the current cell exceeds 5, execute the next line.
Target. wraptext = true' automatic line feed
End if
[A1: B10]. specialcells (xlcelltypeblanks). Rows. Hidden = true 'hide rows with spaces
[A2]. Parent. name' returns the worksheet name of the active cell.
[A2]. Parent. Parent. name 'return the workbook name of the active cell.
As follows: Code Automatically closes a workbook 30 seconds after it is opened (or idle for 30 seconds) without entering or selecting a new one.
Private sub workbook_open () 'workbook opening event
Tt' start the TT process when the workbook is opened
End sub
Private sub workbook_sheetchange (byval sh as object, byval target as range) 'worksheet change event
Tt' start the TT process when any cell in the worksheet changes
End sub
Private sub workbook_sheetselectionchange (byval sh as object, byval target as range) 'worksheet selection change event
The TT process is started when the selection of cells in the TT 'worksheet changes.
End sub
Sub TT () 'tt Process
Dim mynow as date, BL as integer 'defines mynow as the date type, and Bl as a long integer
Mynow = now assigns the current time to the variable mynow
Do 'start the loop statement do
BL = second (now)-second (mynow) 'continuously checks the BL value of the variable in the loop.
If BL = 30 Then goto Cl 'jump to Cl when BL = 30
Doevents 'transfer control so that sheets can continue operations
Loop until BL> 30' when BL> 30, the loop jumps out.
Exit sub
CL:
Application. enableevents = false' to avoid other events
Activeworkbook. Close true' close activity workbook and save
Application. enableevents = true' can trigger other events
End sub
Range ("E4"). addcomment. Text "" & CHR (10) & "content ...... "'Add Annotation
Range ("E4"). Comment. Visible = true' display Annotation
Adjust the specified columns of all worksheets in the workbook to the optimum column width:
Sub: Adjust column width ()
Dim I %
For I = 1 to sheets. Count 'traverse all worksheets in the workbook
Sheets (I). Columns ("A: K"). autofit "adjusts the [A: K] column of each worksheet to the optimum column width.
Next I
End sub
Several forms of do loop statements:
1.
Do while I> 1' is executed when the condition is true
... The statement to be executed
Loop
2.
Do until I> 1' is executed when the condition is false.
... The statement to be executed
Loop
3.
Do
... The statement to be executed
Loop while I> 1' is executed when the condition is true
4.
Do
... The statement to be executed
Loop until I> 1' is executed when the condition is false.
5. While... Wend statement
When the while I> 1' condition is true
... The statement to be executed
Wend
Select "VBA project trust"
Application. sendkeys "% (tmstv) {enter}" 'operation in the Excel window
Application. sendkeys "% (qtmstv) {enter}" 'In The VBE window
Application. commandbars ("command button name"). Position = msobarfloating 'Causes [command button] to be suspended in the table
Application. commandbars ("command button name"). Position = msobartop 'Arrange the [command button] In the toolbar
Activesheet. Protect password: = "wshzw" 'Add a password for worksheet Protection
Activesheet. unprotect password: = "wshzw" 'remove worksheet Protection
Activesheet. protectcontents
Copy and name a worksheet
Sub wshzw ()
Dim I as integer
For I = 1 to 5
Sheets ("sheet1"). Copy after: = sheets (1) 'Before/after copy the new table before/after sheets ("sheet1 ")
Activesheet. Name = I & "" 'is the name of the new table to be copied.
Next I
Sheets ("sheet1"). Name = "" 'is sheets ("sheet1") Renamed
End sub
Application. enableevents = false
......
Application. enableevents = true 'suppress event chained execution
Application. enableevents = false
Activeworkbook. Save 'suppress beforesave events
Application. enableevents = true' suppress specified events
Application. displayalerts = false' blocking confirmation prompt
Application. screenupdating = false
.......
Application. screenupdating = true' freeze the screen to speed up Program Run

activecell. currentregion. select 'select the region connected to the active cell
range ("A2: A20 "). numberformatlocal = "00-00" 'format
activesheet. cells. specialcells (xlcelltypelastcell ). row 'last row of the used region
activesheet. copy before: = sheets (1) 'Copy the activity worksheet before the first worksheet
range ("A2: A20 "). formulahidden = true' formula for hiding some cells when the worksheet is protected

Filedatetime ("E: \ My Documents ents \ 33.xls ")
Or
Filedatetime (thisworkbook. fullname) 'date and time after the file is created or last modified
Filelen (thisworkbook. fullname)/1024
Or
Filelen ("E: \ My Documents \ temp \ 33.xls")/1024 'file length (size), in KB
Application. asktoupdatelinks = false' do not ask whether to update the link and automatically update the link
Activesheet. hyperlinks. delete' delete an active worksheet hyperlink
Activeworkbook. savelinkvalues = false' do not save the external link value of the activity workbook
Activesheet. pagesetup. centerfooter = range ("K2"). Value 'specifies the custom footer when printing.
Activesheet. pagesetup. Orientation = xllandscape 'is set to print horizontally.
Activesheet. pagesetup. Orientation = xlportrait 'is set to vertical print
Application. windowstate = xlminimized 'minimize the window
Application. windowstate = xlnormal 'maximize the window
Sub Delete Worksheet ()
Application. displayalerts = false
Sheet1.delete
Application. displayalerts = true
End sub
If yes, add
Sub add Worksheet ()
For I = 1 to 5
Worksheets. Add. Name = I
Next
End sub
[A1: A20]. advancedfilter xlfiltercopy, [B1], unique: = true 'can remove duplicate data
[A2: c32]. replace what: = "F", replacement: = "G" 'search and replace within a specified range
Activesheet. autofiltermode = false' cancel Automatic Filtering
Execute the following statement to effectively reduce the size of the workbook. Before executing the statement, see the role of each statement:
Activesheet. usedrange. clearcomments clear all comments in the range used by the active Worksheet
Activesheet. usedrange. clearformats clear the active worksheet that uses the range of all formats
Activesheet. usedrange. validation. delete' cancels the validity of the data in the range used by the active worksheet.
Activesheet. hyperlinks. delete' delete an active worksheet hyperlink
Activesheet. drawingobjects. delete' deletes all objects in the range used in the active worksheet.
Activesheet. usedrange = activesheet. usedrange. Value 'cancel the formula for the range used in the activity worksheet and keep the value
Also:
Sub X ()
Dim myrange as string
Myrange = activesheet. usedrange. Address 'removes the rows and columns without data from the active worksheet.
End sub
Activeworkbook. fullname 'current window file name and Path
Application. altstartuppath = "E: \ My \ mystart" 'backup startup directory path
Application. autorecover. path' return/set the full path of the temporary file stored in Excel "auto recovery"
Application. defaultfilepath 'option> default working directory in general
Application. Evaluate ("= Info (" "directory" ")") 'default working directory
Application. librarypath 'Return the path of the library folder
Application. networktemplatespath 'returns the network path for saving the template.
Application. path' returns the complete application path.
Application. recentfiles. Item (1). path' returns the path of a recently used file. Item (1) = the first file.
Application. startuppath 'excel Startup Folder path
Application. templatespath 'returns the local path stored in the template
Application. userlibrarypath ': return the installation path of the COM macro loaded on the user's computer.
Debug. Print application. pathseparator 'path separator "\"
Curdir 'default working directory
Excel. Parent. defaultfilepath 'default working directory
Thisworkbook. Path 'returns the path of the current workbook
Dim mm (2, 10)
Range ("A1: B10") = mm' can assign a two-dimensional array to range.
Application. dialogs (xldialogsaveas). Show show save dialog box
[Size = 1] sub x ()
Dim myrange as string
Myrange = activesheet. usedrange. Address 'removes the rows and columns without data from the active worksheet.
End sub
This is equivalent to assigning a new region to a variable. The effect is equivalent to manually deleting unnecessary columns or rows and saving them immediately;
To a function
Private sub worksheet_selectionchange (byval target as range)
'Reverse display of active cell text in the right cell
If activecell. Column 100 ", Operator: = xland ,_
Criteria2: = "<200"
Windows (mybo). worksheets (SHE). Range ("A1: k5000"). Copy _
Destination: = Windows (mybook). worksheets ("acfmis"). Range ("A1 ")