EXCEL VBA 入門與實(shí)用例子

wangyong670 2024-11-10

展開全文

VBA這個(gè)以前用過，放下很久了，最近因需要用Excel處理數(shù)據(jù)，用到了它，使用了其它一些方式，對比一下，感到VBA還是有些長處的。故將以前的學(xué)習(xí)筆記，重新整理了一下，發(fā)表到這里，供有需要的人借鑒。

EXCEL VBA基礎(chǔ)

Visual Basic for Applications（VBA）是 VisualBasic 的一種宏語言，是微軟開發(fā)出來在其桌面應(yīng)用程序中執(zhí)行通用的自動化(OLE)任務(wù)的編程語言。主要能用來擴(kuò)展 Windows 的應(yīng)用程序功能，特別是Microsoft Office軟件。

如何打開 Excel VBA 編輯器

使用快捷鍵 Alt + F11，打開VBA 編輯器

或

點(diǎn)擊“開發(fā)工具”選項(xiàng)卡的“Visual Basiv”圖標(biāo)

（沒有看到“開發(fā)工具”選項(xiàng)卡？后面介紹）

插入模塊

在一個(gè) VBA 工程中想要插入新的模塊時(shí)，可在 VBA 工程右鍵，選擇插入類型——這里選模塊即可，參見下圖：

【VBA的模塊分類

窗體(窗體模塊)，模塊(標(biāo)準(zhǔn)模塊)，類模塊。

窗體，一種特殊的模塊，可以設(shè)計(jì)界面。

模塊，也就是普通模塊，是代碼編輯和執(zhí)行的容器。

類模塊，也是一種特殊的模塊，比較復(fù)雜，可以自定義類，自定義類的屬性，方法，甚至事件。關(guān)于類，類是對象的原始模型，是沒有實(shí)例化的對象，類實(shí)例化以后叫對象。

一般是用模塊寫代碼?！?/p>

運(yùn)行 VBA 代碼

現(xiàn)在先給出一段簡單的演示代碼，即在“模塊1”中輸入以下一段代碼

Sub MyCode()

Sheet1.Range("A1") = "Hello World"

End Sub

★在VBA編輯器中運(yùn)行 VBA 代碼，可用以下 3 種方法之一：

1.使用菜單欄命令

首選，將光標(biāo)放置在要運(yùn)行的代碼的任意一處，再在菜單欄選擇“運(yùn)行→運(yùn)行子過程/用戶窗體”命令

2.首選，將光標(biāo)放置在要運(yùn)行的代碼的任意一處，再點(diǎn)擊快捷工具欄?按鈕。

3.使用快捷鍵 F5

首選，將光標(biāo)放置在要運(yùn)行的代碼的任意一處，再使用快捷鍵 F5，即可運(yùn)行代碼。

運(yùn)行后，按Alt + Q 鍵返回表格窗口，可以看到在 Sheet1 工作表 A1 單元格，寫入 “Hello World” 內(nèi)容，參見下圖：

【如何從VBA窗口返回表格窗口？

按Alt + Q 鍵】

★除了前面介紹了VBA 代碼在VBA 編輯器中運(yùn)行，還可以在Excel 表格界面中，從“開發(fā)工具”選項(xiàng)卡運(yùn)行。

【若沒有看到“開發(fā)工具”選項(xiàng)卡？如下處理

在功能區(qū)空白處右擊，單擊快捷菜單的“開發(fā)工具”項(xiàng)，添加“開發(fā)工具”選項(xiàng)卡，參見下圖：

】

點(diǎn)擊“開發(fā)工具”選項(xiàng)卡的 “宏”圖標(biāo)，會彈出工作簿包含的所有宏的列表，選擇想要的宏，點(diǎn)擊右側(cè)“執(zhí)行”按鈕，參見下圖：

★通過給“按鈕”指定宏的方式運(yùn)行

找到“開發(fā)工具→插入”單擊。選中表單控件部分的“按鈕”，再在工作表（Worksheet）的合適位置單擊，這時(shí)出現(xiàn)一個(gè)宏列表，其中選擇想要指定的宏，點(diǎn)擊確定，完成指定宏【之后，右擊它將出現(xiàn)的快捷菜單可修改之】。參見下面動圖：

保存含有VBA代碼的excel文件

