EXCEL VBA初步(3)

[statements] [exit for] [statements] Next [element]

WITH 语句 With object [statements] [statements] End with Eg.

With mylable .height=2000 .width=2000

.caption=”this is mylable” End with

EXIT语句 Exit do Exit for

Exit function Exit property Exit sub

简单的用户交互

Msgbox(prompt[,butons] [,title] [,helpfile,context]

Inputbox(prompt [,title][,default][,xpos][,ypos] [,helpfile,context] EXCEL内置对话框

Banswer=application.dialogs(xldialogopen).show 窗体

MODAL类如MSGBOX,INPUTBOX,DEBUG等 MODELESS

使用debug Sub 挂起() Dim i As Long

For i = 1 To 10 Step 1 Debug.Print i Debug.assert I<8 Next i End Sub

EXCEL的对象模型

Application.workbooks(1).worksheets(1).cells(1,1)=100 控制EXCEL状态和显示 Eg.

Debug.Print ActiveWorkbook.Path

Debug.Print ActiveWorkbook.FullName

Debug.Print Application.TemplatesPath Debug.Print Application.Path

Debug.Print Application.DefaultFilePath 返回对象

Workbooks.add Workbooks.close

Workbooks.open “c\\yourpath\\yourworkbook.xls”

Workbooks.opentext ”c\\test.txt”,startrow:=3,datatype:=xldelimited,comma:=true Woekbooks(1)

Workbooks(“book1”) Workbooks(“book1.xls”)

执行操作

Application.Calculate Worksheets(1).Calculate

Application.Range(\

Dim strword As String

strword = applicatin.Selection

If Not Application.CheckSpelling(strword) Then Application.Selection .Font.Color = vbRed End If

Evaluate

expression.Evaluate (Name) [a1].Value = 25

Evaluate(\

Set firstcellinsheet = Workbooks(\

Set firstcellinsheet = Workbooks(\

Worksheets(\boldcell = \

Application.Evaluate(boldcell).Font.Bold = True

Public Sub testmail()

If IsNull(Application.MailSession) Then Application.MailLogon End If

ActiveWorkbook.sendmsil \aplication.MailLogoff End Sub

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”)