Excel教程:实用的VBA代码,赶紧收藏!

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

全套Excel视频教程,扫码观看!

编按:

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

【前言】

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

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

1

单元格的偏移

在工作表函数中,有一个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变量;

Line3rg向左偏移两个位置的单元格+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的功能是一样的,这在以后我们讲到数组、字典时,都会有很多的应用,希望同学们能够在文章中有所掌握。

2

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

在本小节中,作者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

【代码解析】:

Line1WITH语句,提取代码中相同的父级对象(Sheets("范围确认")),作为公共从属关系;

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

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

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

Line5原理同Line4;

Line6WITH语句的结束语句。

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

小思考

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

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

3.如果要找到首行或者首列,是不是从末行再End(xlUp)就是首行,或者从数据中部的某个单元格End(xlUp)也是首行;首列同理?

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

3

单元格的“行”和“列”

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

通过上面的代码,我们应该已经发现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

【代码解析】:

Line1判断当被选中对象的行号小于等于10,并且列号等于1的时候;

Line2将被选中的单元格行号赋值给一个变量a;

Line3使用Range(cells,cells)的表达式,构建一个根据备选单元格行号,列出的单元格区域。比如:选择第3行,就构建以C1单元格为左上角的3*3单元格矩阵,并且选中这个区域;

Line4使用ROWS属性,将SELECTION(被选中的单元格)中涉及的单元格赋值给Rng变量;

Line5使用MSGBOX函数,显示Rng.ADDRESS(地址)属性信息框。

【编后语】

今天的信息量又是不少(我从来不写单一的内容),每天进步一点点,忘了就回来再看看,持之以恒的前提下,更应该明白“天道酬勤”的道理。“多看、多练、多总结”永远是成功者的不二法门。

扫二维码免费学Excel等视频

Excel教程相关推荐

花了6小时,仅为你写出一篇能够学懂的VBA教程!
Excel教程:19秒,搞定16000行Excel数据自动分组编号!
再见Ctrl+C!提取1000个文件名,用它10秒都嫌多!【Excel教程】

让工作提速百倍的「Excel极速贯通班」

(0)

相关推荐