我也冒个泡，7月11日的编程日记

hym5235492

2008-7-11 星期五

终于又完成了一个独立的模块。这一个模块是对每日下载的邮件、小信封进行登记、编号、搜索、打开调用。通过这个模块的编制，基本上掌握了VBA对文件的操作技巧。

心得：

1、 VBA 对文件的操作既可以利用标准功能，也可以利用文件对象模型，我觉得这两者可以结合使用。我的方法是：

先用 subfolder 调出指定目录下所有的子目录，
然后用 getfolder（）.file 取出每一个目录下所有的文件，
然后用 getbasename 取出每一个文件的基本名，
再用标准功能的 filedatetime 取出文件的修改日期。
再把对这些文件的信息（文件基本名、修改日期等等）进行适当的处理，存入sheet里面。
为了能够调用这些文件，在登记文件基本名之后，把该文件的超链接也写入文件基本名所在的单元格。

掌握了对文件的操作技巧，我感觉 VBA 的编程更加灵活了，所有的模块都可以单独写，然后进行简单的拼装就可以了。
而且，理论上说，可以在excel里面对整个电脑的文件系统进行操作，换一句话说：完全又可能把 Excel 做成一个绝对个性化的【资源管理器】。
——当然这只是理论上可行，应该不会有人这样干的。

2、文件的记录总是不断更新的，因此，每一次更新都需要检查是否已经做了记录，而这个比较的过程很难把握。如果不加限制，我粗略估算了一下，程序在极限情况下要循环将近一百万次，这显然是不现实的。因此，先对记录进行排序，然后设定一些条件进行比较，使得这个比较的过程被限制在很有限的范围内。如此程序才是一个切实可行的程序。

所以说，写代码并不难，难得就是设定这些条件，你得考虑如何根据实际的工作需要去设立。

即便如此，最终的代码也还是很复杂，4层的for循环是家常便饭，for 循环里总是少不了分支，分支里差不多也都嵌套着一个for 循环。为什么会如此复杂呢？

—— 我觉得，一个真实的程序（而不是教科书上的程序），总是对应着实际工作中很多的细节需要，这些需要基本上不会是单一的，而是复合的、交叉的，所以，程序为了避免各种错误的操作，就不得不考虑 N 多的操作可能性，为这些可能分别编写分支。

for循环是计算机的精髓，而如何使得for循环的次数减少则是算法的精髓，让for循环次数减少的关键，也还是来自于实际工作中存在的诸多限制，利用这些限制，我们可以极大地压缩运算的步骤。这些限制，就是我们在实际工作时的边界条件，如何设定边界条件，是很有趣、也很烦人的一件事。

3、制作了一个模仿 google 风格的搜索界面，每一次搜索的时间大约3～5秒（还是比较长的），在搜索过程中，屏幕象死了一样，因此，又编了一个进度条，这样等待搜索结果的时候就不会觉得枯燥了。进度条的运用使模块看上去更加象那么一回事，自我感觉非常得爽（^_^）。

4、但是进度条的使用也带来了一个新的问题：为了使用进度条，就得引入新的窗口界面，并且得把搜索的代码写在进度条的窗口里，由于搜索的结果是一个范围，这个范围是用数组进行传递的，于是就出现了这样一个问题 —— VBA 提示“数组不可以作为公共变量”。为了不影响编程的进度，折腾了一会之后，我用起了 VBA 编程最后的那根救命稻草 —— 通过sheet表进行数据的中转。

把搜索的关键字通过“”（空格）进行拆分，放到65535行里，把搜索的结果（记录的范围）放到256列里，每次搜索前、搜索完毕之后，及时把记录清除，如此根据关键字搜索的功能就顺利实现了。（11日晚上写这个搜索功能，前前后后折腾了将近4个小时。）

5、编写这个模块，耗费的时间比例和以前很类似：

