Excel-Application in C #

Microsoft. Office. InterOP. Excel. Application xlapp = new Microsoft. Office. InterOP. Excel. Application ();
Microsoft. Office. InterOP. Excel. Workbook workbook = xlapp. workbooks. Add (true );
Microsoft. Office. InterOP. Excel. worksheet = (Microsoft. Office. InterOP. Excel. worksheet) Workbook. worksheets [1];
Microsoft. Office. InterOP. Excel. Range range;

 

 

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, in the macro dialog box, the option compare text string is not case sensitive. Option base 1 'specifies that the first subscript of the array is 1 (2) on error resume next'. Ignore the error and continue executing VBA.CodeTo 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' inProgramThe warning box is not displayed during execution.

(6) application. screenupdating = false'close screen refresh application. screenupdating = true' open screen refresh

(7) application. Enable. cancelkey = xldisabled 'Disable Ctrl + break to abort the function Workbook of macro operation

(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 current workbook name thisworkbook. fullname, and 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 ': Maximize the worksheet of the current workbook

(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' change the page settings to landscape

(31) activesheet. pagesetup. rightfooter = activeworkbook. fullname: Enter the file path activesheet at the end of the table set on the page. pagesetup. leftfooter = application. username' place the user name in the footer cell/cell area of the activity Worksheet (32) activecell. currentregion. select or range (activecell. end (xlup), activecell. end (xldown )). select select the range included in the current active cell. There are no blank rows between the top, bottom, and left.

(33) cells. Select all cells in the current Worksheet

(34) range ("A1 "). clearcontents 'clear the Content Selection in cell A1 on the activity worksheet. clearcontents 'clears the range ("A1: D4") of the selected region "). 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 into cell B1 range ("A1: D8 "). copy range ("F1") 'copies the cell area to the range ("A1: D8") in the area starting with cell F1 "). cut range ("F1") 'Cut the cell area A1 to D8 and copy it to the area starting with cell F1 ("A1 "). currentregion. copy sheets ("sheet2 "). range ("A1") 'copies 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 and consists of a rectangular cell block, contains 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:, ") 'returns the address of the super-level link in the cell and assigns a value (43) textcolor = range (" A1 "). font. colorindex' checks the text color of cell A1 and returns 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") 'reference cell C5 cells. Item (5, 3)' reference 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 (11,3) '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 the data area of the cell range ("data "). columns. count 'range ("data") of the number of columns in data in the cell area "). 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 name of the drop-down list of the 3rd fields generated by the automatic filter command.

(59) range ("A1: C3"). Name = "Computer" 'name A1: C3 the area is 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 "'rename the area computer to 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 formula and Function

(67) application. worksheetfunction. isnumber ("A1") 'Use the worksheet function to check whether the data in cell A1 is a number.

(68) range ("A: "). find (application. worksheetfunction. max (range ("A: "))). activate 'activate the maximum cell in column A of the cell Region

(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 you must use the r1c1 style expression chart.

(70) activesheet. chartobjects. Count 'to obtain the number of charts in the current Worksheet

(71) activesheet. chartobjects ("chart1"). Select 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 of the chart 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 'specifies 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 the C drive 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) 'form height is 0.88 me of the current active window height. width = int (0.88 * activewindow. width) 'form width is the 0.88 event of the current active window height

(92) application. enableevents = false'disable all events application. enableevents = true' enable all events note: Not Applicable to event objects triggered by user Form Controls

(93) set excelsheet = Createobject ("Excel. sheet ") 'create an Excel worksheet object excelsheet. application. visible = true' sets the application object to make Excel visible. application. cells (1, 1 ). value = "data" 'enter the text excelsheet In the first unit of the table. saveas "C: \ test. xls "'Save the table to the C: \ test.xls directory excelsheet. application. quit' close Excel Set excelsheet = nothing 'to release the object variable

(94) 'declare and create an Excel object to 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 the reference call mysub (Createobject ("Excel. application") of an Excel. Application object "))

(96) set D = Createobject (scripting. dictionary) 'create a dictionary object variable

(97) D. Add "A", "Athens" 'add keywords and other entries for object variables

(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 'Whenever any cell in the worksheet is recalculated, this function application is forcibly calculated. volatile false' the function is recalculated only when one or more parameters of the function change.