带你入门VBA如何控制单元格

1、带你入门VBA，第五讲：如何控制单元格 单元格是我们平时接触最多的，所以此讲专门介绍和练习单元格的使用和控制。马上就要开始。在VBA中，单元格常用的表示方法有两种，一种是RANGE女口 A1在VBA的表示方法是 Range(a1)A1:100 的表法方法是：Range(A1:A100)和以前一样，大家先在工作表中插入一个矩形作为执行宏的按纽在按纽上单击右键，单击指定宏，再单击新建,在VBE窗口中的代码窗口会出现Sub矩形1_单击()End Sub在两句中间输入Ra nge(a1)=1000Range可以代表一个单元格区域，也可以代表一个单元格，通过Range(a1)=1000 也可以看岀，它的

2、用法是 Range后括号中带上A1就可以表示我们平时在工作表 用到的中的A1单元格了，它在表示一个连续区域时是这个的,Range(“区域的左上角单元格：区域的右下角单元格 )请把刚才的代码再加上一句：Ra nge(a1:a10)=1000对,如果是不连续的单元格多个区域，它是这样表示的：Range(单元格区域1,单元格区域2.)把刚才的代码替换为：Ran ge(a1:a10,c1:c10,e1:e10) = 100 再试试以下是引用 matsumi 在2004-10-25 21:35:00的发言：a1=1000 与 Range(a1)=1000 有何区别？A1是Range(A1)的简写.二者在

