二维码 购物车
部落窝在线教育欢迎您!

VBA实战入门教程(二):RANGE对象的偏移及行列确定

 

作者:E图表述来源:部落窝教育发布时间:2020-04-28 09:28:08点击:8460

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按: 

哈喽,大家好!VBA实战入门教程第2篇来了!今天我们将继续讲解RANGE对象的偏移、行列范围,包括RANGE.OFFSET属性RANGE.RESIZE属性RANGE.END属性RANGE.ROW和RANGE.COLUMN属性

 

【前言】


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

 

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

 

 

一、单元格的偏移

 

 

在工作表函数中,有一个OFFSET函数,作者E图表述之前也专门写过它的文章《Excel进阶之路必学函数:动态统计之王——OFFSET(上篇)》,不熟悉这个函数的同学可以看看,因为如果同学们理解了这个函数的原理,那么对下面的两个RANGE属性,将有很大的帮助。

 

1.RANGE.OFFSET属性

 

大家可以看出OFFSET的写法都是一样的,在工作表函数中,OFFSET函数是既有偏移的功能,也有指定引用范围是几行几列的功能。可是在VBARANGE.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开始的13列的区域进行求和运算,并将结果赋值给单元格B5

 

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

 

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

 

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

 

特别提示

 

Application.WorksheetFunction这句代码就是调用工作表函数的语句,后面接写工作表函数,与日常使用函数的方法一样,只需记得单元格对象的表达方式,写成RANGE或者CELLS即可。这个语句我们在前一篇文章《VBA系列基础教程(一):excel中最常使用的对象——RANGE中也提到过。

 

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

 

 

小结

 

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

 

 

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

 

 

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

 

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

 

 

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

 

1.RANGE.END属性

 

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

 

CTRL+←,能找到连续区域的左边界;

CTRL+→,能找到连续区域的右边界;

CTRL+↑,能找到连续区域的上边界;

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

 

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

 

 

我们举个例子看一下,数据源如下:

 

 

为了更好地说明代码的应用,作者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 With

End Sub

 

 

【代码解析】:

 

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

 

Line2A100000单元格为基点,EndxlUp)向上取连续区域的边界,用Row属性显示此边界单元格的行号,并赋值给变量a

 

Line3AX1单元格为基点,End1)向左取连续区域的边界,用Column属性显示此边界单元格的列号,并赋值给变量b

 

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

 

Line5原理同Line4

 

Line6WITH语句的结束语句。

 

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

 

小思考

 

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

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

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

 

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

 

 

三、单元格的“行”和“列”

 

 

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

 

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

 

那同学们可能会说,工作表函数中也有ROWSCOLUMNS函数,机智如你,是的,VBA中也有 RANGE.ROWSRANGE.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 If

End Sub

 

 

【代码解析】:

 

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

 

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

 

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

 

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

 

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

 

【编后语】

 

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

 

本文配套的练习课件请加入QQ群:1043683754下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

VBA中的RANGE 《VBA系列基础教程(一):excel中最常使用的对象——RANGE

VBA制作疫情地图《用excel制作全国动态疫情地图(VBA篇)》

VBA使excel自杀《灵异事件!一打开工作簿,文件就“自杀”了???》

VBA制作九九乘法表《5种最简单的用excel制作九九乘法表的方法》