Go to: Typical Excel VBA code

 

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)