vba excel编程三日谈(2)



vba excel编程三日谈(1)

vba excel编程三日谈(2)

vba excel编程三日谈(3)

表格基本操作

由于表格操作内容繁多, 本文将以例子为主演示一些常用的操作.

下面的例子将演示怎么遍历worksheet,选中worksheet,添加worksheet,删除worksheet.

[vb] view plain copy

1. Sub test8()  
2.     Dim sh As Worksheet  
3.     For Each sh In ActiveWorkbook  
4.         If sh.Name = "Sheet1" Then  
5.             sh.Range("A1").Value = "Haha"  
6.         End If  
7.     Loop  
8.     Sheets("Sheet1").Select  
9.     ActiveSheet.Range("A1").Value = "Hello"  
10.     '在当前活动sheet前添加一个新sheet  
11.     ActiveWorkbook.Worksheets.Add ActiveSheet  
12.     '新sheet会成为活动sheet,给它一个名字  
13.     ActiveSheet.Name = "New sheet1"  
14.     ActiveWorkbook.Worksheets.Add ActiveSheet  
15.     ActiveSheet.Name = "New sheet2"  
16.     '因为删除sheet会弹出警告消息,所以先禁止警告消息  
17.     Application.DisplayAlerts = False  
18.     ActiveWorkbook.Worksheets(2).Delete  
19.     '恢复警告消息  
20.     Application.DisplayAlerts = True  
21. End Sub  

  

下面例子将演示单元格复制,粘贴,行列删除等操作

[vb] view plain copy

1. Sub test10()  
2.     Range("A1").Copy Range("A2")  
3.     Range("A1").Copy Range("A2:A10")  
4.     Range("B1:D1").Copy Range("B2:D10")  
5.     ActiveWorkbook.Sheets("Sheet1").Range("A1:E1").Copy Sheets("Sheet2").Range("A1:E1")  
6.     Sheets("Sheet1").Cells(1, 1).Copy Sheets("Sheet1").Cells(11, 11)  
7.     '可以这样引用一个range  
8.     [a1:e1].Copy [g1:k1]  
9.     '可以通过单元格获取它所在的行然后执行行操作  
10.     [A1].EntireRow.Copy [A11].EntireRow  
11.     '列操作  
12.     [A1].EntireColumn.Copy [F1].EntireColumn  
13.     [A2].EntireRow.Delete  
14.     [g1].EntireColumn.Delete  
15.     [a1:a10].Cut [a20]  
16. End Sub  

下面的例子演示如何获得某行最大使用列数, 某列最大使用行数, 整个sheet的最大行数,列数:

 

[vb] view plain copy

1. Sub test11()  
2.     '第A列最大使用行数  
3.     MsgBox [a65536].End(xlUp).Row  
4.     '第B列最大使用行数  
5.     MsgBox [b65536].End(xlUp).Row  
6.     '第一行最大使用列数  
7.     MsgBox [iv1].End(xlToLeft).Column  
8.     '第二行最大使用列数  
9.     MsgBox [iv2].End(xlToLeft).Column  
10.     '整个sheet最大使用行数  
11.     MsgBox ActiveSheet.UsedRange.Rows.Count  
12.     '整个sheet最大使用列数  
13.     MsgBox ActiveSheet.UsedRange.Columns.Count  
14. End Sub  

End属性应用于Range对象，同样也返回一个Range对象。该对象代表包含源区域的区域结尾处的单元格。如果你还是不明白，那请你在工作表里试一试，分别按Ctrl+上、下、左、右方向键，看看得到的是什么？End属性返回的单元格就相当于在源单元格按住Ctrl键+上（或下、左、右）方向键所得到的单元格。

xlToLeft ：向左移动，相当于在源区域按Ctrl+左方向键。

xlToRight：向右移动，相当于在源区域按Ctrl+右方向键。

xlUp：向上移动，相当于在源区域按Ctrl+上方向键。

xlDown：向下移动，相当于在源区域按Ctrl+下方向键。

