白话Excel函数公式 Office易学宝微视频教程合集(Excel+Word+PPT)
笨办法学VBA(从入门到精通) 高效办公必会的Office实战技巧
财务总监的Excel私房课 Excel数据透视表实战秘技
Excel图表神技
楼主: hustnzj

【日积月累】上11118班所学到的基础知识点

  [复制链接]
 楼主| 发表于 2012-5-8 20:52:17 | 显示全部楼层
本帖最后由 hustnzj 于 2012-5-19 19:21 编辑

2012-5-17:

一、【Excel中行高、列宽的单位分别代表多少厘米?】
首先:行高的单位是磅,但这个可不是我们平时熟悉的那个重量单位。它代表的是打印字符的高度的度量单位。
但实际上我们只需要记住:1英寸=2.54厘米,并且像素解析度(当前屏幕的像素密度)在:96像素/英寸的情况下,就可以求出来了。
首先,我们只需要在运行msgbox Application.DefaultWebOptions.PixelsPerInch,就可以知道像素解析度是否为96了。(如果不是96,在下图的像素显示也会不一样的)
然后我们在Excel中将某行按住拖拉至显示像素为96:
拖拉行高.jpg
这时显示行高为72个单位,那么行高的1个单位就=2.54/72=0.035277778厘米了。
同理,我们拖拉列宽至显示像素为96:
拖拉列宽.jpg
这时显示列宽为11.38个单位,那么列宽的1个单位就=2.54/11.38=0.035277778厘米了。

其他比较有意思的语句:
msgbox Application.InchesToPoints(1)    得到1英寸代表多少磅。
msgbox Application.CentimetersToPoints (1)  得到1厘米代表多少磅。

二、【如何在VBE中调试语句】
这一个算是基础和技巧了。在我最开始学VBA时,我是插入Stop语句到某一行代码中然后进行调试的,很笨吧?呵呵。
其实如果要进行调试,有很多方法:
1) 设置断点:
在VBE中,在要调试的代码那一行,按F9,即可,看到如下图:
F8设置断点.jpg
深红色这一行就是我们设置断点所在行,运行此代码到此行,代码就会处于中断状态:
F8设置断点(中断).jpg
这样,我们就能不管前面的语句,很方便的从我们想调试的地方调试代码了。
备注:F9设置断点可以设置多个。清除断点就是再按一下F9。清除所有断点:Ctrl + Shift + F9.

2)  逐语句执行:F8。
按一下F8就执行一句代码,在本地窗口中,我们可以看到每一步所有变量的变化情况,这是最常用的一个调试方法。
F8逐句执行.jpg

3) 运行到光标处:Ctrl + F8
这个与设置断点有些类似,就是将光标放在我们要中断的那一行,然后直接Ctrl + F8,程序就会直接运行到当前这一行。与设置断点不同的是,这个每次运行完后,就得找到这一行,再重新按Ctrl + F8,如果想要多次调试的话,还是设置断点方便些。

4)  添加监视:
这个设置在处理大循环时非常有用。
有时,我们会遇到这样的情况,一个65536次的循环,中间有一些比较复杂的处理过程,有可能语法没有错误,运行成功,但是最终的结果不对。这时,我们如果要查看到底是循环到那一次出现错误了,该怎么办?比如我们预计是到10000次时出现错误,不会吧?难道要我们一直F8到10000次循环,显然是不现实的。那么我们就可以添加监视:
在VBE的菜单中,调试--添加监视,设置监视表达式:i=10000, 中断条件为“当监视值为真时中断”,如下图:
添加监视.jpg

点确定后,就会弹出监视窗口:
监视窗口.jpg
直接运行代码,可以看到i=10000次时,程序自动中断,是不是很省事呀?太爽了吧,哈哈。


监视为真时中断.jpg
回复 支持 反对

使用道具 举报

 楼主| 发表于 2012-5-8 20:53:23 | 显示全部楼层
本帖最后由 hustnzj 于 2012-5-20 21:59 编辑

2012-5-18:

       今天发现这个帖子得了个精华,一方面感谢小月老师的鼓励,一方面感谢Leroy师父的鼓励,一方面感谢同学们对我这个帖子的支持,另一方面感觉自己这个帖子还比较单薄啊,所以,还需要继续努力,呵呵。

【如何取得动态数据源的大小?】
在我们实际工作中,经常会遇到数据源区域会变化的情况,那么我们应该如何去取得动态数据源的大小呢?
1.  CurrentRegion:
对于一个连续区域,如果要取得动态数据源的区域,最方便的方法是使用CurrentRegion属性(也就是Ctrl+Shift+8快捷键实现的效果),可以看到,这个操作可以获得一个被空白行和空白列包围的单元格区域。
连续的数据源.jpg
写法为:[A1].Currentregion (或者是:要取得区域中任意一个单元格.Currentregion)
运行MsgBox [a1].CurrentRegion.Address,可以看到此区域的范围大小。
此写法非常简单有效,只要能保证数据源是连续区域即可。

