Visual Basic for Application

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   ‘The note of Visual Basic for Application
   ‘VBA 是由微软开发出来的一种宏语言，寄于对Windows桌面程序功能的扩展，和VB语言结构相似,是VB的子集；
   ‘VBA常见的作用：
       ‘1.规范用户的操作，控制用户的操作行为;
       ‘2.操作界面人性化，方便用户的操作;
       ‘3.多个步骤的手工操作通过执行VBA代码可以迅速的实现;
       ‘4.实现一些VB无法实现的功能;
       ‘5.用VBA制做EXCEL登录系统;
       ‘6.利用VBA可以Excel内轻松开发出功能强大的自动化程序.
   ‘VBA中常见的数据类型：
       ‘1.  Integer    整型     %
       ‘2.  Single    单精度    ！
       ‘3.  Double    双精度    #
       ‘4.  String    字符串    $
       ‘5.  Long      长整型    &
       ‘6.  Currency  货币型    @

    ‘VBA判断语句：
      ‘(1)IF判断语句：
       ‘单行判断语句：不需要使用End If
               Dim a As Integer
               a = 4
               If a > 3 Then MsgBox "大于3"
  
      ‘多行判断语句：需要使用End If
               If a > 2 Then
                        MsgBox "大于2"
               ElseIf a > 5 Then
                        MsgBox "大于5"
               Else
                        MsgBox "feirup"
              End If
      ‘(2)IIF判断语句：
               MsgBox IIf(a > 2, "不错", "一般")

      ‘选择语句：
        ‘Select Case语句：
               Dim i As Integer
               i = 1
               Select Case i
                        Case Is > 0
                               MsgBox "正数"
                        Case Is < 0
                               MsgBox "负数"
                        Case Else
                               MsgBox "0"
                End Select
     ‘循环语句：
       ‘DO...LOOP语句：
                Dim t As Integer
                t = 1
                Do
                    t = t + 1
                    If t > 10 Then MsgBox "大于10了"
                Loop

    ‘While语句：
             Dim rs%
             rs = 2
   
            Do While Cells(rs, 2) <> ""
                 Cells(rs, 3) = "非若"
                 rs = rs + 1
            Loop
    ‘Until循环语句：
            Dim t%
            t = 2
            Do Until Cells(t, 2) = ""
                  Cells(t, 3) = "feiruo"
                   t = t + 1
            Loop
    ‘for each...next语句：

            Dim rng As Range, n!
            For Each rng In Sheet1.Range("a1:a10")
                  If rng = "A3" Then
                     rng.Interior.ColorIndex = 3
                  Else
                     rng.Interior.ColorIndex = 6
                  End If
            Next
    ‘for...next语句：
            Dim i!, j!
            For i = 1 To 100 Step 2
                 j = j + 1
            Next i
            MsgBox "j=" & j

     ‘End与Exit语句：
          ‘1. Exit Do
          ‘2.Exit For
          ‘3.Exit Function
          ‘4.Exit Sub
            Dim r!
            For r = 2 To 7
              If Sheet1.Cells(r, 1) = "非若" Then
            Exit For
              End If
            Next r
            MsgBox "非若（第一个）在第" & r & "行！"


         Sub button()

          ‘1.End
          ‘2.End Function
          ‘3.End If
          ‘4.End Select
          ‘5.End Sub
             Dim i!
              i = 3
            MsgBox "i=" & i
             End ‘直接停止程序
            MsgBox "退出le"
             End Sub

    ‘跳转语句：
    ‘GoTo line无条件转移程序中指定的程序行
         Dim str$, k!
         k = 1
      line:
         k = k + 1
         If k > 3 Then Exit Sub
              str = InputBox("请输入用户名！")
         If str <> "admin" Then GoTo line
   ‘错误分支语句：
         Dim i!
        ‘On Error Resume Next ‘当遇到错误的时候继续执行下去
         On Error GoTo ERROR:

             For i = 2 To 8
                  Cells(i, 4) = Cells(i, 3) + Cells(i4)
             Next i
             MsgBox "运行完毕"

ERROR: MsgBox "遇到错误！"

    ‘width语句：对某个对象执行一系列的语句是，不用重复指出对象的名称
           ‘a = Range("a1").Address
           ‘b = Range("a1").Parent.Name
      With Range("a1")
           a = .Address
           b = .Parent.Name
          MsgBox a ‘$A$1
          MsgBox b ‘Sheet1
      End With
    


End Sub

‘VBA 函数：
   Dim i%
   
   For i = 1 To 11 Step 1
   Sheet1.Cells(i, 4) = "=sum(a" & i & ":b" & i & ")"
   Sheet1.Cells(i, 3) = "=COUNTIF(A1:A11,"">12"")"
   Sheet1.Cells(i, 5) = "=sum(INDIRECT(""a1:a10""))"
   Next i

Dim i%
   
   MsgBox Application.WorksheetFunction.CountIf(Range("a1:a10"), 14) ‘3  统计3的14的个数 等价于：
   
   ‘MsgBox Application.CountIf(Range("a1:a10"), 14)
   
   ‘MsgBox WorksheetFunction.CountIf(Range("a1:a10"), 14)
   
    MsgBox VBA.Format(Range("b1"), "0.00")
    
    MsgBox Format(Range("b1"), "0.00")

 ‘自定义函数：
    Function sex(rng As Range)
     MsgBox sex = IIf(Mid(rng, 15, 3) Mod 2, "男", "女")
   End Function