[ 내용 추가 2012.02.25 ]

    아래 게시물이 좀 더 정리된 아이입니다.

    아래 링크를 참고하세요..

    http://www.abyul.com/zbxe/100478

[ 내용 추가 끝~ ]

 

 

최근에 아별툴의 설치시 에러를 유발시키는 이유를 알아낸 것 같습니다. ㅠㅠ

오피스2010 32bit와 64bit 버전의 문제점인 듯 싶네요..

 

api호출문을 수정하여 호환성을 갖도록 수정하겠습니다..

이거 머.. 산 넘어 산이군요.. ㅋㅋ

 

참고로.. 64비트 오피스 프로그램에서 win32 api를 사용할 경우, 아래의 에러 메시지를 띄웁니다.

컴파일 오류입니다:
이 프로젝트의 코드를 업데이트해야 64비트 시스템에서 사용할 수 있습니다.
Declare 문을 검토하고 업데이트한 다음 PtrSafe 특성으로 표시하십시오.

 

 

해결 방법은,

아래와 같은 조건부 컴파일 명령문을 사용하는 것입니다.

#If VBA7 Then    ' VBA7

선언부-64비트용

#Else    ' Downlevel when using previous version of VBA7

선언부-32비트용

#End If

 

 

 

출처 : http://www.jkp-ads.com/articles/apideclarations.asp

Declarations by API function

Function name Declarations (32 bit followed by 64 bit)
FindWindow
Private DeclareFunction FindWindow Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private
Declare PtrSafe Function FindWindow Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
FindWindowEx
Private DeclareFunction FindWindowEx Lib "USER32" _
Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 AsLong, _
ByVal lpsz1 AsString, ByVal lpsz2 As String) As Long

Private
Declare PtrSafe Function FindWindowEx Lib "USER32" _
Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, _
ByVal lpsz1 AsString, ByVal lpsz2 As String) As LongPtr
GetClassName
Public DeclareFunction GetClassName Lib "USER32" Alias "GetClassNameA" _
(ByVal hWnd As Long, ByVal lpClassName As String, _
ByVal nMaxCount As Long) As Long

Public Declare PtrSafe Function GetClassName Lib "USER32" Alias "GetClassNameA" _
(ByVal hWnd AsLongPtr, ByVal lpClassName As String, _
ByVal nMaxCount As LongPtr) As LongPtr
getDC
Private DeclareFunction GetDC Lib "USER32" (ByVal hWnd As Long) As Long

Private
Declare PtrSafe Function GetDC Lib "USER32" (ByVal hWnd As LongPtr) As LongPtr
GetDesktopWindow
Public DeclareFunction GetDesktopWindow Lib "USER32" () As Long

Public Declare PtrSafe Function GetDesktopWindow Lib "USER32" () As LongPtr
getDeviceCaps
Private DeclareFunction GetDeviceCaps Lib "gdi32" (ByVal hDC As Long, ByVal nIndex AsLong) AsLong

Private
Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hDC As LongPtr, ByVal nIndex AsLong) AsLong
GetDriveType
Private DeclareFunction GetDriveType Lib "kernel32" Alias _
"GetDriveTypeA" (ByVal sDrive As String) As Long

Private Declare PtrSafe Function GetDriveType Lib "kernel32" Alias _
"GetDriveTypeA" (ByVal sDrive As String) As LongPtr
GetForegroundWindow
Declare Function GetForegroundWindow Lib "user32.dll" () As Long

Declare PtrSafe Function GetForegroundWindow Lib "user32.dll" () As LongPtr
Declare Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long

Private
Declare PtrSafe Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) AsLong
GetKeyState
Declare Function GetKeyState Lib "USER32" (ByVal vKey As Long) As Integer

Declare
PtrSafe Function GetKeyState Lib "USER32" (ByVal vKey As Long) As Integer
GetLastInputInfo
#If VBA7 Then
Private Type LASTINPUTINFO
cbSize As LongPtr
dwTime As LongPtr
End Type
Private Declare PtrSafe Sub GetLastInputInfo Lib "USER32" (ByRef plii As LASTINPUTINFO)
#Else
Private Type LASTINPUTINFO
cbSize AsLong
dwTime AsLong
End Type
Private DeclareSub GetLastInputInfo Lib "USER32" (ByRef plii As LASTINPUTINFO)
#End If
GetSystemMetrics
Private DeclareFunction GetSystemMetrics Lib "USER32" (ByVal nIndex As Long) As Long