2.  UsedRange:
有时候,我们也能看到有人用ActiveSheet.UsedRange来表示,但我们应该知道ActiveSheet.UsedRange代表的是活动工作表的已经使用了的区域,也就是说,有可能不是一个连续区域,而是几个分开不连续的区域,比如:
不连续的数据源.jpg
在这种情况下,我们使用ActiveSheet.UsedRange就能够取得这些不连续区域的一个总的大区域了。


3.  End(精确制导):
可以看到,单元格.Currentregion 与 ActiveSheet.UsedRange这两种写法霸气有余而细腻不足,有时候,我们需要精确的计算出一个动态区域的大小,那么就要使用End属性了。
End属性在实际工作中用得是非常普遍的,主要是用来动态的取得一个区域的范围。一般用法为:
Cells(rows.count,1).End(3).Row,很多同学可能都是初次看见End(3)这种写法。实际上3就是xlup的常数值。
在Range.End(Direction)中,Direction这个参数有4种方向,4种值,如下图:
End属性值的含义与方向.jpg
因此End(3)实际上就相当于End键+向上的方向键 操作执行的效果。
比如,我们要求的数据源在行方向上是不断增加的并且有空行间隔,但在列方向上我们只取到第5列:
不连续的数据源(End).jpg
那么我们可以这样写:
MsgBox Range("A1:E" & Cells(Rows.Count, 1).End(3).Row).Address
有些同学看不明白了:Cells(Rows.Count,1)是什么意思呢?我们一个个分析,Cells(行序号,列序号),没问题吧?那么Rows.Count就是行序号,而在Excel03中,Rows.Count代表的是一个工作表的总行数65536,在Excel07或10中,就代表的是1048576行了。因此, Cells(Rows.Count, 1)就代表的是第一列最后一个单元格。Cells(Rows.Count, 1).End(3)就相当于在第一列最后一个单元格按End+向上键,从而到达的单元格,Cells(Rows.Count, 1).End(3).Row自然就是这个单元格的行号了,也就是我们要求的数据源区域(不管是连续还是间断的)最后一行的行号了。这样,我们就能够精确的找到我们想要的数据源区域了。

4.  由此,我们可以看到: VBA在取得最后一行行号,最后一列列号这类问题上,使用End属性比函数公式来得更加直接,简单,有效。
最后一行行号:Cells(Rows.Count,1).End(3).Row
最后一列列号:Cells(1, Columns.Count).End(1).Column
注意:这里的两个1需要根据实际情况作相应的改变。
而在我们做动态数据源的透视表的过程中,使用单元格.Currentregion比使用Offfset函数要好很多。



回复 支持 反对

使用道具 举报

 楼主| 发表于 2012-5-8 20:54:50 | 显示全部楼层
本帖最后由 hustnzj 于 2012-5-24 22:17 编辑

2012-5-19

【应用于Range对象的Find方法】

Find方法是一个避免使用VBA循环的非常好用的方法,在某些情况下,可以大幅度提高代码的效率。


1.   参数解析及应用技巧:
1) 参数解析:
expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SerchFormat)
expression      必需。它代表一个Range 对象,这就是查找区域。Find方法在这个区域中查找并返回符合条件的第一个单元格。如果没有查找到匹配单元格,就返回Nothing
参数就是查找条件了。很多的,晕了吧?
Find参数.jpg


可以看到,大部分参数都与用户界面的“查找和替换”对话框中的设置一致。
这些参数都要特别注意,某个参数的常量值没用对,结果就有可能不对。
比如LookAt:=xlWhole表示的是完全匹配,也就是精确查找。LookAt参数默认是xlPart,部分匹配,也就是模糊查找,这个区别一定要注意,否则有时候就会出错。

在这个对话框中,只有AfterSearchDirection这两个参数没有,下面将详细说下。

After参数:
最开始学用Find方法时,多半都不会注意到这个参数,也就忽略了,但如果不指定本参数,搜索将从区域的左上角单元格(也就是第一个单元格)之后(也就是第二个单元格)开始,一开始并不会查找第一个单元格,如果这里有我们要的数据,那么就有可能遗漏掉了。
解决方法:After:=查找区域.Cells(查找区域.Cells.count)
这个用法确保是从查找区域的第一个单元格开始查找。这是因为After表示查找过程将从After参数之后的单元格开始。我们这里设置成查找区域的最后一个单元格:查找区域.Cells(查找区域.Cells.count),那么最后一个单元格的下一个单元格是什么?是第一个单元格。 为什么是第一个单元格呢?
我们要知道Find方法的默认查找顺序:从指定单元格开始查找,从上到下,从左往右,找到最后一个单元格后,它会自动环绕回第一个单元格。(见动画)

