VBA基础

The contents are notes taken from Excel与VBA学习频道

VBE

Visual Basic Editor 进入方式有两种：

1. 快捷键Alt+F11
2. 菜单，开发工具 -> Visual Basic

VBE默认布局

除了这7个默认的组件外，还有4个是比较常用的，有监视窗口（视图-监视窗口）、对象浏览器(VBE环境下按F2)、视图菜单以及工具菜单。

数据类型

类型	字节大小	描述
Boolean	2	True or False
Byte	1	Integer from 0 to 255
Integer	2	Integer from -32,768 to 32,767
Long	4	Integer from -2,147,483,648 to 2,147,483,647
Single	4	Single accuracy floating point value
Double	8	Double accuracy floating point value
Currency	8
Date	8
Decimal	14
String	10+n
Variant	22+n

变量

1. 如何声明或者定义变量
2. 变量有哪些类型
3. 变量作用的范围和生命周期。

Dim i As Integer #本地变量
Static myGender As Boolean #静态变量
Private myWeight As Single #私有变量
Public myHeight As Double #公有变量

# 快捷声明变量
Dim i%, s$, j&
Dim i As Integer, s As String, j As Long

类型	类型声明字符
Integer	%
Long	&
Single	!
Double	#
String	$
Currency	@

常量

Const pi As Double = 3.1415926

运算符

类别	运算符
算术运算符	+, -, *, /, ^, mod 除余, \ 除数, & 连接字符, like 比较
关系运算符	<, >, <=, >=, =
逻辑运算符	and, or, not, xor(异否)
其他运算符	[] evaluate的简化, : 程序连接符, (space)_ 程序换行符

语句

1. 分支语句

• if 语句

Sub ifSentence()
  Dim k As Integer
  k=Range("A1").Value
  If k>0 Then
    MsgBox "The value of A1 is positive"
  ElseIf k=0 Then
    MsgBox "The value of A1 is zero"
  Else
    MsgBox "The value of A1 is negative"
  End If
End Sub

• Select Case 语句

Sub selectcaseSentence()
  Dim k As Integer
  k=Range("A1").Value
  Select Case Range("A1").Value
    Case Is > 0
      MsgBox "The value of A1 is positive"
    Case Is = 0
      MsgBox "The value of A1 is zero"
    Case Else
      MsgBox "The value of A1 is negative"
  End Select
End Sub

2. 循环语句

Sub Sum1To50()
  Dim i As Integer, s As Integer
  s = 0 #initiation
  # Do While ... Loop 
  Do While i <= 50
    s = s + i
    i = i + 1
  Loop
  # Do ... Loop While
  Do
    s = s + i
    i = i + 1
  Loop While i <= 50
  # Do Until ... Loop
  Do Until i > 50
    s = s + i
    i = i + 1
  Loop
  # While ... Wend
  While i <= 50
    s = s + i
    i = i + 1
  Wend
  # For ... Next
  For i = 1 To 50
    s = s + i
  Next i
  MsgBox "The sum of 1+2+...+50 is " & s
End Sub

# For Each ... Next
Sub Sum1To50()
  Dim i, j, s As Integer, arr(1 To 50)
  s = 0
  For i = 1 To 50
    arr(i) = i
  Next i
  For Each j In arr
    s = s + j
  Next j
  MsgBox "The sum of 1+2+...+50 is " & s
End Sub

3. 其他语句

• Exit系列

Exit Sub
Exit Function
Exit For
Exit Do

• Goto系列

Sub GotoEg()
  Dim x As Integer
  Dim sr
100
  sr = Application.InputBox("Input a number: ","Input Notice")
  If Len(sr) = 0 Or Len(sr) = 5 Then GoTo 100
End Sub

# Change Even Numbers in A1:A10 to "Even Number"
Sub GoSubReturn()
  Dim x As Integer
  For x = 1 To 10
    If Cells(x, 1) Mod 2 = 0 Then GoSub 100
  Next x
  Exit Sub
100:
  Cells(x, 1) = "Even Number"
  Return