Private
Declare PtrSafe Function GetSystemMetrics Lib "USER32" (ByVal nIndex As Long) As Long
GetTempPath
Declare Function GetTempPath Lib "kernel32" _
Alias "GetTempPathA" (ByVal nBufferLength As Long, _
ByVal lpbuffer As String) As Long

Declare PtrSafe Function GetTempPath Lib "kernel32" _
Alias "GetTempPathA" (ByVal nBufferLength As longptr, _
ByVal lpbuffer As String) As Long
getTickCount
Private DeclareFunction getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) AsLong

Private
Declare PtrSafe Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) AsLong
'
getTime
Private DeclareFunction timeGetTime Lib "winmm.dll" () As Long

Private
Declare PtrSafe Function timeGetTime Lib "winmm.dll" () As Long
GetWindow
Public DeclareFunction GetWindow Lib "USER32" _
(ByVal hWnd AsLong, ByVal wCmd As Long) As Long

Public Declare PtrSafe Function GetWindow Lib "USER32" _
(ByVal hWnd As LongPtr, ByVal wCmd As LongPtr) As LongPtr
GetWindowLong
Private DeclareFunction GetWindowLong Lib "USER32" Alias "GetWindowLongA" (ByVal hWnd AsLong, ByVal nIndex As Long) As Long

