办公室实用VBA小之代码部分(三)研究报告.docx

办公室实用VBA小程序之代码（2）摘要：本部分是办公室实用VBA小程序（一）的各项功能的具体代码，现分享给大家。2、常用功能区代码：Sub一键打印()ProgressBar1.Visible = TrueApplication.ScreenUpdating = FalseApplication.DisplayAlerts = FalseProgressBar1.Min = 0ProgressBar1.Max = Sheets.CountIf MsgBox(是否已经全部调整好打印格式？, vbYesNo, 警告) = vbYes Then For Each MyshtIn WorksheetsMysht.PrintOutActivePrinter:=Ricoh Aficio MP 2550B PCL 在 Ne00:cot = cot + 1 ProgressBar1.Value = cot NextApplication.ScreenUpdating = TrueElse Exit SubEnd IfLabel2.Caption = 打印完毕,共打印 cot 张。cot = 0End Sub显示被隐藏的工作表Sub 显示隐藏表()Application.ScreenUpdating = FalseFor Each MyshtInActiveWorkbook.Worksheets If Mysht.Visible xlSheetVisible ThenMysht.Visible = xlSheetVisiblecot = cot + 1 End IfNext If cot 0 Then Label2.Caption = 已显示 cot 张被隐藏的工作表。 Else Label2.Caption = 该Workbook中无被隐藏的工作表。 End Ifcot = 0Application.ScreenUpdating = TrueEnd Sub隐藏非活动工作表Sub隐藏非活表()For Each MyshtInActiveWorkbook.Worksheets If Mysht.Name ActiveSheet.Name ThenMysht.Visible = xlSheetHiddencot = cot + 1 End IfNext If cot 0 Then Label2.Caption = 已隐藏 cot 张非活动工作表。 Else Label2.Caption = 该Workbook中已无非活动工作表。 End Ifcot = 0End SubSub 取消合并单元格()For Each rng In ActiveSheet.UsedRange.Cells If rng.MergeCells = True Thenrng.UnMerge End IfNextLabel2.Caption = 所有合并单元格取消完毕！End SubSub 查找清除空格()Dim rng As RangeIf MsgBox(是否在查找后进行替换？, vbYesNo, 提醒您:) = vbYes Then On Error Resume Next For Each rng In ActiveSheet.UsedRange If InStr(1, rng, Chr(32)) Thenrng.Replace What:=Chr(32), Replacement:=rng.Interior.Color = vbYellowcot = cot + 1 End If Next Unload UserForm2 If cot = 0 Then UserForm2.Label2.Caption = 定位完毕，本表中无空格！ Else UserForm2.Label2.Caption = 共有 cot 个单元格含有空格，已黄色显示并替换！ End If Else For Each rng In ActiveSheet.UsedRange On Error Resume Next If InStr(1, rng, Chr(32)) Thenrng.Interior.Color = vbYellowcot = cot + 1 End If Next Unload UserForm2 If cot = 0 Then UserForm2.Label2.Caption = 定位完毕，本表中无空格！ Else UserForm2.Label2.Caption =