微软的产品非常富有效率和生产力,虽然网上经常有人指摘微软的产品,贬低其“傻瓜化”的产品设计。但我认为,这更多属于一种偏见。用什么技术并不重要,人类最终的发展,就是要解放生产力。微软的产品能够让人们更富有效率的工作、写作、写代码,那么就有其巨大的价值。当你为一家公司工作,就要看这个产品是否对公司有利,而不能仅仅出于自己的个人喜好。
我也曾经熟悉各种编程工具,但是我认为,对于绝大多数人来说,如果要进行数据分析,微软的办公产品配合宏/VBA已经够用了。
我有时会亲自录入网上的一些认为有用的数据到access里面,然后用vba进行分析,输出表格。下面这篇文章,介绍一些使用access + vba进行数据分析的知识和技能。
主要内容
1、背景
2、宏
3、VBA
1、背景
这篇文章将主要讲述如何使用VBA配合ACCESS进行数据分析.
ACCESS、EXCEL相对于那些昂贵的分析工具,可以提供给个人投资者更好的性价比。我们需要能够熟练使用EXCEL和ACCESS辅助自己的数据分析。
ACCESS是小型数据库,许多人使用EXCEL来处理数据,但是当数据越来越多,需要更多的统计、存储、管理数据的功能,EXCEL就变得越来越不适应需求了。一般这个时候,需要用上更强大的数据库管理系统。
2、宏
ACCESS 2016的宏功能有了极大的飞跃,可以有临时变量,也有了简单的错误处理机制。许多简单的工具,都可以借助宏完成,而不需要编写VBA代码去实现。
我们常使用宏的方式是,在设计视图中,添加一个按钮,然后在单击事件中调用/编写宏。当你打开宏编辑器,可以看到ACCESS已经提供了非常多的自动化的宏操作供你选择,你只需要点点鼠标,就能够完成任务,比如打开窗体、打开报表、打开表、输出数据到EXCEL文件、输出数据到文本文件。
你可以对宏进行分组,这样方便调用宏。
你可以设置宏的执行条件。
你可以使用临时变量,这个变量还可以和VBA交互。
你可以把宏转化为VBA代码,这是一种很好的学习VBA代码的方式。
ACCESS提供了数据宏,有before和after change等各种事件,帮您保证数据的一致性、有效性。
由于已经拥有了VBA的强大功能,那么建议仅仅使用宏来实现简单的任务,如果是复杂的逻辑,放到VBA里面去实现更好,这样也方便后续功能需求的展开。
3、VBA
VBA的语法和VB类似,但是和VB.net并非一样的东西。access内建了VBA引擎来执行你编写的代码。VBA的编程,主要是基于事件触发的机制去编写代码。也就是说access应用程序是由事件驱动的,access对象可以响应各种类型的事件,你不需要掌握所有事件,有用到的时候去查就可以了。不要去修改事件过程的命名和参数,Access VBA依赖于Object_Event命名约定将过程绑定到对象的事件。
例如以下的例子,打开某个窗体
DoCmd.openform "frmMyform", acNormal
DoCmd是一个能执行许多任务的对象。openform是要执行的任务。而frmMyform是参数,是打开窗体的名字。acNormal表示打开窗体是以正常的视图打开。
VBA可以调用Windows API,和ADO(activX data object),DAO(Data access objects)交互。
模块/过程/函数
模块是一个容器,你可以组织类似的代码、过程到一起。模块是独立于其他access对象的,其他对象(窗体、报表)可以访问存储在模块里面的代码。我们可以创建两类模块:模块(标准模块)和类模块。标准模块和类模块的区别在于类模块支持事件。
如下的例子创建一个标准模块并创建一个过程
选择创建 --> 模块, 将打开一个窗口,输入如下的代码创建一个过程
点击“执行子过程”可以调用这个过程。上图第一行代码Option Compare Database是一个声明语句,声明字符串比较的方式,使用默认即可。
可以马上调用某个过程,通过立即窗口(按ctrl +G) ,输入过程名回车,就会调用过程,方便调试。
使用Dim声明变量的时候,最好指定数据类型,对于文本使用String,对于整数使用Long,对于有小数的数字使用Double,对于True/False值使用Boolean。
如下的例子创建一个函数
函数和过程类似,区别在于函数有返回值。
编写代码的时候,换行符是 “空格+下划线” ,使用换行符可以让你的代码更容易阅读。
你可以把多个语句写在同一行,需要用冒号分隔。
智能感知
自动完成:输入doc,然后按ctrl + speace,会自动输入docmd,ctrl+space快捷键可能和你的输入法切换快捷键有冲突,调整之即可。
语法简述
和VB类似,有各种循环、条件控制语句。
对于select case语句,只有第一个匹配的语句执行,其他匹配的语句被忽略。
有两种循环语句:do ... loop for ... next
do until xxx
xxx
loop
for i = 1 to 10
xxx
next i
对象和集合
ACCESS VBA不是面向对象设计的语言,但是经常要和对象/集合打交道。集合是一个容器,用于保存某种特定的对象类型的所有成员(集合本身也是一个对象)。
尽管对象浏览器不显示特定的代码示例,但多数情况下,查看与属性、方法或事件关联的语法可能足以开始编写VBA代码,或弄清楚对象的详细信息。
VB里常访问对象是通过类似如下的语法
Mycollection.item(9).someproperty = value
例如
MyForm.Controls.Item(1).Text = "Name"
一般vb中就使用点操作符来不断访问下一级的对象/属性/方法。
例如,遍历控件
for i=0 to me.controls.count -1
next
for each mycontrols in me.controls
next
变量、数据类型
要养成好习惯,先声明变量(option explicit强制,或在选项中设置),并且指定变量类型。
一般使用string、currency、date、double、long、boolean这些常用的类型即可。
模块内的变量可以被模块内的过程/函数访问。
如果在模块中定义public变量,这个变量可以被其他模块内的过程访问
应该把全局变量集中放在一个模块中定义,方便管理。
三种作用域:public,private,local。public能被应用的任何过程访问,private仅能被模块内的过程访问,local变量仅能在过程内访问。建议简单使用access,不要去使用什么public,public static变量,尽量使用局部变量,如果有全局配置,那么就使用全局的只读常量,或者从数据库读取配置。范围越小越好。
定义全局常量的语法
public const consname as type = constvalue
数组默认下标从0开始, dim array(10) as long,你也可以定义上下标范围,例如dim array( 1 to 10) as long
使用ADO对象访问数据
一般使用ADO对象来操作数据,ADO比DAO更高级,更强大。ADO和DAO对象模型不同于access对象模型。ADO基于microsoft的ActiveX技术只需要几个对象就可执行Access应用程序中几乎所有的数据访问任务。使用ADO对象需要引用ADO库(VBA编辑器|工具|引用)。
连接字符串可能比较难写,可以使用excel获取access数据,然后查看excel的连接属性。
我整理修改的访问数据集的访问
Option Compare Database
Option Explicit
Private Sub OpenRecordset()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sql As String
On Error GoTo ErrHandler
Set conn = New ADODB.Connection
conn.Open CurrentProject.Connection
Set rst = New ADODB.Recordset
sql = "select * from table_name;"
rst.ActiveConnection = conn
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Open sql
Do Until rst.EOF
Debug.Print rst!LastName
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
conn.Close
Set conn = Nothing
ErrExit:
Exit Sub
ErrHandler:
MsgBox "Error is " & Err.Description
LogError "cmdError_Click", Err.Number, Err.Description
Resume ErrExit
End Sub
Public Sub LogError(ProcName As String, _
ErrNum As Integer, ErrDescription As String)
Dim sFile As String, lFile As Long
Dim aLogEntry(1 To 6) As String
Const sLOGFILE = "Error.log"
Const sLOGDELIM = "|"
On Error Resume Next
sFile = CurrentProject.Path & "" & sLOGFILE
lFile = FreeFile
aLogEntry(1) = Format(Now, "yyyy-mm-dd hh:mm:ss") 'Date stamp
aLogEntry(2) = ErrNum
aLogEntry(3) = ErrDescription
aLogEntry(4) = ProcName
'The following may be NULL
aLogEntry(5) = Screen.ActiveForm.Name
aLogEntry(6) = Screen.ActiveControl.Name
Open sFile For Append As lFile
Print #lFile, Join(aLogEntry, sLOGDELIM)
Close lFile
End Sub
对以上代码的一些说明
如果不指定CursorType和LockType,默认情况下,ADO会将记录集创建为adOpenForwardOnly/adLockReadOnly类型的记录集。
有两种方法引用单个字段,一个是感叹号!,例如,adRs!CustomerID, adRs!Company。一个是flelds集合,例如,adRs.Fields("CustomerID").Value。
adOpenStatic指的是使用静态类型光标打开记录集,数据是静态的,不能向记录中添加新纪录。
adLockOptimistic允许其他用户处理通过ADO代码锁定的记录。
代码片段
判读记录集为空,可以通过判断记录集记录数为0来确定,或者使用如下的方式
If adRs.BOF And rs.EOF Then
Debug.Print "No records to process"
Exit Sub
End If
调试和记录错误
如果是编写比较长的代码,编写了部分后,可执行编译,以尽早发现错误。
传统调试技术有:1、使用msgbox弹出窗口;2、使用debug.print向立即窗口输出信息。
可以使用断点调试,查看执行过程变量的变化。
你应该在代码中有错误处理机制。
閱讀更多 跨市場分析 的文章