VBA在Excel中的應用(一)

  1. 檢查活動單元格是否存在

Sub activeCell()

If ActiveCell IsNothingThenEnd If

End Sub

2. 通過指定偏移量設置活動單元格

Sub offset()

ActiveCell.Offset(RowOffset:=-2, ColumnOffset:=4).Activate

End Sub

Offset函數的第一個參數為Row的偏移量,第二個參數為Column的偏移量(可以不指定),使用時可以直接給定值,如Offset(2, 4)。值小於0向相反方向偏移。Offset().Activate與Offset().Select在效果上等同。 3. 設置活動單元格的當前值

Sub SetValue

ActiveCell.Value =”Hello World!”

End Sub

4. 為當前活動單元格設置公式

Sub fomula()

ActiveCell.Formula =”=SUM($G$12:$G$22)”

End Sub

將公式的表達式直接賦值給Formula屬性,公式表達式可以參考Excel中的公式菜單,如求和、計數、求平均值等。 5. 獲取當前活動單元格的地址

Sub selectRange()

MsgBox ActiveCell.Address

End Sub

地址的格式如:$A$11。 6. 獲取從當前活動單元格開始到邊界單元格的區域VBA在Excel中的應用(一)

‘ 從當前單元格到最頂端

Sub SelectUp()

Range(ActiveCell, ActiveCell.End(xlUp)).Select

End Sub

‘從當前單元格到最底端

Sub SelectDown()

Range(ActiveCell, ActiveCell.End(xlDown)).Select

End Sub

‘從當前單元格到最右端(等同於xlEnd)

Sub SelectToRight()

Range(ActiveCell, ActiveCell.End(xlToRight)).Select

End Sub

‘從當前單元格到最左端

Sub SelectToLeft()

Range(ActiveCell, ActiveCell.End(xlToLeft)).Select

End Sub

VBA在Excel中的應用(一)

7. 當前活動單元格所在區域選擇

Sub SelectCurrentRegion()

ActiveCell.CurrentRegion.Select

End Sub

對CurrentRegion屬性所代表的區域的說明:

CurrentRegion返回活動單元格所在的周圍由空行和空列組成的單元格區域(這個似乎有點不太好理解) ,可以看下圖的示例:

VBA在Excel中的應用(一)
  可以這樣理解CurrentRegion屬性所代表的區域,即以活動單元格為中心,它所包含的矩形區域的每一行和每一列中至少包含有一個數據,上圖中的藍色陰影區域中,無論活動單元格是哪一個,其所在的當前區域均為同一區域,如B5:D7區域中的B5和C6單元格。A4的當前區域表示為A1:D7,A8的當前區域表示為A5:D11,A12的當前區域只有它本身。

使用CurrentRegion屬性相當於在Excel工作表中選擇菜單「編輯-定位」命令,在彈出的「定位」對話框中單擊「定位條件」按鈕,然後在「定位條件」對話框中選中「當前區域」選項按鈕,或者相當於使用Ctrl+Shift+*組合鍵。在Excel2007中,該命令在以下地方可以找到:

VBA在Excel中的應用(一)
  在下拉菜單中選擇「Go To Special…」 ,在對話框中選擇「Current region」。

VBA在Excel中的應用(一)
  有關使用CurrentRegion的一些例子:

? 在下圖中,要使用空白單元格上方的有數據的單元格中的數據來填充空白單元格。

VBA在Excel中的應用(一)

代碼如下,VBA在Excel中的應用(一)

Sub FillBlankCells()

Worksheets(“sheet1”).Range(“A1″).CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 =”=R[-1]C”

Worksheets(“sheet1”).Range(“A1”).CurrentRegion.Value = Worksheets(“sheet1”).Range(“A1”).CurrentRegion.Value

End SubVBA在Excel中的應用(一)

執行之後,工作表中單元格A1所在當前區域中的空白單元格被相應數據填充,如下圖。

VBA在Excel中的應用(一)

? 如下圖,對第三列進行降序排序。

VBA在Excel中的應用(一)

代碼如下: Sub testSort()

Dim rng As Range

