vba excel编程三日谈(2)

vba excel编程三日谈(1)

vba excel编程三日谈(2)

vba excel编程三日谈(3)

表格基本操作

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

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

Sub test8() Dim sh As Worksheet For Each sh In ActiveWorkbook If sh.Name = “Sheet1” Then sh.Range(“A1”).Value = “Haha” End If Loop Sheets(“Sheet1”).Select ActiveSheet.Range(“A1”).Value = “Hello” ‘在当前活动sheet前添加一个新sheet ActiveWorkbook.Worksheets.Add ActiveSheet ‘新sheet会成为活动sheet,给它一个名字 ActiveSheet.Name = “New sheet1” ActiveWorkbook.Worksheets.Add ActiveSheet ActiveSheet.Name = “New sheet2” ‘因为删除sheet会弹出警告消息,所以先禁止警告消息 Application.DisplayAlerts = False ActiveWorkbook.Worksheets(2).Delete ‘恢复警告消息 Application.DisplayAlerts = TrueEnd Sub

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

Sub test10() Range(“A1”).Copy Range(“A2”) Range(“A1”).Copy Range(“A2:A10”) Range(“B1:D1”).Copy Range(“B2:D10”) ActiveWorkbook.Sheets(“Sheet1”).Range(“A1:E1”).Copy Sheets(“Sheet2”).Range(“A1:E1”) Sheets(“Sheet1”).Cells(1, 1).Copy Sheets(“Sheet1”).Cells(11, 11) ‘可以这样引用一个range [a1:e1].Copy [g1:k1] ‘可以通过单元格获取它所在的行然后执行行操作 [A1].EntireRow.Copy [A11].EntireRow ‘列操作 [A1].EntireColumn.Copy [F1].EntireColumn [A2].EntireRow.Delete [g1].EntireColumn.Delete [a1:a10].Cut [a20]End Sub

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

Sub test11() ‘第A列最大使用行数 MsgBox [a65536].End(xlUp).Row ‘第B列最大使用行数 MsgBox [b65536].End(xlUp).Row ‘第一行最大使用列数 MsgBox [iv1].End(xlToLeft).Column ‘第二行最大使用列数 MsgBox [iv2].End(xlToLeft).Column ‘整个sheet最大使用行数 MsgBox ActiveSheet.UsedRange.Rows.Count ‘整个sheet最大使用列数 MsgBox ActiveSheet.UsedRange.Columns.CountEnd Sub

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

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

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

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

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

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

Sub classify() Dim maxrow1&, maxrow2&, i& Dim cls As String Dim sh As Worksheet starttime = Timer maxrow1 = [a65536].End(xlUp).Row For i = maxrow1 To 2 Step -1 ‘MsgBox Sheets(“students”).Name cls = Sheets(“students”).Cells(i, 4).Value MsgBox cls maxrow2 = Sheets(cls).[a65536].End(xlUp).Row + 1 Sheets(“Students”).Cells(i, 3).EntireRow.Copy Sheets(cls).Cells(maxrow2, 1).EntireRow Next MsgBox “Total time used: ” & Timer – starttime & ” second(s)”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事件处理函数:

Private Sub UserForm_Initialize() s_class.AddItem (“三一班”) s_class.AddItem (“三二班”) s_class.AddItem (“三三班”)End Sub

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

Private Sub save_Click() Dim maxrow& Dim id& If s_name.Value = “” Then MsgBox “Name is required.” Exit Sub End If If s_age.Value = “” Then MsgBox “Age is required.” Exit Sub End If If IsNumeric(s_age.Value) = False Then MsgBox “Age should be a number.” Exit Sub End If maxrow = ActiveWorkbook.Worksheets(“Students”).[a65536].End(xlUp).Row + 1 If IsNumeric(ActiveWorkbook.Worksheets(“Students”).Cells(maxrow – 1, 1).Value) = False Then id = 1 Else id = ActiveWorkbook.Worksheets(“Students”).Cells(maxrow – 1, 1).Value + 1 End If ActiveWorkbook.Worksheets(“Students”).Cells(maxrow, 1) = id ActiveWorkbook.Worksheets(“Students”).Cells(maxrow, 2) = s_name.Value ActiveWorkbook.Worksheets(“Students”).Cells(maxrow, 3) = s_age.Value ActiveWorkbook.Worksheets(“Students”).Cells(maxrow, 4) = s_class.ValueEnd Sub

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

Private Sub Workbook_Open() UserForm1.ShowEnd Sub

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

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

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

环境不会改变，解决之道在于改变自己。