期末了，媳妇计算学生平时成绩好辛苦，于是准备编写Excel宏程序提高媳妇工作的效率，节省时间。于是简单记录Excel宏编程的相关知识。

基本概念

为了编写Excel宏，首先需要了解一些基本概念：

工作簿

工作簿相关概念有：Workbooks、Workbook、ActiveWorkbook、ThisWorkbook。

Workbooks集合包含excel中所有当前打开的excel工作簿，亦即所有打开的excel文件；
Workbook对应Workbooks中的成员，即其中的excel文件；
ActiveWorkbook代表当前处于活动状态的工作簿，即当前显示的excel文件；
ThisWorkbook代表其中有Visual Basic代码正在运行的工作簿。

在具体使用中可用Workbooks(index)来引用Workbook对象，其中index为工作簿名称或编号，如Workbooks(1)、 Workbooks(“年度报表.xls”)。而编号按照创建或打开工作簿的顺序来确定，第一个打开的工作簿编号为1，第二个打开的工作簿为2……。

工作表

工作表相关概念有：Worksheets、Worksheet、ActiveSheet。

Worksheets集合包含工作簿中所有的工作表，即一个excel文件中的所有数据表页；
Worksheet则代表其中的一个工作表；
ActiveSheet代表当前处于的活动状态工作表，即当前显示的一个工作表。

图表

图表相关概念有：Chart 、Charts、ChartObject、ChartObjects、ActiveChart。

Chart代表工作簿中的图表。该图表既可为嵌入式图表（包含在ChartObject中），也可为一个分开的（单独的）图表工作表。
Charts代表指定工作簿或活动工作簿中所有图表工作表的集合，但不包括嵌入式在工作表或对话框编辑表中的图表。使用Charts(index) 可引用单个Chart图表，其中index是该图表工作表的索引号或名称；如Charts(1)、Charts(“销售图表”)。
ChartObject代表工作表中的嵌入式图表，其作用是作为Chart对象的容器。利用ChartObject可以控制工作表上嵌入式图表的外观和尺寸。
ChartObjects代表指定的图表工作表、对话框编辑表或工作表上所有嵌入式图表的集合。

单元格

单元格相关概念有：Cells、ActiveCell、Range、Areas。

Cells(row,column)代表单个单元格，其中row为行号，column为列号。如可以用Cells(1,1)、Cells(10,4)来引用”A1”、”D10” 单元格。
ActiveCell代表活动工作表的活动单元格，或指定工作表的活动单元格。
Range代表工作表中的某一单元格、某一行、某一列、某一选定区域（该选定区域可包含一个或若干连续单元格区域）或者某一三维区域。可用Range(arg)来引用单元格或单元格区域，其中arg可为单元格号、单元格号范围、单元格区域名称。如Range(“A5”)、 Range(“A1:H8”)、Range(“Criteria”)。虽然可用Range(“A1”)返回单元格A1，但用Cells更方便，因为此时可用变量指定行和列。
Areas 为选定区域内的连续单元格块的集合，其成员是Range对象。

行与列

行与列相关概念有：Rows、Columns、Row、Column。

Rows、Columns分别代表活动工作表、单元格区域范围Range、指定工作表中的所有行数、列数。

基本语法

Visual Basic for Applications（VBA）是一种Visual Basic的一种宏语言，主要能用来扩展Windows的应用程序功能，特别是Microsoft Office软件。也可说是一种应用程序视觉化的Basic Script。 1994年发行的Excel 5.0版本中，即具备了VBA的宏功能。Excel宏编程主要使用VBA。

数据类型

基本数据类型

即Primary Type Data，下述列表的括号内为字节数：

Byte (1)：无符号类型，取值范围0-255
Boolean (2)
Integer (2)
Long (4)
Single (4)
Double (8)
Currency (8)
Decimal (14)
Date (8)
String
Object (4)
Variant （根据分配确定）

自定义的数据类型

相当于C语言的struct，例如：

Type 自定义类型名
     元素名  As 类型
      …
     [元素名 As 类型]
End Type

数组

Option Base 0 '數組索引值從0開始
Option Base 1 '數組索引值從1開始
Dim MyArray(10) '聲明一個數組變量，10是最大的可用的數組索引值
MyArray(5) = 101 '給數組的元素賦值
Dim Data(10,5) '聲明一個二維數組變量
Data(1,1) = "A001" '給數組元素賦值
Dim cArr(-11 To 20, 1 To 3) As String '聲明一個數組，定義數組索引值的上下界
Dim dArr() As String '聲明動態數組
ReDim dArr(0 To 5, 1 To 2) '改變動態數組的尺寸默認把原數據清除。如果保留原來的數據，必須加上參數Preserve。
                                '使用Preserve參數時只能改變最後一維的大小
