Excel VBA 使用函數範例參考之六

自定義菜單
　　把建立和刪除自定義菜單的代碼分別寫在Workbook_open和Workbook_beforeclosed
　　的事件中。
　　應該用VBA，工作薄代碼中有workbook-open()過程，在該過程中寫入
　　with activeworkbook
　　.sheets("表2").active
　　end with
　　VBA實現向鎖定工作表中插入行,並自動複制上面行中指定列的函數
　　Option Explicit
　　Public Const strPass = "123" 123是口令
　　Sub 行上再插入一行()
　　ActiveSheet.Unprotect password:=strPass
　　Selection.Copy
　　Selection.Insert Shift:=xlDown
　　Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
　　SkipBlanks:= _
　　False, Transpose:=False
　　Application.CutCopyMode = False
　　ActiveSheet.Protect password:=strPass
　　End Sub
　　如何使不出現每次關閉XLS文件時出現的：
　　“XXX.xls檔已被修改，是否可在其修改後的內容？”字樣？？
　　可以在工作表關閉之前進行手工保存工作
　　ThisWorkbook.save
　　如何實現動態時間顯示?
　　sub mytime
　　range("a1")=now()
　　Application.OnTime Now + Timevalue("00:00:01"), "mytime"
　　end sub
　　用 vba 判斷指定 excel 檔是否打開？
　　For Each w In Workbooks
　　If w.Name XXX Then
　　…………
　　End If
　　Next w
　　vba怎麽調用excel自帶的函數?比如vlookup?
　　Application.WorksheetFunction.f(x)
　　f(x)是你想使用的工作表函數
　　但是用內部函數時引用單元格會出錯，怎麽辦？
　　把你要引用的單元格改成VBA認可格式（類型）。如在Excel中的“F7:F12”應改爲
　　“Range("F7:F12")”等。
　　VBA中如何關閉,保存和退出Excel?
　　Workbooks("你的工作簿").Save。
　　下表舉例說明瞭使用 Rows 和 Columns 屬性的一些行和列的引用。
　　引用 含義
　　Rows(1) 第一行
　　Rows 工作表上所有的行
　　Columns(1) 第一列
　　Columns("A") 第一列
　　Columns 工作表上所有的列
　　若要同時處理若幹行或列，請創建一個對象變量並使用 Union 方法，將對 Rows 屬
　　性或 Columns 屬性的多個調用組合起來。下例將活動工作簿中第一張工作表上的第
　　一行、第三行和第五行的字體設置爲加粗。
　　Sub SeveralRows()
　　Worksheets("Sheet1").Activate
　　Dim myUnion As Range
　　Set myUnion = Union(Rows(1), Rows(3), Rows(5))
　　myUnion.Font.Bold = True
　　End Sub
　　如果只是你說的只連接幾個儲存格那用簡單的方法
　　Range("A1").Formula = Application.Evaluate("=[Book2.xls]Sheet1!A1")
　　或
　　Range("A1").Formula = "=[Book2.xls]Sheet1!A1"
　　請問在vba如何呼叫已定義的名稱範圍
　　我在a1:b100插入名稱∶myrange
　　請問我如何用vba選取此範圍
　　Range("myrange").Select
　　如何訪問沒有打開的EXCEL文件？
　　Sub AlternativeImport()
　　Dim xlapp As Excel.Application
　　Dim wbSource As Excel.Workbook
　　Set xlapp = New Excel.Application
　　xlapp.EnableEvents = False
　　Set wbSource = xlapp.Workbooks.Open("C:\test\Book2.xls")
　　Range("A1:A10").Value = wbSource.Sheets("Sheet1").Range
　　("A1:A10").Value
　　wbSource.Close False
　　xlapp.Quit
　　End Sub
　　怎樣使VBAprject工程不可查看？（不用密碼）
　　用可編輯十六進制文件的軟件工具（如WinHex等）打開Excel.xls,在文件的尾部,查
　　找ID="{00000000-0000-0000-0000-000000000000}"(有工程鎖定密碼時),或
　　ID="{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}"(沒有工程鎖定密碼時),修改其中
　　的任意1位後,保存,即可達到目的.當查看工程是會出現“工程不可查看”的提示.
　　注意:修改前,一定要備份原文件,以防不測
　　如何用VBA控制報表的格式（左邊距，紙張大小，列印第幾頁等）
　　列印第幾頁控制:ActiveWindow.SelectedSheets.PrintOut From:=x, To:=y
　　ActiveSheet.PageSetup.LeftMargin= 左邊距
　　ActiveSheet.PageSetup..PaperSize = 紙張大小
　　如何使VBA自動消除使用COPY複制後産生的虛線框？
　　Application.CutCopyMode = False
　　替換Excel 97的菜單欄是很容易的，只需創建一個新的菜單欄就會刪除Excel 97的
　　菜單欄。當需要恢複Excel 97的菜單欄時，只要刪除新創建的菜單欄就可以了。該
　　系統的自定義菜單中只需兩個命令按鈕，一個用來返回到系統的主畫面
　　（ReturnMAIN），另一個用來退出系統（ExitSYS）。下麵是模塊（Module）中有關
　　的巨集或是事件控制程式。
　　Sub ZapMenu( )
　　On Error Resume Next
　　CommandBars(“保險查詢系統”).Delete
　　End Sub
　　這是一個用來刪除自定義菜單欄的宏。語句On Error Resume Next保證無論自
　　定義菜單欄是否存在都能正確刪除它。
　　Sub ExitSYS( )
　　ZapMenu
　　ActiveWorkbook.Close SaveChanges := False
　　End Sub
　　這是用來退出系統的宏。它刪除自定義菜單，並關閉活動的工作簿（不提示保存
　　修改）。
　　Sub ReturnMAIN( )
　　Worksheets(“保險查詢系統”).Select
　　End Sub
　　該宏用來返回主畫面。它啟動“保險查詢系統”工作表。
　　Sub SetMenu( )
　　Dim myBar As CommandBar
　　Dim myButton As CommandBarButton
　　ZapMenu
　　Set myBar = CommandBars.Add(Name:=“保險查詢系統”, _
　　Position :=msoBarTop, _
　　MenuBar :=True)
　　Set myButton = myBar.Controls.Add(msoControlButton)
　　myButton. = msoButtonCaption
　　myButton.Caption = “退出[&E]”
　　myButton.OnAction = “ExitSYS”
　　Set myButton = myBar.Controls.Add(msoControlButton)
　　myButton. = msoButtonCaption
　　myButton.Caption = “返回[&R]”
　　myButton.OnAction = “ReturnMAIN”
　　myButton.Visible = False
　　myBar.Protection = msoBarNoMove + msoBarNoCustomize
　　myBar.Visible = True
　　End Sub
　　這個巨集包含五部分。第一部分定義了一對變量。第二部分首先運行ZapMenu巨集，
　　保證保險查詢系統菜單欄是不存在的，然後創建它。參數MenuBar的值設爲True，確
　　保這個新創建的命令欄爲一菜單欄。第三部分和第四部分將兩個命令按鈕加入到菜單
　　欄中。並設置ReturnMAIN命令按鈕的初始狀態爲不可見狀態。最後一部分保護這個
　　新創建的菜單欄，使用戶不能移動也不能自定義新菜單欄。
　　工作表彙總
　　Sub sum() '表彙總,第1張的a1:e20等於所有表的相同單元格的和
　　Attribute sum.VB_ProcData.VB_Invoke_Func = "z\n14"
　　Dim X As Worksheet
　　For y = 1 To 20
　　For z = 1 To 5
　　For Each X In Worksheets
　　shname = X.Name
　　ActiveSheet.Cells(y, z).Value = ActiveSheet.Cells(y, z).Value +
　　Worksheets(shname).Cells(y, z)
　　Next
　　Next z
　　Next y
　　End Sub(王朝網路 wangchao.net.cn)
補充說明:
在我們日常使用Excel的時候，不僅會用到當前Excel文件的數據，還經常需要訪問其他的數據文件。這些數據文件可能是Excel文件、文本文件或數據庫文件等。經常有朋友會問如何在vba代碼裏操作這些數據文件？本文就系統地介紹一下在Excel中應用VBA操作數據文件的方法。
　　本文主要介紹四種常用的方法：
　　1、利用Excel對象來處理文件；
　　2、利用VBA文件處理語句來處理文件；
　　3、利用FileSystemObject對象來處理文件；
　　4、利用API函數來處理文件。
　　當然對于數據庫文件，還可以利用ADO＋SQL的方法操作，不過論壇已經有前輩詳細介紹過此類方法，本文就不再重複了。
　　一、利用Excel對象來處理文件
　　利用Excel對象自帶的方法來操作文件是最方便，也是最簡單的。
　　我們主要利用Workbooks集合和Workbook對象的方法來操作文件。
　　1、打開Excel文件
　　我們可以用Workbooks.Open方法打開一個Excel工作簿。
　　Workbooks.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)
　　 其中FileName是必選的參數，表示要打開的工作簿名，如果沒有指定路徑，則代表當前路徑。另外14個是可選參數，除了密碼參數，其他的一般很少用。具體的含義可以參看VBA的幫助。
　　例：
　　 Workbooks.Open "F:\test.xls"
　　可以打開F盤的test.xls文件。
　　2、打開文本文件
　　 使用Open方法也可以打開文本文件，但建議使用OpenText方法。此方法是載入一個文本文件，並將其作爲包含單個工作表的工作簿進行分列處理，然後在此工作表中放入經過分列處理的文本文件數據。完整語法如下：
　　Workbooks.OpenText(FileName, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers, Local)
　　關于以上參數的具體含義可以參看VBA的幫助，這裏就不重複了。在實際的編程中，一般無需對這些複雜的參數進行處理。可以通過錄制宏來得到打開一個文本文件的VBA代碼。具體方法就是選擇“文件——打開”，然後選擇打開文本文件，就會出現文本導入向導，一步一步執行完，直到文本打開後，停止錄制。
　　以下是錄制宏得到的代碼:
　　Sub Macro1()
　　'
　　' Macro1 Macro
　　' 宏由 MC SYSTEM 錄制，時間: 2007-3-29
　　'
　　'
　　 Workbooks.OpenText Filename:="F:\CallWindowProc.txt", Origin:=xlWindows, _
　　 StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
　　 ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
　　 , Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
　　 TrailingMinusNumbers:=True
　　End Sub
　　在實際編程中只要做相應的修改就可以使用了。
　　3、打開其他文件
　　利用Excel對象還可以打開XML文件和一些數據庫（如Access）文件，對應XML文件，需要Excel2003以上的版本。
　　OpenXML方法的語法如下：
　　Workbooks.OpenXML(Filename, Stylesheets, LoadOption)
　　FileName String 類型，必需。要打開的文件名。
　　Stylesheets Variant 類型，可選。單個值或值的數組，用于指定要應用哪些 XSL 轉換 (XSLT) 樣式表處理指令。
　　LoadOption Variant 類型，轉換。指定 Excel 打開 XML 數據文件的方式。可爲 XlXmlLoadOption 常量之一。
　　XlXmlLoadOption 可爲以下 XlXmlLoadOption 常量之一：
　　xlXmlLoadImportToList 將 XML 數據文件的內容置于 XML 列表中。
　　xlXmlLoadMapXml 在“XML 結構”任務窗格中顯示 XML 數據文件的架構。
　　xlXmlLoadOpenXml 打開 XML 數據文件。文件的內容將展開。
　　xlXmlLoadPromptUser 提示用戶選擇打開文件的方式。
　　示例
　　下面的代碼打開了 XML 數據文件“customers.xml”並在 XML 列表中顯示了此文件的內容。
　　Sub UseOpenXML()
　　 Application.Workbooks.OpenXML _
　　 Filename:="customers.xml", _
　　 LoadOption:=xlXmlLoadImportToList
　　End Sub
　　OpenDatabase 方法語法如下：
　　Workbooks.OpenDatabase(FileName, CommandText, CommandType, BackgroundQuery, ImportDataAs)
　　FileName String 類型，必需。連接字符串。
　　CommandText Variant 類型，可選。查詢的命令文本。
　　CommandType Variant 類型，可選。查詢的命令類型。以下是可用的命令類型：Default、SQL 和 Table。
　　BackgroundQuery Variant 類型，可選。查詢的背景。
　　ImportDataAs Variant 類型，可選。確定查詢的格式。
　　示例
　　本示例中，Excel 打開了“northwind.mdb”文件。
　　Sub OpenDatabase()
　　Workbooks.OpenDatabase FileName:="C:\northwind.mdb"
　　End Sub
　　4、保存文件
　　文件的保存使用Workbook對象的Save或SaveAs方法。
　　Save方法使用簡單，語法爲
　　expression.Save，expression是某個Workbook對象。
　　如：ActiveWorkbook.Save
　　即保存當前活動工作簿。
　　如果是第一次保存工作簿或要另存爲，請使用 SaveAs 方法爲該文件指定文件名。
　　其語法爲：
　　expression.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)
　　具體參數含義可參看VBA幫助，使用都比較簡單。
　　示例
　　本示例新建一個工作簿，提示用戶輸入文件名，然後保存該工作簿。
　　Set NewBook = Workbooks.Add
　　Do
　　 fName = Application.GetSaveAsFilename
　　Loop Until fName <> False
　　NewBook.SaveAs Filename:=fName
　　Application.GetSaveAsFilename爲調出標准的“另存爲”對話框，獲取用戶文件名，但並不真正保存任何文件，然後使用代碼保存文件。還有Application.GetOpenFileName可以調出標准的“打開”對話框。
　　5、關閉文件
　　關閉文件可以使用Workbooks集合或Workbook對象的 Close 方法。前者是關閉所有打開的工作簿，後者關閉特定的工作簿。
　　Workbook對象的 Close 方法語法爲：
　　expression.Close(SaveChanges, Filename, RouteWorkbook)
　　SaveChanges參數表示是否保存更改，對許多不需要更改的操作，可設置爲False以免彈出保存更改提示的對話框。
　　FileName 可選。以此文件名保存所做的更改。
　　RouteWorkbook 可選。如果指定工作簿不需要傳送給下一個收件人（沒有傳送名單或已經傳送），則忽略該參數。
　　示例
　　本示例關閉 Book1.xls，並放棄所有對此工作簿的更改。
　　Workbooks("BOOK1.XLS").Close SaveChanges:=False
　　
　　本示例關閉所有打開的工作簿。如果某個打開的工作簿有改變，Microsoft Excel 將顯示詢問是否保存更改的對話框和相應提示。
　　Workbooks.Close
　　6、綜合實例
　　假如F盤有一個Excel文件test.xls，現在有另一個Excel文件要訪問test.xls的數據，我們來看用VBA代碼如何操作。代碼如下：
　　Public Sub test()
　　Application.ScreenUpdating = False
　　Workbooks.Open "f:\test.xls"
　　ThisWorkbook.Sheets(1).Range("b1") = ActiveWorkbook.Sheets(1).Range("a2")
　　ActiveWorkbook.Close
　　Application.ScreenUpdating = True
　　End Sub
　　首先關閉屏幕刷新，是爲了防止test.xls在打開時被看見(有時候還是看的見）。打開後，見test.xls的Sheet1的單元格A2中的值賦給當前工作簿的Sheet1的單元格B2，然後關閉test.xls。
　　當要打開的工作簿不確定的時候，可以通過調用打開對話框來讓用戶自己選擇。
　　可改爲如下：
　　Public Sub test()
　　Application.ScreenUpdating = False
　　Dim Filename as String
　　Filename = Application.GetOpenFileName
　　Workbooks.Open Filename
　　ThisWorkbook.Sheets(1).Range("b1") = ActiveWorkbook.Sheets(1).Range("a2")
　　ActiveWorkbook.Close
　　Application.ScreenUpdating = True
　　End Sub
　　7、總結
　　利用Excel對象的方法進行文件操作是最簡單，也是最方便的，適合初學者。對于Excel文件格式，如果我們僅僅是讀取其表格中的內容，這種方法也是首選。對于文本文件的操作，使用第二種方法比較方便，若要將文本轉換成表格，那麽使用此方法也是合適的.

---

 相關閱讀...

Excel VBA 使用函數範例參考之一
Excel VBA 使用函數範例參考之二
Excel VBA 使用函數範例參考之三
Excel VBA 使用函數範例參考之四
Excel VBA 使用函數範例參考之五