Microsoft Office Tutorials and References
In Depth Information
Using the Windows API
A potential problem with using add-ins to store custom functions is that your workbook
is dependent on the add-in file. If you need to share your workbook with a colleague,
you also need to share a copy of the add-in that contains the functions.
Using the Windows API
VBA can borrow methods from other files that have nothing to do with Excel or VBA — for
example, the Dynamic Link Library (DLL) files that Windows and other software use. As a result, you
can do things with VBA that would otherwise be outside the language’s scope.
The Windows Application Programming Interface (API) is a set of functions available to Windows
programmers. When you call a Windows function from VBA, you’re accessing the Windows API.
Many of the Windows resources used by Windows programmers are available in DLLs, which
store programs and functions and are linked at runtime rather than at compile time.
64-bit Excel and API functions
Excel 2010 adds a new challenge to using Windows API functions in your code because
Excel 2010 is also available in a 64-bit version. If you want your code to be compatible
with the 32-bit versions (including Excel 2007) and 64-bit version of Excel 2010, you need
to declare your API functions twice, using compiler directives to ensure that the correct
declaration is used.
For example, the following declaration works with 32-bit Excel versions, but causes a compile
error with 64-bit Excel 2010:
Declare Function GetWindowsDirectoryA Lib “kernel32” _
(ByVal lpBuffer As String, ByVal nSize As Long) As Long
In many cases, making the declaration compatible with 64-bit Excel is as simple as adding the
word PtrSafe after the Declare keyword. The following declaration is compatible with both
32-bit Excel 2010 and 64-bit Excel 2010:
Declare PtrSafe Function GetWindowsDirectoryA Lib “kernel32” _
(ByVal lpBuffer As String, ByVal nSize As Long) As Long
However, the code will fail in Excel 2007 (and earlier versions) because the PtrSafe keyword is
not recognized.
In Chapter 26, I describe how to make API function declarations compatible with all versions of
32-bit Excel and 64-bit Excel 2010.
 
Search JabSto ::




Custom Search