（1）、构思表格（记录的格式）、初步构思界面，1天；
（2）、精确构建界面，3～4个小时；
—— 这两步基本上决定了你所写代码的复杂程度。
（3）、编写核心代码，使得模块能够初步运行起来。一般而言，这包括窗体装载和激活时的初步设置、窗体要显示的各项内容、翻页、记录的修改和添加、必须手输的数据录入界面（能用组合框的尽量用组合框），窗体退出时的设置。编写这个模块一般都在半天左右。
（4）、调试 —— 这又是一个漫长的过程。前前后后时间累计约8小时。一边调试，一边不断地修改代码，有时甚至要对整段的代码段进行重写。而且常常会在调试的过程中，发现需要增加一些功能，也就是要整段整段地添加代码。

如此，编写一个独立的功能的时间比例是：
构思：入手：核心代码：调试 ＝＝ 5：1：1：2

可见，编程的过程是一个两头大、中间小，开头比结尾大的过程。而最重要的，还是开始的酝酿和构思的过程，这个过程决定着从局部模块到整个系统的水准。

hym5235492

QUOTE:

以下是引用hym5235492在2008-7-12 2:48:37的发言：

2008-7-11 星期五

先用 subfolder 调出指定目录下所有的子目录，
然后用 getfolder（）.file 取出每一个目录下所有的文件，
然后用 getbasename 取出每一个文件的基本名，
再用标准功能的 filedatetime 取出文件的修改日期。
再把对这些文件的信息（文件基本名、修改日期等等）进行适当的处理，存入sheet里面。

其实当初不应该这么写代码，直接用标准功能的filesearch 调出每一个文件，效率更高，实现得也更完美，文件对象模型只在 getbasename的时候用得着，其余的都不必用。

subfolder 在程序的主流程里根本用不着，只在界定进度条的范围时用得上。（程序文件所在的目录下没有下载的邮件，因此进度条的范围是所有文件数 ＋子目录数－程序文件目录下的文件数。但事实上，如果不用getfolder.().files，也就不存在这些加加减减的事了。）

也许，没有特别的需要，对文件进行操作用标准模块就足够了，省得还要引用 scripting runtime。

模块已经在部门里开始使用了，以后有机会再改吧。

willin2000

[em17]

hym5235492

