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

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

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

怎么办呢?

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

食用方法:

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

 

分享一些VBA例程

 

Excel应用——生成二维码

现在,二维码应用的越来越普遍了。很多时候,我们都需要生成二维码。而在Office家族中,我们可以通过VBA里的barcodectrl控件来生成条码和二维码。但是遇到中文字符的情况下,barcodectrl控件生成的效果就不理想了。

经过搜索,发现是因为excel中使用的编码解码后不支持中文,所以,阁主转向了zxing的怀抱。zxing是一个基于java语言的开源二维码/条码生成和识别库。已经有大牛将其封装为.net可用的形式zxing.net 版。阁主基于zxing.net库编写了一个excel生成二维码的加载项。

链接: https://pan.baidu.com/s/1TzaWjwKlHAjYEDKOcgS-fw 提取码: q2xu

本站小水管:excelQRcode_1.0.0.5

Excel应用——2018河南高考志愿填报

2015年,阁主的亲戚高考。高考完毕,亲人想让我给一些志愿填报的参考意见。但阁主毕竟不是教育行业,所了解的也仅仅是个别学校。如何相对全面的了解各个学校的招生录取情况,来为填报志愿做一个相对较好的选择,成为了摆在面前的一道题。

在收集资料过程中,阁主发现虽然每年高考成绩的分数线各不相同,但由于考生数量和招生计划的比例一般不会发生突变。而河南省由估分报志愿改为查分报志愿之后,高考估分偏差引起的录取不确定性被消除。因此可以用提档线人数占总考生人数比值来估计高校的热门程度,和录取的可能性。于是,阁主就制作了一份包含有3年历史数据的高考分数段分析表,期望能够给亲戚的志愿选择上提供一些参考。

自2015年起,阁主每年都会更新表格中数据,并作出分析图,发布到网络上,希望能够帮助到需要的弟弟妹妹们。

2018年河南理科高考分数段分析及志愿填报参考

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等操作过的数据,该方法不适用。