【原】Excel教程：实用的VBA代码，赶紧收藏！

Excel教程平台 2020-10-30

展开全文

回复[目录]学习113篇Excel教程

全套Excel视频教程，扫码观看！

编按：

哈喽，大家好！昨天和大家承诺，“在看”数超过30，小编明天就继续爆肝更新VBA!感谢大家的支持，没想到昨天的文章“在看”数已经快100了！既然如此，那小编自然也不能辜负大家的信任，今天我们继续学习VBA！

【前言】

在上一篇文章中，作者E图表述给大家分享了关于RANGE对象引用的方法。通过回馈的信息来看，和作者想的一样——确实有很多的同学，知道并且想学习EXCEL VBA知识。

那就让我们继续上次的内容，依然来讲RANGE。也许同学们会问，为什么还是RANGE呢？因为在EXCEL中，只要是数据处理的问题，最后都会归结为单元格的引用和操作。即便是操作其他的对象，最终也会将内存中计算或者提取的数据结果，赋值到单元格中来，所以说基础永远是基础，根基稳了，高级操作才能压的住。

单元格的偏移

在工作表函数中，有一个OFFSET函数，作者E图表述之前也专门写过它的文章《月薪上万必学10大excel函数之十：它身负5大绝技，是动态统计之王》，不熟悉这个函数的同学可以看看，因为如果同学们理解了这个函数的原理，那么对下面的两个RANGE属性，将有很大的帮助。

温馨提示：加入下面QQ群：1043683754，下载教程配套的课件练习操作。

1.RANGE.OFFSET属性

大家可以看出OFFSET的写法都是一样的，在工作表函数中，OFFSET函数是既有偏移的功能，也有指定引用范围是几行几列的功能。可是在VBA中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向左偏移两个位置的单元格+rg向左偏移一个位置的单元格，最后将计算结果赋值给rg变量。

通过上面的代码解析，我们可以看出OFFSET属性的作用，其含义就是根据基点单元格，按照参数进行偏移，以偏移后的位置作为新的基点。

2.RANGE.RESIZE属性

相对于一个基点的位置，我们可以使用VBA来选择引用区域的面积有多大，这里就需要使用RANGE.RESIZE属性了，如下代码：

Sub 统计() 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：调用工作表函数SUM，对基于单元格地址B2开始的1行3列的区域进行求和运算，并将结果赋值给单元格B5；

Line2：调用工作表函数AVERAGE，原理同Line1，对该区域进行平均值运算，并将结果赋值给单元格B6；

Line3：调用工作表函数MAX，原理同Line1，对该区域进行最大值运算，并将结果赋值给单元格B7；

Line4：调用工作表函数MIN，原理同Line1，对该区域进行最小值运算，并将结果赋值给单元格B8；

特别提示

Application.WorksheetFunction这句代码就是调用工作表函数的语句，后面接写工作表函数，与日常使用函数的方法一样，只需记得单元格对象的表达方式，写成RANGE或者CELLS即可。这个语句我们在前一篇文章《花了6小时，仅为你写出一篇能够学懂的VBA教程！》中也提到过。

通过上面的代码解析，我们可以看出RESIZE属性的作用，其含义就是根据基点单元格，按照参数进行扩展，以扩展后的区域作为新的操作对象。

小结

RANGE的OFFSET属性和RESIZE属性的结合，和工作表函数OFFSET的功能是一样的，这在以后我们讲到数组、字典时，都会有很多的应用，希望同学们能够在文章中有所掌握。

“首行、首列”与“末行、末列”

在本小节中，作者E图表述将带领大家一起学习在使用EXCEL VBA过程中最频繁的语句之一，“查找数据源的起末范围，以及引出行列号”。

在之前的学习中，我们已经说过，单元格的操作将是同学们日后使用EXCEL VBA中操作最多的对象（没有之一），所以对于引用一个单元格区域，知道这个区域的起末范围，对于快速写出对应的引用表达式是十分关键的。例如下面的数据源：

这样的一个数据源，有几行几列呢？很简单，数一数，15行8列。但是电脑不会像我们这样“数”，计算机的任何操作都是基于人工赋予的指令。所以它需要我们写出EXCEL可以识别的语言，才能执行和我们一样“数”的过程。

1.RANGE.END属性

我们都知道一个小技巧，就是在一个工作表数据源范围内，选中某一个单元格，分别按：

CTRL+←，能找到连续区域的左边界；

CTRL+→，能找到连续区域的右边界；

CTRL+↑，能找到连续区域的上边界；

CTRL+↓，能找到连续区域的下边界。

在VBA中有同样效果的语句，就是RANGE.END属性，对应的规律也是相当的易懂易记，不信你看下表：

图片有误end3表示↑，end4表示↓。举个例子看一下，数据源如下：

为了更好地说明代码的应用，作者E图表述去掉了行号和列号的标题栏，用VBA来确定这个行列的起止范围，代码如下：

Sub 末行末列() With Sheets("范围确认") a = .Range("A100000").End(xlUp).Row b = .Range("AX1").End(1).Column .Cells(a + 2, b - 1) = "末行号是：" & a .Cells(a + 3, b - 1) = "末列号是：" & b End WithEnd Sub

【代码解析】：

Line1：WITH语句，提取代码中相同的父级对象（Sheets("范围确认")），作为公共从属关系；

Line2：以A100000单元格为基点，End（xlUp）向上取连续区域的边界，用Row属性显示此边界单元格的行号，并赋值给变量a；

Line3：以AX1单元格为基点，End（1）向左取连续区域的边界，用Column属性显示此边界单元格的列号，并赋值给变量b；

Line4：Cells（a+2，b-1），我们上次已经说过如何用CELLS表达单元格地址，那么把末行下移两行，末列左移一列，作为输入单元格，输入字符串，这里的&和工作表中的&是一样的效果；

Line5：原理同Line4；

Line6：WITH语句的结束语句。

这段代码中只写出了End（xlUp）和End（xlLeft）的用法，大家可以尝试着写写其他的效果。

小思考

1.为什么我的单元格基点，用了“A100000”和“AX1”？

2.为什么网上有的代码会用A65000这个单元格行号？

3.如果要找到首行或者首列，是不是从末行再End（xlUp）就是首行，或者从数据中部的某个单元格End（xlUp）也是首行；首列同理？

如果你还没有思路，就想想CTRL+箭头，你是从哪个单元格作为基点的。

单元格的“行”和“列”

行列交叉，整齐有序，纵横间就是我们的电子表格，那么“既要纵横，岂能不谈行列”。

通过上面的代码，我们应该已经发现RANGE.ROW和RANGE.COLUMN两个属性，分别是代表指定单元格的“行号”和“列号”，这个记起来应该也不难，毕竟在工作表函数中，也有ROW函数和COLUMN函数。

那同学们可能会说，工作表函数中也有ROWS和COLUMNS函数，机智如你，是的，VBA中也有 RANGE.ROWS和RANGE.COLUMNS属性。那么结合这些属性，我们一起举一个案例看一下：

【Step1】：新建一个工作表，命名为“RC属性”；

【Step2】：在工程窗口中，双击“RC属性”工作表对象，在通用菜单栏中选择Worksheet；

【Step3】：在右边的菜单栏中选择SelectionChange事件（选取改变时，触发代码）；

【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