2008年10月6日～9日

 
 那些在编的时候感觉很不错的代码，等过了一段日子，当眼界提高之后，往往会觉得很垃圾，而且时间一长有些代码的含义还得费很大的力气去回忆，因此，一个业余的编程爱好者对比较大的程序进行大的手术是需要很大的决心和毅力的。

 经过很长一段时间的酝酿，终于下定决心对系统的主要代码进行一次大修。毕竟系统中的一些内容天天在用，编程时的一些狭隘思路严重影响了操作的易用性。

 这一次改代码，还有一个很大的促成因素是：在国庆之前，我去一家软件公司咨询编写数据库的有关事宜，在交流过程中，受到了很多的启发。

 
 这一次主要修改了这几个部分：
 1、将第一个核心模块，即每日数据的汇总统计，彻底换一个思路，不再用VBA去循环积算，而是通过在一张大表中设立所有需要积算或累计的量（每一个量为一列），然后将基本的数据赋给大表，再将积算的公式赋给相应的单元格，最后将这个大表视为一个数据库，将需要的数据导出到各相关的分表、统计表等等当中。
    —— 如此一来，与原来写的代码相比，就有了一个很重大的进步：对于初始数据的录入，如果出现错误可以进行反复录入或修改，而不会对数据库产生影响。而且利用了 Excel 自身的强大计算能力，运行的速度也明显提高了。

 2、将几个常用的单独的子模块嵌入到大的系统当中，并通过自定义快捷键将30多个表格、20多个窗口、上百个命令按钮糅合起来，如此一来，操作明显方便了很多，而且看起来越来越“象”一个真正的数据库系统了。

 到此为止，这个系统对于我这样的业余编程爱好者来说就算是初步完成了。尽管大部分代码都很垃圾，但是我仍然很为自己自豪，毕竟能够独立写出这样一个相对比较完整的系统，在普通人群中也算得上很稀罕了。

 
 在本帖的最后，我想对打算用 Excel VBA 开发实用的程序的人分享几个很基本、然而确实又是非常重要的经验心得：

 1、记得在刚开始编程的时候，看到 bengdeng 提醒一位网友，尽量把程序运行涉及到表格都放在一个文件里，减少对别的工作簿的操作，当时没觉得有什么，等到几个独立的子模块在运行时经常发生冲突之后，才渐渐地体会越来越深刻。大部分事件、子程序运行都依赖于从 →工作簿→工作表的定位，当运行前，当前的activeworkbook因为各种各样很容易发生的因素被改变了，可想而知程序运行时会出现什么现象。为了避免这个现象，在没有把几个模块糅合到一个文件里之前，我花费了N多的心血，然而结果依然很让人懊丧。

 所以，只要条件允许，就尽可能把相关的工作表都放在一个工作簿里，不用怕文件大、运行慢，运行慢毕竟比没事就出错要好。

 当然，在一个已经在使用的工作簿中添加模块、代码存在风险，可能在调试代码过程中把正在用的文件、数据破坏掉。这个问题容易解决，方法就是在写代码之前把思路想好、想透彻，或者先在一个备份文件里写，写好了、调试得差不多的时候，把代码通过 →导出→导入的方法（或者直接复制→粘贴）写入正式的文件里。

 2、尽可能利用 Excel 自身的强大的计算功能，以简化代码、提高运算效率，具体的方法就是上面说过的，尽可能多地给单元格赋公式，让 Excel 自己去算，而不是通过 VBA 做复杂的循环运算再给单元格赋值。虽然理论上VBA可以进行绝大部分的计算，但正像伊里奇的书中所说的：“现钟不打，倒去敛铜。”

 3、在写代码的时候，尽量用系统提供那些基本的命令，少用、至少是慎用扩展命令。这个道理很明显，你引用的库越多，系统移到别的电脑不能运行、出错的概率就越大。实际上，大部分的代码都用不到很复杂的命令、属性、参数。各类网站上提供的很多程序，看起来非常的花哨，但是我对其实用性都很怀疑。
 真正的实用的系统，不在于代码是否复杂、命令（函数）是否奇妙，而在于是否有足够的容错性、是否能够考虑到足够多的边界条件并提前预防、是否能够提供尽可能多的数据关联。对于业余编程爱好者而言，在为自己个人、或者所在单位编写实用的小软件的时候，最大的精力大部分都将放在对各种边界条件、对各种数据关联方式的考虑上。
 因此，代码的复杂、新奇，甚至所谓的算法是否精妙，都不是最重要的。
   —— 当然，这只是对业余人士而言，对于专业人员来说当然有高得多的要求。

 4、一定、一定、一定要在代码中加入足够多的批注，而且，只要你的 Excel VBA 允许，就尽可能用中文变量！！！ 这是一个绝对好的习惯。我想只要是真正编写过比较复杂的程序的人都能够深深地体会到这一点。
 我深深认同王轶男（《编程黑马真言》的作者）、伊里奇（《透视Excel VBA应用与开发》的作者）的观点，在代码的档案化方面，无论怎样的“变态”，最终都将被证明是值得的。
 我的代码由于有大量的中文变量，而且每一个最小的代码单元段都有明确的批注，在给很多单元格连续进行赋值时甚至对每一个单元格的含义都进行说明，因此，代码的含义即使是不会编程的人也能猜出个八九不离十。

 
 总而言之，作为业余编程爱好者的我们编写实用的小软件，是为了实实在在的“用”，而不是为了参加比赛，或向别人展示一个多么精美的玩具。

alzeng

   我VBA学得不好，不过自己觉得从无到有也有了点小小的进步。楼主的思路很清晰，思维模式也很干练，值得效仿和学习。

  继续关注楼主的编程日记。

forrest

LZ的逻辑条理性很强，关注日后的更新。

咖啡可乐

还真的向楼主好好学习了

hym5235492

编程的时候，程序与程序之间需要传送变量，这种传送让没有更多编程经验的人感到神秘，更感到畏惧，以至于我在刚开始的时候，能避开就尽量避开。