Set rng = Worksheets(“sheet1”).Cells(1, 1).CurrentRegion

rng.Sort Key1:=rng.Cells(1, 3), Order1:=xlDescending, Header:=xlYes

End Sub

執行之後,工作表中的數據將按照第三列的數據降序排序,如下圖。

VBA在Excel中的應用(一)
  8. 使用SpecialCells方法

該方法用於返回與指定形態和值相符合的所有單元格,其中第一個參數為xlCellType類型所代表的常數。 xlCellTypeAllFormatConditions 任何格式的單元格。

xlCellTypeAllValidation 帶數據校驗的單元格。

xlCellTypeBlanks 空單元格。

xlCellTypeComments 包含註釋的單元格。

xlCellTypeConstants 包含常數的單元格。

xlCellTypeFormulas 包含公式的單元格。

xlCellTypeLastCell 已用範圍的最後一個單元格。

xlCellTypeSameFormatConditions 有相同格式的單元格。

xlCellTypeSameValidation 有相同數據校驗準則的單元格。

xlCellTypeVisible 所有可見單元格。

第二個參數為可選參數。如果xlCellType為xlCellTypeConstants或xlCellTypeFormulas 之一,該參數用於確定結果中應包含哪些類型的單元格。將某幾個值相加可使此方法返回多種形態的單元格。默認情況下將指定所有常數或公式,對其形態則不加類型。它可以是下列常數之一。

xlErrors

xlLogical

xlNumbers

xlTextValues

Sub SelectActiveArea()

Range(Range(“A1”), ActiveCell.SpecialCells(xlTypeLastCell)).Select

End Sub

有關使用SpecialCells的一個例子:

將下圖所示的數據按順序存放到一個新建的工作表中,

VBA在Excel中的應用(一)
  VBA在Excel中的應用(一)

Sub toAcol()

Dim newSht As Worksheet

Dim Rng As Range

Dim allDat As Range

Dim pt As Range

Dim i AsLong

‘選擇工作表中所有有內容的單元格

Set allDat = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)

‘新增工作表

Set newSht = Worksheets.Add

‘設置新工作表中的起始位置

Set pt = newSht.Range(“a1”)

ForEach Rng In allDat.Areas

For i =1To Rng.Cells.Count

pt = Rng.Cells(i)

Set pt = pt.Offset(1, 0)

Next

Next

‘重命名新工作表

newSht.Name =”newSht”& Worksheets.Count

End SubVBA在Excel中的應用(一)

執行後,在名稱為「newSht4」的工作表中會出現如下圖所示的數據。

VBA在Excel中的應用(一)
  9. 通過Application.WorksheetFunction調用Proper方法

Sub FixText()

ActiveCell.Value = Application.WorksheetFunction.Proper(“asdf”)

End Sub

該方法將給定的表達式中的第一個字母大寫,而其餘字母小寫,示例中的代碼將活動單元格的值設置為「Asdf」。 10. EntireRow和EntireColumn

Sub SelectColumn()

ActiveCell.EntireColumn.Select

End Sub

Sub SelectRow()

ActiveCell.EntireRow.Select

End Sub

EntireColumn用於選擇當前活動單元格所在的整列,EntireRow用於選擇當前活動單元格所在的整行。 11. 找出當前所選區域中包含最大值的單元格

VBA在Excel中的應用(一)

Sub GoToMax()

Dim WorkRange As Range

IfTypeName(Selection) <>”Range”ThenExitSub

If Selection.Count =1Then

Set WorkRange = Cells

Else

Set WorkRange = Selection

EndIf

MaxVal = Application.Max(WorkRange)

OnErrorResumeNext

WorkRange.Find(What:=MaxVal, _

After:=WorkRange.Range(“A1”), _

LookIn:=xlValues, _

LookAt:=xlPart, _

SearchOrder:=xlByRows, _

SearchDirection:=xlNext, MatchCase:=False).Select

If Err <>0ThenMsgBox”Max value was not found: “& MaxVal

End SubVBA在Excel中的應用(一)

12. WarpText屬性

Sub ToggleWrapText()

IfTypeName(Selection) =”Range”Then

