The VBA basics are coming to an end in everyone's attention. There are many classic VBA operations that we just briefly introduce, to use VBA skillfully, You need to digest and absorb it slowly, and then summarize and improve it in practice. Finally, we collected common VBA code to attract a large number of readers and hope to provide some inspiration to everyone.
Application (EXCEL program)
Application. enableevents = true/false' enable/disable all events
Application. displayalerts = true/false' display/Close warning box prompt box
Application. screenupdating = true/false' display/Close screen refresh
Application. statusbar = "" 'displays text in the address bar, and uses the caption attribute in the title bar.
Application. cursor = xlibeam ': Set the cursor shape to I-shaped, xlwait to Hourglass (wait), and xlnormal to normal.
Application. windowstate = xlminimized: the window is minimized, xlmaximized is maximized, and xlnormal is normal.
Application. activatemicrosoftapp xlmicrosoftword' enable the word Application
Application. templatespath 'obtain the location of the workbook Template
Application. calculatefull re-calculate data in all opened workbooks
Application. recentfiles. Maximum = 2' set the number of recently used document lists to 2
Application. recentfiles (3). open' open the 3rd most recently opened documents
Application. autocorrect. addreplacement "sweek", "software report" 'automatically correct the entered "sweek" to "software report"
Application. dialogs (xldialouplint). Show 'displays the document printing dialog box.
Application. ontime now + timevalue ("00:00:45"), "process" '45 minutes later, execute the specified process
Application. ontime timevalue ("14:00:00"), "process" 'Two o'clock P.M. execute the specified process
Application. ontime earliesttime: = timevalue ("14:00:00 "),_
Procedure: = "process", schedule: = false' cancel the execution of the process at the specified time
Workbook/Worksheet
Activeworkbook. Sheets. Count 'to obtain the number of worksheets in the active workbook.
Activeworkbook. linksources (xlexcellinks) (1) 'returns the first link in the current workbook.
Thisworkbook. worksheets ("sheet2"). Visible = xlsheethidden "hides a worksheet, just like executing the" format-worksheet-hide "operation in the Excel menu
Thisworkbook. worksheets ("sheet2"). Visible = xlsheetveryhidden 'hide the worksheet. You cannot re-display the worksheet by executing "format-worksheet-unhide" in the Excel menu.
Thisworkbook. worksheets ("sheet2"). Visible = xlsheetvisible 'Show the hidden Worksheet
Thisworkbook. Sheets (1). protectcontents 'check whether the worksheet is protected
Activesheet. Columns ("B"). Cut
Activesheet. Columns ("F"). Insert 'Move the data in column B to column F in the preceding two statements, and move the data in Column C to the left.
Activesheet. Range ("A: A"). entirecolumn. autofit 'automatically adjusts the column width of column A of the current worksheet.
Activesheet. cells. specialcells (xlcelltypeconstants, xltextvalues) 'select constants and text cells in the current Worksheet
Activesheet. cells. specialcells (xlcelltypeconstants, xlerrors + xltextvalues) 'select cells with constants, text, and error values in the current Worksheet
Activesheet. usedrange. Rows. Count 'number of rows used in the current Worksheet
Thisworkbook. worksheets. Add thisworkbook. worksheets (3), 2' Add two new worksheets before the first Worksheet
Activesheet. Move after: = activeworkbook ._
Sheets (activeworkbook. Sheets. Count) 'Move the current worksheet to the end of the worksheet
Worksheets (Array ("sheet1", "sheet2"). Select both worksheet sheet1 and sheet2
Activesheet. usedrange. formatconditions. delete' Delete the condition format applied to the current Worksheet
Cells. hyperlinks. delete' unlinks all cells in the current Worksheet
Activesheet. pagesetup. rightfooter = activeworkbook. fullname: the file path is displayed in the footer.
Activesheet. printpreview enablechanges: = false disable the "set" and "margin" Buttons displayed in the "print preview" Window of Excel.
Cell/cell area
Activesheet. usedrange. row' gets the row number of the first row of the used cell area
Range ("a65536"). End (xlup). row' returns the last row of column A (that is, the total number of records)
Cell. Range ("A1"). hasformula' check whether the first cell in the cell or cell area contains a formula or whether the cell in the cell. hasformula' worksheet contains a formula
Target. entirecolumn. Select select the entire column where the cell is located. Target. entirerow. Select is the entire row where the selected cell is located.
The row number of the activecell. row' active cell. activecell. column indicates the number of columns of the active cell.
Activewindow. scrollrow = 2' scroll down the current worksheet window to 2nd rows
Activewindow. scrollcolumn = 5' scroll the current worksheet window to column 5th
Worksheets ("sheet1"). Range ("A1: C5"). copypicture xlscreen, xlbitmap 'Copy the content of the specified cell area to a screen snapshot
Selection. hyperlinks. delete' delete all links in the selected region
Activesheet. cells (1, 1 ). font. bold = true 'bold property is used to set whether the font is bold, the size property sets the font size, and the colorindex Property sets the font color (whose value is the color index number ), italic attribute sets whether the font is skewed, and name sets the font name.
Activesheet. cells (1, 1). Interior. colorindex = 3' set the cell background color to red.
Isempty (activecell. Value) 'determines whether there is a value in the active cell
Activecell. value = ucase (activecell. Value) 'converts the characters in the current cell to uppercase.
Activecell. value = strconv (activecell. Value, vblowercase) 'converts the string in the active cell to lowercase.
Activecell. currentregion. Select 'select the continuous non-empty area where the current active cell is located. You can also use range (activecell, usedrange. End (xldown). Select
Activecell. offset (). Select 'Move the active cell down one row
Range ("B2"). offset (columnoffset: = 1) or range ("B2"). offset (, 1) 'reads data from the right cell of the specified Cell
Range ("B2"). offset (rowoffset: =-1) or range ("B2"). offset (-1) 'reads data from the row of cells in the specified cell.
Range ("A1"). Copy range ("B1") 'Copy the content in cell A1 to B1.
Range ("A1: D8"). Copy range ("H1") 'copies the specified cell area to the area starting with H1. You can use the cut method to perform the cut operation.
Activewindow. rangeselection. value = "software report" 'enter the specified string in the selected cell area.
Forms (controls)
Option explicit forces the declaration of all variables in the module
Userform1.show 'displays the user form
Load userform1 'loads a user form, but the form is hidden.
Userform1.hide 'hide user forms
Unload userform1 or unload me 'unmount the user form
Me. Height = int (0.5 * activewindow. Height) 'the height of the form is half of the height of the current active window. The width is set to activewindow. width.
Userform1.combox1. additem sheets ("sheet1"). cells (1, 1) 'add data in the specified cell to the compound box
Listbox1.list = myproduct () 'Add the value of array myproduct to listbox1 in the list box
Listbox1.rowsource = "sheet1! Isum "'adds the value of the isum area named in Sheet 1 to the list box.
Listbox1.selected (0) 'select the specified entry in the list box
Listbox1.removeitem listbox1.listindex' remove the entries in the list box.
If msgbox ("do you want to exit ?", Vbyesno) <> If the return value of vbyes then exit sub is not "yes", exit
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.
Msgbox "This is the first line." & vbnewline & "second line." 'forces line feed in the message box, And vbcrlf can also be used to replace vbnewline.
Msgbox "average value:" & format (application. worksheetfunction. average (selection), "#,## 0.00"), vbinformation, "display selected area average" 'use the worksheet function to return the average value of the selected area and follow the specified display format
Formulas and functions
Application. worksheetfunction. isnumber ("A1") 'checks whether the data in the specified cell is a number.
Range ("A: A"). Find (application. worksheetfunction. Max (range ("A: A"). Activate
'Activate the maximum cell in column A of the cell area
Application. macrooptions macro: = "getsum", category: = 4' specify the custom getsum function to the "statistical function" category in Excel.
Application. macrooptions macro: = "getsum ",_
Description: = "sum first, and then output ." 'Function description for the custom function getsum
Application. worksheetfunction. counta (cell. entirecolumn) 'returns the number of non-empty cells in the column where the cell is located. The row uses the entirerow attribute.
Application. worksheetfunction. counta (cells) 'returns the number of non-empty cells in the current worksheet.
Charts
Activesheet. chartobjects. delete' Delete All chartobject objects in the worksheet
Activeworkbook. charts. delete all the chart worksheets in the current workbook activesheet. chartobjects. Count to get the number of charts in the current Worksheet
Worksheets ("sheet1"). chartobjects (1). Chart ._
Export filename: = "C:/mychart.gif", filtername: = "GIF" 'refers to map 1 in the work table to drive C and name it mychart.gif.
The above code has passed our tests. You can use it with confidence and summarize and refine it on this basis. If you have some experience using VBA, write it down and share it with everyone. {
Function onclick ()
{
Diggit (845212,21145, 1)
}
} "> 0 {
Function onclick ()
{
Diggit (845212,21145, 2)
}
} "> 0
0
(Please comment on the article)