虽然从编写系统的第一天，我就已经知道可以通过sheet（“”）.range（“”）.value来传送数据，但是当时觉得，如此编写代码肯定是太业余了，专业人士肯定有更牛B的方法。于是挖空心思寻找传送变量的方法，除非是实在没辙了，才通过单元格传送参数。而通过单元格传送参数的方法也就被我称为“最后的救命稻草”。

随着系统逐渐地变大，需要传送的参数越来越多，而通过设置全局变量来传送参数日益显示出其不足之处，终于有一天我认识到，那根所谓的“最后的救命稻草”，其实才是真正的、根本的参数传递的渠道。而全局变量与之相比，其实只是更适合于局部模块内部的，甚至只是适合于连续运行的几段代码之间传递参数的一种局部方法。

在编制稍大型的系统时，这种对比是非常明显的。

举一个简单而直接的例子，打开一个窗体，初始化时给一些文本框赋初始值，由于大部分的文本框都会有change事件，那么在读初始值时，这些事件就会被触发，而在这个时候，这些事件往往是不需要的。那么避免的方法显然就是用一些标记来进行分辨。
而这些标记，用全局变量几乎是无法实现的。因为在你启动这个窗体的时候，产生标记的代码很可能已经彻底退出了，即便你在那些进程中定义了全局变量，也赋了值，但是由于那些进程退出了，变量也就被释放了，因此你无法获得那些参数。
而把这些参数放到一个专门的 sheet表 的 range里，那么，它们就成为了系统的标记，当你启动一个新的窗体时，就可以调用这个标记。比如，在窗体初始化的时候，这些标记值为 0，使change事件被屏蔽；初始化完毕，你立即将其改为 1，然后在窗体运行期间，你再改变文本框的值时就可以立即触发change事件。

认识到这一点之后，我干脆在系统里专门建立了一个sheets（“系统标记”），专门存放系统所有的标记，并对系统代码进行了一次全面的修改，改完之后发现，系统变得非常得灵活，稳定性也大大地提高，看起来也越来越象一个专业的系统了。于是我开始明白，所谓的“最后的救命稻草”，其实真的就是大型编程的一个基本要素，而不是万不得已时才应急的东西。

想象一下windows系统的注册表，不也是这个意思吗！！！我深信，所有的大型软件、系统，一定都有很多的类似的系统标记。

回过头来再看程序之间的变量传送，我们就会有一种醍醐灌顶的感觉，原来所谓的变量传送竟然是那么的简单，一点也不神秘。

alzeng

非常不错的心得，谢谢分享，^_^

hym5235492

前几天我准备写一个在窗体上显示图表的模块，结果用Google查遍了整个网络，关于利用Excel VBA实现此功能的网页寥若晨星，而反复搜寻之后也终于让我有所得，有2位网友的解决方法比较好：

1、        http://club.excelhome.net/thread-345009-1-1.html ，
第4楼的HLAI网友提供方法很直接，其特点是容易实现，几乎不需要用VBA编程。

2、        http://club.excelhome.net/thread-243472-1-1.html
网友andysky 在 Excel home 论坛分享的一个方法，这个方法非常精妙，虽然复杂一点，但是跟用chartspace控件比起来要轻松多了。
这种思路是：
创建图表 →修改图表→存为图形文件→导入窗体的图形控件中→删除图形文件

编程中我认为比较重要的几个要点：
1、绘制图表前一定要先删除所有图表；
2、准备的数据必须有列标题，否则生成的图表很难看
3、一定要给生成的图表命名，这样后续的处理就比较容易了，否则非常麻烦。
4、生成的图表修改到多大合适，需要反复调试才行，不同的人有不同的口味。
5、别忘了删除图形文件，那只是一个临时的玩意。

我正是在andysky 网友解决思路的基础上，为自己刚开发的一个管理系统增加了一个完整的能耗数据的图表分析模块。（代码将近1500行，目前已经集成了31张表格）

真要感谢伟大的网络！如果没有网络，没有Excel吧吧主bengdeng的帮助，没有网上那么多网友的分享，很难想像我这样的计算机外行居然能够编写出一个近万行代码的管理软件。