若想保存含有VBA代碼的文件，應(yīng)選擴(kuò)展名為.xlsm，或.xls，【提示：xls是2003版本之前的文件，不管有沒有宏程序的話都是xls文件，從excel2007開始有了區(qū)分，.xlsm是含有VBA代碼（宏）的，.xlsx是不含VBA代碼（宏）的，默認(rèn)是.xlsx，如果不想含有代碼，可以保存為xlsx，即可自動刪除其中VBA代碼】參見下圖：

VBA 過程和函數(shù)

☆VBA 過程以 Sub 語句開始，以 End Sub 語句結(jié)束，包含一個(gè)或多個(gè)語句，完成一個(gè)特定的目標(biāo)。

VBA 過程的基本語法如下：

Sub 過程名([參數(shù)])

語句1

語句2

...

語句n

End Sub

其中 [參數(shù)]可以沒有，若有語法如下：

變量名1 As 數(shù)據(jù)類型,...變量名n As 數(shù)據(jù)類型

☆VBA 函數(shù)

VBA 函數(shù)與 VBA 過程很相似，除了使用的關(guān)鍵詞外，主要區(qū)別是，函數(shù)可以返回值。

VBA 函數(shù)基本語法如下：

Function [函數(shù)名]([參數(shù)]) As [返回值類型]

語句1

語句2

...

語句n

[函數(shù)名] = [返回值]

End Function

函數(shù)包含的語句中，相比過程，可以看到多一個(gè) [函數(shù)名] = [返回值] 語句，這是函數(shù)的返回值語句。

Excel VBA對象模型

Excel VBA對象模型是編程時(shí)可以使用的對象的層次結(jié)構(gòu)，它使得引用要操控的對象更容易。Excel VBA里有好多對象，這些對象其實(shí)都是有層級關(guān)系的，就像一棵樹一樣，最高級的是Application，簡化的對象的層次結(jié)構(gòu)參見下圖：

這些對象一般常用的有以下四種：

應(yīng)用程序（Application）

工作簿（Workbook）

工作表（Worksheet）

范圍（Range）

許多使用 Excel 完成的工作都是圍繞這四種對象及其成員進(jìn)行的。位于頂層的是Application對象，也就是Excel應(yīng)用程序本身，它包含Excel中的其它的對象，如Workbook對象；一個(gè)Workbook對象包含其它一些對象，如Worksheet對象；而一個(gè)Worksheet對象又可以包含其它對象，如Range對象，一個(gè) Range 對象，表示一個(gè)單元格、行、列、包含一個(gè)或多個(gè)單元格塊的單元格選定區(qū)域（選定區(qū)域可能是連續(xù)的，也可能不是連續(xù)的）或甚至多個(gè)工作表上的一組單元格）。

引用對象

在編寫VBA代碼時(shí)，了解如何引用對象是至關(guān)重要的。很明顯，想要開始處理特定的VBA對象時(shí)，必須先識別它，也就是說，告訴VBA要處理哪個(gè)對象。

可以用句點(diǎn)連接對象名來限定是對某個(gè)對象成員的引用。

例如，Application.Workbooks(“Book1.xls”).Worksheets(“Sheet1”).Range(“A1”) 表明是對工作簿Book1上的工作表Sheet1中單元格A1的引用，其中Application代表Excel應(yīng)用程序本身，可省略。特別地，若Book1是當(dāng)前活動工作簿，則上述語句可簡寫為 Worksheets(“Sheet1”).Range(“A1”) ；若Sheet1是當(dāng)前活動工作表，則又可簡寫為 Range(“A1”) 。因此，若在引用中省略了工作簿對象，則表明是使用當(dāng)前活動工作簿；若再省略了工作表對象，則表明是使用當(dāng)前活動工作表。

Worksheet.Cells 屬性語法

expression.Cells

expression 一個(gè)代表 Worksheet 對象的變量?？梢栽?Cells 關(guān)鍵字后面緊接著指定行和列索引。

返回一個(gè) Range 對象，該對象代表工作表上 (單元格，而不只是當(dāng)前使用的單元格) 。

如：

Worksheets("Sheet1").Cells(5, 3).Font.Size = 14 '將 Sheet1 中單元格 C5 的字號設(shè)置為 14 磅。

一個(gè)實(shí)用實(shí)例：比較兩個(gè)Excel表中相同、不同的數(shù)據(jù)行

數(shù)據(jù)如下圖所示：

