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、颜色

  • 颜色的设置就两个属性 colorcolorIndex
  • 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
  • 数据的拆分和汇总练习
参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:精致技术 设计师:CSDN官方博客 返回首页

打赏作者

stanwuc

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值