vba调用api能干什么（操作系统API调用和使用其它应用程序的对象库）

程序语言开发应用程序都可以调用系统库和其它应用程序的外部库，VBA也不例外。

API stands for Application Programming Interface.

API表示（操作系统提供的）应用程序编程接口。

API's 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 "blue" 'remove 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》

https://books.goalkicker.com/ExcelVBABook/

－End－

,