在此，我也贴出我写的关于生成图表的代码中最核心的那一段，为其他网友再次编写类似代码时提供参考，主要是把我通过录制宏弄清楚的部分图表代码的含义与大家做一个分享。

调用该子程序前，需要编写为sheets("图表专用")准备数据的代码，相信对于需要这段代码的人而言没有困难。
能耗分析.分输量趋势图...等等之类都是窗体上创建的图形控件，当然可以随心所欲地另建。

Sub 绘制图表(图表类型 As Integer, 图表对象 As Integer)

     Dim 数据区域 As Range
     Dim 新图表 As ChartObject
     Dim 文件名 As String
     
     '选中要绘制图表的区域
     On Error Resume Next
     Set 数据区域 = Range(Cells(2, 1), Cells(数据区域最后一行, 数据区域最后一列))
     Set 数据区域 = Range(Cells(2, 1), Cells(数据区域最后一行, 数据区域最后一列))
     数据区域.Activate
     Sheets("图表专用").ChartObjects.Delete  '删除已经存在的图表
     Set 新图表 = Sheets("图表专用").ChartObjects.Add(100, 0, 500, 290)    '左边距，顶边距，宽，高
     'Charts.Add              '绘制图表
     Select Case 图表类型
         Case 1
             新图表.Chart.ChartType = xlLineMarkers     '图表类型为折线图
         Case 2
             新图表.Chart.ChartType = xlColumnStacked   '图表类型为堆积柱形图
         Case 3
             新图表.Chart.ChartType = xl3DPie           '图表类型为三维饼图
         Case 4
             新图表.Chart.ChartType = xlColumnClustered '图表类型为默认的柱形图
     End Select
     '图表数据源
     新图表.Chart.SetSourceData Source:=数据区域, PlotBy:= _
         xlColumns            '图表数据源，产生在列
     新图表.Chart.Location Where:=xlLocationAsObject, Name:="图表专用"  '嵌入图表
     '保留X轴刻度，保留Y轴刻度
     If 图表类型 <> 3 Then
         With 新图表.Chart
             .HasAxis(xlCategory, xlPrimary) = True  '有X轴
             .HasAxis(xlvalue, xlPrimary) = True     '有Y轴
         End With
         'X、Y轴线的类型自动
         新图表.Chart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
         '调整X轴格式
         '新图表.Chart.Axes(xlCategory).TickLabels.NumberFormatLocal = "m"".""d"  '日期格式为 “月.日”
         With 新图表.Chart.Axes(xlCategory).TickLabels
         .Alignment = xlCenter
         .Offset = 100
         .Orientation = xlUpward
         .ReadingOrder = xlContext
         .NumberFormatLocal = "d""日"""
         End With
         With 新图表.Chart.Axes(xlCategory).TickLabels.Font
             .FontStyle = "常规"
             .Size = 10
             .Strikethrough = False
             .Superscript = False
             .Subscript = False
             .OutlineFont = False
             .Shadow = False
             .Underline = xlUnderlineStyleNone
             .ColorIndex = xlAutomatic
             .Background = xlAutomatic
         End With
     End If
     
     '关闭图例选项
     新图表.Chart.HasLegend = False
     '对于能耗组分堆积图或是饼图，必须显示图例选项
     If 单位能耗.value = True Or 图表类型 = 3 Then
         新图表.Chart.HasLegend = True   '打开图例选项
         新图表.Chart.Legend.Position = xlRight  '靠右
     End If
     '如果绘制饼图，重新设置图例位置、绘图区位置及范围
     If 图表类型 = 3 Then
         '图例位置向上调，
         With 新图表.Chart.Legend
             .Left = 420
             .Top = 40
         End With
         '绘图区适当拉伸
         With 新图表.Chart.PlotArea
             .Left = 25
             .Top = 85
             .Width = 445
             .Height = 180
         End With
     End If

     '图表缩放
     ActiveSheet.Shapes(新图表.Name).ScaleWidth 0.61, msoFalse, msoScaleFromTopLeft
     ActiveSheet.Shapes(新图表.Name).ScaleHeight 0.98, msoFalse, msoScaleFromTopLeft
     '设置边框
     With Selection.Border
         .Weight = 2
         .LineStyle = -1
     End With
     '颜色
     With Selection.Interior
         .ColorIndex = 34
         .PatternColorIndex = 1
         .Pattern = 1
     End With
     '图表边框倒圆角
     Sheets("图表专用").DrawingObjects(新图表.Name).RoundedCorners = True
     Sheets("图表专用").DrawingObjects(新图表.Name).Shadow = False
     '设置标题内容
     With 新图表.Chart
         .HasTitle = True
         .ChartTitle.Characters.Text = WS2.Range("A1").Text
     End With
     '设置标题格式，红色是3
     新图表.Chart.ChartTitle.Select  '选中标题
     'Selection.AutoScaleFont = True
     新图表.Chart.ChartTitle.AutoScaleFont = True
     With Selection.Font
         .Name = "黑体"
         .FontStyle = "常规"
         .Size = 14
         .Strikethrough = False
         .Superscript = False
         .Subscript = False
         .OutlineFont = False
         .Shadow = False
         .Underline = xlUnderlineStyleNone
         .ColorIndex = 3
         .Background = xlAutomatic
     End With
     '设置绘图区的颜色
     新图表.Chart.PlotArea.Select
     With Selection.Border
         .ColorIndex = 16
         .Weight = xlThin
         .LineStyle = xlContinuous
     End With
     '设置绘图区的填充色及填充方法
     Selection.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=1, _
         Degree:=0.231372549019608
     With Selection
         .Fill.Visible = True
         .Fill.ForeColor.SchemeColor = 2
     End With
         
     ' 将绘制并重新设置后的图表保存为一个临时的图片文件 → 导入窗体
     文件名 = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
     新图表.Chart.Export Filename:=文件名, filtername:="gif"
     ' 将临时的图片文件 → 导入窗体
     Select Case 图表对象
         Case 1
             能耗分析.分输量趋势图.Picture = LoadPicture(文件名)
             能耗分析.分输量趋势图.Visible = False
             能耗分析.分输量趋势图.Visible = True
         Case 2
             能耗分析.输差趋势图.Picture = LoadPicture(文件名)
             能耗分析.输差趋势图.Visible = False
             能耗分析.输差趋势图.Visible = True
         Case 3
             能耗分析.单位能耗趋势图.Picture = LoadPicture(文件名)
             能耗分析.单位能耗趋势图.Visible = False
             能耗分析.单位能耗趋势图.Visible = True
         Case 4
             能耗分析.能耗指标趋势图.Picture = LoadPicture(文件名)
             能耗分析.能耗指标趋势图.Visible = False
             能耗分析.能耗指标趋势图.Visible = True
         Case 5
             能耗分析.绘制其它图表.Picture = LoadPicture(文件名)
             能耗分析.绘制其它图表.Visible = False
             能耗分析.绘制其它图表.Visible = True
     End Select
     
     '删除临时的图片文件
     VBA.Kill 文件名

End Sub

Function 数据区域最后一行()
    Sheets("图表专用").Select
    Cells(2, 1).Select
    Selection.End(xlDown).Select
    数据区域最后一行 = ActiveCell.Row
End Function

Function 数据区域最后一列()
    Sheets("图表专用").Select
    Cells(2, 1).Select
    Selection.End(xlToRight).Select
    数据区域最后一列 = ActiveCell.Column
End Function

大家注意程序里的这一段重复的代码：
  On Error Resume Next
     Set 数据区域 = Range(Cells(2, 1), Cells(数据区域最后一行, 数据区域最后一列))
     Set 数据区域 = Range(Cells(2, 1), Cells(数据区域最后一行, 数据区域最后一列))

这个代码还是有一点意义的。我在Excel吧和bengdeng版主探讨过这个问题（见http://www.excelba.com/bbs/Show.asp?bid=1&aid=2399 ），如果在调用该子程序之前，在程序里加一行
Sheets（”图表专用”）.select
确实是可以避免产生 Error ‘1004’，但是我对其它类似的代码反复调试之后，认为象这样即便在发生错误的时候也能使程序继续运行的方法，还是很值得保留在程序里的。