3、用法上没有什么区别，但在输入时有点不同，如当你输入A1后加点时,后面不会岀现属性和方法列表，而输入Range(A1)加点后则会岀现属性列表供你选取以下是引用 gvntw 在 2004-10-25 21:40:00的发言：也可以这样吧：Union(a1:a10, b3:c10,d5:f7) = 100Union是求多个单元格区域的并集，用法是Union (单元格区域1，单元格区域 2.)它返回的是所有区域的所有单元格集合请运行下面的一句：Application.Intersect(Range(a1:a10), Range(a3:c4).Select这句有个Select,它是单元格被选取的意思。在

4、我们录制宏时，会岀现大量的select,其实在程序中多数Select是多余的，它影响速度又会影响程序的运行。如：Ran ge(C16).Select ActiveCell.FormulaRICI = 100 Ran ge(C11).Select其实我只是在 C16单元格中填入数值100，就会岀现这么一大堆代码，这此代码可以优化为：Ran ge(C16).=100以下是引用 兰色幻想在2004-10-25 21:51:00的发言：下面请运行下面的一句：Application.Intersect(Range(a1:a10), Range(a3:c4).Select这一句的意思是选中区域A1 : A

5、10和A3:C4重叠的区域，即两个区域共有的区域，此名代码运行的结果是选中区域 A3 : A4注意In tersect方法是一个非常实用的一个方法，比如我们在动态选取工作表Sheet1A列中已存在数据的区域时，就会用到它。在介绍这个用法前还要介绍一下另一个工作表中非常实用的属性：Usedra nge你可以选试着运行一下：Activecell.usedra nge.Select运行Activecell.usedrange.Select你会发现，在当前工作表中的所有用过的区域全被选中了。你明白了吗，其实usedrange就是工作表中所有已存在内容的矩形区域，为什么不说是存在内容的单元格呢？原因是比

6、如你在一个空工作表中的B3和C5单元格任意输入一个数值，运行Activecell.usedrange.Select后选取的不只是 B3和C5单元格，而是 B3:C5单元格区域以下是引用 gvntw 在 2004-10-25 22:42:00的发言：Sheet1.UsedRange.Select我们平时在程序中会看到Activesheetworksheets(sheet1)sheet1sheets(sheet1)Sheets(1)顺便说一说他们的区别Activesheet是指当前活动工作表，即你正在操作的工作表worksheets(sheet1) 等同于sheets(sheet1) 是特指工作表

7、 Sheet1,注意这时的 Sheet1是工作表的名 子,就如同“员工工资表一样是工作表的名称 ，而Sheet1和Sheets(1),不管你如何命名，Sheet1和Sheets(1) 就只代表第一个工作表，Sheet2和Sheets(2)代表第二个工作表。下面以一个实例说明如何动态选取一个区域：要求：无论工作表中所有单元格如何填入，运行程序后要选取A列已用所有区域：代码如下：In tersect(Ra nge(a:a),Sheets(sheet1).UsedRa nge ).SelectRange(a:a)是A列，Sheets(sheet1).UsedRange是工作表 sheetl已使用的所

8、有单元格区域，用nersect：求二者的-共有区域，结果就是我们所要的丄.列已用所有区域址耐Z 盘d a岂帝伸人M詞阴|目HulW，|画HhwoftE |同“加“ 仁 HI幌中 占备刍|才诩 卜程弓忡*4Sub矩形1_单击()Application.lntersect(Range(a:a), Sheets(sheet1).UsedRange).SelectEnd Sub以下是引用 先锋在2004-10-25 23:27:00的发言： 结果不是的。你把A9 , A10，A11内容删掉，再运行它仍然选 A3 : A12这个区域？其实这中间有个规律：如果你输入的是非数字的字符，即使你删除了，也被当做

9、已用区域(已用过的 区域)，而当你删除的是你输入的数字时则不受这个限制)以下是引用 先锋在2004-10-26 13:01:00的发言：兰老师：还是不明白？输入非数字的字符时，确实是这样。但是当输入的数字时，有时还是受这个限制的，如附件中的A3删掉后，怎么还选呢？应该是受Sheets(sheet1).UsedRange是工作表sheet1已使用的所有单元格区域”的影响吧，结果只是二者的共有区域，并不是我们所要的A列已用所有区域。因为这个区域是矩形区域，在其他列第三行已填有内容，所以A3即使删除也属已用区域。你可以再试着在 C20填入任意一个内容，A列选取的也是到 20行如果只是简单的给单元格赋

10、值就还是用的好,快捷.如果还要有其他控制最好还是用range().以下是引用 wuaza在2004-10-26 20:48:00的发言：考一下大家，sheet1.rows(2).ra nge(a3).select是选中哪个单元格？其实它和这句一样的效果：sheet1.Ra nge(a2).Ra nge(a3).Select这种表示方式是对工作表区域重新进行定位坐标，原来默认是以A1为坐标右上端点，而Range(a2).Range(a3)则重新新定义以 A2为右上角顶点坐标，在这种坐标系下的A3当然是A4 了，再如：Range(B2).Range(a3) 的结果是以 B2为右上端点，这种坐标下结

11、果就为B4 了sheet1.rows(2).ra nge(a3).select是以第二行最右端点为新坐标顶点说了这么多还没有介绍到另一个单元格的表示方式：CellSCellS (行数，列数)如 A1 : Cells(1,1) 用 range 表示：range(a1)b2: cells(2,2) 用 range 表示：range(b2)c100: cells(100,3) 用 range 表示：range(c100)cells也是一个常用的单元格表示方法，它和Range在表示单元格时有什么共同点和区别呢？Range可以表示单元格，也可以表示单元格区域cells也是这样，但除了 cells作为一外

12、集合对象外其他只能表示一个独立的单元格，如：Cells.select选取工作表所有单元格Cells(2,2).select 选取 B2 单元格所以在表示单元格区域时，除表示全部单元格外，其他均需用 Range来表示如：range(a1:b20).选中A列连续数据区域中最后一个单元格：cells(ra nge(a1).Curre ntRegio n.Rows.cou nt,1).select以下是引用612321 在2004-10-27 11:44:00的发言：老师，我要为我的VBA程序设置密码保护的怎么设置啊？在点击VBE编辑器菜单的工具-VBAProject属性保护以下是引用 wuaza 在

13、2004-10-27 11:34:00的发言：再讲一讲offset和resize的用法吧。Offset是单元格或单元格区域的移动offset (移动行数,移动列数)resize是单元格或单元格区域的行数和列数重新设置后范围大小resize (变动后行数，变动后列数)例：Ran ge(A1:B2).Select选取A1:B2区域Ran ge(A1:B2).Offset(3, 0).SelectA1:B2区域向下移动三行，结果是选中A4:B5区域Ran ge(A1:B2).Resize(2, 4).SelectA1:B2区域重新设置，行数为2,列数为4 ,结果为选 取A1:D2Ran ge(A1:

14、B2).Resize(Ra nge(A1:B2).Rows.Cou nt + 2, Ran ge(A1:B2).Colum ns.Cou nt +4).SelectA1:B2区域重新设置，在原来行数的基础上 加2行,在原来列数的基础上 加4列，运行结果为：结果是选取A1:F4Sub矩形1_单击()Range(A1:B2).Offset(3, O).SelectEnd SubSub矩形2_单击()Range(A1:B2).SelectEnd SubSub矩形3_单击()Range(A1:B2).Resize(2, 4).SelectEnd SubSub矩形4_单击()Range(A1:B2).R

15、esize(Range(A1:B2).Rows.Count +2, Range(A1:B2).Columns.Count +4).SelectEnd Sub以下是引用 hpw 在2004-10-27 17:14:00的发言：不好意思，上面说的太罗索我其实就是想把上面说的第一步要操作的内容都去掉，只保留0.690,然后改成p=0.690,放到表格的标题后面就可以了 .呵呵，不知道这次说清楚了没有.一直等待.On Error Resume Next女讪错误，执行下一句K = -9设置变量K的初始值，设置K目的是控制在购买可能性后添加数的位置EEE = Application.Countlf(Col

16、umns(2), Crosstabs)EEE为循环的次数,有多少个Crosstabs就行循环多少次 For I = 1 To EEE 设置循环 K = K + 12因为每个 Pearson Chi-Square 相隔 12 行,所以设置循环一次加12 AAA = Application.Match(Crosstabs, Columns(2), 0)计算 Crosstabs的位置 BBB = Application.Match(Tables, Columns(2), 0)计算 Tables 的位置 CCC =Application.Match(Pearson Chi-Square, Column

17、s(2), 0)计算 Pearson Chi-Square 的位置 DDD =Format(Cells(CCC, 5), 0.000) 把相应 Pearson Chi-Squar 的数值赋予 DDD，如第一个是 0.690 Range(Cells(AAA, 1), Cells(BBB, 1).EntireRow. Delete 把 Crosstabs 和 Tables 之间的行删除(包括含 Crosstabs和Tables的行)Cells(K, 2)= 附表 单位购买的可能性(P= & DDD & )在表头加入相应的内容 Next以下是引用 hnymlzs 在 2004-10-27 22:14

18、:00的发言:兰大侠你好：能把下的代码给解释一下Private Sub Workbook_BeforeClose(Ca ncel As Boolea n) Term in ate Set appTime = Noth ing Set objBt n =Nothi ng End SubPrivate Sub Workbook_Ope n() In it (1000) End SubPrivate Declare Function SetTimer Lib user32 (ByVal hWnd As _ Long, ByVal nIDEvent As Long, ByVal uElapse As

19、Long, _ ByVal lpTimerFu nc As Long) As LongPrivate Declare Function KillTimer Lib user32 (ByVal hWnd As _ Long, ByVal nIDEvent As Long) As Long Dim hTimer Public objBtn As Comma ndBarC on trolSub TimerProc(ByVal hWnd&, ByVal Msg&, ByVal idEve nt&, ByVal dwTime &) DoEve nts On Error Resume Next Range

20、(A1) = Format(Now, hh:mm:ss AM/PM) End SubSub Init(Interval&) hTimer = SetTimer(0, 0, Interval, AddressOf TimerProc) End SubSub Termi nate() Call KillTimer(0, hTimer) End Sub学得可真快，都到 API啦，我也不太懂，跟着学 上传的代码可能只是一部分，蓝色部分放入thisworkbook模块，褐色部分在普通模块。作用：类似电子时钟，每秒(1000毫秒)更新一次时间。工作表打开时运行In it (1000)，即运行 SetTim

21、er(0, 0, I nterval, AddressOf TimerProc) ，其中 In terval为In it传来的1000 ；SetTimer : API函数，用来分配定时器，周期性地在指定间隔的时间过去时调用过程；有四个参数：第三个参数指定时间间隔，以毫秒为单位(此处1000 );第四个参数指定函数的过程实例，即按定时器的通知调用过程，此处调用TimerProc过程。TimerProc过程将系统时间(now)写入A1单元格，由于定时器的设定，每秒写一次。关闭工作簿时运行Terminate ， KillTimer也是API函数，用于清除定时器，终止调用。以下是引用 hnymlzs

22、在2004-10-27 22:14:00的发言：兰大侠你好：能把下的代码给解释一下？Private Sub Workbook_BeforeClose(Ca ncel As Boolea n) Term in ate Set appTime = Noth ing Set objBt n =Nothi ng End SubPrivate Sub Workbook_Ope n() In it (1000) End SubPrivate Declare Function SetTimer Lib user32 (ByVal hWnd As _ Long, ByVal nIDEvent As Long,

23、 ByVal uElapse As Long, _ ByVal lpTimerFu nc As Long) As LongPrivate Declare Function KillTimer Lib user32 (ByVal hWnd As _ Long, ByVal nIDEvent As Long) As Long Dim hTimer Public objBtn As Comma ndBarC on trolSub TimerProc(ByVal hWnd&, ByVal Msg&, ByVal idEve nt&, ByVal dwTime &) DoEve nts On Error

24、 Resume Next Range(A1) = Format(Now, hh:mm:ss AM/PM) End SubSub Init(Interval&) hTimer = SetTimer(0, 0, Interval, AddressOf TimerProc) End SubSub Termi nate() Call KillTimer(0, hTimer) End Sub学得可真快，都到 API啦，我也不太懂，跟着学上传的代码可能只是一部分，蓝色部分放入thisworkbook模块，褐色部分在普通模块。作用：类似电子时钟，每秒(1000毫秒)更新一次时间。工作表打开时运行In it

25、(1000)，即运行 SetTimer(O, 0, I nterval, AddressOf TimerProc) ，其中 In terval为In it传来的1000 ；SetTimer : API函数，用来分配定时器，周期性地在指定间隔的时间过去时调用过程；有四个参数：第三个参数指定时间间隔，以毫秒为单位(此处1000 );第四个参数指定函数的过程实例，即按定时器的通知调用过程，此处调用TimerProc过程。TimerProc过程将系统时间(now)写入A1单元格，由于定时器的设定，每秒写一次。关闭工作簿时运行 Terminate ， KillTimer也是API函数，用于清除定时器，终

26、止调用。以下是引用 兰色幻想在2004-10-25 22:56:00的发言：我们平时在程序中会看到Activesheetworksheets(sheet1)sheet1sheets(sheet1)Sheets(1)顺便说一说他们的区别Activesheet是指当前活动工作表，即你正在操作的工作表worksheets(sheet1) 等同于sheets(sheet1) 是特指工作表 Sheet1,注意这时的 Sheet1是工作表的名 子,就如同“员工工资表一样是工作表的名称 ，而Sheet1和Sheets(1),不管你如何命名，Sheet1和Sheets(1) 就只代表第一个工作表，Sheet2

27、和Sheets(2)代表第二个工作表。sheet1和sheets(1)不同，sheet1是工作表的对象实例名称，在工作表属性中可以修改，也就是说只 要不岀现重名，sheets(2)的实例名称也可以是Shee以下是引用 啊一在2004-10-28 13:40:00的发言：比如我在SHEET1中放置一个按扭，单击就选定没有数据的行，当然了要VBA自己判断到底到哪一行有数据，然后选择剩下没有数据的空白行.Ran ge(A1:A1000).SpecialCells(xlCellTypeBla nks).E ntireRow.Select电点击浏览该文件SpecialCells方法此对象代表与指定类型及

28、值相匹配的所有单元格。语法expression.SpecialCells( Type, Value )expression必选。该表达式返回一个Range 对象。Type Long 类型，必选。要包含的单元格。可为以下XICellType常量之一。常量说明xlCellTypeAIIFormatConditions任意格式的单元格xlCellTypeAIIValidation具有有效条件的单元格xlCellTypeBIanks空单元格xlCellTypeComments包含注释的单元格xlCellTypeConstants包含常量的单元格xlCellTypeFormulas包含公式的单元格xlC

29、ellTypeLastCell已用区域的最后一个单元格xlCellTypeSameFormatConditions具有相同格式的单元格xlCellTypeSameValidation具有相同有效条件的单元格xlCellTypeVisible所有可见单元格Value Variant类型，可选。如果Type 为 xICellTypeConstants或 xICellTypeFormuIas 之一，此参数可用于确定结果中应包含哪几类单元格。将某几个值相加可使此方法返回多种类型的单元格。默认情况下将选定所有常量或公式，对其类型则不加区别。可为以下XlSpecialCellsValues常量之一：xlE

30、rrors 、xlLogical 、xlNumbers 或 xlTextValues。注：上面的常量是和编辑菜单定位定位条件相对应的，说白了，就是通过 VBA来控制定位功能以下是引用 yigepure 在2004-10-2821:55:00的发言：Curre ntRegi on这个不是取它周围的单元格区域吗.为什么不连续就不行呢.A列最后一个单元格可以用Ran ge(A65536).e nd(xlup).select以下是引用啊一在2004-10-2912:35:00的发言：兰老师(我知道你不喜欢这样的称呼可我不知道你的性别和年龄)！我的意思是选取数据区域之外的行，就是有数据的行的下一行到65

31、536行之间的所有行。我这样写了，也能实现但是是我自己摸索的肯定不是规范的语法，不是最快捷的，请兰老师看看有没有更好的办法：Sub 隐藏()Dim i As Integer i = Application.WorksheetFunction.Max(Range(Range(B9),Range(B65536).End(xlUp)Range(Cells(i, 256), Cells(65536,256).EntireRow.Hidden= True End SubRange(Cells(Range(A65536).End(xlUp).Row+ 1, 1), Cells(65536,1).Entir

32、eRow.Hidden= True我们平时一个文件来回修改的久了体积就会变的很大，比如我做的那个成绩册，没多少代码竟然1.47M，后来我想了一个办法把体积缩小了40多倍。方法:1. 打开那个比较大的文件，再重新打开一个EXECL应用程序，把工作表中的所有控件复制到新的工 作表中，然后按原来的宏指定，因为复制过来的时候宏是不能跟着过来的。2. 把那个大文件里面的所有模块拖到新的工作表里.(记住，如果SHEET和TIISWORKBOOK里有代码的话也复制过来.3. 存盘。这样，新建的这个文件就和原来的大文件一样，而且体积小了许多以下是引用 啊一在2004-11-1 9:24:00 的发言：兰老师，

33、我有两个问题：1. 点一个控件的时候就禁止Worksheet_Selectio nCha nge(ByVal Target As Ra nge)里面程序的运行，不知道有没有方法.请老师指导.-2. 只要激活指定的单元格的时候才运行Worksheet_Selectio nCha nge(ByVal Target As Ra nge) 里面的程序.-谢谢了老师！1、appliaction. enableevents = false2、if target.address $C$1 THEN EXIT SUB以下是引用 啊一在2004-11-1 9:24:00 的发言：兰老师，我有两个问题：1. 点一

34、个控件的时候就禁止Worksheet_Selectio nCha nge(ByVal Target As Ra nge)里面程序的运行，不知道有没有方法.请老师指导.-2. 只要激活指定的单元格的时候才运行Worksheet_Selectio nCha nge(ByVal Target As Ra nge) 里面的程序.一谢谢了老师！这两个你都可以用条件加以控制，比如第一个，你可以在程序中这样设置，IF Sheets(sheet1).a1=1 the n程序End if如果你想用按纽控制，你只须设置Sheets(sheet1).A1=“”恢复Sheets(sheet1).A1=1第二同理IF

35、Activecell.address=$a$1 then程序End if以下是引用 啊一在2004-11-1 9:49:00的发言：谢谢shangyu老师！好高兴又学了一招绝的.我本来的代码是间接的实现现在可以直接达到目的了呵呵，高兴死了 .sorry !打错了，是 applicati on.en ableeve nts = false即运行控件中的代码时，禁止事件(包括selectio ncha nge 事件)触发；在代码结束前需要applicati on.en ableeve nts = true 恢复以下是引用 啊一在2004-11-2 22:20:00的发言：不是啊老师，如果值和原来不

36、一样才运行程序，是的,仅值变化就行了 .辛苦了老师，我在等您！试试Private Sub Worksheet_Selectio nCha nge(ByVal Target As Ra nge)aaa = Application.Sum(Columns(5) If aaa a1 Then aaa = MsgBox(数值已改动,1 + 64, 提示)If aaa = 1 The n a1 = Applicatio n.Sum(Colum ns(5) End If End If End Sub以下是引用 啊一在2004-11-2 22:56:00的发言：Private Sub Worksheet_S

37、electio nCha nge(ByVal Target As Ra nge)aaa = Application.Sum(Columns(5) If aaa a1 Then aaa = MsgBox(数值已改动,1 + 64, 提示)If aaa = 1 The n a1 = Applicatio n.Sum(Colum ns(5) End If End If End Subshangyu老师，能不能解释一下每行的意思，比如aaa a1这里的a1是啥意思呢？是指a1单元格还是有其他意思？是把第五列的合计数先放在A1单元格中，然后当单元格发生变动时，再把后来的值和 A1的值进行比较在sheet

38、模块中(右击工作表标签查看代码)：Dim OldValue As Stri ngPrivate Sub Worksheet_Change(ByVal Target As Range) 当激活单元格后，将现在值与OldValue 比较确定是否改变值If Target = OldValue The n MsgBox Target.Value = OldValue Else MsgBoxTarget.Value OldValue End If End SubPrivate Sub Worksheet_Selectio nCha nge(ByVal Target As Ran ge)当鼠标选定单元格时

39、单元格的值赋OldValue OldValue = Target End Sub由于先前四讲的基础，本章节相对来说简单一点，现总结如下：本章主要讲述了在vba中如何控制单元格以及单元格区域、单兀格表示方法1、Range 方法(1)range(a1)单一单元格(2)range(a1:b10)连续单元格(3) range(a1:a10,b1:c10,e2:e10)不连续多单元格，相当于 union，不连续单元格引进变量Sub test()a = 1b = 3Range(a & a & :b & b &,e & a & :f & b).SelectEnd Sub注意逗号也在引号内。如果写成这样：Ra nge(a & a & :b & b, e & a & :f & b).Select他可是一个连续区域，其实质就是range(a1,c2).select他选定的是 a1:c2 连续区域2、 cells方法，cells表示单元格的集合，也就是所有的单元格，除此之外cells只能代表单一单元格，如果引入单元格区域就必须结合range解决(1) cells(行号，列标)单一单元格(2) ra nge(cells(irow,jcolum n),cells(mrow, ncolu mn)他表示一个区域3、其他(1)offse