【原】Excel教程：實(shí)用的VBA代碼，趕緊收藏！

Excel教程平臺(tái) 2020-10-30

展開全文

回復(fù)[目錄]學(xué)習(xí)113篇Excel教程

全套Excel視頻教程，掃碼觀看！

編按：

哈嘍，大家好！昨天和大家承諾，“在看”數(shù)超過(guò)30，小編明天就繼續(xù)爆肝更新VBA!感謝大家的支持，沒(méi)想到昨天的文章“在看”數(shù)已經(jīng)快100了！既然如此，那小編自然也不能辜負(fù)大家的信任，今天我們繼續(xù)學(xué)習(xí)VBA！

【前言】

在上一篇文章中，作者E圖表述給大家分享了關(guān)于RANGE對(duì)象引用的方法。通過(guò)回饋的信息來(lái)看，和作者想的一樣——確實(shí)有很多的同學(xué)，知道并且想學(xué)習(xí)EXCEL VBA知識(shí)。

那就讓我們繼續(xù)上次的內(nèi)容，依然來(lái)講RANGE。也許同學(xué)們會(huì)問(wèn)，為什么還是RANGE呢？因?yàn)樵贓XCEL中，只要是數(shù)據(jù)處理的問(wèn)題，最后都會(huì)歸結(jié)為單元格的引用和操作。即便是操作其他的對(duì)象，最終也會(huì)將內(nèi)存中計(jì)算或者提取的數(shù)據(jù)結(jié)果，賦值到單元格中來(lái)，所以說(shuō)基礎(chǔ)永遠(yuǎn)是基礎(chǔ)，根基穩(wěn)了，高級(jí)操作才能壓的住。

單元格的偏移

在工作表函數(shù)中，有一個(gè)OFFSET函數(shù)，作者E圖表述之前也專門寫過(guò)它的文章《月薪上萬(wàn)必學(xué)10大excel函數(shù)之十：它身負(fù)5大絕技，是動(dòng)態(tài)統(tǒng)計(jì)之王》，不熟悉這個(gè)函數(shù)的同學(xué)可以看看，因?yàn)槿绻瑢W(xué)們理解了這個(gè)函數(shù)的原理，那么對(duì)下面的兩個(gè)RANGE屬性，將有很大的幫助。

溫馨提示：加入下面QQ群：1043683754，下載教程配套的課件練習(xí)操作。

1.RANGE.OFFSET屬性

大家可以看出OFFSET的寫法都是一樣的，在工作表函數(shù)中，OFFSET函數(shù)是既有偏移的功能，也有指定引用范圍是幾行幾列的功能?？墒窃赩BA中RANGE.OFFSET屬性只有偏移的作用。如下代碼：

Sub 求和() Dim rg As Range Set rg = Range("C2") rg.Value = rg.Offset(0, -2) + rg.Offset(0, -1)End Sub

【代碼解析】：

Line1：定義rg為單元格變量；

Line2：將單元格地址C2賦值給rg變量；

Line3：rg向左偏移兩個(gè)位置的單元格+rg向左偏移一個(gè)位置的單元格，最后將計(jì)算結(jié)果賦值給rg變量。

通過(guò)上面的代碼解析，我們可以看出OFFSET屬性的作用，其含義就是根據(jù)基點(diǎn)單元格，按照參數(shù)進(jìn)行偏移，以偏移后的位置作為新的基點(diǎn)。

2.RANGE.RESIZE屬性

相對(duì)于一個(gè)基點(diǎn)的位置，我們可以使用VBA來(lái)選擇引用區(qū)域的面積有多大，這里就需要使用RANGE.RESIZE屬性了，如下代碼：

Sub 統(tǒng)計(jì)() Range("B5") = Application.WorksheetFunction.Sum(Range("B2").Resize(1, 3)) Range("B6") = Application.WorksheetFunction.Average(Range("B2").Resize(1, 3)) Range("B7") = Application.WorksheetFunction.Max(Range("B2").Resize(1, 3)) Range("B8") = Application.WorksheetFunction.Min(Range("B2").Resize(1, 3))End Sub