Find的环绕特性.gif

SearchDirection参数:
顾名思义,搜索的方向。
xlNext 默认值 (一般我们都是用这个)
但有时候xlPrevious这个参数会有意想不到的好处:
我们前面知道:End方法只能找到某一列的最后一行,而对于多列,如果不能保证到底以那一列作为主列(即不管如何都会填写内容的),如下图:

Find最后一行.jpg
那么,我们可以使用Find方法配合xlPrevious参数来找到多列中的最后一行的行号:
lastRow = Sheets(“haha”).Cells.Find("*", SearchDirection:=xlPrevious).Row


2.  Find方法的优缺点:
1) 优点:只查找到匹配条件的单元格,避免使用VBA循环进行一一对比。
2) 缺点:
Find方法会将查找的参数记录下来,所以每次在使用Find时要格外小心。每次使用此方法后,参数 LookInLookAtSearchOrder MatchByte, MatchCase 的设置都将被保存。
这个缺点有可能会造成你在代码中查找没问题,但只要使用了这个代码,用户界面的“查找和替换”对话框的参数设置就会被更改。在使用手动的“查找和替换”时又要手动去改这些参数,很是麻烦。
因此,可以变通一下,
比如说先查找要查找的内容,查找完毕后,再查找一次空值,并设置参数为默认值:
With Range(“B:B”)
        Set rng = .Find(What:="haha", After:=.Cells(.Cells.Count), LookAt:=xlWhole)
        …………
        …………
        …………
        .Find What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
                                                                       xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , MatchByte:=False, SearchFormat:=False
End With

这样,就能查找框的参数设置就回到默认的了。

3.  Find方法的重复使用:
可以使用 FindNext FindPrevious 方法重复搜索。
当搜索到指定的搜索区域的末尾时,本方法将绕回到区域的开始继续搜索。发生绕转后,若要停止搜索,请保存第一个找到的单元格地址,然后依据该保存地址测试每个后续查找到的单元格地址。具体例子如下:
下面这个示例是在单元格区域 A1:A500 中查找值为 2 的单元格,并将这些单元格右边一个单元格的值变为 5

  1. With Worksheets(1).Range("a1:a500")
  2.     Set c = .Find(2, LookIn:=xlValues)
  3.     If Not c Is Nothing Then
  4.         firstAddress = c.Address   '保留第一个满足条件单元格的地址
  5.         Do
  6.             c.offset(,1).Value = 5
  7.             Set c = .FindNext(c)   '按Find方法的条件接着查找,相当于用户界面“查找和替换”对话框的“查找下一个”
  8.         Loop While Not c Is Nothing And c.Address <> firstAddress   '定义循环终止条件:当找不到(只有一个满足条件)或者再次查找到第一个满足条件单元格(表示已经绕完一圈)
  9.     End If
  10. End With
复制代码

FindPreviousFindNext 的功能相似,只是方向不同,就不再敷述了。

5.25   在火云老师的提醒下,发现了上面这个例子其实是错的(自己检讨下,过于相信帮助文件了,汗……)
实际上应该分成两种情形来看:
  1. Sub text()   '替换的情形
  2.     With Worksheets(1).Range("a1:a25")
  3.         Set c = .Find(2, LookIn:=xlValues)
  4.         If Not c Is Nothing Then
  5.             firstAddress = c.Address
  6.             Do
  7.                 c.Value = 5
  8.                 Set c = .FindNext(c)
  9.             Loop While Not c Is Nothing   ' 当找不到(只有一个满足条件)
  10.         End If
  11.     End With
  12. End Sub

  13. Sub TEST1()   '查找不替换的情形
  14.     With Worksheets(1).Range("a1:a25")
  15.         Set c = .Find(2, LookIn:=xlValues)
  16.         If Not c Is Nothing Then
  17.             firstAddress = c.Address   '保留第一个满足条件单元格的地址
  18.             Do
  19.                 c.Offset(, 1).Value = 5
  20.                 Set c = .FindNext(c)   '按Find方法的条件接着查找,相当于用户界面“查找和替换”对话框的“查找下一个”
  21.             Loop While c.Address <> firstAddress   '再次查找到第一个满足条件单元格(表示已经绕完一圈)
  22.         End If
  23.     End With
  24. End Sub
复制代码

再上个附件来吧,要是再错了也好指正,呵呵。
Find的两种不同情况.rar (6.87 KB, 下载次数: 26)
回复 支持 反对

使用道具 举报

 楼主| 发表于 2012-5-8 20:56:19 | 显示全部楼层
本帖最后由 hustnzj 于 2012-5-23 21:19 编辑

