VBA Excel programming three-day discussion (2)

VBA Excel programming three-day discussion (1)

VBA Excel programming three-day discussion (2)

VBA Excel programming three-day discussion (3)

Basic table operations

Due to the wide range of table operations, this article mainly uses examples to demonstrate some common operations.

The following example shows howTraverse worksheet, select worksheet, add worksheet, and delete Worksheet.

Sub test8 () <br/> dim sh as worksheet <br/> for each SH in activeworkbook <br/> If sh. name = "sheet1" then <br/> Sh. range ("A1 "). value = "Haha" <br/> end if <br/> loop <br/> sheets ("sheet1 "). select <br/> activesheet. range ("A1 "). value = "hello" <br/> 'Add a new sheet before the current active sheet <br/> activeworkbook. worksheets. add activesheet <br/> 'new sheet will become an active sheet and give it a name <br/> activesheet. name = "New sheet1" <br/> activeworkbook. worksheets. add activesheet <br/> activesheet. name = "New sheet2" <br/> 'because a warning message is displayed when you delete a sheet, the application is forbidden. <br/>. displayalerts = false <br/> activeworkbook. worksheets (2 ). delete <br/> 'Restore warning message <br/> application. displayalerts = true <br/> end sub

The following example demonstratesCopy, paste, and delete rows and columnsAnd other operations

Sub test10 () <br/> range ("A1 "). copy range ("A2") <br/> range ("A1 "). copy range ("A2: A10") <br/> range ("B1: d1 "). copy range ("B2: D10") <br/> activeworkbook. sheets ("sheet1 "). range ("A1: E1 "). copy sheets ("sheet2 "). range ("A1: E1") <br/> sheets ("sheet1 "). cells (1, 1 ). copy sheets ("sheet1 "). cells (11, 11) <br/> 'You can reference a range as follows <br/> [A1: E1]. copy [g1: k1] <br/> 'You can use a cell to obtain its row and perform a row operation. <br/> [a1]. entirerow. copy [A11]. entirerow <br/> 'column operation <br/> [a1]. entirecolumn. copy [F1]. entirecolumn <br/> [a2]. entirerow. delete <br/> [G1]. entirecolumn. delete <br/> [A1: A10]. cut [A20] <br/> end sub

The following example shows howObtains the maximum number of columns used by a row, the maximum number of rows used by a column, the maximum number of rows in the entire sheet, and the number of columns.:

 Sub test11 () <br/> 'maximum number of rows used in column A <br/> msgbox [a65536]. end (xlup ). row <br/> 'maximum number of rows used in column B <br/> msgbox [b65536]. end (xlup ). row <br/> 'maximum Number of columns used in the first row <br/> msgbox [iv1]. end (xltoleft ). column <br/> 'maximum Number of columns used in the second row <br/> msgbox [iv2]. end (xltoleft ). column <br/> 'maximum Number of sheet rows <br/> msgbox activesheet. usedrange. rows. count <br/> 'maximum Number of columns used by the entire sheet <br/> msgbox activesheet. usedrange. columns. count <br/> end sub

The end attribute is applied to the range object, And a range object is also returned. This object represents the cell at the end of the region that contains the source region. If you still don't understand it, please give it a try in the work table and press Ctrl + top, bottom, left and right arrow keys to see what you get? The cell returned by the end attribute is equivalent to the cell obtained by pressing CTRL + on the source cell (or down, left, right.

Xltoleft: Move to the left, which is equivalent to pressing CTRL + left arrow key in the source area.

Xltoright: Move to the right, which is equivalent to pressing CTRL + right arrow key in the source area.

Xlup: Move up, equivalent to pressing CTRL + on the source area.

Xldown: Move down, equivalent to pressing CTRL + down in the source area.

There are the following Excel files. Copy the student information to the corresponding class according to the class..

Sub classify () <br/> dim maxrow1 &, maxrow2 &, I & <br/> dim CLS as string <br/> dim sh as worksheet <br/> starttime = timer <br/> maxrow1 = [a65536]. end (xlup ). row <br/> for I = maxrow1 to 2 step-1 <br/> 'msgbox sheets ("Students "). name <br/> CLS = sheets ("Students "). cells (I, 4 ). value <br/> msgbox CLS <br/> maxrow2 = sheets (CLS ). [a65536]. end (xlup ). row + 1 <br/> sheets ("Students "). cells (I, 3 ). entirerow. copy sheets (CLS ). cells (maxrow2, 1 ). entirerow <br/> next <br/> msgbox "Total time used:" & timer-starttime & "second (s)" <br/> end sub <br/>

Event:Workbook, worksheet, and the user-defined forms mentioned later define many events. We can add RESPONSE event processing functions for them. for example, double-click thisworkbook on the left, there are two dropdowns in the upper part of the Right, and select workbook on the left, the dropdown on the right will display the corresponding event list, click any one to add the event processing function. you can also double-click a sheet or user-defined form to add a response event handler for them.

Custom form: You can customize a form to respond to your input and output. For example, add a userform:

Take the Excel file of the student information classification just now as an example. The following form is designed. When you click Save, verify that the data entered by the user is correct and insert the data into the sheet. the textbox and drop-down menus are named s_name, s_age, and s_class respectively:

Because the student class is a drop-down menu, you also need to add options for the drop-down menu. Double-click the blank position of userform1 and add the initialize event handler function for userform:

Private sub userform_initialize () <br/> s_class.additem ("Class 3") <br/> s_class.additem ("Class 3") <br/> s_class.additem ("Class 3 ") <br/> end sub

Double-click the Save button to add the click response function for the Save button:

Private sub save_click () <br/> dim maxrow & <br/> dim ID & <br/> If s_name.value = "" Then <br/> msgbox "name is required. "<br/> exit sub <br/> end if <br/> If s_age.value =" "Then <br/> msgbox" Age is required. "<br/> exit sub <br/> end if <br/> If isnumeric (s_age.value) = false then <br/> msgbox" Age shocould be a number. "<br/> exit sub <br/> end if <br/> maxrow = activeworkbook. worksheets ("Students "). [a65536]. end (xlup ). row + 1 <br/> If isnumeric (activeworkbook. worksheets ("Students "). cells (maxrow-1, 1 ). value) = false then <br/> id = 1 <br/> else <br/> id = activeworkbook. worksheets ("Students "). cells (maxrow-1, 1 ). value + 1 <br/> end if <br/> activeworkbook. worksheets ("Students "). cells (maxrow, 1) = ID <br/> activeworkbook. worksheets ("Students "). cells (maxrow, 2) = s_name.value <br/> activeworkbook. worksheets ("Students "). cells (maxrow, 3) = s_age.value <br/> activeworkbook. worksheets ("Students "). cells (maxrow, 4) = s_class.value <br/> end sub

You can use userform1.show to display this form. You can also press F5 to preview. Double-click thisworkbook and add the open Event Response Function on the right side:

Private sub workbook_open () <br/> userform1.show <br/> end sub <br/>

Close the Excel file and restart it to test.

This file can be downloaded here: students.zip

 

OK. So far, you are familiar with some common Excel operations.