VBA用到一定的时候，就会发现有些功能实现不了，被限制束缚了，这个时候一旦接触到了Windows API，就感觉又有了一片新天地。
其实很多系统的操作，像文件的操作等，都是要调用Windows API的，就算是VBA里的文件操作语句：
代码语言：javascript复制Open pathname For mode [ Access access ] [ lock ] As [ # ] filenumber [ Len = reclength ]底层实现上应该也是调用Windows API。
而很多没有的功能，也只是VBA没有帮忙实现，需要我们自己去调用Windows API实现罢了。
我原来刚接触API的时候，基本上就是在网上查，然后复制代码，对于代码的原理几乎不明白，出了问题也很难发现。
现在想想其实应该先了解一些数据类型、参数传递、内存方面的东西，再来用API的时候，就会顺利许多。
1、读取文件：
在Excel VBA里读取文件分3步：
- Open语句打开文件
- Get语句读取数据
- Close关闭打开的文件
用API读取文件其实也是一样的，只是我们要自己去声明这3个语句：
代码语言：javascript复制Public Declare Function CreateFile Lib "kernel32" Alias "CreateFileA" (ByVal lpFileName As String, ByVal dwDesiredAccess As Long, ByVal dwShareMode As Long, ByVal lpSecurityAttributes As Long, ByVal dwCreationDisposition As Long, ByVal dwFlagsAndAttributes As Long, ByVal hTemplateFile As Long) As Long
Public Declare Function ReadFile Lib "kernel32" (ByVal hFile As Long, ByVal lpBuffer As Long, ByVal nNumberOfBytesToRead As Long, ByRef lpNumberOfBytesRead As Long, ByVal lpOverlapped As Long) As Long
Public Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long函数名称不大同，参数也复杂许多，但读取数据的功能是差不多的。
API的复杂之处我认为就是参数多样，而且传递的时候非常要注意是传值还是传地址。
2、参数传递
值参数
很多参数API中只是使用，所以VBA里传递是按值传递还是按照地址传递其实都是一样的，这个和VBA里的Function是一样的。
另外有很多复杂的参数，一般都不大常用，这种在VBA里我们直接声明为Byval后传递0就可以，像CreateFile里的dwFlagsAndAttributes和hTemplateFile我们都可以设置为0，其他几个都有对应的常量选用。
指针参数
这是一种非常容易出错的参数，比如ReadFile中有个参数lpNumberOfBytesRead，这种参数是一种指针，API函数会在内部操作这个指针，所以如果这个参数一旦传递错误，比如按Byval传递了，而数字没有初始的时候是0，API函数去操作指针地址0的时候，必然会出错造成Excel的崩溃。
3、API读取文件代码
代码语言：javascript复制'lpFileName 文件名
'dwDesiredAccess 访问模式
Const GENERIC_READ As Long = &H80000000
Const GENERIC_WRITE As Long = &H40000000
'dwShareMode 共享模式，意思就是当前进程打开之后，还允许其他程序做什么，0就是独占了
Const FILE_SHARE_DELETE As Long = &H4
Const FILE_SHARE_READ As Long = &H1
Const FILE_SHARE_WRITE As Long = &H2
'lpSecurityAttributes 安全属性(也即销毁方式), 为NULL，句柄就不能被子进程继承。
Type SECURITY_ATTRIBUTES
nLength As Long
lpSecurityDescriptor As Long
bInheritHandle As Long
End Type
'dwCreationDisposition how to create
Const CREATE_NEW As Long = &H1 ' 创建新文件/对象(当对象已经存在是将返回失败)。
Const CREATE_ALWAYS As Long = &H2 ' 总是创建(如果对象存在就覆盖它，清除当前属性，把文件属性和dwFlagsAndAttributes指定的标志相结合)。
Const OPEN_EXISTING As Long = 3 ' 打开文件(如果不存在就返回失败)。
Const OPEN_ALWAYS As Long = &H4 ' 存在就打开；若不存在，假如dwCreationDisposition==CREATE_NEW就创建一个新文件。
Const TRUNCATE_EXISTING As Long = &H5 ' 存在就打开，且清空文件内容(至少要有GENERIC_WRITE权限)；若文件不存在就返回失败。
'dwFlagsAndAttributes 文件属性
Const FILE_ATTRIBUTE_READONLY As Long = &H1
Const FILE_ATTRIBUTE_NORMAL As Long = &H80
'hTemplateFile 模板文件句柄
Const INVALID_HANDLE_VALUE As Long = -1
'成功返回文件句柄
'失败返回INVALID_HANDLE_VALUE
Public Declare Function CreateFile Lib "kernel32" Alias "CreateFileA" (ByVal lpFileName As String, ByVal dwDesiredAccess As Long, ByVal dwShareMode As Long, ByVal lpSecurityAttributes As Long, ByVal dwCreationDisposition As Long, ByVal dwFlagsAndAttributes As Long, ByVal hTemplateFile As Long) As Long
'dwMoveMethod
Const FILE_BEGIN As Long = 0
Const FILE_CURRENT As Long = 1
Const FILE_END As Long = 2
Public Declare Function SetFilePointer Lib "kernel32" (ByVal hFile As Long, ByVal lDistanceToMove As Long, lpDistanceToMoveHigh As Long, ByVal dwMoveMethod As Long) As Long
'lpSecurityAttributes As SECURITY_ATTRIBUTES
Public Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
'lpBuffer 用来接收从文件中读出的数据的缓冲区指针
'nNumberOfBytesToRead 指明要读的字节总数
'lpNumberOfBytesRead 一个变量指针，用来存储实际传输的字节总数
'如果函数正确，返回非零。
Public Declare Function ReadFile Lib "kernel32" (ByVal hFile As Long, ByVal lpBuffer As Long, ByVal nNumberOfBytesToRead As Long, ByRef lpNumberOfBytesRead As Long, ByVal lpOverlapped As Long) As Long
'lpOverlapped As OVERLAPPED
Type OVERLAPPED
Internal As Long
InternalHigh As Long
offset As Long
OffsetHigh As Long
hEvent As Long
End Type
Sub TestAPIReadFile()
Dim hFile As Long
hFile = CreateFile(ThisWorkbook.Path & "test.txt", GENERIC_READ, 0, 0, OPEN_EXISTING, 0, 0)
If hFile = INVALID_HANDLE_VALUE Then
Debug.Print "open出错"
Exit Sub
End If
Dim ret As Long
Dim b() As Byte
Dim nNumberOfBytesToRead As Long
nNumberOfBytesToRead = 32
ReDim b(nNumberOfBytesToRead - 1) As Byte
Dim retlen As Long
ret = ReadFile(hFile, VarPtr(b(0)), nNumberOfBytesToRead, retlen, 0)
If ret = 0 Then
Debug.Print "read出错"
CloseHandle hFile
Exit Sub
End If
Printf "文件读取成功：读的字节总数%d， 实际读取字节总数%d，%s", nNumberOfBytesToRead, retlen, VBA.StrConv(b, vbUnicode)
CloseHandle hFile
End Sub
