先讲工作簿和工作表的相关操作。
一、工作簿和工作表的相关操作
1、保存工作簿
Sub SaveWorkBooks()
Dim book As Workbook
For Each book In Workbooks
'path等于空说明是新文件,就不保存
If book.path <> "" Then book.Save
Next book
End Sub
改进一下,判断保存过又没有修改的,就不保存了,提高一下效率
Sub SaveWorkBooks2()
Dim book As Workbook
For Each book In Workbooks
'path等于空说明是新文件,就不保存
If book.path <> "" Then
'用saved判断如果修改后没有保存过才保存,提高一下效率
If book.Saved <> True Then
book.Save
End If
End If
Next book
End Sub
如果保存完了需要关机,再加一句workbook.save即可
Sub SaveCloseWorkBook()
Dim book As Workbook
For Each book In Workbooks
'path等于空说明是新文件,就不保存
If book.path <> "" Then
'用saved判断如果修改后没有保存过才保存,提高一下效率
If book.Saved <> True Then
book.Save
book.Close
End If
End If
Next book
End Sub
2、隐藏工作表
用户选择一个Range区域,陨星湖把这个区域外的行和列进行隐藏,当然了,把selection替换成自己想选择的区域同理,就不示范了。
Sub Hide()
'隐藏未选择的其他所有行和列
Dim row1 As Long, row2 As Long
Dim col1 As Long, col2 As Long
'用rows.count获取工作表的总行数
'用columns.count获取工作表的总列数
If TypeName(Selection) <> "Range" Then Exit Sub
'如果工作簿全部隐藏,就取消隐藏
'注意:判断是否隐藏用rows和columns,但是取消隐藏是用cells
If Rows(Rows.Count).EntireRow.Hidden Or Columns(Columns.Count).EntireColumn.Hidden Then
Cells.EntireRow.Hidden = False
Cells.EntireColumn.Hidden = False
Exit Sub
End If
'获取选中框的左上角单元格行列数
row1 = Selection.Range("A1").Row
col1 = Selection.Range("A1").Column
'获取选中框的右下角单元格行列数
row2 = Selection.Range("A1").Row + Selection.Rows.Count - 1
col2 = Selection.Range("A1").Column + Selection.Columns.Count - 1
'进行隐藏操作
'隐藏行
Range(Cells(1, 1), Cells(row1 - 1, 1)).EntireRow.Hidden = True
Range(Cells(row2, 1), Cells(Rows.Count, 1)).EntireRow.Hidden = True
'隐藏列
Range(Cells(1, 1), Cells(1, col1 - 1)).EntireColumn.Hidden = True
Range(Cells(1, col2 + 1), Cells(1, Columns.Count)).EntireColumn.Hidden = True
End Sub
3、创建一个工作表的链接页
Sub CreatLink()
'新建sheets(1),把其他sheet名称写在sheet1并做个链接
Dim i As Integer
Sheets.add before:=Sheets(1)
For i = 2 To Sheets.Count
Sheets(i).Hyperlinks.add _
Anchor:=Sheets(1).Range("A" & i - 1), _
Address:="", _
SubAddress:="'" & Worksheets(i).name & "'!A1", _
TextToDisplay:=Sheets(i).name
Next
End Sub
二、VBA其他内容
1、改变布尔值Not的用法
Sub NotBool()
'改变布尔类型的值,加个Not在前面既可以
If Not TypeName(Selection) <> "Range" Then
MsgBox "你选择的是单元格!"
End If
End Sub
2、fotmat显示个性化时间
Sub DisplayTime()
'显示时间
'主要用到format,此前计算大量复制粘贴数据,计算消耗时间的时候用过
Dim theDate As String
Dim theTime As String
theDate = Format(Date, "yyyy年mm月dd日")
theTime = Format(Now(), "hh时MM分ss秒")
MsgBox Application.UserName & theDate
MsgBox Application.UserName & theTime
Select Case TimeValue(theTime)
Case Is < 0.5
MsgBox Application.UserName & ":上午好!"
Case 0.5 To 0.75
MsgBox Application.UserName & ":下午好!"
Case Is > 0.7083
MsgBox Application.UserName & ":晚上好!"
End Select
End Sub