What If Something Goes Wrong

One of the hardest parts of working with the Windows API functions is identifying the cause of any errors. If an API call fails for any reason, it should return some indication of failure (usually a zero result from the function) and register the error with Windows. You should then be able to use the VBA function Err.LastDLLError to retrieve the error code, and use the FormatMessage API function to retrieve the descriptive text for the error:

'Windows API declaration to get the API error text Private Declare Function FormatMessage Lib "kernel32" _ Alias "FormatMessageA" ( _ ByVal dwFlags As Long, _ ByVal lpSource As Long, _ ByVal dwMessageld As Long, _ ByVal dwLanguageId As Long, _ ByVal lpBuffer As String, _ ByVal nSize As Long, _ Arguments As Long) As Long

'Constant for use in the FormatMessage API function Private Const FORMAT_MESSAGE_FROM_SYSTEM As Long = &H1000

Sub ShowExcelWindowSize()

'Define some variables to use in the API calls Dim hWnd As Long, uRect As RECT

'Get the handle on Excel's main window hWnd = FindWindow("XLMAIN", Application.Caption)

'An error occurred, so get the text of the error MsgBox LastDLLErrText(Err.LastDllError) Else

'Etc. End If End Sub

Function LastDLLErrText(ByVal lErrorCode As Long) As String 1 *****************************************************

' * Function Name: LastDLLErrText

' * Input: lErrorCode - a Windows error number

' * Output: Returns the description corresponding to the error

' * Purpose: Retrieve a Windows error description

1 *****************************************************

Dim sBuff As String * 255, iAPIResult As Long 'Get the text of the error and return it iAPIResult = FormatMessage(FORMAT_MESSAGE_FROM_SYSTEM, 0&, lErrorCode, _ 0, sBuff, 255, 0)

LastDLLErrText = Left(sBuff, iAPIResult) End Function

The full code for this example can be found in the module 'm1_ExcelWindowSize' in the API Examples.xlsm workbook, available at www.wrox.com.

Unfortunately, this technique does not always work. For example, if you change the class name to XLMAINTEST in the FindWindow function call, you may expect to get an error message of Unable to find window. In Windows XP, the error information is populated with the cryptic text The system cannot find the file specified. In most cases, you do get more useful error information, as shown in the next section.

0 0

Post a comment