VBA学习笔记1：宏、判断、循环、分支语句、函数与公式

'VBA语句
'一、宏程序语句（运行后可以弯沉一个功能）
'1. 在单元格A1中输入100
 Sub test()  '开始语句
   Range("a1") = 100
 End Sub  '结束语句

'2. 录制宏(将当前光标所选中的单元格内容复制到单元格B9中)
 Sub copy()
   Selection.Copy
   Range("B9").Select
   ActiveSheet.Paste
 End Sub

'3. 录制宏（删除单元格A1中的内容）
 Sub delete()
   Range("A1").Select
   Selection.ClearContents
 End Sub

'二、函数程序语句（运行后可以返回一个值）
 Function shcount()
   shcount = Sheets.Count
 End Function

'三、在程序中应用的语句
'1. 调用test
 Sub test2()
   Call test  '调用test语句
 End Sub

'2. 第一列的前100行等于1到100
 Sub test3() 
   For x = 1 To 100   'for next 循环语句
     Cells(x, 1) = x
   Next x
 End Sub


'变量的存活周期
'1. 过程级变量:过程结束，变量值释放
'2. 模块级变量:变量的值只在本模块中保持，工作簿关闭时随时释放
'3. 全局级变量: 在所有的模块中都可以调用，值会保存到EXCEL关闭时才会被释放。
 
'变量的释放
'1. 一般情况下，过程级变量在过程运行结束后就会自动从内存中释放
'2. 只有一些从外部借用的对象变量才需要使用set 变量=nothing进行释放。


'VBA对象
'VBA中的对象其实就是我们操作的具有方法、属性的excel中支持的对象

'Excel中的几个常用对象表示方法
 '1、工作簿
     ' Workbooks 代表工作簿集合，所有的工作簿,Workbooks(N)，表示已打开的第N个工作簿
     ' Workbooks ("工作簿名称")
     ' ActiveWorkbook 正在操作的工作簿
     ' ThisWorkBook '代码所在的工作簿
 '2、工作表
     'Sheets("工作表名称")
     'Sheet1 表示第一个插入的工作表,Sheet2表示第二个插入的工作表....
     'Sheets(n) 表示按排列顺序，第n个工作表
     'ActiveSheet 表示活动工作表，光标所在工作表
     'worksheet 也表示工作表，但不包括图表工作表、宏工作表等。
  '3、单元格
     'cells 所有单元格
     'Range ("单元格地址")
     'Cells(行数,列数)
     'Activecell 正在选中或编辑的单元格
     'Selection 正被选中或选取的单元格或单元格区域


'VBA属性
'VBA属性就是VBA对象所具有的特点
'表示某个对象的属性的方法是: 对象.属性=属性值
'1. 单元格A1的值等于100
 Sub ttt()
   Range("a1").Value = 100  '单元格的值
 End Sub
'或者：
 Sub ttt()
   Dim rg As Range  '定义Range对象rg
   Set rg = Range("a1")
   rg = 100
 End Sub

'2. 将第一个工作表改为“工作表1”
 Sub ttt1()
   Sheets(1).Name = "工作表1"  '工作表的名称
 End Sub

'3. 将工作表sheet2中单元格A1的值改为“abcd”
 Sub ttt2()
   Sheets("Sheet2").Range("a1").Value = "abcd"  '工作表中单元格的值
 End Sub

'4. 将单元格A2填充为红色
 Sub ttt3()
   Range("A2").Interior.ColorIndex = 3  '单元格的内部填充色
 End Sub


'VBA方法
'VBA方法是作用于VBA对象上的动作
'表示用某个方法作用于VBA的对象上，可以用下面的格式：
'1. 将单元格A1的值复制到A2
 Sub ttt4()
   Range("A1").Copy Destination:= Range("A2")  'Destination:=可以省略
 End Sub

'2. 将sheet2放在sheet1后面  
 Sub ttt5()
   Sheet1.Move before:=Sheets("Sheet2")
 End Sub


'判断语句
'一、IF判断
'1. 判断单元格A1的值，若大于0则单元格B1的值为“正数”，否则为“负数或0”
 Sub judge1()  '单条件判断
   If Range("a1").Value > 0 Then
     Range("b1") = "正数"
   Else
     Range("b1") = "负数或0"
   End If
 End Sub

