EXCEL VBA初步

Application.Quit

Application.Undo

Set myrange = Worksheets(\answer = Application.WorksheetFunction.Min(myrange) MsgBox answer

dbaverage = Application.WorksheetFunction.Average(12, 14, 13, 19, 21)

Application.Dialogs(xlDialogOpen).Show

dlganswer = Application.Dialogs(xlDialogOpen).Show

expression.ontime(earliesttime,procedure,latesttime,schedule)

Application.OnTime Now + TimeValue(\Application.OnTime TimeValue(\

Sub mytime()

Application.Caption = Now

Application.OnTime Now + TimeValue(\End Sub

WINDOW对象和WINDOWS集合

Application.Windows.Arrange (xlArrangeStyleTiled)

With ThisWorkbook.NewWindow .Caption = \ .Activate End With

WORKBOOK对象

ActiveSheet.Range(\ActiveSheet.Range(\

ActiveSheet.Range(\

Sub usepassword()

Dim wkbone As Workbook

Set wkb = Workbooks.Open(\wkbone.Password = InputBox(\wkbone.Close End Sub

Private Sub listsheets() Dim sh As Worksheet Dim rng As Range Dim i As Integer

Set rng = ActiveSheet.Range(\For Each sh In thisworkbooks.Sheets rng.Offset(i, 0).Value = sh.Name i = i + 1 Next sh End Sub

Sheets集合对象

Workbooks(1).Sheets(1).Visible = xlsheethiodden

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets.Add()

ThisWorkbook.Sheets(1).PrintOut from:=1, to:=1, copies:=2, preview:=True

ActiveWorkbook.Sheets(1).Select

Workbook的方法

thisapplication.Workbooks(1).Activate

Workbooks(1).Close savechanges:=False

ActiveWorkbook.SaveAs\

Workbook事件

Sub workbook_addininstall()

With Application.CommandBars(\ .Caption = \ .OnAction = \ End With End Sub

Worksheet对象

Worksheets(1).Calculate

Worksheets(\

MsgBox Worksheets(\

Worksheets(\

Worksheets(\

Worksheets(\

Worksheets(\

Worksheets(\

Worksheets(\

Worksheets(\

Range对象

Worksheets(“sheet1”).range(“a5”).value=worksheets(“sheet1”).range(“a1”).value

Worksheets(“sheet1”).activate Range(“a1:h8”).formula=”rand()”

Worksheets(1).range(“criteria”).clearcontents

Worksheets(1).cells(1,1).value=24

Activesheet.cells(2,1).formula=”=sum(b1:b5)”

Worksheets(1).range(“c5:c10”).cells(1,1).formula=”=rand()”

Dim r1 As Range, r2 As Range, mymultiarearange As Range Worksheets(\Set r1 = Range(\Set r2 = Range(\

Set mymultiarearange = Union(r1, r2) mymultiarearange.Select Sub nomultiareaselection()

numberofselectedareas = Selection.Areas.Count If numberofselectedareas > 1 Then

MsgBox \ End If End Sub

Worksheets(1).range(“a1”).activate

Worksheets(“sheet1”).range(“a1”).addcomment(“mycomment”)

Msgbox activecell.address

Worksheets(“sheet1”).range(“a1:d13”).calculate

Worksheets(“sheet1”).range(“a1:d13”).cells.count

Worksheets(“sheet1”).range(“a1:d13”).cells.checkspelling

Worksheets(“sheet1”).range(“a1:d13”).clear

Worksheets(“sheet1”).range(“a1:d13”).clearcomments

Worksheets(“sheet1”).range(“a1:d13”).clearcontents

Msgbox worksheets(“sheet2”).range(“b3:d12”).column

Msgbox worksheets(“sheet2”).range(“b3:d12”).row

Dim i As Long, j As Long Dim rng As rang

Set rng = ActiveSheet.Range(\With rng

For i = 1 To .Columns.Count For j = 1 To .Rows.Count .Cells(j, i).Value j & \ Next j Next i End With

Worksheets(“sheets1”).activate Activecell.currentregion.select

Range(“b4”).end(xlup).select

Range(“b4”).end(xltoright).select

Range(“b4”).end(xlup).select

Worksheets(\

Range(\

Worksheets(“sheets1”). Range(“f19:g20”).copy

Worksheets(“sheets1”). Range(“f19:g20”).pastespecial