課題-Excel-VBA-編程教程excel高級教程

1、課題-Excel-VBA-編程教程（完 整版）excel高級教程Excel_VBA_編程教程(完整版)excel高級教程VBA - Excel編程概念之：【單元格和區(qū)域】一、如何引用單元格和區(qū)域使用Visual Basic 的普通任務(wù)是指定單元格或單元格區(qū)域，然后對該單元格或 單元格區(qū)域進(jìn)行一些操作，如輸入公式或更改格式。通常用一條語句就能完成 操作，該語句可標(biāo)識單元格，還可更改某個屬性或應(yīng)用某個方法。在 Visual Basic 中，Range 對象既可表示單個單元格，也可表示單元格區(qū)域。 下列主題說明了標(biāo)識和處理 Range對象最常用的方法。用A1樣式記號引用單元格和單元格區(qū)域 可使用Ra

2、nge屬性來引用A1引用樣式中的單元格或單元格區(qū)域。下述子程 序?qū)卧駞^(qū)域A1:D5的字體設(shè)置為加粗。Sub FormatRa nge()Workbooks("Book1").Sheets("Sheet1").Ra nge("A1:D5") _ .Fon t.Bold = TrueEnd SubRange屬性的一些 A1樣式引用。下表演示了使用引用Ran ge("A1")Ran ge("A1:B5")Ran ge("C5:D9,G9:H16")Ran ge("A:

3、A")Ran ge("1:1")Ran ge("A:C")Ran ge("1:5")Ran ge("1:1,3:3,8:8")Ran ge("A:A,C:C,F:F")含義單元格A1從單元格A1到單元格B5的區(qū)域多塊選定區(qū)域A列第一行從A列到C列的區(qū)域從第一行到第五行的區(qū)域第1、3和8行A、C和F列用編號引用單元格通過使用行列編號，可用Cells屬性來引用單個單元格。該屬性返回代表單個單元格的 Range對象。下例中，Cells(6,1)返回Sheet1上的單元格 A6， 然后將Val

4、ue屬性設(shè)置為10。Sub En terValue()Worksheets("Sheet1").Cells(6, 1).Value = 10End Sub因為可用變量替代編號，所以Cells屬性非常適合于在單元格區(qū)域中循環(huán)，如 下例所示。Sub CycleThrough()Dim Counter As IntegerFor Counter = 1 To 20Worksheets(”Sheet1")Cells(Counter, 3).Value = CounterNext CounterEnd Sub注意 如果要同時更改某一單元格區(qū)域中所有單元格的屬性或?qū)ζ鋺?yīng)用方法

5、， 可使用Range屬性。有關(guān)詳細(xì)信息，請參閱用A1樣式記號引用單元格。引用行和列 可用Rows屬性或Columns屬性來處理整行或整列。這兩個屬性返回代表單 元格區(qū)域的Range對象。下例中，用Rows(1)返回Sheetl上的第一行， 然后將單元格區(qū)域的Font對象的Bold屬性設(shè)置為True。Sub RowBold()Worksheets(MSheet1 n).Rows(1 ).Font.Bold = TrueEnd Sub下表舉例說明了使用Rows和Columns屬性的一些行和列的引用。引用含義Rows(1) Rows第一行工作表上所有的行Columns(1| Columns(” A”

6、) Columns第一列 第一列工作表上所有的列若要同時處理若干行或列，請創(chuàng)建一個對象變量并使用Union方法，將對 Rows屬性或Columns屬性的多個調(diào)用組合起來。下例將活動工作簿中第一 張工作表上的第一行.第三行和第五行的字體設(shè)置為加粗。Sub SeveralRows()Worksheets(MSheet1.ActivateDim myUnion As RangeSet myUnion = Union(Rows(1), Rows(3), Rows(5) myUnion.Font.Bold = TrueEnd Sub用快捷記號引用單元格 可用方括號將A1引用樣式或命名區(qū)域括起來，作為Ra

7、nge屬性的快捷方式。 這樣就不必鍵入單詞“ Ran ge或使用引號，如下例所示。Sub ClearRa nge()Worksheets("Sheet1").A1:B5.CIearCo nte ntsEnd SubSub SetValue()MyRa nge.Value = 30End Sub引用命名區(qū)域用名稱比用A1樣式記號更容易標(biāo)識單元格區(qū)域。若要命名選定的單元格區(qū)域， 請單擊編輯欄左端的名稱框，鍵入名稱，再按Enter。引用命名區(qū)域下例引用了名為“MyBook.xls ”的工作簿中的名為“MyRange的單元格區(qū)域。Sub FormatRa nge()Ran ge(&

8、quot;MyBook.xls!MyRa nge").Fo nt.ltalic = TrueEnd Sub下例引用名為“ Report.xls ”勺工作簿中的特定工作表單元格區(qū)域“Sheet1!Sales ”Sub FormatSales()Ra nge("Report.xlsSheet1!Sales").BorderArou nd Weight:=xlthinEnd Sub若要選定命名區(qū)域，請用 GoTo方法，該方法將激活工作簿和工作表，然后選 定該區(qū)域。Sub ClearRa nge()Applicatio n.Goto Refere nce:="M

9、yBook.xls!MyRa nge"Selecti on. ClearC ontentsEnd Sub下例顯示對于活動工作簿將如何編寫相同的過程。Sub ClearRa nge()Applicatio n. Goto Refere nce:="MyRa nge"Selecti on. ClearC ontentsEnd Sub在命名區(qū)域中的單元格上循環(huán)下例用For Each.Next循環(huán)語句在命名區(qū)域中的每一個單元格上循環(huán)。如果 該區(qū)域中的任一單元格的值超過 limit的值，就將該單元格的顏色更改為黃色Sub ApplyColor()Const Limit As

10、 In teger = 25For Each c In Ra nge("MyRa nge")If c.Value > Limit Thenc.ln terior.ColorI ndex = 27End IfNext cEnd Sub相對于其他單元格來引用單元格處理相對于另一個單元格的某一單元格的常用方法是使用Offset屬性。下例中，將位于活動工作表上活動單元格下一行和右邊三列的單元格的內(nèi)容設(shè)置為 雙下劃線格式。Sub Un derli ne()ActiveCell.Offset(1, 3).Fo nt.U nderli ne = xlDoubleEnd Sub注意

11、可錄制使用Offset屬性(而不是絕對引用)的宏。在 工具”菜單上，指 向宏”再單擊錄制新宏”然后單擊確定”再單擊錄制宏工具欄上的 相對 引用”按鈕。若要在單元格區(qū)域中循環(huán)，請在循環(huán)中將變量與 Cells屬性一起使用。下例以5 為步長，用5到100之間的值填充第三列的前 20個單元格。變量counter 用作Cells屬性的行號。Sub CycleThrough()Dim coun ter As In tegerFor coun ter = 1 To 20Worksheets("Sheet1").Cells(cou nter, 3).Value = cou nter * 5

12、 Next coun terEnd Sub用Range對象引用單元格 如果將對象變量設(shè)置為 Range對象，即可用變量名方便地操作單元格區(qū)域。下述過程創(chuàng)建了對象變量 myRange，然后將活動工作簿中 Sheetl上的單元 格區(qū)域A1:D5賦予該變量。隨后的語句用該變量代替該區(qū)域?qū)ο螅?以修改該區(qū) 域的屬性。Sub Ran dom()Dim myRa nge As RangeSet myRa nge = Worksheets("Sheet1").Ra nge("A1:D5")myRa nge.Formula = "=RAND()"my

13、Ra nge.F on t.Bold = TrueEnd Sub引用工作表上的所有單元格如果對工作表應(yīng)用 Cells屬性時不指定編號，該屬性將返回代表工作表上所有 單元格的Range對象。下述Sub過程清除活動工作簿中Sheet1上的所有單元格的內(nèi)容。Sub ClearSheet()Worksheets("Sheet1").Cells.ClearCo nte ntsEnd Sub引用多個單元格區(qū)域使用適當(dāng)?shù)姆椒梢院苋菀椎赝瑫r引用多個單元格區(qū)域?？捎肦ange和Union方法引用任意組合的單元格區(qū)域；用 Areas屬性可引用工作表上選定 的一組單元格區(qū)域。使用Range屬性

14、通過在兩個或多個引用之間放置逗號，可使用 Range屬性來引用多個單元格 區(qū)域。下例清除了 Sheet1上三個單元格區(qū)域的內(nèi)容。Sub ClearRa nges()Worksheets("Sheet1").Ra nge("C5:D9,G9:H16,B14:D18"). _ClearC ontentsEnd Sub命名區(qū)域使得用 Range屬性處理多個單元格區(qū)域更為容易。下例可在三個命 名區(qū)域處于同一工作表時運(yùn)行。Sub ClearNamed()Ran ge("MyRa nge, YourRa nge, HisRa nge").CIea

15、rCo nte ntsEnd Sub使用Union方法用Union方法可將多個單元格區(qū)域組合到一個Range對象中。下例創(chuàng)建了名為 myMultipleRange 的Range 對象，并將其定義為單元格區(qū)域C3:D4的組合，然后將該組合區(qū)域的字體設(shè)置為加粗。Sub MultipleRa nge()Dim r1, r2, myMultipleRa nge As RangeSet r1 = Sheets("Sheet1").Ra nge("A1:B2")Set r2 = Sheets("Sheet1").Ra nge("C3:D

16、4")Set myMultipleRa nge = Union (r1, r2) myMultipleRa nge.F on t.Bold = TrueEnd Sub使用Areas屬性可用Areas屬性引用選定的單元格區(qū)域或多塊選定區(qū)域中的區(qū)域集合 程計算選定區(qū)域中的塊數(shù)目，如果有多個塊，就顯示一則警告消息。Sub Fi ndMultiple()If Selection.Areas.Count > 1 ThenMsgBox "Ca nnot do this to a multiple selectio n."End IfA1:B2 和下述過End SubVB

17、A - Excel編程概念之：【單元格和區(qū)域】三、選定和激活單元格VBA - Excel編程概念之：【單元格和區(qū)域】二、在單元格區(qū)域中循環(huán)在單元格區(qū)域中循環(huán)使用Visual Basic時，經(jīng)常需要對某一單元格區(qū)域內(nèi)的每個單元格運(yùn)行同一段語句。為達(dá)到這一目的，可組合循環(huán)語句和一個或多個方法來標(biāo)識每個單元格， 一次針對一個單元格，并執(zhí)行該操作。在單元格區(qū)域中循環(huán)的一種方法是將For.Next循環(huán)語句與Cells屬性配合使用。使用Cells屬性時，可用循環(huán)計數(shù)器(或其他變量或表達(dá)式)來替代單 元格索引編號。下例中，變量 cou nter代替了行號。此過程將在單元格區(qū)域 C1:C20中循環(huán)，將所有絕對

18、值小于0.01的數(shù)字都設(shè)置為0 (零)。Sub Rou ndToZero1()For Cou nter = 1 To 20Set curCell = Worksheets("Sheet1").Cells(Counter, 3)If Abs(curCell.Value) < 0.01 Then curCell.Value = 0Next Cou nterEnd Sub在單元格區(qū)域中循環(huán)的另一種簡便方法是使用For Each.Next循環(huán)語句和由Range屬性指定的單元格集合。在每一次循環(huán)過程中，Visual Basic 都為下一 個單元格自動設(shè)置一個對象變量。下述過程在

19、單元格區(qū)域A1:D10中循環(huán)，將所有絕對值小于0.01的數(shù)字都設(shè)置為0 (零)。Sub Rou ndToZero2()For Each c In Worksheets("Sheet1").Ra nge("A1:D10").CellsIf Abs(c.Value) < 0.01 Then c.Value = 0NextEnd Sub如果不知道要循環(huán)的單元格區(qū)域的邊界，可用CurrentRegion屬性返回活動單元格周圍的區(qū)域。例如，下述過程在工作表上運(yùn)行時，將在活動單元格周圍的 區(qū)域內(nèi)循環(huán)，將所有絕對值小于0.01的數(shù)字都設(shè)置為0 (零)。Sub R

20、ou ndToZero3()For Each c In ActiveCell.Curre ntRegio n. CellsIf Abs(c.Value) < 0.01 Then c.Value = 0NextEnd Sub選定和激活單元格使用Microsoft Excel時，通常要先選定單元格或單元格區(qū)域，然后執(zhí)行某一操作，如設(shè)置單元格的格式或在單元格中輸入數(shù)值。但在Visual Basic 中，通常在修改單元格之前不必先選定它們。例如，如果要用 Visual Basic在單元格D6中輸入公式，就不必先選定單元 格D6，而只需返回Range對象，然后將該對象的Formula屬性設(shè)置為所需

21、 的公式，如下例所示。Sub En terFormula()Worksheets("Sheet1").Ra nge("D6").Formula ="二SUM(D2:D5)"End Sub有關(guān)使用其他方法在不選定單元格的情況下對其進(jìn)行控制的示例，請參閱如何 引用單元格和區(qū)域。使用 Select方法和 Selection 屬性Select方法激活工作表和工作表上的對象；而Selection 屬性返回代表活動工 作簿中活動工作表上的當(dāng)前選定區(qū)域的對象。在成功使用Selection屬性之前，必須先激活工作簿，并激活或選定工作表，然后用Sele

22、ct方法選定單元格區(qū)域 (或其他對象)。宏錄制器經(jīng)常創(chuàng)建使用 Select方法和Selection 屬性的宏。下述Sub過程 是用宏錄制器創(chuàng)建的，該過程演示了 Select方法和Selection屬性在一起使 用的方法。Sub Macro1()Sheets("Sheet1").SelectRa nge("A1").SelectActiveCell.FormulaR1C1 = "Name"Ra nge("B1").SelectActiveCell.FormulaR1C1 = "Address"Ra

23、 nge("A1:B1").SelectSelecti on.Fon t.Bold = TrueEnd Sub下例完成同樣的任務(wù)，但不激活或選定工作表或單元格。Sub Labels()With Worksheets("Sheet1").Ran ge("A1") = "Name".Ra nge("B1") = "Address".Ran ge("A1:B1").Fo nt.Bold = TrueEnd WithEnd Sub選定活動工作表上的單元格如果用Se

24、lect方法選定單元格，應(yīng)注意Select方法僅用于活動工作表。如果 從模塊中運(yùn)行Sub過程，必須先在該過程中激活工作表，然后才能用Select方法選定單元格區(qū)域，否則該方法將失敗。例如，下述過程在活動工作簿中將Sheet1中的一行復(fù)制到 Sheet2 上。Sub CopyRow()Worksheets("Sheet1").Rows(1).CopyWorksheets("Sheet2").SelectWorksheets("Sheet2").Rows(1).SelectWorksheets("Sheet2").Pa

25、steEnd Sub激活選定區(qū)域內(nèi)的單元格可用Activate方法激活選定區(qū)域內(nèi)的單元格。即使選定了單元格區(qū)域，也只能 有一個活動單元格。下述過程選定了一個單元格區(qū)域，然后激活該區(qū)域內(nèi)的一 個單元格，但并不改變選定區(qū)域。Sub MakeActive()Worksheets("Sheet1").ActivateRa nge("A1:D4").SelectRa nge("B2").ActivateEnd SubVBA - Excel編程概念之：【單元格和區(qū)域】四、處理三維區(qū)域如果要處理若干工作表上相同位置的單元格區(qū)域，可用Array函數(shù)選

26、定兩張或多張工作表。下例設(shè)置三維單元格區(qū)域的邊框格式。Sub FormatSheets()Sheets(Array("Sheet2", "Sheet3", "Sheet5").SelectRa nge("A1:H1").SelectSelecti on .Borders(xlBottom).L in eStyle = xlDoubleEnd Sub下例應(yīng)用FillAcrossSheets方法，在活動工作簿中，將Sheet2上的單元格區(qū)域的格式和任何數(shù)據(jù)復(fù)制到該工作簿中所有工作表上的相應(yīng)區(qū)域中。Sub FillAll()Worksheets("Sheet2").Ra nge("A1:H1") _.Borders(xlBottom).L in eStyle = xlDoubleWorksheets.FillAcrossSheets (Worksheets("Sheet2") _.Ra nge("A1:H1")End SubVBA - Excel編程概念之：【單元格和區(qū)域】五、處理活動單元格處理活動單元格ActiveCell屬性返回代表活動單