The contents are notes taken from Excel與VBA學習頻道

VBE

Visual Basic Editor 進入方式有兩種：

1. 快捷鍵Alt+F11
2. 菜單，開發(fā)工具 -> Visual Basic

VBE默認布局

除了這7個默認的組件外，還有4個是比較常用的搏讶，有監(jiān)視窗口（視圖-監(jiān)視窗口）勿锅、對象瀏覽器(VBE環(huán)境下按F2)、視圖菜單以及工具菜單唉工。

數(shù)據(jù)類型

變量

1. 如何聲明或者定義變量
2. 變量有哪些類型
3. 變量作用的范圍和生命周期。

Dim i As Integer #本地變量
Static myGender As Boolean #靜態(tài)變量
Private myWeight As Single #私有變量
Public myHeight As Double #公有變量

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

常量

Const pi As Double = 3.1415926

運算符

語句

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. 循環(huán)語句

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. 無參數(shù)過程

call calculateSum

Sub calculateSum()
  # codes...
End Sub

2. 有參數(shù)過程

call iSum(98, "Adam Williams")

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

• 參數(shù)的引用類型：傳值引用（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

自定義函數(shù)

1. 自定義函數(shù)的編寫

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

* 可選參數(shù)
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

* 不定參數(shù)
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. 自定義函數(shù)的調用

• 直接調用
• 單元格調用

對象、屬性和方法

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
* 帶參數(shù)的屬性
Sub PramProperty()
  Dim r
  r = Range("A1").Address(row absolute:=True, column absolute:=True)
  #參數(shù)名稱可省略墨林，r=Range("A1").Address(True,True)
  MsgBox r
End Sub

3. 方法

Sub ObjectMethod()
  Range("A1").Copy Destination:=Range("B1")
  #將A1單元格復制到B1
  #如果將參數(shù)放在括號里赁酝，那么對象的方法將返回具體的值
  #可能是對象，也可能是數(shù)值
  #必須將返回值賦予某變量
  r = Range("A1").Copy(Destination:=Range("B1"))
  MsgBox r #返回True
End Sub

4. 事件
   事件是由用戶或者系統(tǒng)觸發(fā)的旭等，可以在代碼中響應的代碼酌呆。比如當我們移動鼠標，打開工作薄搔耕，激活工作表隙袁，選中單元格，改變單元格的數(shù)值弃榨，點擊按鈕或窗體菩收，敲擊鍵盤等等這些都會產生一系列的事件，通過編寫代碼響應這些事件鲸睛，當發(fā)生此類事件時坛梁，程序代碼就會進行相應的操作。比如我們想每次打開workbook時都提醒當前的時間腊凶，就可以編寫這最最常用的Workbook_Open事件來實現(xiàn)目的划咐。

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

Range 對象：單元格

常用屬性

常用方法

Worksheet對象

常用屬性

常用方法

Workbook對象

常用屬性

常用方法