If UBound(vTemp) = -1 Then
     '判斷數組變量vTemp是否為 空數組
End If
Erase MyArrar, Data 'Erase語句清除數組元素，釋放變量佔用的空間

常量

日期常量由符号“#”将字符括起来，如#2012-1-1#。

系统定义常量有3个：True、False和Null。

固有常量是编程时引用的对象库定义的常量。所有固有常量都可以在宏或VBA代码中使用。通常，固有常量通过前两个字母来指明定义该常量。来自VB库的常量则以“vb”开头。来自Access的常量以“ac”开头。可以使用对象浏览器来查看所有对象库中的固有常量列表。

可以自行定义常量。如：

1	Global Const 符号常量名称 = 常量值

控制结构

if 语句

if 條件1 then
   語句1
elseif 條件2 then
   語句2
elseif ...
    ...
else
   語句n
end if

Select Case 语句

Select Case 表達式
   Case 表達式列表1
       語句1
   Case 表達式列表2
       語句2
       ...
   Case 表達式列表n
       語句n
End Select

Do…Loop 语句

Do While或Until 條件
   語句塊1
   Exit Do
   語句塊2
Loop

Do
   語句塊1
   Exit Do
   語句塊2
Loop While或Until 條件

For…Next语句

1
2
3

For 循環控制變量=初值To 終值Step 步長
   語句塊 ‘Exit For語句可以跳出循環體
Next

For Each … Next语句

For Each 循環控制變量 In 集合變量
   語句塊 
   Exit For語句可以跳出循環體
Next 循環控制變量

跳出本次循环的continue语句

VBA没有类似C语言的continue语句。通常可如此写程序：

For 循環控制變量=初值 To 終值 Step 步長
   Do '用于模拟continue
       語句塊 
       If 条件 Then Exit Do '用于模拟continue
       語句塊 
   Loop While False '用于模拟continue
Next

With语句

1
2
3

With 對象引用
   語句塊
End With

On Error语句

1	On Error Goto 出錯處理語句的label '跳轉到出錯處理語句

或者

1	On Error Resume Next '遇到錯誤，不管錯誤，繼續往下執行

过程与函数

Sub 過程名(參數表)
    語句塊
    Exit Sub
    語句塊
End Sub

Function 函數名(參數表) As Type
   語句塊
   函數名=表達式
   Exit Function
End Function

常用内置函数

VBA的常用内置函数列表:

