VBA操作Excel的一个简单网络应用

前言：

Excel基本是用来处理表单数据的，里面自带一些工具和函数，熟练运用工具和函数可以让Excel变得比较高效。但是，有时面对一些繁琐的数据它就无能为力了，因此微软推出了VBA开发，VBA处理Excel数据既高效也比较简单，适合编程小白上手。

VBA 是基于Visual Basic 发展而来的，它们具有相似的语言结构。VB是设计用于创建标准的应用程序，而VBA是使已有的应用程序(EXCEL等)自动化；VB具有自己的开发环境；而VBA必须寄生于已有的office应用程序，是其中的一个开发模块。

效果图：

screen1.png

screen2.png

screen3.png

上图的这个功能并不具有VBA开发功能的一个代表性，仅供练手，功能是输入关键字后在Excel表中显示关键字的描述列表。

VBA在Excel中的应用

VBA能实现Excel本身应用公式等不能实现的更多非常强大的自动化功能，小到自动算数、做表，大到办公平台，如：
1.数据自动录入客户系统
2.系统网页数据抓取
3.任务自动分配
4.状态跟踪管理
5.邮件自动批量发送
6.报告自动做成
7.问卷调查（配合Outlook）
8.项目管理（配合Access数据库）
9.能力测试系统
10.知识库
11.问询管理系统
12.员工报销管理系统

代码分析：

Dim listDataArr As Collection 'excel显示所需的数组数据Sub testVBA()
Dim URLStr As String 'API
Dim originalStr As String '网络请求后的原始字符串数据
Dim dataDic As Dictionary '字符串转化为的json，即dic
keyword = Application.InputBox("请输入需查询的关键字：")
'随意使用的一个免费API用于下面的练习
URLStr = "http://baike.baidu.com/api/openapi/BaikeLemmaCardApi?scope=103&format=json&appid=379020&bk_length=600&bk_key=" & keyword '执行get请求
originalStr = LXHelpModel.XMLHttpGET(URLStr)
If Len(originalStr) = 2 Then
MsgBox ("请输入有效的关键字，如：vba、互联网、app等")
Exit Sub
End If 'json解析，将服务器返回的字符串数据转化为json（Dictionary），这里使用的是www.json提供的官方解析方法（使用别的方法解析时含有数组的数据将得到JScriptTypeInfo类型的数据，不可使用）
Set dataDic = JSON.parse(originalStr)
Set listDataArr = dataDic("card") updateExcelDataEnd Sub
'更新excel数据
Function updateExcelData()
'清空旧数据
ActiveSheet.Cells.ClearContents
'ActiveSheet.Cells.Interior.ColorIndex = 0 '将数据更新到excel指定的cell中
Dim item As Dictionary
For i = 1 To listDataArr.Count
Set item = listDataArr.item(i)
ActiveSheet.Cells(3 + i, 1) = item("name")
ActiveSheet.Cells(3 + i, 3) = item("format")(1)

ActiveSheet.Cells(3 + i, 1).Font.Color = RGB(0, 200, 50)
'ActiveSheet.Cells(3 + i, 1).Interior.Color = RGB(0, 50, 200)
Next
End Function


此效果在LXTestModel模块中实现，将testVBA过程指定为一个按钮的宏，可以通过点击点击宏按钮选择testVBA再运行，也可以直接点击查询按钮即可运行testVBA方法。
代码分析在注释中已经写的很明了了，主要通过ActiveSheet.Cells(3 + i, 1) = item(&＃8220;name&＃8221;)给表格的指定位置赋值。

遇到的坑：

解析json时，若字段里包含有数组时，则无法使用此字段，类行为JScriptTypeInfo。

产生这个结果的原因是创建的ScriptControl对象，指定为JS语言去解析导致的。须要用JSON网站提供的专门解析json的库去解析，下载地址www.json.org，找到VB-JSON下载，然后需导入cJSONScript.cls、cStringBuilder.cls、JSON.bas这三个文件到工程中，然后Set dataDic = JSON.parse(originalStr)这样使用，传入一个json字符串，将返回Dictionary类型的字典，这个类型iOS开发者很熟悉，java中叫做map。

导入VBJson的三个文件后，提示错误：“用户定义类型未定义”，代码并定位到方法RStoJSON

需将类型ADODB.Recordset改为Object使编译通过。

使用Dictionary后提示错误：“用户定义类型未定义”

产生这个结果的原因是VBA本身不支持此数据类型，需引用库，按如下操作点击：工具->引用->勾选“Microsoft Scripting Runtime” ->确定。不建议使用Set dic=CreateObject("scripting.dictionary")这种方式创建

使用Utf8ToUnicode方法解乱码时，提示错误：运行时错误‘424’：要求对象

在此文件第一行添加如下代码

Private Declare Function MultiByteToWideChar Lib "kernel32" (ByVal CodePage As Long, ByVal dwFlags As Long, ByVal lpMultiByteStr As Long, ByVal cchMultiByte As Long, ByVal lpWideCharStr As Long, ByVal cchWideChar As Long) As Long
Private Const CP_UTF8 = 65001
Option Explicit


网络请求时使用Inet方式，错误代码提示定位在Inet1，提示错误：变量未定义

Inet是一个用于网络请求的控件，需添加此控件到Execl中，按如下操作：开发工具->最后一个插入控件（在图片右边）->在下拉列表中找到“Microsoft Internet Transfer Control”并点击->点击在任意表格位置插入。若下拉列表中无“Microsoft Internet Transfer Control”， 则需先安装MSINET.OCX，再操作。

运行宏无效

可能是运行后出现错误，修改完代码后未点击重新设置

VBA开发刚研究两天还是菜鸟，不当之处还望指正。

源码请点击github地址下载。