Private
Declare PtrSafe Function GetWindowLong Lib "USER32" Alias "GetWindowLongA" (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr
GetWindowsDirectory
Declare Function GetWindowsDirectory& Lib "kernel32" Alias _
"GetWindowsDirectoryA" (ByVal lpbuffer As String, _
ByVal
nSize AsLong)

Declare PtrSafe Function GetWindowsDirectory& Lib "kernel32" Alias _
"GetWindowsDirectoryA" (ByVal lpbuffer As String, _
ByVal
nSize As LongPtr)
GetWindowText
Public DeclareFunction GetWindowText Lib "USER32" Alias "GetWindowTextA" _
(ByVal hWnd AsLong, ByVal lpString As String, _
ByVal cch AsLong) AsLong

Public Declare PtrSafe Function GetWindowText Lib "USER32" Alias "GetWindowTextA" _
(ByVal hWnd As LongPtr, ByVal lpString As String, _
ByVal cch As LongPtr) As Long
InternetGetConnectedState
Public DeclareFunction InternetGetConnectedState _
Lib "wininet.dll" (lpdwFlags As Long, _
ByVal dwReserved As Long) As Boolean

Public Declare PtrSafe Function InternetGetConnectedState _
Lib "wininet.dll" (lpdwFlags As LongPtr, _
ByVal dwReserved As long) As Boolean
IsCharAlphaNumericA
Private DeclareFunction IsCharAlphaNumericA Lib "USER32" (ByVal byChar As Byte) As Long

Private
Declare PtrSafe Function IsCharAlphaNumericA Lib "USER32" (ByVal byChar As Byte) As Long
ReleaseDC
Private DeclareFunction ReleaseDC Lib "USER32" (ByVal hWnd As Long, ByVal hDC AsLong) AsLong

Private
Declare PtrSafe Function ReleaseDC Lib "USER32" (ByVal hWnd As LongPtr, ByVal hDC As LongPtr) As Long
SendMessage
Public DeclareFunction SendMessageA Lib "user32" (ByVal hWnd As Long, ByVal wMsg AsLong, _
ByVal wParam AsLong, lParam As Any) As Long
Public Declare PtrSafe Function SendMessageA Lib "user32" (ByVal hWnd As LongPtr, ByVal wMsg AsLong, _
ByVal wParam AsLong, lParam As Any) As LongPtr
SetActiveWindow
Declare Function SetActiveWindow Lib "user32.dll" (ByVal hWnd As Long) As Long

Declare PtrSafe Function SetActiveWindow Lib "user32.dll" (ByVal hWnd As LongPtr) AsLongPtr
SetCurrentDirectory
Private DeclareFunction SetCurrentDirectoryA Lib "kernel32" (ByVal lpPathName As String) As Long

Private
Declare PtrSafe Function SetCurrentDirectoryA Lib "kernel32" (ByVal lpPathName As String) As Long
SetWindowLongPtr
Private DeclareFunction SetWindowLongPtrLib "USER32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

Private
Declare PtrSafe Function SetWindowLongPtr Lib "USER32" Alias "SetWindowLongA" (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
SHBrowseForFolder
#If VBA7 Then
Private Type BROWSEINFO
hOwner As LongPtr
pidlRoot As Longp
pszDisplayName AsString
lpszTitle AsString
ulFlags AsLong
lpfn As LongPtr
lParam As LongPtr
iImage AsLong
End Type

Private Declare PtrSafe Function SHBrowseForFolder Lib "shell32.dll" Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As LongPtr
#Else
Private Type BROWSEINFO
hOwner AsLong
pidlRoot AsLong
pszDisplayName AsString
lpszTitle AsString
ulFlags AsLong
lpfn AsLong
lParam AsLong
iImage AsLong
End Type

Private DeclareFunction SHBrowseForFolder Lib "shell32.dll" Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long
#End If
Private Const BIF_RETURNONLYFSDIRS = &H1
ShellExecute
Private DeclareFunction ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Private
Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongPtr
SHFileOperation
#If VBA7 Then
Type SHFILEOPSTRUCT
hWnd As LongPtr
wFunc As Long
pFrom AsString
pTo AsString
fFlags AsInteger
fAborted AsBoolean
hNameMaps As Longptr
sProgress AsString
End Type
Declare PtrSafe Function SHFileOperation Lib "shell32.dll" Alias "SHFileOperationA" _
(lpFileOp As SHFILEOPSTRUCT) As LongPtr
#Else
Type SHFILEOPSTRUCT
hWnd AsLong
wFunc AsLong
pFrom AsString
pTo AsString
fFlags AsInteger
fAborted AsBoolean
hNameMaps AsLong
sProgress AsString
End Type
Declare Function SHFileOperation Lib "shell32.dll" Alias "SHFileOperationA" _
(lpFileOp As SHFILEOPSTRUCT) As Long
#End If
SHGetPathFromIDList
Private DeclareFunction SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, ByVal pszPath AsString) AsBoolean

Private Declare PtrSafe Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" _
(ByVal pidl As LongPtr, ByVal pszPath As String) As Boolean
timeGetTime
Private DeclareFunction timeGetTime Lib "winmm.dll" () As Long

Private
Declare PtrSafe Function timeGetTime Lib "winmm.dll" () As Long

 

 

 

 

출처 : http://msdn.microsoft.com/en-us/library/ee691831.aspx#odc_office2010_Compatibility32bit64bit_ApplicationProgrammingInterfaceCompatibility

Application Programming Interface Compatibility

The combination of VBA and type libraries gives you lots of functionality to create Microsoft Office applications. However, sometimes you must communicate directly with the computer’s operating system and other components such as when you manage memory or processes, when working with the user interface such as windows and controls, or when modifying the Windows registry. In these scenarios, your best option is to use one of the external functions that are embedded in dynamic linked library (DLL) files. You do this in VBA by making API calls using Declare statements.

note Note:

Microsoft provides a Win32API.txt file which contains 1,500 Declare statements and a tool to cut and paste the Declare statement that that you want into your code. However, these statements are for 32-bit systems and must be converted to 64-bit by using the information discussed later in this article. Existing Declare statements will not compile in 64-bit VBA until they have been marked as safe for 64-bit by using the PtrSafe attribute. You can find samples of this type of conversion at Excel MVP Jan Karel Pieterse’s Web site at: http://www.jkp-ads.com/articles/apideclarations.asp.

The Office Code Compatibility Inspector user’s guide is a useful tool to inspect the syntax of API Declare statements for the PtrSafe attribute, if needed, and the appropriate return type.

Declare statements resemble one of the following, depending whether you are calling a subroutine (which has no return value) or a function (which does have a return value).

Public/Private Declare Sub SubName Lib "LibName" Alias "AliasName" (argument list)
Public/Private Declare Function FunctionName Lib "Libname" alias "aliasname" (argument list) As Type

The SubName function or FunctionName function is replaced by the actual name of the procedure in the DLL file and represents the name that is used when the procedure is called from VBA code. You can also specify an AliasName argument for the name of the procedure, if desired. The name of the DLL file that contains the procedure being called follows the Lib keyword. And finally, the argument list contains the parameters and the data types that must be passed to the procedure.

The following Declare statement opens a subkey in the Windows registry and replaces its value.

Declare Function RegOpenKeyA Lib "advapi32.dll" (ByVal Key As Long, ByVal SubKey As String, NewKey As Long) As Long

The Windows.h (window handle) entry for the RegOpenKeyA function is as follows:

LONG RegOpenKeyA ( HKEY hKey, LPCSTR lpSubKey, HKEY *phkResult );

In Microsoft Visual C and Microsoft Visual C++, the previous example compiles correctly for both 32-bit and 64-bit. This is because HKEY is defined as a pointer, whose size reflects the memory size of the platform that the code is compiled in.

In previous versions of VBA, there was no specific pointer data type so the Long data type was used. And because the Long data type is always 32-bits, this breaks when used on a system with 64-bit memory because the upper 32-bits may be truncated or may overwrite other memory addresses. Either of these situations can result in unpredictable behavior or system crashes.

To resolve this, VBA now contains a true pointer data type: LongPtr. This new data type enables you to write the original Declare statement correctly as:

Declare PtrSafe Function RegOpenKeyA Lib “advapire32.dll” (ByVal hKey as LongPtr, ByVal lpSubKey As String, phkResult As LongPtr) As Long

This data type and the new PtrSafe attribute enable you to use this Declare statement on either 32-bit or 64-bit systems. The PtrSafe attribute indicates to the VBA compiler that the Declare statement is targeted for the 64-bit version of Office 2010. Without this attribute, using the Declare statement in a 64-bit system will result in a compile-time error. Note that the PtrSafe attribute is optional on the 32-bit version of Office 2010. This enables existing Declare statements to work as they always have.

The following table provides more information on the new qualifier and data type already discussed as well as another data type, two conversion operators, and three functions.

 

Type Item Description

Qualifier

PtrSafe

Indicates that the Declare statement is compatible with 64-bits. This attribute is mandatory on 64-bit systems.

Data Type

LongPtr

A variable data type which is a 4-bytes data type on 32-bit versions and an 8-byte data type on 64-bit versions of Office 2010. This is the recommended way of declaring a pointer or a handle for new code but also for legacy code if it has to run in the 64-bit version of Office 2010. It is only supported in the VBA 7 runtime on 32-bit and 64-bit. Note that you can assign numeric values to it but not numeric types.

Data Type

LongLong

This is an 8-byte data type which is available only in 64-bit versions of Office 2010. You can assign numeric values but not numeric types (to avoid truncation).

Conversion Operator

CLngPtr

Converts a simple expression to a LongPtr data type.

Conversion Operator

CLngLng

Converts a simple expression to a LongLong data type.

Function

VarPtr

Variant converter. Returns a LongPtr on 64-bit versions, and a Long on 32-bit versions (4 bytes).

Function

ObjPtr

Object converter. Returns a LongPtr on 64-bit versions, and a Long on 32-bit versions (4 bytes).

Function

StrPtr

String converter. Returns a LongPtr on 64-bit versions, and a Long on 32-bit versions (4 bytes).

The follow example shows how to use some of these items in a Declare statement.

Declare PtrSafe Function RegOpenKeyA Lib "advapi32.dll" (ByVal Key As LongPtr, ByVal SubKey As String, NewKey As LongPtr) As Long

Note that Declare statements without the PtrSafe attribute are assumed not to be compatible with the 64-bit version of Office 2010.

As stated earlier, there are two new conditional compilation constants: VBA7 and Win64. To ensure backward compatibility with previous versions of Microsoft Office, you use the VBA7 constant (this is the more typical case) to prevent 64-bit code from being used in the earlier version of Microsoft Office. For code that is different between the 32-bit version and the 64-bit version, such as calling a math API which uses LongLong for its 64-bit version and Long for its 32-bit version, you use the Win64 constant. The following code demonstrates the use of these two constants.

#if Win64 then
   Declare PtrSafe Function MyMathFunc Lib "User32" (ByVal N As LongLong) As LongLong
#else
   Declare Function MyMathFunc Lib "User32" (ByVal N As Long) As Long
#end if
#if VBA7 then
   Declare PtrSafe Sub MessageBeep Lib "User32" (ByVal N AS Long)
#else
   Declare Sub MessageBeep Lib "User32" (ByVal N AS Long)
#end if

To summarize, if you write 64-bit code and intend to use it in previous versions of Microsoft Office, you will want to use the VBA7 conditional compilation constant. However, if you write 32-bit code in Office 2010, that code works as is in previous versions of Microsoft Office without the need for the compilation constant. If you want to ensure that you are using 32-bit statements for 32-bit versions and 64-bit statements for 64-bit versions, your best option is to use the Win64 conditional compilation constant.

Using Conditional Compilation Attributes

The following code is an example of legacy VBA code that needs to be updated. Notice the data types in the legacy code that are updated to use LongPtr because they refer to handles or pointers

Legacy VBA Code

Declare Function SHBrowseForFolder Lib "shell32.dll" _
  Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long
  
Public Type BROWSEINFO
  hOwner As Long
  pidlRoot As Long
  pszDisplayName As String
  lpszTitle As String
  ulFlags As Long
  lpfn As Long
  lParam As Long
  iImage As Long
End Type

New VBA Code

#if VBA7 then    ' VBA7 
Declare PtrSafe Function SHBrowseForFolder Lib "shell32.dll" _
  Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Public Type BROWSEINFO
  hOwner As LongPtr
  pidlRoot As Long
  pszDisplayName As String
  lpszTitle As String
  ulFlags As Long
  lpfn As LongPtr
  lParam As LongPtr
  iImage As Long
End Type
 
#else    ' Downlevel when using previous version of VBA7

Declare Function SHBrowseForFolder Lib "shell32.dll" _
  Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Public Type BROWSEINFO
  hOwner As Long
  pidlRoot As Long
  pszDisplayName As String
  lpszTitle As String
  ulFlags As Long
  lpfn As Long
  lParam As Long
  iImage As Long
End Type
 
#end if
Sub TestSHBrowseForFolder ()
    Dim bInfo As BROWSEINFO
    Dim pidList As Long

    bInfo.pidlRoot = 0&
    bInfo.ulFlags = &H1
    pidList = SHBrowseForFolder(bInfo)
End Sub

Frequently Asked Questions

The following are frequently asked questions that relate to the 32-bit and 64-bit versions of Microsoft Office.

When should I use the 64-bit version of Microsoft Office?
This is more a matter of which host application (Excel, Word, and so forth) you are using. For example, Excel is able to handle much larger worksheets with the 64-bit version of Microsoft Office.
Can I install 64-bit and 32-bit versions of Microsoft Office side-by-side?
No.
When should I convert Long parameters to LongPtr?
You need to check the Windows API documentation on the Microsoft Developers Network for the function you want to call. Handles and pointers need to be converted to LongPtr. As an example, the documentation for RegOpenKeyA provides the following signature:
No code example is currently available or this language may not be supported.
The parameters are defined as:

 

Parameter Description

hKey [in]

A handle to an open registry key.

lpSubKey [in, optional]

The name of the registry subkey to be opened.

ulOptions

This parameter is reserved and must be zero.

samDesired [in]

A mask that specifies the desired access rights to the key.

phkResult [out]

A pointer to a variable that receives a handle to the opened key.

In Win32API_PtrSafe.txt, the Declare statement is defined as:
Declare PtrSafe Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hKey As LongPtr, ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As LongPtr) As Long
Should I convert pointers and handles in structures?
Yes. See the MSG type in Win32API_PtrSafe.txt:
Type MSG
    hwnd As LongPtr
    message As Long
    wParam As LongPtr
    lParam As LongPtr
    time As Long
    pt As POINTAPI
