• 1. Accessing VBA Editor 打开Editor(Alt+F11)
• 2. Excel VBA Terminologies 术语
• 3. Message Box
• 4. Input Box
• 5. Syntax
• 6. Redim Statement
• 7. What is a Function
• 8. Sub Procedures
• 9. VBA Events
  • 9.1 WorkSheet Events
  • 9.2 Workbook Events
• 10. errors
• 11. What are Excel Objects
• 12. VBA Text Files
• 13. Option Explicit ,检查模式，能减少拼写错误，建议使用
• 14. For … Loop
• 15. Range / Methods

之前有个项目需要使用VBA，就特意学习了下，记录于此以备忘。

1. Accessing VBA Editor 打开Editor(Alt+F11)

2. Excel VBA Terminologies 术语

1. Modules, Modules is the area where code is written.( Insert » Module)
2. Procedure, The two main types of Procedures are Sub and Function.
   • Function, functions may or may not return a value.
   • Sub procedures, DONOT Return a value

3. Message Box

a = MsgBox("Do you like blue color?",3,"Choose options")
msgbox ("The Value of a is " & a)

4. Input Box

Function addtwo()
    Dim first As Integer
    Dim second As Integer

    first = InputBox("hi", "input")
    second = InputBox("hi", "input")

    addtwo = first + second
End Function

5. Syntax

• Data Types
  1. Numeric Data-Types　→　Byte / Integer / Double …
  2. Non-Numeric Data Types　→　String / Date / Boolean
• Constants

Const MyInteger As Integer = 42
MsgBox "Integer is " & MyInteger

• operator
  1. Arithmatic Operators
  2. Comparison Operators
  3. Logical Operators
  4. Concatenation Operators

• Loop Control Statements

• String Functions → Lcase / Left

• Date Functions

• Time Functions

• Array（Array Methods）

6. Redim Statement

ReDim Statement is used to Declare dynamic-array variables and allocate or reallocate storage space.

7. What is a Function

Note that a value/values can be returned with the function name itself.

Function findArea(Length As Double, Optional Width As Variant)
    If IsMissing(Width) Then        
        findArea = Length * Length
    Else
        findArea = Length * Width
    End If
End Function

8. Sub Procedures

9. VBA Events

9.1 WorkSheet Events

1. Worksheet Events are triggered when there is a change in the worksheet.
2. It is created by performing right-click on the sheettab and choosing ‘view code’

9.2 Workbook Events

Workbook events are triggered when there is a change to the workbook on the whole.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    MsgBox "New Sheet Created Successfully"
End Sub

10. errors

1. Syntax errors
2. Runtime errors
3. Logical errors

11. What are Excel Objects

Application.ActiveCell.Font.Bold = True
Workbooks.Add
Workbooks("Test.xls").Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("A1:A4").Value = 5

• Application Objects
• WorkBook Objects
• WorkSheet Objects
• Range Objects

12. VBA Text Files

1. Output
2. Using Write Command
3. VBA - Programming Charts
4. VBA - User Forms

13. Option Explicit ,检查模式，能减少拼写错误，建议使用

Option Explicit
Private Sub CommandButton1_Click()
    Dim yourMsg As String
    Cells(1, 2) = "Your first VBA program"
    yourMsg = Cells(1, 2)
    MsgBox yourMsg
End Sub

如上面，如果将yourMsg拼写错误的话，能检查出来

14. For … Loop

Option Explicit
Private Sub CommandButton1_Click()
    Dim i, j As Integer
    For i = 1 To 10
    For j = 1 To 5
    Cells(i, j).Value = i + j
    Next j
    Next i
End Sub

15. Range / Methods

Option Explicit
Private Sub CommandButton1_Click()
    Dim selectedRng As String
    selectedRng = InputBox("Enter")
    Range(selectedRng).ClearContents
End Sub

Sheet1.Cells.ClearFormats / Range(selectedRng).Select など