VBA应用——遍历文件夹及其子文件夹

经常使用excel来进行数据统计和汇总分析的我们有时候需要将很多年份的数据进行汇总,如果我们使用VBA来帮助我们汇总,将会节约我们很多的时间。

如果需要汇总的文件都在一个文件夹中而不存在子文件夹,我们可以在网上找到很多简单的代码,告诉我们用VBA提供的Dir函数来获取文件列表。但如果我们需要汇总的文件有一些在子文件夹中,我们使用Dir来获取文件列表的方法就不太好用了。

怎么办呢?

下面,阁主呈上一份能够遍历文件夹及子文件夹获取文件列表的代码,以飨读者。

食用方法:

函数返回了一个Collection对象,可以通过遍历Collection对象的值来获得目录和文件列表。

 

分享一些VBA例程

 

VBA调试——逐语句/逐过程/跳出

VBA作为excel的三大神器之一,初学者经常会遇到各种错误无从下手。语法错误好解决,有些程序逻辑上的错误就需要慢慢调试,慢慢培养思维习惯了。而调试VBA代码,是发现错误,分析错误,解决错误的重要一环。

诸多VBA课程都侧重于从语法/小练习/小案例上开始讲起,这当然便于循序渐进,保持学习兴趣,但是排查错误作为编制程序,尤其是新手编制程序必经的一环,大都放在最后捎带脚提一下,阁主觉得有必要在这里对VBA的调试功能做个简单的介绍,尽量让对VBA感兴趣的朋友可以独立排除程序中的错误。

阁主在这里说下 逐语句/逐过程/跳出 三个调试指令在使用过程中的区别。

逐语句:以单个语句为单位,逐条执行.
遇到调用的自定义子函数或子过程,会进入子过程和子函数逐条执行。

逐过程:在光标所在过程以单个语句为单位,逐条执行.
遇到调用自定义子函数或子过程,会自动执行到子函数或子过程返回后的下一条语句。

跳出:光标所在过程执行到返回上一层。

 

VBA应用——按颜色求和

阁主在工作中,偶尔会碰到需要对标记颜色后的数据进行求和的情况,但是EXCEL中又没有合适的函数可以用来进行按颜色求和,于是,阁主用VBA写了一个函数,以后用起来就方便了。

使用方法:

1、打开VBA编辑器。开发工具→Visual Basic。开发工具栏默认不显示,在选项中设置其可见。

2、插入模块。打开Visual Basic编辑器后,插入→模块,双击新插入的模块打开代码编辑窗口。

3、粘贴代码。复制下方代码粘贴到代码编辑窗口

4、在单元格中调用函数。函数有两个参数,第一个是需要求和的区域,第二个参数为求和所参照的颜色标准。

 

 

VBA应用——记录更改履历

阁主在工作过程中,用excel编制一些文件,需要记录变更履历。而excel没有一个方便的版本管理工具,阁主就想到了excel自带的vba环境。何不自己写一个出来呢?

先来说说编程思路,思路对了,程序就出来了一半。

首先,我们记录更改履历,就需要记录原值,而excel工作簿和工作表的change事件不能返回原值,所以阁主采用了一个取巧的办法,当然,在使用时候就有了限制。

软件思路

①更改发生,产生change事件,并伴随参数target和sh,target是变更的区域,sh是变更的工作表。

②worksheet的change事件代码运行。这时,可以获取到更改区域和更改后的值,将更改后的值存放到数组中以备用。

③调用application.undo方法。该方法放弃修改后的新值并返回原值。将原值存入数组。

④再次调用application.undo方法。恢复目标区域的值为修改后的值。

⑤将获取到的值写入变更履历工作表中

更改履历能够处理的是使用者的键入、删除、更改操作,对于执行vba或者其他方面造成的更改无法记录。这是源于阁主使用了application.undo方法来获取更改位置的原值。而vba等操作过的数据,该方法不适用。