MsgBox
InputBox
舍入函数：Fix 向0取整,Int向下取整, Round四舍五入
Rnd 返回0-1内的单精度随机数
字符串函数：
- Filter：对字符串的一维数组的过滤
- InStr([Start, ],[, Compare])与InStrRev：查找子串
- Len 字符串长度
- Join：连接一维数组中的所有子字符串
- Left,Right,Mid 截取子字符串
- Space(数值) 生成空格字符串
- Ucase,Lcase 大小写转换函数
- Ltrim, Rtrim,Trim 删除首尾空格
- Replace
- Split：分割一个字符串成为一维数组
- StrComp：字符串比较
- StrConv：字符串转换
- String(number, character)：制定字符重复若干次
- StrReverse
日期/时间有关函数：
- Year, Month, Day, WeekDay,Hour,Minute,Second 截取日期时间分量
- DateAdd 日期/时间增量函数
- DateDiff(<间隔类型>,<日期1>,<日期2>[,W1][,W2])日期/时间的距离函数
- DatePart(<分割类型>,<日期>[,w1][,w2])时间分割函数
- DateSerial(<表达式1>,<表达式2>,<表达式3>) 合成日期；DateValue(“字符串表达式”)返回日期；
- Date,Time,Now,Timer 返回日期时间
- DateValue
- TimeSerial：由时间序列得到时间对象
- TimeValue：由时间字符串得到时间对象
- Weekday：获得日期的周几
- WeekdayName
转换函数：CBool、CByte、CCur、 CDate、 CDbl、CDec、CInt、 CLng、CLngLng、CLngPtr、CSng、CStr、CVar、CVErr、Asc(<字符串表达式>)返回第一个字符的Ascii编码值、Chr(ASCII码)返回字符、Hex、Oct、Str(<数值表达式>)返回字符串、Val(string)、Format、FormatCurrency、FormatDateTime、FormatNumber、FormatPercent、MonthName
Nz(表达式或字段属性值[,规定值])如果是空，则返回0或者””或者函数的第二个参数值
验证函数：isNumeric、isDate、isNull、isEmpty IsArray、IsError、IsMissing、IsObject
数学函数：Abs、Sqr、Tan、Atn（即atan）、Sin、Cos、Exp（e为基的指数）、Log自然对数
Array:构造一个Array对象
CallByName: get or set a property, or invoke a method at run time using a string name.
控制流：Choose:类似于C语言的select语句、IIf相当于IF-ELSE语句、Switch
Command：获取命令行参数
CreateObject：创建ActiveX对象
CurDir：返回指定驱动器的当前工作路径
由基本数学函数导出的函数：Sec、Cosec、Cotangent、Cotan、Arcsin、Arccos、Arcsec、Arccosec、Arccotan、HSin、HCos、HTan、HSec、HCosec、HCotan、HArcsin、HArccos、HArctan、HArcsec、HArccosec、HArccotan、LogN
DoEvents：暂时把CPU控制权交回给系统。
Environ：返回环境变量的值
文件操作：
- Dir：返回满足条件的所有文件、目录的名字
- EOF
- FileAttr
- FileDateTime
- FileLen
- FreeFile Function
- GetAttr：返回文件、目录的属性值
- Input：读取文件
- Loc：文件指针位置
- LOF：文件打开时的指针位置
- Seek：文件指针定位
- Spc：使用Print做position output
- Tab：用于Print函数
- Error:错误号对应的错误消息
- Windows Registry中的数据：GetAllSettings、SaveSetting、DeleteSetting、GetSetting
- GetObject：ActiveX组建的引用
- IMEStatus：返回当前Input Method Editor (IME)。
- Macintosh平台：MacID、MacScript
金融函数：
- DDB：使用double-declining balance计算贬值
- FV:计算固定利率的年金
- IPmt：计算利率
- IRR：计算利率
- MIRR：计算利率
- NPer：计算周期数
- NPV：计算net present value
- Pmt：计算支付数
- PPmt：计算本金支付数
- PV：计算present value
- Rate：利息率
- SLN：straight-line depreciation
- SYD：计算sum-of-years’ digits depreciation
Partition：返回字符串，表示一个数值名字落在各个range内。常用于SQL select语句
QBColor：颜色值
RGB：颜色值
TypeName：得到变量的类型名
VarType：得到变量的类型数

表达式

比较特殊的运算符有指数运算^，浮点除法/，整数除法\，取模运算Mod，不等逻辑比较运算<>

简单示例

在Excel开发工具中点击录制宏，生成一个VBA过程，创建如下代码计算学生平均成绩：

Sub 计算平均成绩()
'
' 计算平均成绩 宏
' 计算平均成绩
'
' 快捷键: Ctrl+l
'
    Application.Goto Reference:="计算平均成绩"
    
    Dim count As Integer ' 统计学生总数
    count = Range("A1").CurrentRegion.Rows.count ' 统计学生总数
    
    ActiveSheet.Cells(1, "E").Value = "平均成绩"
    Dim workSocre  As Double ' 课程分
    Dim videoScore As Double ' 视频分
    Dim discussScore As Double ' 讨论分
    
    For i = 2 To count
        ' 计算学生平均成绩
        workSocre = ActiveSheet.Cells(i, "A").Value
        videoScore = ActiveSheet.Cells(i, "B").Value
        discussScore = Discuss(ActiveSheet.Cells(i, "C").Value)
        ActiveSheet.Cells(i, "E").Value = workSocre * 0.6 + videoScore * 100 * 0.2 + discussScore * 0.2
    Next
    MsgBox ("平均成绩计算完成!")
End Sub


Function Discuss(discussCount As Integer) As Integer
    Dim score As Integer
    Select Case discussCount
        Case Is >= 20
        score = 100
        Case Is >= 15
        score = 80
        Case Is >= 10
        score = 60
        Case Is >= 5
        score = 40
        Case Is > 0
        score = 20
        Case Is = 0
        score = 0
    End Select
    Discuss = score
End Function

参考链接

Excel宏教程 (宏的介绍与基本使用),by 远洪.
Visual Basic for Applications,by wikipedia.
VBA——Range操作,by 风之工程师.