【代碼解析】：

Line1：調(diào)用工作表函數(shù)SUM，對(duì)基于單元格地址B2開始的1行3列的區(qū)域進(jìn)行求和運(yùn)算，并將結(jié)果賦值給單元格B5；

Line2：調(diào)用工作表函數(shù)AVERAGE，原理同Line1，對(duì)該區(qū)域進(jìn)行平均值運(yùn)算，并將結(jié)果賦值給單元格B6；

Line3：調(diào)用工作表函數(shù)MAX，原理同Line1，對(duì)該區(qū)域進(jìn)行最大值運(yùn)算，并將結(jié)果賦值給單元格B7；

Line4：調(diào)用工作表函數(shù)MIN，原理同Line1，對(duì)該區(qū)域進(jìn)行最小值運(yùn)算，并將結(jié)果賦值給單元格B8；

特別提示

Application.WorksheetFunction這句代碼就是調(diào)用工作表函數(shù)的語(yǔ)句，后面接寫工作表函數(shù)，與日常使用函數(shù)的方法一樣，只需記得單元格對(duì)象的表達(dá)方式，寫成RANGE或者CELLS即可。這個(gè)語(yǔ)句我們?cè)谇耙黄恼隆?a target="_blank" data-itemshowtype="0" tab="innerlink" data-linktype="2">花了6小時(shí)，僅為你寫出一篇能夠?qū)W懂的VBA教程！》中也提到過(guò)。

通過(guò)上面的代碼解析，我們可以看出RESIZE屬性的作用，其含義就是根據(jù)基點(diǎn)單元格，按照參數(shù)進(jìn)行擴(kuò)展，以擴(kuò)展后的區(qū)域作為新的操作對(duì)象。

小結(jié)

RANGE的OFFSET屬性和RESIZE屬性的結(jié)合，和工作表函數(shù)OFFSET的功能是一樣的，這在以后我們講到數(shù)組、字典時(shí)，都會(huì)有很多的應(yīng)用，希望同學(xué)們能夠在文章中有所掌握。

“首行、首列”與“末行、末列”

在本小節(jié)中，作者E圖表述將帶領(lǐng)大家一起學(xué)習(xí)在使用EXCEL VBA過(guò)程中最頻繁的語(yǔ)句之一，“查找數(shù)據(jù)源的起末范圍，以及引出行列號(hào)”。

在之前的學(xué)習(xí)中，我們已經(jīng)說(shuō)過(guò)，單元格的操作將是同學(xué)們?nèi)蘸笫褂肊XCEL VBA中操作最多的對(duì)象（沒(méi)有之一），所以對(duì)于引用一個(gè)單元格區(qū)域，知道這個(gè)區(qū)域的起末范圍，對(duì)于快速寫出對(duì)應(yīng)的引用表達(dá)式是十分關(guān)鍵的。例如下面的數(shù)據(jù)源：

這樣的一個(gè)數(shù)據(jù)源，有幾行幾列呢？很簡(jiǎn)單，數(shù)一數(shù)，15行8列。但是電腦不會(huì)像我們這樣“數(shù)”，計(jì)算機(jī)的任何操作都是基于人工賦予的指令。所以它需要我們寫出EXCEL可以識(shí)別的語(yǔ)言，才能執(zhí)行和我們一樣“數(shù)”的過(guò)程。

1.RANGE.END屬性

我們都知道一個(gè)小技巧，就是在一個(gè)工作表數(shù)據(jù)源范圍內(nèi)，選中某一個(gè)單元格，分別按：

CTRL+←，能找到連續(xù)區(qū)域的左邊界；

CTRL+→，能找到連續(xù)區(qū)域的右邊界；

CTRL+↑，能找到連續(xù)區(qū)域的上邊界；

CTRL+↓，能找到連續(xù)區(qū)域的下邊界。

