Excel VBA（02）工作簿、工作表、单元格操作

工作簿操作

一、概述

• 一个 excel 文件对应一个 workbook，打开后对应一个文件窗口

Windows(1).Visible = True

二、基本操作

• workbook操作：open、add、save、saveas、close

1、判断文件是否存在和打开

• 是否存在：输出为 0 就是不存在

Debug.Print Len(Dir("d:\test.xlsx"))

• 是否打开

Dim x As Integer
For x = 1 To Windows.Count
    If Windows(x).Caption = "test.xlsx" Then
        Debug.Print "file is open"
    End If
Next x

2、文件新建、保存、备份、打开、关闭、复制、删除

• 新建和保存

Dim wb As Workbook
Set wb = Workbooks.Add
wb.Sheets(1).Range("a1").Value = "test"
wb.SaveAs "D:\A.xlsx"

• 备份

Dim wb As Workbook
Set wb = ThisWorkbook
wb.Save
wb.SaveCopyAs "D:\A11.xlsx"

• 打开和关闭

Dim wb As Workbook
Set wb = Workbooks.Open("D:\A.xlsx")
Debug.Print wb.Sheets(1).Range("a1")
wb.Close True

• 复制和删除

FileCopy "D:\A.xlsx", "D:\A23.xlsx"
Kill "D:\A11.xlsx"

工作表操作

• 存在：sheets.count sheets(n).name
• 插入：sheets.add
• 隐藏：sheets(n).visible = False
• 移动：

Sheets("Sheet2").Move before:=Sheets("Sheet1")
Debug.Print ActiveSheet.Name

• 复制：sheets("模板").copy before:=sheets("sheet1")
• 保护：protect "password" 保护状态确认：protectContents = True
• 删除：delete
• 选取：select

thisworkbook.path属性

单元格操作

一、单元格选取

1、一个单元格

• 就是cells 和 range 的各种花式表示方法
• 注意一下

[a1].Value = 12432543

默认的 range 对象

• 如果 range 不指明从属的 sheet 的话，有以下几种情况
• 代码在 sheet 中就是代码所在sheet 里面的range
• 代码在workbook 或者模块中指的就是当前活动 sheet 里面的range

2、单元格区域

• 相邻和不相邻的区域选取
• offset是设置偏移量
• resize是选中相应的原点扩展区域
• union可以形成区域集合

Range("a1,c5").Select
Range("a1").Offset(1, 2).Value = "test"
Range("a1").Resize(3, 5).Value = 123
Union(Range("a1"), Range("c3")).Value = "union"

4、行和列表示

• 基本来讲 rows 可以选取连续的行，非连续的话还是要用 range
• 列的话就是 columns 和 entireColumn，使用基本和行是一致的

Rows(1).Select
Rows("1:5").Select
Range("1:2,4:5").Select
Range("c4,f5").EntireRow.Select

range("a:b,d:f").select

5、坐标原点重置

• 第一 range 就是新的坐标原点，后面的range 是基于这个原点的重新定位

Range("b1").Range("b1").Value = "test_b1"

6、被选取的单元格表示

• 就是前面提过的 selection，表示正在被选中的区域

二、特殊单元格定位

1、已使用区域

• 使用过的区域，包含有数据的区域的最大的行和列

Sheets(1).UsedRange.Select

2、某单元格所在的连续区域

• 这里的连续不是指所有的格子都有数据，只要区域的行列连续即可

Range("g18").CurrentRegion.Select

3、交叉区域

Application.Intersect(Range("2:5"), Range("a:b")).Select

4、用定位条件获取特殊单元格

• 通过xlCellTypeBlanks(定位空的格子)等类型我们就可以定位特殊格子
• 其他类型的查询可以在vb界面选中相应的单词按 F1 进入帮助文档

Range("g17:h18").SpecialCells(xlCellTypeBlanks).Value = "test"

5、端点单元格

• 包含上下和左右端点：xlup xldown xltoright xltoleft

Range("a65536").End(xlUp).Offset(1, 0).Value = 1000
Debug.Print Range("e1").End(xlToRight).Column

三、单元格信息

1、单元格值

• value 是获取相应的值，text取包含格式的内容，formula取格子里面的公式

excel 中公式注释用 '单引号

2、单元格地址

• address(1,1)其中的两个参数是分别设置行列坐标是绝对还是相对值的

3、单元格行列信息

• row column 区域第一行列的编码
• count 是区域总的行列数

With Range("h12").CurrentRegion
    Range("a1") = .Row
    Range("b1") = .Rows.Count
    Range("c1") = .Column
    Range("d1") = .Columns.Count
    Range("e1") = .Range("a1").Address
End With

4、格式信息