有如下excel文件, 请按班级把学生信息copy到相应的班级.

[vb] view plain copy

1. Sub classify()  
2.     Dim maxrow1&, maxrow2&, i&  
3.     Dim cls As String  
4.     Dim sh As Worksheet  
5.     starttime = Timer  
6.     maxrow1 = [a65536].End(xlUp).Row  
7.     For i = maxrow1 To 2 Step -1  
8.         'MsgBox Sheets("students").Name  
9.         cls = Sheets("students").Cells(i, 4).Value  
10.         MsgBox cls  
11.         maxrow2 = Sheets(cls).[a65536].End(xlUp).Row + 1  
12.         Sheets("Students").Cells(i, 3).EntireRow.Copy Sheets(cls).Cells(maxrow2, 1).EntireRow  
13.     Next  
14.     MsgBox "Total time used: " & Timer - starttime & " second(s)"  
15. End Sub  

事件: workBook, worksheet, 以及后面要讲的用户自定义form等都定义了很多事件, 我们可以为它们添加响应的事件处理函数. 比如双击左边的ThisWorkBook, 则在右边的上部有两个dropdown,左边选中WorkBook, 则右边的dropdown就会显示相应的事件列表,单击任意一个即可添加事件处理函数. 同样双击一个sheet或用户自定义form也可以为它们添加响应的事件处理函数.

自定义Form:用户可以自定义表单来响应用户的输入输出.如下图示添加一个userForm:

以刚才的学生信息分类的excel为例,我们设计出如下的form, 当用户点击save的时候,验证用户输入的数据是否正确, 然后把数据插入到sheet中. 其中两个textbox和下拉菜单分别命名为s_name, s_age, s_class:

因为学生班级为一个下拉菜单, 所以还要为下拉菜单添加选项, 双击userForm1的空白位置,为UserForm添加initialize事件处理函数:

[vb] view plain copy

1. Private Sub UserForm_Initialize()  
2.     s_class.AddItem ("三一班")  
3.     s_class.AddItem ("三二班")  
4.     s_class.AddItem ("三三班")  
5. End Sub  

  

双击Save按钮, 为save按钮添加click响应函数:

[vb] view plain copy

1. Private Sub save_Click()  
2.     Dim maxrow&  
3.     Dim id&  
4.     If s_name.Value = "" Then  
5.         MsgBox "Name is required."  
6.         Exit Sub  
7.     End If  
8.     If s_age.Value = "" Then  
9.         MsgBox "Age is required."  
10.         Exit Sub  
11.     End If  
12.     If IsNumeric(s_age.Value) = False Then  
13.         MsgBox "Age should be a number."  
14.         Exit Sub  
15.     End If  
16.     maxrow = ActiveWorkbook.Worksheets("Students").[a65536].End(xlUp).Row + 1  
17.     If IsNumeric(ActiveWorkbook.Worksheets("Students").Cells(maxrow - 1, 1).Value) = False Then  
18.         id = 1  
19.     Else  
20.         id = ActiveWorkbook.Worksheets("Students").Cells(maxrow - 1, 1).Value + 1  
21.     End If  
22.     ActiveWorkbook.Worksheets("Students").Cells(maxrow, 1) = id  
23.     ActiveWorkbook.Worksheets("Students").Cells(maxrow, 2) = s_name.Value  
24.     ActiveWorkbook.Worksheets("Students").Cells(maxrow, 3) = s_age.Value  
25.     ActiveWorkbook.Worksheets("Students").Cells(maxrow, 4) = s_class.Value  
26. End Sub  

通过UserForm1.show 即可显示这个form. 也可以按F5预览. 双击ThisWorkbook, 在右边添加open事件响应函数:

[vb] view plain copy

1. Private Sub Workbook_Open()  
2.     UserForm1.Show  
3. End Sub  

现在关闭excel文件,再重新开启,即可测试.

该文件可在此处下载:students.zip

 

OK, 到此为止, 已经熟悉了excel一些常用的操作.