vba series函数

vba基础

一、基础部分

（一）快捷键

alt + f11，打开编辑窗口

f1，帮助

f5，全部执行

f8，单步执行

---

（二）基本概念

1. 对象

1.1 定义：

一切可操作的东西，比如：工作簿、工作表、单元格、图表、透视表等。

集合：一种特殊的对象，一群对象的统称。

1.2 常用对象：

1.2.1 工作簿（Workbooks）

Workbooks(N) 第N个工作簿

Workbooks("工作簿名")

ActiveWorkbook 活动工作簿

ThisWorkBook 代码所在工作簿

1.2.2 工作表（Worksheets）

Sheets(N) 第N个工作表

Sheets("工作表名")

SheetN 第N个工作表

ActiveSheet 活动工作表

1.2.3 单元格（cells）

Range("单元格地址")

Cells(行号,列号)

[A1] 单元格简写

Activecell 活动单元格

Selection 当前被选取的区域

2. 属性

2.1 定义：

对象的特性。

2.2 例子：

Sub 名称属性()
    Sheet1.Name = "表1"
End Sub


Sub 值属性()
    Sheet1.Range("a1").Value = "赋值测试"
End Sub

3. 方法

3.1 定义：

是对对象的一种操作行为

3.2 例子：

Sub 选择方法()
    Sheet1.Range("a1:a10").Select
End Sub

    
Sub 复制方法()
    Sheet1.Range("a1:a10") = 1
    Sheet2.Range("a11:a20").Copy Sheet1.Range("a1")
End Sub

    
Sub 添加方法()
    Sheets.Add After:=ActiveSheet
End Sub

    
Sub 删除方法()
    ActiveSheet.Delete
    Sheets(3).Delete  '删除第三个sheets
    Sheet1.Range("a1,a3").Delete
End Sub

4. 常量与变量

Sub 声明变量()
    Dim bianliang As String  'as [数据类型] 可省略
    bianliang = "变量"
End Sub


Sub 声明常量()
    Const changliang As Single = 3.14  'as [数据类型] 可省略
End Sub


Sub 声明对象()
    Dim book_1 As Workbook, sheet_1 As Worksheet, range_1 As Range
    Set range_1 = [a1]  '给将对象赋值给变量时要用Set
End Sub

5. 常用运算符

Sub 算数运算符()
    MsgBox 2 + 3  '加
    MsgBox 2 - 3  '减
    MsgBox 2 * 3  '乘
    MsgBox 2 / 3  '除
    MsgBox 20 \ 3  '整除
    MsgBox 20 Mod 3  '求余
    MsgBox 2 ^ 3  '求幂
End Sub


Sub 字符连接符()
    MsgBox "字符" + "运算符"
    MsgBox "字符" & "运算符"
End Sub


Sub 比较运算符()
    MsgBox 1 = 1
    MsgBox 2 < 1
    MsgBox 2 > 1
    MsgBox 2 <= 1
    MsgBox 2 >= 1
    MsgBox 2 <> 1
End Sub


Sub 逻辑运算符()
    MsgBox 1 < 2 And 2 > 3
    MsgBox 1 < 2 Or 2 > 3
    MsgBox Not 1 < 2
End Sub

---

（三）基本语句结构

1. 条件语句

1.1 if 语句

'if语句
Sub 单条件语句()
    Dim num As Integer
    num = 50
    If num >= 60 Then
        MsgBox "及格"
    Else
        MsgBox "不及格"
    End If
End Sub


Sub 多条件语句()
    Dim num As Integer
    num = 81
    If num >= 90 Then
        MsgBox "优秀"
    ElseIf num >= 80 Then
        MsgBox "良好"
    ElseIf num >= 60 Then
        MsgBox "及格"
    Else
    MsgBox "不及格"
    End If
End Sub


Sub 函数型条件语句()
    Dim num As Integer
    num = 45
    MsgBox IIf(num >= 90, "优秀", IIf(num >= 80, "良好", IIf(num >= 60, "及格", "不及格")))
End Sub

1.2 select case 语句

'select case语句
Sub 单条件语句()
    Dim num%  '同 as integer
    
start:  '定位标记
    num = InputBox("请输入成绩：")
    
    Select Case num
    Case Is >= 60
        MsgBox "及格"
    Case Else
        MsgBox "不及格"
        GoTo start  '定位到重新开始的地方
    End Select
    
End Sub


Sub 多条件语句_1()
    Dim num%  '同as integer
    num = InputBox("请输入成绩：")
    
    Select Case num
    Case Is >= 90
        MsgBox "优秀"
    Case Is >= 80
        MsgBox "良好"
    Case Is >= 60
        MsgBox "及格"
    Case Else
        MsgBox "不及格"
    End Select
    
