Excel-VBA常用宏应用技巧 Excel-常用宏技巧 If Application.InputBox("请输入密码:") = 1234 Then [A1] = 1 '密码正确时执行 Else: MsgBox "密码错误,即将退出!" '此行与第2行共同设置密码 End If
X = MsgBox("是否真的要结帐?", vbYesNo) If X = vbYes Then Close
ActiveSheet.Protect Password:=641112 ' 保护工作表并设置密码 ActiveSheet.Unprotect Password:=641112 '撤消工作表保护并取消密码
For Each w In Workbooks If w.Name <> ThisWorkbook.Name Then w.Close SaveChanges:=True End If Next w
Application.WindowState = xlMaximized
MsgBox "The name of the active sheet is " & ActiveSheet.Name
ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"
Sheets(4).Activate '下述过程激活工作簿中的第1张工作表。 Worksheets(1).Activate
ThisWorkbook.Saved = True ThisWorkbook.Close
Worksheets(1).EnableCalculation = False
Workbooks.Open ("C:\MyFolder\MyBook.xls")
MsgBox Worksheets("Sheet1").Range("A1").Value
For Each ws In Worksheets MsgBox ws.Name Next ws
Set NewSheet = Worksheets.Add NewSheet.Name = "current Budget"
'Private Sub Workbook_NewSheet(ByVal Sh As Object) Sh.Move After:=Sheets(Sheets.Count) End Sub
'Private Sub App_WorkbookNewSheet(ByVal Wb As Workbook, _ ByVal Sh As Object) Sh.Move After:=Wb.Sheets(Wb.Sheets.Count) End Sub
Set NewSheet = Sheets.Add(Type:=xlWorksheet) For i = 1 To Sheets.Count NewSheet.Cells(i, 1).Value = Sheets(i).Name Next i
Worksheets("Sheet1").Activate ActiveWindow.ScrollRow = 10
'Private Sub Workbook_SheetCalculate(ByVal Sh As Object) With Worksheets(1) .Range("a1:a100").Sort Key1:=.Range("a1") End With End Sub 本示例显示工作表 Sheet1 的打印预览。 Worksheets("Sheet1").PrintPreview
ActiveWorkbook.Save
For Each w In Application.Workbooks w.Save Next w Application.Quit
Worksheets.Add Count:=2, Before:=Sheets(1)
Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"
Application.OnTime TimeValue("17:00:00"), "my_Procedure"
Application.OnTime EarliestTime:=TimeValue("17:00:00"), _ Procedure:="my_Procedure", Schedule:=False
'Private Sub Worksheet_Calculate() Columns("A:F").AutoFit End Sub
ActiveWorkbook.PrecisionAsDisplayed = True
Worksheets("Sheet1").Range("A1:G37").Cut
计算所有打开的工作簿、工作簿中的一张特定的工作表或者工作表中指定区域的单元格,如下表所示: '要计算 '依照本示例 所有打开的工作簿 ' Application.Calculate (或只是 Calculate) 指定工作表 '计算指定工作表Sheet1 Worksheets("Sheet1").Calculate 指定区域 'Worksheets(1).Rows(2).Calculate
Worksheets(1).EnableCalculation = False
Worksheets("Sheet1").UsedRange.Columns("A:C").Calculate
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
Worksheets(1).ScrollArea = "a1:f10"
Set NewBook = Workbooks.Add Do fName = Application.GetSaveAsFilename Loop Until fName <> False NewBook.SaveAs Filename:=fName
Workbooks.Open "ANALYSIS.XLS" ActiveWorkbook.RunAutoMacros xlAutoOpen
With ActiveWorkbook .RunAutoMacros xlAutoClose .Close End With
'Sub UseCanonical() Display the full path to user. MsgBox ActiveWorkbook.FullNameURLEncoded End Sub
MsgBox ActiveWorkbook.FullName
Workbooks("BOOK1.XLS").Close SaveChanges:=False
Workbooks.Close
'Private Sub Workbook_BeforePrint(Cancel As Boolean) For Each wk In Worksheets wk.Calculate Next End Sub
Set c1 = Sheets("sheet1").QueryTables(1).ResultRange.Columns(1) c1.Name = "Column1" c1.End(xlDown).Offset(2, 0).Formula = "=sum(Column1)"
ActiveWorkbook.RejectAllChanges
Worksheets("Sheet1").Activate SolverReset SolverOptions Precision:=0.001 SolverOK SetCell:=Range("TotalProfit"), _ MaxMinVal:=1, _ ByChange:=Range("C4:E6") SolverAdd CellRef:=Range("F4:F6"), _ Relation:=1, _ FormulaText:=100 SolverAdd CellRef:=Range("C4:E6"), _ Relation:=3, _ FormulaText:=0 SolverAdd CellRef:=Range("C4:E6"), _ Relation:=4 SolverSolve UserFinish:=False SolverSave SaveArea:=Range("A33")
Charts(Array("Chart1", "Chart3", "Chart5")).Visible = False
'Private Sub Worksheet_Activate() Range("a1:a10").Sort Key1:=Range("a1"), Order:=xlAscending End Sub
ActiveWorkbook.ChangeLink "c:\excel\book1.xls", _ "c:\excel\book2.xls", xlExcelLinks
ActiveSheet.EnableAutoFilter = True ActiveSheet.Protect contents:=True, userInterfaceOnly:=True
ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly
ActiveWorkbook.AutoUpdateFrequency = 3
'Sub ClearSheet() Worksheets("Sheet1").Cells.ClearContents End Sub 相关内容
|
|