'2. 若单元格A1的值大于0则B1的值为“正数”，等于0为“等于0”，否则为“负数”
 Sub judge2()  '多条件判断
   If Range("a1").Value > 0 Then
     Range("b1") = "正数"
   ElseIf Range("a1") = 0 Then
     Range("b1") = "等于0"
   ElseIf Range("B1") <= 0 Then
     Range("b1") = "负数"
   End If
 End Sub

'3. 若单元格A1和A2的值不为空值，则A3=A1*A2
 Sub judge3()
  If Range("a1") <> "" And Range("a2") <> "" Then
    Range("a3") = Range("a1") * Range("a2")
  End If
 End Sub

'4. 区间判断：若A2<=1000，则B2=0.01；若1000<A2<=3000，则B2=0.03；若A2>3000，则B2=0.05
 Sub scope()
  If Range("a2") <= 1000 Then
    Range("b2") = 0.01
  ElseIf Range("a2") <= 3000 Then
    Range("b2") = 0.03
  ElseIf Range("a2") > 3000 Then
    Range("b2") = 0.05
  End If
 End Sub

'二、IIF判断
'1. 若A1小于等于0，则A3等于“负数或零”，否则等于“负数”
 Sub 判断4()
   Range("a3") = IIf(Range("a1") <= 0, "负数或零", "负数")
 End Sub

'三、select判断
'1. 若单元格A1的值大于0，则B1等于“正数”，否则等于“负数或0”
 Sub 判断1()  '单条件判断
   Select Case Range("a1").Value
   Case Is > 0
     Range("b1") = "正数"
   Case Else
     Range("b1") = "负数或0"
   End Select
 End Sub

'2. 若单元格A1的值大于0，则B1等于“正数”，等于0则B1等于“0”，否则等于“0”
 Sub 判断2()  '多条件判断
   Select Case Range("a1").Value
   Case Is > 0
     Range("b1") = "正数"
   Case Is = 0
     Range("b1") = "0"
   Case Else
     Range("b1") = "负数"
   End Select
 End Sub

'3. 若A3的值小于“G”，则
 Sub 判断3()
  If Range("a3") < "G" Then
    MsgBox "A-G"
  End If
 End Sub

'4. 区间判断：若0<=A2<=1000，则B2=0.01；若1001<=A2<=3000，则B2=0.03；若A2>3000，则B2=0.05
 Sub scope()
  Select Case Range("a2").Value
  Case 0 To 1000
    Range("b2") = 0.01
  Case 1001 To 3000
    Range("b2") = 0.03
  Case Is > 3000
    Range("b2") = 0.05
  End Select
 End Sub


'循环语句
'1. D列的值等于B列乘以C列（前10行）的4种实现方法：
'方法1：
 Sub t1()
 Dim x As Integer  '定义整数变量x
   For x = 2 To 10 Step 1
     Range("d" & x) = Range("b" & x) * Range("c" & x)
   Next x
 End Sub

'方法2：
 Sub t2()
 Dim rg As Range
   For Each rg In Range("d2:d10")
     rg = rg.Offset(0, -2) * rg.Offset(0, -1)
   Next rg
 End Sub

'方法3：
 Sub t3()
 Dim x As Integer
   x = 1
   Do
     x = x + 1
     Cells(x, 4) = Cells(x, 2) * Cells(x, 3)
   Loop Until x = 10
 End Sub

'方法4：
 Sub t5()
  x = 1
  Do While x < 10
    x = x + 1
    Cells(x, 4) = Cells(x, 2) * Cells(x, 3)
  Loop
 End Sub

'2. 将A1:B7和D5:E9区域中的空值更改为0
 Sub s1()
  Dim rgg As Range
  For Each rgg In Range("a1:b7,d5:e9")
    If rgg = "" Then
      rgg = 0
    End If
  Next rgg
 End Sub

'3. 若第一列相邻两行之间的差值不等于1，则在第二列相应位置输入“断点”(前10行)
 Sub s2()
  Dim x As Integer
  x=0
  Do
    x = x + 1
    If Cells(x + 1, 1) <> Cells(x, 1) + 1 Then
       Cells(x, 2) = "断点"
       Exit Do
    End If
  Loop Until x = 11
 End Sub