End TypeF
When should I use strptr, varpt, and objptr?
You should use these functions to retrieve pointers to strings, variables and objects, respectively. On the 64-bit version of Microsoft Office, these functions will return a 64-bit LongPtr, which can be passed to Declare statements. The use of these functions has not changed from previous versions of VBA. The only difference is that they now return a LongPtr.

Conclusion

The addition of a 64-bit version of Office 2010 enables you to move more data around for increased capability. When writing 32-bit code, you can use the 64-bit version of Microsoft Office without any changes. However, when you write 64-bit code, you should ensure that your code contains specific keywords and conditional compilation constants to ensure that the code is backward compatible with earlier version of Microsoft Office, and that the correct code is being executed if you mix 32-bit and 64-bit code.

Additional Resources

For more information about Declare statements, see the following resources:

 

 

 

 

 

출처 : http://msdn.microsoft.com/en-us/library/aa671659.aspx

Anatomy of a Declare Statement

Here is an example of the Declare statement for the GetTempPath function, which returns the path to the Microsoft® Windows® temporary folder:

Private Declare Function GetTempPath Lib "kernel32" _
         Alias "GetTempPathA" (ByVal nBufferLength As Long, _
         ByVal lpBuffer As String) As Long

The Declare keyword alerts VBA that you want to include the definition for a DLL function in your project. A Declare statement in a standard module can be public or private, depending on whether you want the API function to be available only to a single module or to the entire project. In a class module, a Declare statement must be private.