Selection.WrapText =Not ActiveCell.WrapText

EndIf

End Sub

WarpText屬性用於指示當前活動單元格是否被設置為允許換行。

  360pskdocImg_18_xyz  返回目錄

ActiveWorkbook

1. 獲取當前活動工作簿的名稱 Sub test()

MsgBox ActiveWorkbook.FullName

End Sub

2. 打開工作表 Sub filePath()

Dim filePath AsString

filePath = ActiveWorkbook.Path

Workbooks.Open (filePath &”\”&”MyWorkbook.xls”)

End Sub

3. 保存工作表 Sub webPage()

ActiveWorkbook.SaveAs _

Filename:=ActiveWorkbook.Path &”\myXclfile.htm”, _

FileFormat:=xlHtml

End Sub

4. 預覽工作表 Sub pre()

ActiveWorkbook.WebPagePreview

End Sub

5. 發布Excel文件到指定的目錄360pskdocImg_19_xyz

PublicSub SaveRangeWeb()

ActiveWorkbook.PublishObjects.Add _

SourceType:=xlSourceRange, _

Filename:=ActiveWorkbook.Path &”\Sample1.htm”, _

Sheet:=ActiveSheet.name, _

Source:=”$A$1:$B$11″, _

HtmlType:=xlHtmlStatic

ActiveWorkbook.PublishObjects(1).Publish (True)

ActiveWorkbook.PublishObjects(1).AutoRepublish (False)

End Sub360pskdocImg_20_xyz

上述代碼可以將當前工作簿中所選擇的區域以htm文件的格式發布到一個指定的目錄中,該目錄可以是本地目錄,也可以是遠程服務器上的目錄,或者是Sharepoint中的一個特定的Folder。Publish方法的參數為True表示如果目標地址的文件存在則替換,為False表示如果目標地址的文件存在則追加。AutoRepublish方法的參數用於指示當Excel文件保存的時候是否自動重新發布。

在Excel2007中,相當於點擊窗體左上角的Office按鈕,選擇「發布」,點擊「Document Management Server」,在彈出的對話框中選擇相應的格式對文檔進行發布操作。 6. 遍歷ActiveWorkbook中的表單集合 Sub Test()

ForEach Item In ActiveWorkbook.Sheets

Debug.Print Item.name

Next Item

End Sub

7. 關閉當前工作簿 Sub close()

ActiveWorkbook.Close SaveChanges:=False

End Sub

將當前工作簿關閉,SaveChanges為False表示不保存當前更改。 8. 保護工作簿的結構和窗體 Sub protect()

ActiveWorkbook.Protect Password:=”pass”, Structure:=True, Windows:=True

End Sub

該操作相當於在Excel2007中,選擇「Review」菜單,選擇「Protect Workbook」,點擊「Protect Structure and Windows」操作,該代碼示例中給該操作設置了一個用於還原的密碼。 9. 列印工作表 Sub print()

ActiveWorkbook.Sheets(1).Printout Copies:=2, Collate:=True

End Sub

10. 移除工作簿中的個人信息 Sub remove()

ActiveWorkbook.RemovePersonalInformation =True

End Sub

11. 為工作簿設置打開密碼 Sub pass()

ActiveWorkbook.Password =”pass”

End Sub

該操作相當於在Excel2007中,點擊「另存為」,在彈出的對話框中選擇「工具」,點擊「General Options…」,在彈出的對話框中設置用於打開工作簿的密碼。 12. 為工作簿設置可寫密碼 Sub passWrite()

ActiveWorkbook.WritePassword =”pass”

End Sub

該操作相當於在Excel2007中,點擊「另存為」,在彈出的對話框中選擇「工具」,點擊「General Options…」,在彈出的對話框中設置可修改工作簿的密碼。 13. 在當前工作簿中打開新窗口 Subnew()

ActiveWorkbook.Windows(1).NewWindow

End Sub

14. 通過編程方式查找遍歷工作簿當中的所有鏈接360pskdocImg_21_xyz

Sub PrintSimpleLinkInfo()

Dim avLinks As Variant

Dim nIndex AsInteger