With Range("d6")
    [a3] = .Font.ColorIndex
    [a4] = .Font.Size
    [a5] = .Interior.ColorIndex
    [a6] = .Borders.LineStyle
End With

5、批注信息

range("a1").comment.text

6、位置信息

• 就是 top、left、height、width四个属性

7、上级信息

• parent.parent.name

8、内容判断

• hasFormula 、hyperlinks.count

四、单元格格式

1、颜色

• 颜色的设置就两个属性 color 和 colorIndex
• Color 属性的属性值是rgb（1,2,3）或者QBColor(x)

2、内容判断

• 是否为空

range() = ""
len(range()) = 0
vba.isEmpty([a1])

• 是否为数字

vba.isNumeric([a1])  and [a1] <> ""
application.isNumber([a1])

• 是否为文本

application.isText([a1])
vba.typeName([a1].value) = "string"

• 是否为汉字：[a1]>"z"
• 错误值判断：vba|application.isError([a1])
• 日期判断：vba.isDate([a1])

指定数字格式：range().numberFormatLocal = "0.00"

3、单元格合并

• 合并：就是merge
• 返回合并后信息：mergeArea.address

Range("a2:b2").Merge
Debug.Print Range("a2").MergeArea.Address
Debug.Print Range("a2").MergeCells

• 判断是否合并

'单个格子返回 true 和 false
range("A1").mergecells
'区域返回 null 错误
isnull(range("A1:B5").mergeCells)

五、单元格编辑

1、输入内容

• chr(10)是换行符

2、复制和剪切

'直接复制
range("a1:c2").copy range("h1")

'也是复制
range("a1:c2").copy 
activesheet.paste range("h1")

'粘贴含公式
range("a1:c2").copy 
range("h1").pasteSpecial(xlPasteFormulas)

'同样格式的区域粘贴并相加
range("a1:a2").copy 
range("c1:c2").pasteSpecial operation := xladd

'剪切
range("a1:c2").cut
activesheet.paste range("h1")

'最直接的复制!!!!
range("a1:a3") = range("c1:c3").value

3、公式填充

range("b1") = "=a1 * 10"
range("b1:b10").filldown

Range("a11") = "=sum(a1:a10)"
Range("a11:c11").FillRight

4、行列的插入和删除

Rows(4).Insert
Rows(5).Delete
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete '空行删除

六、单元格查找

• 具体的操作涉及 ：match find方法，还可能用到countif方法
• 其中 match 和 countif 都是工作表函数，find 是单元格函数

库存增删改查示例

• 入库单
  
• 库存明细表
  
• 代码

Sub insert1()
    Dim ks As Integer, rc As Integer
    Dim head As Range
    Dim rk As Worksheet
    Dim rkRows  As Integer
    rkRows = Range("b12").End(xlUp).Row - 3
    Set rk = Sheets("入库单")
    Call search1
    With Sheets("库存明细表")
        ks = .Range("a65536").End(xlUp).Row + 1
        'Debug.Print ks
        Set head = rk.Range("c2,e2,g2")
        head.Copy .Range("a" & ks).Resize(rkRows, 1)
        rk.Range("b4:g" & (3 + rkRows)).Copy
        .Range("d" & ks).PasteSpecial (xlPasteAllExceptBorders)
    End With

End Sub

Sub search1()
    Dim a As Integer, sr As Integer, er As Integer
    Dim drows As Integer
    Dim ws As Worksheet
    Set ws = Sheets("库存明细表")
    If Application.CountIf(ws.Range("c:c"), [g2]) = 0 Then
        MsgBox "单号不存在，可以录入"
    Else
        MsgBox "单号存在,请勿重复录入"
        sr = ws.[c:c].Find([g2], , , , , xlNext).Row
        er = ws.[c:c].Find([g2], , , , , xlPrevious).Row
        drows = er - sr + 4
        Range("b4:g" & drows) = ws.Range("d" & sr & ":i" & er).Value
        [c2] = ws.Range("a" & sr).Value
        [e2] = ws.Range("b" & sr).Value
        End
    End If

End Sub

Sub delete1()
    Dim sr  As Integer, er As Integer
    Dim kc As Worksheet
    Set kc = Sheets("库存明细表")
    If Application.CountIf(kc.Range("c:c"), [g2]) = 0 Then
        MsgBox "单号不存在"
    Else:
        sr = kc.[c:c].Find([g2], , , , , xlNext).Row
        er = kc.[c:c].Find([g2], , , , , xlPrevious).Row
        kc.Range(sr & ":" & er).delete
        MsgBox "单号相关数据删除了"
    End If
    
End Sub

Sub update1()
    Call delete1
    Call insert1
End Sub

• 获取表格最下一行非空行的行数：sheets(name).cells.find("*",,,,xlPrevious).row
• 数据的拆分和汇总练习