2012-5-20(快结课了,大家一定要坚持下去呀,呵呵)

【最常见的人机交互】

1. Msgbox函数:
这个很简单,显示一个消息提示框:
语法:
MsgBox(prompt[, buttons] [, title] [, helpfile, context])
Prompt:提示文字:这个是一个字符串,最大长度约为1024个字符,可以用Chr(10) & Chr(13)来换行。
例子:
  1. MsgBox "我爱VBA" & Chr(10) & Chr(13) & "11118班"
复制代码
Msgbox1.jpg

Buttons:这个参数比较有趣。因为它的值(值的具体含义参考帮助文件)决定了消息弹出框的显示元素:
1)(0–5):按钮的数目及形式。
2)(16, 32, 48, 64):图标的样式。
3)(0, 256, 512):缺省按钮。
4)(0, 4096):消息框的强制回应。
如果省略,则 buttons 的缺省值为 0(即只显示OK按钮,且不显示图标,且此按钮为缺省按钮,且应用程序强制返回;应用程序一直被挂起,直到用户对消息框作出响应才继续工作。)
例子:

  1. MsgBox "我爱VBA" & Chr(10) & Chr(13) & "11118班", 4 + 32 + 0 + 0
复制代码
Msgbox2.jpg
这个参数不仅可以写为4+32+0+0这种表达式,还可以直接写成和的形式:36

  1. MsgBox "我爱VBA" & Chr(10) & Chr(13) & "11118班", 36
复制代码

可以看到效果与上面是相同的。要注意的是:这个参数如果写成和的形式,那么其和的值必须要是上面这4组参数才行。比如下面这种写法就是无效的:

  1. MsgBox "我爱VBA" & Chr(10) & Chr(13) & "11118班", 1 + 2 + 3 + 4
复制代码


Title:顾名思义,就是标题的意思了,呵呵。
Msgbox3.jpg

还有两个参数,helpfile,context因为用得很少,就不多说了,呵呵。

下面是我带14214班时Hylees同学的一个Msgbox小作品:(这里的vbCrLf就是Chr(10) & Chr(13)的意思)

  1. Sub Text()
  2.     MsgBox _
  3.     "      {@}  *  {@}" & vbCrLf _
  4.          & _
  5.            "    {@}  *  {@}  * {@}" & vbCrLf _
  6.          & _
  7.            "{@}  *  {@}  *  {@}  *  {@}" & vbCrLf _
  8.          & _
  9.            "  \ {@}  *  {@}  * { @} /" & vbCrLf _
  10.          & _
  11.            "    \  \ \  l   /  /  /" & vbCrLf _
  12.          & _
  13.            "      \ \ \ Y  /  / /" & vbCrLf _
  14.          & _
  15.            "        \ \ l / /" & vbCrLf _
  16.          & _
  17.            "          \ Y /" & vbCrLf _
  18.          & _
  19.            "          > = <" & vbCrLf _
  20.          & _
  21.            "        / / * \ ", , "Happy Valentine's Day!"
  22. End Sub
复制代码
Msgbox4.jpg
怎么样,不错吧,哈哈。

2.  InputBox 函数:
语法:
InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])
前后各两个参数代表的含义都与Msgbox函数相同,这里就不说了。
Default 代表的是默认值。
Xpos 代表对话框的左边与屏幕左边的水平距离。忽略,就是水平居中。
Ypos  代表对话框的上边与屏幕上边的距离。忽略,就是垂直居中。

  1. Sub a()
  2.     InputBox "请输入内容", "hustnzj", "默认值"
  3. End Sub
复制代码


3.  Excel的InputBox方法。
由于对InputBox直接F1并不会出来这个方法的帮助,所以一般很少被注意到。实际上,这个方法非常有用。它前面接的是Application。
当我们写成Application.InputBox,然后对InputBox进行F1,InputBox方法(而非InputBox函数)的帮助才会弹出。这再次告诉我们,要想查到完整的帮助信息,最好使用F2调出对象浏览器,然后查找关键词,这样才能确保一览无遗。
Inputbox(F2).jpg

语法:
expression.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)
前面的几个参数都与上面的大同小异,就不罗嗦了。
最重要的是Type这个参数:
Type      Variant 类型,可选。指定返回的数据类型。如果省略此参数,对话框将返回文本。可以是下列值的其中一个或其中几个的和。
含义
0公式
1数字
2文本 (字符串)
4逻辑值 (TrueFalse)
8单元格引用,作为一个 Range 对象
16错误值,如 #N/A
64数值数组