在VBA中有同樣效果的語(yǔ)句，就是RANGE.END屬性，對(duì)應(yīng)的規(guī)律也是相當(dāng)?shù)囊锥子洠恍拍憧聪卤恚?/span>

圖片有誤end3表示↑，end4表示↓。舉個(gè)例子看一下，數(shù)據(jù)源如下：

為了更好地說(shuō)明代碼的應(yīng)用，作者E圖表述去掉了行號(hào)和列號(hào)的標(biāo)題欄，用VBA來(lái)確定這個(gè)行列的起止范圍，代碼如下：

Sub 末行末列() With Sheets("范圍確認(rèn)") a = .Range("A100000").End(xlUp).Row b = .Range("AX1").End(1).Column .Cells(a + 2, b - 1) = "末行號(hào)是：" & a .Cells(a + 3, b - 1) = "末列號(hào)是：" & b End WithEnd Sub

【代碼解析】：

Line1：WITH語(yǔ)句，提取代碼中相同的父級(jí)對(duì)象（Sheets("范圍確認(rèn)")），作為公共從屬關(guān)系；

Line2：以A100000單元格為基點(diǎn)，End（xlUp）向上取連續(xù)區(qū)域的邊界，用Row屬性顯示此邊界單元格的行號(hào)，并賦值給變量a；

Line3：以AX1單元格為基點(diǎn)，End（1）向左取連續(xù)區(qū)域的邊界，用Column屬性顯示此邊界單元格的列號(hào)，并賦值給變量b；

Line4：Cells（a+2，b-1），我們上次已經(jīng)說(shuō)過(guò)如何用CELLS表達(dá)單元格地址，那么把末行下移兩行，末列左移一列，作為輸入單元格，輸入字符串，這里的&和工作表中的&是一樣的效果；

Line5：原理同Line4；

Line6：WITH語(yǔ)句的結(jié)束語(yǔ)句。

這段代碼中只寫出了End（xlUp）和End（xlLeft）的用法，大家可以嘗試著寫寫其他的效果。

小思考

1.為什么我的單元格基點(diǎn)，用了“A100000”和“AX1”？

2.為什么網(wǎng)上有的代碼會(huì)用A65000這個(gè)單元格行號(hào)？

3.如果要找到首行或者首列，是不是從末行再End（xlUp）就是首行，或者從數(shù)據(jù)中部的某個(gè)單元格End（xlUp）也是首行；首列同理？

如果你還沒(méi)有思路，就想想CTRL+箭頭，你是從哪個(gè)單元格作為基點(diǎn)的。

單元格的“行”和“列”

行列交叉，整齊有序，縱橫間就是我們的電子表格，那么“既要縱橫，豈能不談行列”。

通過(guò)上面的代碼，我們應(yīng)該已經(jīng)發(fā)現(xiàn)RANGE.ROW和RANGE.COLUMN兩個(gè)屬性，分別是代表指定單元格的“行號(hào)”和“列號(hào)”，這個(gè)記起來(lái)應(yīng)該也不難，畢竟在工作表函數(shù)中，也有ROW函數(shù)和COLUMN函數(shù)。

那同學(xué)們可能會(huì)說(shuō)，工作表函數(shù)中也有ROWS和COLUMNS函數(shù)，機(jī)智如你，是的，VBA中也有 RANGE.ROWS和RANGE.COLUMNS屬性。那么結(jié)合這些屬性，我們一起舉一個(gè)案例看一下：

【Step1】：新建一個(gè)工作表，命名為“RC屬性”；

【Step2】：在工程窗口中，雙擊“RC屬性”工作表對(duì)象，在通用菜單欄中選擇Worksheet；

【Step3】：在右邊的菜單欄中選擇SelectionChange事件（選取改變時(shí)，觸發(fā)代碼）；

【Step4】：輸入代碼如下：

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Row <= 10 And Target.Column = 1 Then a = Target.Row Range(Cells(1, 3), Cells(a, 3 + a - 1)).Select Set Rng = Selection.Rows MsgBox Rng.Address End IfEnd Sub