End Sub

过程

1. 无参数过程

call calculateSum

Sub calculateSum()
  # codes...
End Sub

2. 有参数过程

call iSum(98, "Adam Williams")

Sub iSum(score As Integer, name As String)
  # codes...
End Sub

• 参数的引用类型：传值引用（Byval）与传址引用（Byref）

# By Value
Sub refByVal(ByVal x)
  x = x + 1
End Sub
# By Reference
Sub refByRef(ByRef y)
  y = y + 1
End Sub
# Example
Sub refExample()
  Dim k1, k2
  k1 = 1
  k2 = 1
  Call refByVal(k1)
  Call refByRef(k2)
  MsgBox k1 # k1 is 1, no change in value
  MsgBox k2 # k2 is 2, no change in address
End Sub

自定义函数

1. 自定义函数的编写

* 基础形式
Function  a1(b As Integer, c As Integer)
  a1 = b + c
End Function

* 可选参数
Function  a(c As Integer, Optional b As Integer = 1)
  a = b + c
End Function
Sub param()
  Debug.Print a(2) # a=2+1=3
  Debug.Print a(2, 1) # a=2+1=3
  Debug.Print a(2, 2) # a=2+2=4
End Sub

* 不定参数
Function SimpleSum(ParamArray a())
  For i = LBound(a) To UBound(a)
    s = s + a(i)
  Next i
  SimpleSum = s
End Function
Sub paramArray()
  Debug.Print SimpleSum(1) # 1
  Debug.Print SimpleSum(1, 2) # 1+2=3
  Debug.Print SimpleSum(1, 2, 3) # 1+2+3=6
End Sub

2. 自定义函数的调用

• 直接调用
• 单元格调用

对象、属性和方法

1. 对象

* 层次对象
Application.Workbooks("mybook.xls").Worksheets("mysheet").Range("A1:D10")

2. 属性

Sub Properties()
  Dim r
  #可读属性，读出A1单元格的值并赋于r
  r = Range("A1").Value 
  #可写属性，将r的值赋于A1单元格的值
  Range("A1").Value = r
End Sub
* 带参数的属性
Sub PramProperty()
  Dim r
  r = Range("A1").Address(row absolute:=True, column absolute:=True)
  #参数名称可省略，r=Range("A1").Address(True,True)
  MsgBox r
End Sub

3. 方法

Sub ObjectMethod()
  Range("A1").Copy Destination:=Range("B1")
  #将A1单元格复制到B1
  #如果将参数放在括号里，那么对象的方法将返回具体的值
  #可能是对象，也可能是数值
  #必须将返回值赋予某变量
  r = Range("A1").Copy(Destination:=Range("B1"))
  MsgBox r #返回True
End Sub

4. 事件
   事件是由用户或者系统触发的，可以在代码中响应的代码。比如当我们移动鼠标，打开工作薄，激活工作表，选中单元格，改变单元格的数值，点击按钮或窗体，敲击键盘等等这些都会产生一系列的事件，通过编写代码响应这些事件，当发生此类事件时，程序代码就会进行相应的操作。比如我们想每次打开workbook时都提醒当前的时间，就可以编写这最最常用的Workbook_Open事件来实现目的。

Private Sub Workbook_Open()
  MsgBox "Current Time is: " & Now
End Sub

Range 对象：单元格

属性	说明	用法举例	返回结果
Address	单元格地址
Cells	单元格对象
Column	列号
Columns	列对象
Count	单元格个数
CurrentRegion	单元格所在的非空范围
End	非空单元格
EntireColumn	单元格所在列
EntireRow	单元格所在行
Font	字体格式设置
Formula	单元格公式
HasFormula	判断是否有公式
Interior	单元格内部格式设置
Name	命名单元格区域
Offset	单元格区域偏移
Parent	返回单元格的父对象
Resize	单元格区域大小变换
Row	行号
Rows	行对象
Value	单元格内容，默认属性

常用属性
常用方法

Worksheet对象

常用属性
常用方法

Workbook对象

常用属性
常用方法