Type 可以是以上允许值的和。例如,对一个可输入文本和数字的输入框,可将 Type 设置为 1 + 2。
应用1:单元格区域引用:
比如在自己的程序中,想要做出选择区域的效果,就可以这样用:
  1. Sub a()
  2.     Set rng = Application.InputBox("请用鼠标选择数据源区域", "hustnzj", Type:=8)
  3.     MsgBox "数据源为:" & rng.Address, , "hustnzj"
  4. End Sub
复制代码
Inputbox1.jpg
Inputbox1-1.jpg



应用2:直接通过Type限制输入内容的类型,不用像Inputbox函数再使用If进行类型的判定。

  1. Sub a()
  2.     Application.InputBox "请输入销售金额:", "hustnzj", Type:=1
  3. End Sub
复制代码

Type为1就限制了对话框中必须输入数字。如果为文本,就弹出如下错误:
Inputbox2.jpg





回复 支持 反对

使用道具 举报

 楼主| 发表于 2012-5-8 21:05:07 | 显示全部楼层
本帖最后由 hustnzj 于 2012-5-25 18:44 编辑

2012-5-21

一、【循环语句补充之一】

1. 课时4视频中主要讲了两种循环结构:For-Next循环(计数循环),Do-Loop/Until循环(Do循环)。
实际上,还有一种循环While-Wend循环(当循环),个人感觉与Do While循环差不多。但Do While循环还可以使用Exit Do随时退出循环,所以这个我用得比较少了,呵呵。

2. 这里,For-Next与Do-Loop/Until做一个区别:
For-Next是按照计算出来的次数执行的循环:这个次数的计算公式为=((终值-初值)\步长)+1。
比如For i=1 to 9 step 3
         ……
       Next i
这个循环次数为=(9-1)\3+1=3,当i=1,4,7时,执行循环,当i=10时,大于终值,循环结束。它的循环终止条件是:循环变量i是否超过终值。

Do-Loop/Until是不用管具体多少次循环,只要设置好循环结束条件(这个条件是我们自己来设置的)就可以了注意,使用这种循环,一定要使循环结束条件在循环体的执行过程中能被改变,不然就死循环了。当然,进入死循环也不用害怕,只需要Ctrl + Break就可以强制终止程序,如果是笔记本的童鞋找不到Break,还可以直接关闭Excel的进程来终止。(说远了,呵呵)

3. 此外,有一个小技巧比较有意思,那就是For-Next循环中的“循环体”是可以省略的,这种循环叫“空循环”,使用“空循环”可以实现“暂停”的效果。但是,这样也有一个缺点,那就是你很难控制时间延长的效果,因为不同的机器性能不同,运算速度不同,For-Next循环的次数是规定好的,那么最后的时间延迟的效果肯定也不完全相同。
这时,更好的办法是使用API函数Sleep,这样就与机器性能无关了,呵呵。

[code=vb]Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)  ‘声明API函数,就像我们声明变量一样,只有这样,我们才能在VBA代码中使用它。
Sub a()
    Sleep 1000   ‘1000代表1000毫秒
    MsgBox "延时1s"
End Sub
[/code]

4. 多重循环:
在实际工作中,我们通常都要使用多重循环才能完成工作。所谓多重循环,就是循环内套循环。外循环套内循环。内外循环的类别可以相同,也可以不同。
比如,我们要遍历一个区域 Range(Cells(2, 2), Cells(1001, 101))并且分别从1赋值到20000,那么最容易想到的方法就是使用一个二重循环:

  1. Sub a()
  2.     t = Timer
  3.     With Range(Cells(2, 2), Cells(1001, 101))
  4.         For i = 1 To 200
  5.             For j = 1 To 100
  6.                 .Cells(i, j) = (i - 1) * 100 + j
  7.             Next j
  8.         Next i
  9.     End With
  10.     MsgBox Timer - t
  11. End Sub

  12. Sub b()
  13.     t = Timer
  14.     For i = 1 To 200
  15.         For j = 1 To 100
  16.             Range(Cells(2, 2), Cells(1001, 101)).Cells(i, j) = (i - 1) * 100 + j
  17.         Next j
  18.     Next i
  19.     MsgBox Timer - t
  20. End Sub
复制代码
这里我们要把 Range(Cells(2, 2), Cells(1001, 101)) 放在循环体外面,这样我们就会少引用很多次 Range(Cells(2, 2), Cells(1001, 101)) 这个对象,程序的效率会好一点点,当对象引用越少,那么你代码的效率也就越高。

二、【数组之威力初显】
接上面的例子。最快的方法还是使用数组来一次性赋值。
  1. Sub Arr()
  2. Dim ar(1 To 200, 1 To 100)
  3.     t = Timer
  4.     For i = 1 To 200
  5.         For j = 1 To 100
  6.             ar(i, j) = (i - 1) * 100 + j
  7.         Next j
  8.     Next i
  9.     Range("B2").Resize(UBound(ar), UBound(ar, 2)) = ar
  10.     MsgBox Timer - t
  11. End Sub