The name of the function that follows the Function keyword is the name you use to call the function from VBA. This name can be identical to the name of the API function itself, or you can use the Alias keyword within the Declare statement to indicate that you intend to call the function by a different name (an alias) in VBA.

In the previous example, the name of the API function in the DLL is GetTempPathA, and the name by which you would call it from VBA is GetTempPath.

Note   The actual name of the DLL function appears after the Alias keyword.
Note   GetTempPath is the name the Win32API.txt file uses to alias the function, but you could change this to be any name you wanted.

Here are a few reasons why you might want to use an alias within a Declare statement:

  • Some API function names begin with an underscore character (_), which is not legal in VBA. To call the function from VBA, you must use an alias name.
  • Because an alias makes it possible for you to name a DLL function anything you want to, you can make the function name conform to your own naming standards within VBA.
  • Because API functions are case-sensitive and VBA functions are not, you can use an alias to change the case of a function name.
  • Some DLL functions have arguments that can take different data types. The VBA Declare statements for these functions define these arguments as type Any. Calling a DLL function with arguments declared as Any can be perilous, because VBA does not perform any data type checking for you. If you want to avoid the hazards of passing arguments as Any, you can declare multiple versions of the same DLL function, each with a different name and a different data type.
  • The Windows API contains two versions of all functions that take string arguments: an ANSI version and a Unicode version. The ANSI version has an "A" suffix, as shown in the previous example, while the Unicode version has a "W" suffix. Although VBA uses Unicode internally, it converts all strings to ANSI strings before calling a function in a DLL, so you usually will use the ANSI version when calling a Windows API function from VBA. The API Viewer add-in automatically aliases all functions that take string arguments, so you can call the function without including the "A" suffix.
  • The Lib keyword specifies which DLL contains the function. Note that the name of the DLL is contained in a string within the Declare statement. If the DLL specified after the Lib keyword is not found on the user's system, a call to the function will fail with run-time error number 48, "Error in loading DLL." Because you can handle this error in your VBA code, you can write robust code that deals with the error gracefully.

    Note   This is not an issue if you are calling a function in one of the basic Windows DLLs, because those DLLs must be present for your application to load.

    The following table describes the most commonly used DLLs in the Windows API:

    DLL Contains
    Kernel32.dll Low-level operating system functions, such as those for memory management and resource handling.
    User32.dll Windows management functions, such as those for message handling, timers, menus, and communications.
    GDI32.dll The Graphics Device Interface (GDI) library, which contains functions for device output, such as those for drawing, display context, and font management.

    Most DLLs, including those in the Windows API, are written in C or C++. Passing arguments to a DLL function therefore requires some understanding of the arguments and data types expected by a C or C++ function, which differ in several ways from those expected by a VBA function.

    In addition, many arguments to DLL functions are passed by value. By default, arguments in VBA are passed by reference, so it is imperative you include the ByVal keyword in the function definition when the DLL function requires that an argument be passed by value. Omitting the ByVal keyword in a function definition might cause an invalid page fault in your application in some cases. In other cases, the VBA run-time error number 49, "Bad DLL calling convention," might occur.

    Passing an argument by reference passes the memory location of that argument to the procedure being called. If the procedure modifies that argument's value, it modifies the only copy of that argument, so when execution returns to the calling procedure, the argument contains the modified value.

    Passing an argument to a DLL function by value, on the other hand, passes a copy of the argument; the function operates on a copy of the argument instead. This prevents that function from modifying the contents of the actual argument. When execution returns to the calling procedure, the argument contains the same value it did before the other procedure was called.

    Because passing by reference makes it possible for an argument to be modified in memory, if you incorrectly pass an argument by reference, the DLL function might overwrite memory that it should not, causing an error or otherwise unexpected behavior. Windows maintains many values that should not be overwritten. For example, Windows assigns to every window a unique 32-bit identifier called a handle. Handles are passed to API functions by value, because if Windows were to modify a window's handle, it would no longer be able to track that window.

    Note   Although the ByVal keyword appears in front of some arguments of type String, strings always are passed to Windows API functions by reference.

    See Also

    API Basics | What Is an API? | Why Use VBA to Call the Windows API? | API Resources | Accessing Functions in a DLL | Constants and User-Defined Types | Understanding Handles | Calling DLL Functions | Passing Arguments by Value or by Reference | Getting the Most Out of Visual Basic for Applications

     

     

     

     

     

     

     

     

     

     

     

     

     

     

profile