'函数与公式
'一、用VBA在单元格中输入普通公式
'1. 实现D2=B2*C2
 Sub t1()
    Range("d2") = "=b2*c2"
 End Sub

'2. 实现第四列等于B列*C列（2到6行）     
 Sub t2()
   Dim x As Integer
   For x = 2 To 6
     Cells(x, 4) = "=b" & x & "*c" & x
     Next x
 End Sub

'二、用VBA在单元格输入带引号的公式
'1. 将A列中“B”对应的B列的值求和（2到6行）
 Sub t3()
    Range("c16") = "=SUMIF(A2:A6,""b"",B2:B6)"  '遇到双引号就把双引号加倍
 End Sub
      
'三、用VBA在单元格中输入数组公式
'1. 将数组B2:B6和C2:C6的积求和
 Sub t4()
   Range("c9").FormulaArray = "=SUM(B2:B6*C2:C6)"  '不需要在公式中加{}
 End Sub
    
'四、利用单元格公式返回值
'1. 用Evaluate直接计算出公式的值 
 Sub t5()
    Range("d16") = Evaluate("=SUMIF(A2:A6,""b"",B2:B6)")
    Range("d9") = Evaluate("=SUM(B2:B6*C2:C6)")
 End Sub
  
'五、借用工作表函数
'1. 统计A列中出现“B”的总次数（前10行）
 Sub t6()
    Range("d8") = Application.WorksheetFunction.CountIf(Range("A1:A10"), "B")  
 End Sub

'六、利用VBA函数
'1. 在单元格C20中返回A20中“E”出现的位置
 Sub t7()
   Range("C20") = VBA.InStr(Range("a20"), "E")  '字符串查找，类似于工作表函数find
 End Sub  

'七、编写自定义函数
“1. 返回当前工作表的名称
  Function wn()
     wn = Application.Caller.Parent.Name  
  End Function


'END语句和Exit语句
'End作用：强制退出所有正在运行的程序

'Exit作用：退出指定的语句
'1、Exit Sub
 Sub e1()
   Dim x As Integer
     For x = 1 To 100
        Cells(1, 1) = x
        If x = 5 Then
          Exit Sub
        End If
     Next x
     Range("b1") = 100
 End Sub

'2、Exit function
 Function ff()
   Dim x As Integer
     For x = 1 To 100
       If x = 5 Then
         Exit Function
       End If
     Next x
     ff = 100
 End Function
   
'3、Exit for
 Sub e2()
   Dim x As Integer
     For x = 1 To 100
        Cells(1, 1) = x
        If x = 5 Then
           Exit For
        End If
     Next x
     Range("b1") = 100
 End Sub

'4、Exit do
 Sub e3()
   Dim x As Integer
     Do
       x = x + 1
       Cells(1, 1) = x
       If x = 5 Then
         Exit Do
       End If
     Loop Until x = 100
     Range("b1") = 100
 End Sub


'分支语句
'1. Goto语句,跳转到指定的地方
 Sub t1()
   Dim x As Integer
   Dim sr
 100:
   sr = Application.InputBox("请输入数字", "输入提示")
   If Len(sr) = 0 Or Len(sr) = 5 Then GoTo 100
 End Sub

'2. gosub..return ,跳过去,再跳回来
 Sub t2()
   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) = "偶数"
   Return          '跳到gosub 100 这一句
 End Sub

'3. on error resume next '遇到错误,跳过继续执行下一句
 Sub t3()
   On Error Resume Next
   Dim x As Integer
   For x = 1 To 10
     Cells(x, 3) = Cells(x, 2) * Cells(x, 1)
   Next x
 End Sub
 
'4. on error goto  '出错时跳到指定的行数
 Sub t4()
   On Error GoTo 100
   Dim x As Integer
   For x = 1 To 10
     Cells(x, 3) = Cells(x, 2) * Cells(x, 1)
   Next x
    Exit Sub
 100:
   MsgBox "在第" & x & "行出错了"
 End Sub
 
'5. on error goto 0 '取消错误跳转
 Sub t5()
   On Error Resume Next
   Dim x As Integer
   For x = 1 To 10
     If x > 5 Then On Error GoTo 0
     Cells(x, 3) = Cells(x, 2) * Cells(x, 1)
   Next x
   Exit Sub
 End Sub