复制代码

大家可以看到使用数组来赋值一次性对区域赋值,与在循环中对单元格对象一一赋值的区别:简直一个是光速,一个是蜗牛啊!呵呵。
VBA的学习过程中,只要你学会了数组,那你编写的代码的运行效率就一下子从单车提升到了火箭,你离高手的水平也就越来越近了。哈哈。
回复 支持 反对

使用道具 举报

 楼主| 发表于 2012-5-8 21:07:07 | 显示全部楼层
本帖最后由 hustnzj 于 2012-5-26 20:36 编辑

2012-5-22

【循环语句补充之二】

1.  Exit 语句:
在循环语句中,有时会根据需要提前退出整个循环。那就是Exit语句:在For-Next循环中,使用的是Exit For,在Do-Loop循环中,使用的是Exit Do语句。
除了在循环中可以使用外,还可以在过程中使用。那就是Exit Sub 和 Exit Function语句。
使用Exit的作用:
1) 减少循环次数,提高代码运行效率.
[code=vb]
Sub test()
    Dim i As Byte, key As Byte
    key = 1
    For i = 1 To 10
        If key = Cells(i, 1) Then
             MsgBox "A1:A10中" & key & "在" & Cells(i, 1).Address(0, 0) & "单元格" & Chr(10) & Chr(13) _
             & "循环次数:" & i
             Exit Sub    '加了Exit,可以看到循环次数大大减少
        End If
    Next i
End Sub
Sub test_withoutExit()
    Dim i As Byte, key As Byte
    key = 1
    For i = 1 To 10
        If key = Cells(i, 1) Then
            MsgBox "A1:A10中" & key & "在" & Cells(i, 1).Address(0, 0) & "单元格"
        End If
    Next i
    MsgBox "循环次数" & i - 1
End Sub

[/code]

2) 在错误处理时比较有用。(这个放在错误处理语句中再说吧,呵呵)


2.  GoTo语句。
使用GoTo语句可以改变程序执行的顺序,可以跳过某一段程序去直接执行另一段程序。其常见写法:GoTo line (line可以为数字,也可以为一个单词)
[code=vb]
Sub test1()
Dim i As Byte, lastRow&
  For i = 1 To Worksheets.Count
        With Worksheets(i)
                lastRow = .Cells(Rows.Count, 1).End(3).Row
                If lastRow = 1 Then GoTo 100
                 .Cells(10, 10) = "haha"   '这里可以换成是自己要进行的操作处理
        End With
100:
    Next i
End Sub

[/code]
在这个程序中,我们遍历本工作簿中的所有工作表,但只有当某个表的lastRow<>1时,我们才会对该表进行某些操作。如果lastRow = 1就直接跳过这些操作步骤,执行Next i。由此可见,使用Goto语句可以使程序结构变得清晰简单。但Goto语句也不能滥用,否则会造成不必要的麻烦,呵呵。

【数组之入门:概念与静态数组的定义】
在前面,我们已经看到了数组操作与单元格操作在运行效率上的天壤之别。因此,学习数组,是我们在学习VBA时绕不过的一道坎。刚开始大家可能一看到数组就犯晕,实际上这只是心理作用,是我们对不熟悉事物的一种恐惧。我可以负责的告诉大家,数组入门其实很简单。
1. 数组的概念:
所谓数组,就是同类型数据的有序集合。英文又写作“Array”。
大家要知道,一般情况下,我们要对数组进行赋值,提取,处理,通常都要对数组元素一一赋值,提取,处理。那么,什么是数组元素呢?
数组中的变量称为数组元素。比如Array(1, 2, 3, 4)这个数组,其中的1,2,3,4就是其元素。

数组元素的表示方法:ar (i) 。其中ar为数组名,i为数组的下标。这个ar也可以写成其他的任何字符串,如arr,arrr,MyArr,只要符合VBA的命名规则就可以了。这是一维数组。
二维数组元素的表示方法为:ar (i, j)
三维数组元素的表示方法为:ar (i, j, k)。
当然,还可以多维,这里就不一一列举了。

2. 静态数组的定义:
数组的定义与变量的定义几乎相同。什么?没有说变量的定义?这个问题在网上到处都有,限于篇幅,这里就不再重复了,不明白的同学自己查下资料吧,呵呵。
定义数组的几种写法:
1) Dim ar(10)   
这里定义ar这个数组有10个元素?对么?不一定,因为这种写法这是写出了下标的上界(就是可以使用的最大值),但并没有说明下标的下界(可以使用的最小值)。如果在模块最开始使用了Option Base 1(也就是声明了此模块中普通数组下标的下界为1),那么是10个元素。如果不声明,那么默认为Option Base 0(也就是此模块中所有普通数组下标的下界为0)。一般情况下,多数人都习惯自己的数组下标从1开始,因此我们只要使用Option Base 1,就能达到目的。
那么,在哪种情况下,即使使用了Option Base 1,也不能使得数组的下标为1呢?
[code=vb]
Sub test3()
    Dim ar
    ar = Split("1/2", "/")