Sheet1和Sheet2表，其中“項(xiàng)名1”、“項(xiàng)名2”列是要比對異同的數(shù)據(jù)，“條件”列中的數(shù)據(jù)是由強(qiáng)制連接運(yùn)算符&將“項(xiàng)名1”、“項(xiàng)名2”列連接起來，這里假設(shè)要比對的數(shù)據(jù)行由兩列組成，更多的列可依此類推。“比對1”和“比對2”過程用Sheet1表的 “標(biāo)志”列來記比對結(jié)果。“Sheet1中有Sheet2中也有”和 “Sheet1中有而Sheet2中沒有”過程用Sheet3表來記處理結(jié)果。

【“&”和“+”兩種運(yùn)算符

“&”是強(qiáng)制性連接，就是不管什么都連接。

“+”是對字符串進(jìn)行連接，對數(shù)字則進(jìn)行加法運(yùn)算。（用“+”號的時(shí)候，“+”前后類型要一致）】

源碼如下：

Sub 比對1()  '在sheet1的標(biāo)志列，將sheet1中有,sheet2也有的，加標(biāo)記A；未有A 的說明將sheet1中有sheet2沒有的
  Sheet1.Range("D2:D1000").ClearContents
  Dim Sheet1數(shù)據(jù)行數(shù), Sheet2數(shù)據(jù)行數(shù)
  Sheet1數(shù)據(jù)行數(shù) = 15
  Sheet2數(shù)據(jù)行數(shù) = 11
  For I = 2 To Sheet1數(shù)據(jù)行數(shù)  'Worksheets("Sheet1").Cells(行,列) 和 Sheet1.Cells(行,列)等效
     For j = 2 To Sheet2數(shù)據(jù)行數(shù)
         If Worksheets("Sheet1").Cells(I, 3) = Worksheets("Sheet2").Cells(j, 3) Then   '兩表的比較列——條件
            Worksheets("Sheet1").Cells(I, 4) = "A"  '在標(biāo)志列加標(biāo)志
            Exit For
         End If
     Next
  Next
End Sub
'Sheet1中有Sheet2中也有,拷貝到heet3中
Sub Sheet1中有Sheet2中也有()
  Sheet3.Range("A2:D1000").ClearContents
  Set conn = CreateObject("adodb.connection")
  conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
  Sq1 = "select  *  from [Sheet1$] where 條件 in(select 條件 from [Sheet2$])"    ''在select之后加Distinct將刪除重復(fù)的
  Sheet3.[B2].CopyFromRecordset conn.Execute(Sq1)
  conn.Close
  Set conn = Nothing
End Sub
Sub 比對2()  '在sheet1標(biāo)志列，將sheet1中有,sheet2沒有的，加標(biāo)記B
  Sheet1.Range("D2:D1000").ClearContents
  Dim Sheet1數(shù)據(jù)行數(shù), Sheet2數(shù)據(jù)行數(shù)
  Sheet1數(shù)據(jù)行數(shù) = 15
  Sheet2數(shù)據(jù)行數(shù) = 11
  For I = 2 To Sheet1數(shù)據(jù)行數(shù)  'Worksheets("Sheet1").Cells(行,列) 和 Sheet1.Cells(行,列)等效
     Worksheets("Sheet1").Cells(I, 4) = "B"  '在標(biāo)志列加標(biāo)志
     For j = 2 To Sheet2數(shù)據(jù)行數(shù)
         If Worksheets("Sheet1").Cells(I, 3) = Worksheets("Sheet2").Cells(j, 3) Then   '兩表的比較列——條件
            Worksheets("Sheet1").Cells(I, 4) = ""
            Exit For
         End If
     Next
  Next
End Sub
'Sheet1中有而Sheet2中沒有,拷貝到heet3中
Sub Sheet1中有而Sheet2中沒有()
  Sheet3.Range("A2:D1000").ClearContents
  Set conn = CreateObject("adodb.connection")
  conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
  Sq1 = "select  *  from [Sheet1$] where 條件 not in(select 條件 from [Sheet2$])"  ''在select之后加Distinct將刪除重復(fù)的
  Sheet3.[a2].CopyFromRecordset conn.Execute(Sq1)
  conn.Close
  Set conn = Nothing
End Sub

現(xiàn)在，你可以用前面介紹的運(yùn)行方法試試了，如：

本站是提供個(gè)人知識管理的網(wǎng)絡(luò)存儲空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自： wangyong670 > 《6. 宏編程》

舉報(bào)/認(rèn)領(lǐng)