工作簿操作

一、概述

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

Windows(1).Visible = True

二、基本操作

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

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

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

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

是否打开

Dim x As IntegerFor x = 1 To Windows.CountIf Windows(x).Caption = "test.xlsx" ThenDebug.Print "file is open"End IfNext x

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

新建和保存

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

备份

Dim wb As WorkbookSet wb = ThisWorkbookwb.Savewb.SaveCopyAs "D:\A11.xlsx"

打开和关闭

Dim wb As WorkbookSet 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").SelectRange("a1").Offset(1, 2).Value = "test"Range("a1").Resize(3, 5).Value = 123Union(Range("a1"), Range("c3")).Value = "union"

4、行和列表示

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

Rows(1).SelectRows("1:5").SelectRange("1:2,4:5").SelectRange("c4,f5").EntireRow.Selectrange("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 = 1000Debug.Print Range("e1").End(xlToRight).Column

三、单元格信息

1、单元格值

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

excel 中公式注释用'单引号

2、单元格地址

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

3、单元格行列信息

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

With Range("h12").CurrentRegionRange("a1") = .RowRange("b1") = .Rows.CountRange("c1") = .ColumnRange("d1") = .Columns.CountRange("e1") = .Range("a1").AddressEnd With

4、格式信息

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

5、批注信息

range("a1").comment.text

6、位置信息

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

7、上级信息

parent.parent.name

8、内容判断

hasFormula 、hyperlinks.count

四、单元格格式

1、颜色

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

2、内容判断

是否为空

range() = ""len(range()) = 0vba.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").MergeDebug.Print Range("a2").MergeArea.AddressDebug.Print Range("a2").MergeCells

判断是否合并

'单个格子返回 true 和 falserange("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").cutactivesheet.paste range("h1")'最直接的复制!!!!range("a1:a3") = range("c1:c3").value

3、公式填充

range("b1") = "=a1 * 10"range("b1:b10").filldownRange("a11") = "=sum(a1:a10)"Range("a11:c11").FillRight

4、行列的插入和删除

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

六、单元格查找

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

库存增删改查示例

入库单

库存明细表

代码

Sub insert1()Dim ks As Integer, rc As IntegerDim head As RangeDim rk As WorksheetDim rkRows As IntegerrkRows = Range("b12").End(xlUp).Row - 3Set rk = Sheets("入库单")Call search1With Sheets("库存明细表")ks = .Range("a65536").End(xlUp).Row + 1'Debug.Print ksSet 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 WithEnd SubSub search1()Dim a As Integer, sr As Integer, er As IntegerDim drows As IntegerDim ws As WorksheetSet ws = Sheets("库存明细表")If Application.CountIf(ws.Range("c:c"), [g2]) = 0 ThenMsgBox "单号不存在，可以录入"ElseMsgBox "单号存在,请勿重复录入"sr = ws.[c:c].Find([g2], , , , , xlNext).Rower = ws.[c:c].Find([g2], , , , , xlPrevious).Rowdrows = er - sr + 4Range("b4:g" & drows) = ws.Range("d" & sr & ":i" & er).Value[c2] = ws.Range("a" & sr).Value[e2] = ws.Range("b" & sr).ValueEndEnd IfEnd SubSub delete1()Dim sr As Integer, er As IntegerDim kc As WorksheetSet kc = Sheets("库存明细表")If Application.CountIf(kc.Range("c:c"), [g2]) = 0 ThenMsgBox "单号不存在"Else:sr = kc.[c:c].Find([g2], , , , , xlNext).Rower = kc.[c:c].Find([g2], , , , , xlPrevious).Rowkc.Range(sr & ":" & er).deleteMsgBox "单号相关数据删除了"End IfEnd SubSub update1()Call delete1Call insert1End Sub

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