End Sub
[/code]
按F8执行到End Sub,打开本地窗口,可以看到即使已经声明Option Base 1,但ar的下标仍然是0。
Split产生的数组.jpg
当然,除了Split函数产生的数组外,还有其他的特殊情况,这些就要大家在日后来自己发现了,呵呵。

2) Dim ar (1 to 10)
这种写法的好处就在于:明确指出这个数组ar的下标下界为1,上界为10,那么就不存在Option Base的影响了。
而且,使用To可以使得数组下标不仅为0或1,还可以为其他值,如-100, 100.
这两种方法定义数组通常都叫做静态数组,也就是在编译时就分配了内存的数组。

动态数组的定义比较复杂,明天继续,呵呵。

5-22.rar

8.15 KB, 下载次数: 25

回复 支持 反对

使用道具 举报

 楼主| 发表于 2012-5-8 21:11:47 | 显示全部楼层
本帖最后由 hustnzj 于 2012-5-31 23:01 编辑

2012-5-23最近一直比较忙,帖子更新速度也慢了,不好意思了,呵呵。

一、【数组之入门:动态数组的定义】
前面讲了如何定义一个静态数组,有静必有动。

1.  什么是动态数组呢?
1)在程序运行时才分配内存;
2)用变量作为下标;
3)必须分两步走:第一步,在模块最开始或者过程的最开始写成Dim ar()这样的形式:空的圆括号,没有下标,表明这是一个动态数组;第二步,在过程中,使用Redim ar(1 to k) 这样的语句来定义:k是变量,代表这个数组ar的下标是可以变化的。

2.  Redim语句的格式:
Redim [Preserve] ar(下标)As 类型

3.  Redim Preserve与Redim有什么区别呢?(是否保留原数据)
Redim preserve能在改变原来数组ar最末维(就是最后一维,比如二维数组,那么就是第二维)的上界时,保留ar中的数据。通常用在ar大小暂时不明,但最后又必须精确大小的地方,一般用循环来确定ar的最终大小。
Redim 则不能保留ar中的数据。
[code=vb]
Option Base 1
Sub a()    'Redim
    Dim arr()
    arr = Array(1, 2, 3)
    ReDim arr(1 To 4)
    MsgBox arr(1) & "," & arr(2) & "," & arr(3) & "," & arr(4)
End Sub

Sub a1()  'Redim preserve
    Dim arr()
    arr = Array(1, 2, 3)
    ReDim Preserve arr(1 To 4)
    MsgBox arr(1) & "," & arr(2) & "," & arr(3) & "," & arr(4)
End Sub
[/code]

4.  Redim语句只有对于动态数组或者数组变量(也就是变量为数组)才能使用,对于静态数组是不能使用的。
[code=vb]
Sub test()
'    Dim ar()      '动态数组,正确
'    Dim ar        '数组变量,正确
    Dim ar(1 To 2)      '运行下去会报错:"数组维数已定义"
    ReDim ar(1 To 10, 1 To 2)
End Sub
[/code]

可以看到,我们把”Dim ar”这句中的ar看成是一个数组变量,因为它本身是一个变量,而一个变体型的变量是可以为任何数据类型的,包括数组,因此,它的用法和动态数组几乎是一样的。我们可以来看下面的例子:
[code=vb]
Sub test1()
    Dim ar()
    ar = [A1:B10].Value
    ReDim ar(1 To 10, 1 To 2)
    ReDim ar(1 To 3, 1 To 3, 1 To 4)
End Sub
Sub test2()
    Dim ar
    ar = [A1:B10].Value
    ReDim ar(1 To 10, 1 To 2)
    ReDim ar(1 To 3, 1 To 3, 1 To 4)
End Sub
[/code]

F8可以看到动态数组ar()与数组变量ar的变化过程是一模一样的。而且我们还可以发现Redim语句可以随意改变一个动态数组或数组变量的维数及数组的大小,但却不能保留数组内已有的元素。
为了保留元素,我们只能使用Redim Preserve语句,但一旦使用了Preserve这个关键字,那么你数组的维数就被固定了,不再能改变数组的维数了。只能改变数组末维的大小了。如果改变了非最后一维的下标上界,就会弹出“下标越界”的错误提示。
[code=vb]
Sub test3()
    Dim ar
    ar = [A1:B10].Value
    ReDim Preserve ar(1 To 10, 1 To 2)
    ReDim Preserve ar(1 To 10, 1 To 3)   'Preserve可以在保留原来数组内元素的前提下,改变最后一维的下标上界。
