vba excel編程三日談(1)
vba excel編程三日談(2)
vba excel編程三日談(3)
表格基本操作
由于表格操作內(nèi)容繁多, 本文將以例子為主演示一些常用的操作.
下面的例子將演示怎么遍歷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
- '恢復(fù)警告消息
- Application.DisplayAlerts = True
- End Sub
下面例子將演示單元格復(fù)制,粘貼,行列刪除等操作
- 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]
- '可以通過單元格獲取它所在的行然后執(zhí)行行操作
- [A1].EntireRow.Copy [A11].EntireRow
- '列操作
- [A1].EntireColumn.Copy [F1].EntireColumn
- [A2].EntireRow.Delete
- [g1].EntireColumn.Delete
- [a1:a10].Cut [a20]
- End Sub
下面的例子演示如何獲得某行最大使用列數(shù), 某列最大使用行數(shù), 整個sheet的最大行數(shù),列數(shù):
- Sub test11()
- '第A列最大使用行數(shù)
- MsgBox [a65536].End(xlUp).Row
- '第B列最大使用行數(shù)
- MsgBox [b65536].End(xlUp).Row
- '第一行最大使用列數(shù)
- MsgBox [iv1].End(xlToLeft).Column
- '第二行最大使用列數(shù)
- MsgBox [iv2].End(xlToLeft).Column
- '整個sheet最大使用行數(shù)
- MsgBox ActiveSheet.UsedRange.Rows.Count
- '整個sheet最大使用列數(shù)
- MsgBox ActiveSheet.UsedRange.Columns.Count
- End Sub
End屬性應(yīng)用于Range對象，同樣也返回一個Range對象。該對象代表包含源區(qū)域的區(qū)域結(jié)尾處的單元格。如果你還是不明白，那請你在工作表里試一試，分別按Ctrl+上、下、左、右方向鍵，看看得到的是什么？End屬性返回的單元格就相當于在源單元格按住Ctrl鍵+上（或下、左、右）方向鍵所得到的單元格。
xlToLeft ：向左移動，相當于在源區(qū)域按Ctrl+左方向鍵。
xlToRight：向右移動，相當于在源區(qū)域按Ctrl+右方向鍵。
xlUp：向上移動，相當于在源區(qū)域按Ctrl+上方向鍵。
xlDown：向下移動，相當于在源區(qū)域按Ctrl+下方向鍵。
有如下excel文件, 請按班級把學(xué)生信息copy到相應(yīng)的班級.
- 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等都定義了很多事件, 我們可以為它們添加響應(yīng)的事件處理函數(shù). 比如雙擊左邊的ThisWorkBook, 則在右邊的上部有兩個dropdown,左邊選中WorkBook, 則右邊的dropdown就會顯示相應(yīng)的事件列表,單擊任意一個即可添加事件處理函數(shù). 同樣雙擊一個sheet或用戶自定義form也可以為它們添加響應(yīng)的事件處理函數(shù).
自定義Form:用戶可以自定義表單來響應(yīng)用戶的輸入輸出.如下圖示添加一個userForm:
以剛才的學(xué)生信息分類的excel為例,我們設(shè)計出如下的form, 當用戶點擊save的時候,驗證用戶輸入的數(shù)據(jù)是否正確, 然后把數(shù)據(jù)插入到sheet中. 其中兩個textbox和下拉菜單分別命名為s_name, s_age, s_class:
因為學(xué)生班級為一個下拉菜單, 所以還要為下拉菜單添加選項, 雙擊userForm1的空白位置,為UserForm添加initialize事件處理函數(shù):
- Private Sub UserForm_Initialize()
- s_class.AddItem ("三一班")
- s_class.AddItem ("三二班")
- s_class.AddItem ("三三班")
- End Sub
雙擊Save按鈕, 為save按鈕添加click響應(yīng)函數(shù):
- 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.Value
- End Sub
通過UserForm1.show 即可顯示這個form. 也可以按F5預(yù)覽. 雙擊ThisWorkbook, 在右邊添加open事件響應(yīng)函數(shù):
- Private Sub Workbook_Open()
- UserForm1.Show
- End Sub
現(xiàn)在關(guān)閉excel文件,再重新開啟,即可測試.
該文件可在此處下載:students.zip
OK, 到此為止, 已經(jīng)熟悉了excel一些常用的操作.
