程序語言開發應用程序都可以調用系統庫和其它應用程序的外部庫，VBA也不例外。

　　1 操作系統API調用 API stands for Application Programming Interface.

　　API表示（操作系統提供的）應用程序編程接口。

　　APIs for VBA imply a set of methods that allow direct interaction with the operating system.

　　VBA的API意味着一組允許與操作系統直接交互的方法。

　　System calls can be made by executing procedures defined in DLL files.

　　可以通過執行DLL文件中定義的過程來進行系統調用。

　　引入Windows API庫的某個函數或過程需要在模塊的頂點聲明，如

　　Declare PtrSafe Function GetWindowsDirectoryA Lib kernel32 _ (ByVal lpBuffer As String, ByVal nSize As Long) As Long

　　declare：在模塊級用于聲明對動态鍊接庫(DLL)中的外部過程的引用；

　　PtrSafe：同時兼容Excel的32位和64位版本；

　　GetWindowsDirectoryA：函數名，可以VBA過程或函數中調用；

　　kernel32：表示上述函數所在的動态庫（DLL);

　　該函數參數lpBuffer：返回Windows所在目錄名稱；

　　該函數參數nSize：Windows所在目錄名稱的字符串長度包含在此參數中；

　　如果代碼模塊是UserFor、Sheet或ThisWorkbook的代碼模塊，就必須用Private關鍵字聲明這個API函數。

　　1.1 Lib User32 Function

　　Option Explicit GetSystemMetrics32 info: http://msdn.microsoft.com/en-us/library/ms724385(VS.85).aspx #If Win64 Then Private Declare Function GetSystemMetrics32 Lib User32 Alias GetSystemMetrics (ByVal nIndex As Long) As Long #ElseIf Win32 Then Private Declare Function GetSystemMetrics32 Lib User32 Alias GetSystemMetrics (ByVal nIndex As Long) As Long #End If VBA Wrappers: Public Function dllGetMonitors() As Long Const SM_CMONITORS = 80 dllGetMonitors = GetSystemMetrics32(SM_CMONITORS) End Function Public Function dllGetHorizontalResolution() As Long Const SM_CXVIRTUALSCREEN = 78 dllGetHorizontalResolution = GetSystemMetrics32(SM_CXVIRTUALSCREEN) End Function Public Function dllGetVerticalResolution() As Long Const SM_CYVIRTUALSCREEN = 79 dllGetVerticalResolution = GetSystemMetrics32(SM_CYVIRTUALSCREEN) End Function Public Sub ShowDisplayInfo() Debug.Print Total monitors: & vbTab & vbTab & dllGetMonitors Debug.Print Horizontal Resolution: & vbTab & dllGetHorizontalResolution Debug.Print Vertical Resolution: & vbTab & dllGetVerticalResolution Total monitors: 1 Horizontal Resolution: 1920 Vertical Resolution: 1080 End Sub

　　1.2 Lib kernel32 Sub

　　Private Declare Sub Sleep Lib kernel32 (ByVal dwMilliseconds As Long) Public Sub TestPause() Dim start As Double start = Timer Sleep 9000 Pause execution for 9 seconds Debug.Print Paused for & Format(Timer - start, #,###.000) seconds Immediate window result: Paused for 9.000 seconds End Sub

　　更加細節參考：Excel VBA｜在VBA中調用Windows API庫中函數-今日頭條

　　可以在下面的地址中查看Windows API文檔：http://www.office-cn.net/t/api/index.html?apihelp.htm

　　2 引用其它應用程序的對象庫 If you use the objects in other applications as part of your Visual Basic application, you may want to establish a reference to the object libraries of those applications （Such as Windows Shell, Internet Explorer, XML HttpRequest, and others）.

　　如果将其他應用程序中的對象用作Visual Basic應用程序的一部分，則可能需要建立對這些應用程序的對象庫的引用，如Windows Shell、Internet Explorer、XML HttpRequest等。

　　包括的文件類型有：

　　類型庫 (*.olb, *.tlb, *.dll) 可執行文件 (*.exe, *.dll) ActiveX控件 (*.ocx) 所有文件 (*.*)

　　更多細節參考：VBA｜如何添加外部對象庫（或控件）引用來擴展VBA功能

　　如使用Microsoft VBScript Regular Expressions可引用以下庫：

　　即可建立以下對象

　　Set createVBScriptRegExObject = CreateObject(vbscript.RegExp)

　　demo code:

　　Populate, enumerate, locate and remove entries in a dictionary that was created with late binding Sub iterateDictionaryLate() Dim k As Variant, dict As Object Set dict = CreateObject(Scripting.Dictionary)// 引用Microsoft Scripting Runtime dict.CompareMode = vbTextCompare non-case sensitive compare model populate the dictionary dict.Add Key:=Red, Item:=Balloon dict.Add Key:=Green, Item:=Balloon dict.Add Key:=Blue, Item:=Balloon iterate through the keys For Each k In dict.Keys Debug.Print k - & dict.Item(k) Next k locate the Item for Green Debug.Print dict.Item(Green) remove key/item pairs from the dictionary dict.Remove blueremove individual key/item pair by key dict.RemoveAll remove all remaining key/item pairs End Sub

　　Access ADODB.Connection 需要引用：

　　demo code:

　　Const SomeDSN As String = DSN=SomeDSN;Uid=UserName;Pwd=MyPassword; Public Sub Example() Dim database As ADODB.Connection Set database = OpenDatabaseConnection(SomeDSN) If Not database Is Nothing Then ... Do work. database.Close Make sure to close all database connections. End If End Sub Public Function OpenDatabaseConnection(ConnString As String) As ADODB.Connection On Error GoTo Handler Dim database As ADODB.Connection Set database = New ADODB.Connection With database .ConnectionString = ConnString .ConnectionTimeout = 10 Value is given in seconds. .Open End With OpenDatabaseConnection = database Exit Function Handler: Debug.Print Database connection failed. Check your connection string. End Function

　　更多細節參考：VBA｜數據庫操作01：使用ADO訪問數據庫-今日頭條

　　Collection無須外部引用：

　　Public Sub Example() Dim foo As New Collection With foo .Add One .Add Two .Add Three .Add Four End With Debug.Print foo.Count Prints 4 End Sub

　　ref:

　　《VBA Notes For Professionals》

　　htt

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!