'    ReDim Preserve ar(1 To 20, 1 To 3)   '但是,如果改变了非最后一维的下标上界,就会弹出“下标越界”的错误提示。
    ReDim Preserve ar(1 To 10, 1 To 3, 1 To 4)   '而且,也不再能改变数组的维数了.
End Sub
[/code]

需要特别注意的是:ReDim可以对动态数组ar()和数组变量ar使用,但Redim Preserve只能对动态数组ar()使用!
[code=vb]
Sub test4()
'    Dim ar   '直接对变量进行ReDim Preserve是行不通的。
    Dim ar()
    ReDim Preserve ar(1 To 10, 1 To 2)
End Sub
[/code]

5.  变通解决ReDim Preserve不能改变非最后一维的下标上界的办法。
一般,对于二维数组,我们使用ReDim Preserve只能改变其末维的大小,而不能改变第一维的大小。但我们在Excel中工作表区域的行又恰好与第一维对应,数据增长方向又一般都是行方向,这样我们就要使用Transpose来解决问题了。
[code=vb]
Sub test5()
    Dim ar, br(), cr(), i&, k&, j&
    ar = [A1:B20]
    For i = 2 To UBound(ar)
        If ar(i, 1) = "ID1" Then
            k = k + 1
            ReDim Preserve br(1 To 2, 1 To k)
            br(1, k) = ar(i, 1): br(2, k) = ar(i, 2)
        Else
            j = j + 1
            ReDim Preserve cr(1 To 2, 1 To j)
            cr(1, j) = ar(i, 1): cr(2, j) = ar(i, 2)
        End If
    Next i
    [E4].Resize(UBound(br, 2), UBound(br)) = Application.Transpose(br)
    [E4].Offset(UBound(br, 2)).Resize(UBound(cr, 2), UBound(cr)) = Application.Transpose(cr)
End Sub
[/code]


二、【Transpose的局限性】
1) 不能转置超过65536行的二维数组,否则一定会报错。
[code=vb]
Sub test6()          '这里,我们还可以发现,使用数组变量ar,有很大的灵活性。
    Dim ar
    Range("a1:B65536").Value = 12445
    ar = Range("a1:B65536")
    ar = Application.Transpose(ar)
End Sub
Sub test7()   '这个会报错“类型不匹配”。
    Dim ar
    Range("a1:B65537").Value = 12445
    ar = Range("a1:B65537")
    ar = Application.Transpose(ar)
End Sub
[/code]

2) 对区域转置,超过65536行时,结果比较古怪,尚未发现原因:
[code=vb]
Sub test8()
    Dim ar
    Range("a1:B65537").Value = 12445
    ar = Application.Transpose(Range("a1:A65537"))
End Sub
[/code]
结果居然变成了一个元素。将ar = Application.Transpose(Range("a1:A65537"))改为ar = Application.Transpose(Range("a1:C1"))结果也不相同。
进一步实验发现:
[code=vb]
Sub test9()
    Dim ar
    ar = Application.Transpose([A1].Resize(65536 * 3, 3))
End Sub
[/code]
将ar改为 = Application.Transpose([A1].Resize(65536 * 3, 2)),结果居然又不同,原因暂时无法解释。

3) 转置时如果未声明数组类型,则数组元素的字符长度不能超过256个,否则报错。声明数组类型为string,则无此限制。
[code=vb]
Sub test10()                    '未声明数组类型,元素字符长度为256时就开始报类型不匹配的错误。
    Dim ar
    ar = [B1:C1]
    ar = Application.Transpose(ar)
End Sub
Sub test11()
    '未声明数组类型,元素字符长度为256时就开始报类型不匹配的错误。
    Dim ar(1 To 2), i As Byte
    For i = 1 To 2
        ar(i) = String(256, "A")
    Next
    [D1].Resize(2, 1) = Application.Transpose(ar)
End Sub
Sub test12()
   '声明数组类型为string就没有这个问题,即使长度超过256
    Dim ar(1 To 2) As String, i As Byte
    For i = 1 To 2
        ar(i) = String(10000, "A")
    Next
    [D1].Resize(2, 1) = Application.Transpose(ar)
End Sub
[/code]

5-23.rar

409.25 KB, 下载次数: 31

回复 支持 反对

使用道具 举报

 楼主| 发表于 2012-5-8 21:12:43 | 显示全部楼层
回复 支持 反对

使用道具 举报

 楼主| 发表于 2012-5-8 21:21:48 | 显示全部楼层
回复 支持 反对

使用道具 举报

 楼主| 发表于 2012-5-8 21:23:44 | 显示全部楼层
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 入学

本版积分规则

快速回复 返回顶部 返回列表