Dim wb As Workbook

Set wb = ActiveWorkbook

avLinks = wb.LinkSources(xlExcelLinks)

IfNotIsEmpty(avLinks) Then

For nIndex =1ToUBound(avLinks)

Debug.Print “Link found to ‘”& avLinks(nIndex) &”‘”

Next nIndex

Else

Debug.Print “The workbook ‘”& wb.name &”‘ doesn’t have any links.”

EndIf

End Sub360pskdocImg_22_xyz

xlLink為一組常量,代表了Excel工作簿中各種不同類型的鏈接。

xlExcelLinks 指向Excel工作表。

xlOLELinks 指向OLE數據源。

xlPublishers Macintosh使用。

xlSubscribers Macintosh使用。

15. 工作簿常用屬性使用360pskdocImg_23_xyz

Sub TestPrintGeneralWBInfo()

Dim wb As Workbook

Set wb = ActiveWorkbook

Debug.Print “Name: “& wb.name

Debug.Print “Full Name: “& wb.FullName

Debug.Print “Code Name: “& wb.CodeName

Debug.Print “Path: “& wb.Path

If wb.ReadOnly Then

Debug.Print “The workbook has been opened as read-only.”

Else

Debug.Print “The workbook is read-write.”

EndIf

If wb.Saved Then

Debug.Print “The workbook does not need to be saved.”

Else

Debug.Print “The workbook should be saved.”

EndIf

End Sub360pskdocImg_24_xyz

360pskdocImg_25_xyz  360pskdocImg_26_xyz  返回目錄

ActiveWorksheet

1. 改變當前工作表的名稱 Sub changeName()

ActiveSheet.name =”My Sheet”

End Sub

2. 向當前工作表添加超鏈接360pskdocImg_27_xyz

PublicSub AddHyperlink()

ActiveSheet.Hyperlinks.Add _

Anchor:=Range(“A1”), _

Address:=””, _

SubAddress:=”‘Sheet1’!A1″, _

ScreenTip:=” Goes to Sheet1″, _

TextToDisplay:=” Link to Sheet1″

End Sub360pskdocImg_28_xyz

3. 使用Copy和Paste方法 Sub copy()

Cells(2, “B”).copy

Range(“B2:B10”).Select

ActiveSheet.Paste

End Sub

單元格拷貝時會同時拷貝該單元格的內容、格式以及公式等信息。 4. 對工作表設置密碼360pskdocImg_29_xyz

Sub protect()

ActiveWorksheet.Protect Password:=”pass”

End Sub

Sub protects()

ActiveWorksheet.Protect Password:=”pass”, AllowFormattingCells:=True, _

AllowSorting:=True

End Sub360pskdocImg_30_xyz

5. 設置工作表的DisplayPageBreaks屬性 Sub Main()

ActiveSheet.DisplayPageBreaks =False

ActiveSheet.DisplayPageBreaks =True

End Sub

DisplayPageBreaks屬性用於指示是否顯示工作表的分頁符,如果沒有安裝列印機,則不能設置該屬性的值。

360pskdocImg_31_xyz  360pskdocImg_32_xyz  返回目錄

AdvancedFilter

1. 使用AdvancedFilter Sub UniqueCustomerRedux()

Range(“J1”).Value = Range(“D1”).Value

Range(“A1”).CurrentRegion.AdvancedFilter xlFilterCopy, CopyToRange:=Range(“J1”), Unique:=True

End Sub

AdvancedFilter的使用類似於在Excel2007中「Data」菜單下「Sort&Filter」分類中的「Advanced」菜單的功能,其中xlFilterAction常量用於指定如何對數據進行Filter。

360pskdocImg_33_xyz  360pskdocImg_34_xyz   返回目錄

AutoFill

1. 使用AutoFill方法自動填充單元格 Sub autoFill()

Range(“F2:F13”).autoFill Destination:=Range(“F2:I11”)

End Sub

用於從SourceRange填充數據到DestinationRange,可選參數xlAutoFillType常量用於指定填充數據的方式。數據填充過程中如果SourceRange和DestinationRange的Rows數目不一致會發生異常。