End Sub


Sub 多条件语句_2()
    Dim num%  '同as integer
    num = InputBox("请输入成绩：")
    
    Select Case num
    Case 90 To 100
        MsgBox "优秀"
    Case 80 To 89
        MsgBox "良好"
    Case 60 To 79
        MsgBox "及格"
    Case Else
        MsgBox "不及格"
    End Select
    
End Sub

2. 循环语句

2.1 for ... next 语句

'for ... next 语句
Sub 循环语句_1()
    ActiveSheet.Select
    For num = 2 To 29
        '将单元格cells(num行, 2列) 与 单元格cells(num行, 3列)相乘，赋值给单元格cells(num行, 4列)
        Cells(num, 4) = Cells(num, 2) * Cells(num, 3)
    Next num    
End Sub


Sub 循环语句_2()
    Dim pas, i
    For i = 3 To 1 Step -1
        pas = InputBox("请输入密码：")
        If pas = 666 Then
            MsgBox "密码正确，欢迎使用！"
            Exit For
        Else
            MsgBox "您还有" & i & "次机会"
        End If
    Next i
End Sub


Sub 循环语句_嵌套()
    ActiveSheet.Select
    Dim i, j As Integer
    For i = 1 To 9
        For j = i To 9
        	Cells(j, i) = i & "x" & j & "=" & i * j
        Next j
    Next i
End Sub

2.2 for each ... next 语句

主要用来循环集合、数组。

集合：

workbooks（当前打开的所有工作簿）

worksheets（指定工作簿下的所有工作表）

range("a1:a6")（指定单元格区域下的所有单元格）

'for each ... next 语句
Sub 循环语句_1()
    Dim book_1 As Workbook
    For Each book_1 In Workbooks
        MsgBox book_1.Name
    Next book_1
End Sub


Sub 循环语句_2()
    Workbooks("test.xlsb").Activate
    Dim sheet_1 As Worksheet
    For Each sheet_1 In Worksheets
        MsgBox sheet_1.Name
    Next sheet_1
End Sub


Sub 循环语句_3()
    Dim rng As Range
    For Each rng In Range("a1:d3")
        MsgBox rng.Address & ":" & rng.Value
    Next rng
End Sub


Sub 循环语句_一个例子()
    Dim rng  As Range
    For Each rng In Range("b2:b11")
        rng.Offset(0, 1) = rng.Value * 0.1  'offset(行, 列) 偏移函数
    Next rng
End Sub

2.3 do ... loop 系列语句

do ... loop

do until ... loop

do while ... loop

do ... loop until

do ... loop while

'do ... loop 语句
Sub 循环语句_1()
    Dim num%
    num = 1
    Do
        num = num + 1
        If num <= 9 Then
            MsgBox num
        Else
            Exit Do
        End If
    Loop
End Sub

    
Sub 循环语句_一个例子()
    Dim row_num%
    row_num = 1
    Do
        row_num = row_num + 1
        If Cells(row_num, "a") <> "" Then
            Cells(row_num, "d") = Cells(row_num, "b") * Cells(row_num, "c")
        Else
            Exit Do
        End If
    Loop
End Sub
        

'do until ... loop 语句
Sub 循环语句_until_1()
    Dim num%
    num = 1
    Do Until num > 9
        num = num + 1
        MsgBox num
    Loop
End Sub

        
Sub 循环语句_until_一个例子()
    Dim row_num%
    row_num = 1
    Do Until Cells(row_num + 1, "a") = ""
        row_num = row_num + 1
        Cells(row_num, "d") = Cells(row_num, "b") * Cells(row_num, "c")
    Loop
End Sub
        
        
'do while ... loop 语句        
Sub 循环语句_while_1()
    Dim num%
    num = 1
    Do While num <= 9
        num = num + 1
        MsgBox num
    Loop
End Sub
        
        
Sub 循环语句_while_一个例子()
    Dim row_num%
    row_num = 1
    Do While Cells(row_num + 1, "a") <> ""
        row_num = row_num + 1
        Cells(row_num, "d") = Cells(row_num, "b") * Cells(row_num, "c")
    Loop
End Sub


'do ... loop until 语句
Sub 循环语句_until_2()
    Dim num%
    num = 1
    Do
        num = num + 1
        MsgBox num
    Loop Until num > 9
End Sub

  
'do ... loop while 语句
Sub 循环语句_while_2()
    Dim num%
    num = 1
    Do
        num = num + 1
        MsgBox num
    Loop While num <= 9
End Sub

二、案例部分

todo