This Page Has Been Replaced

Click here to go to the new page.

Pearson Software Consulting Services

    Windows API Errors And FormatMessage

         When you are coding in VBA and using the Windows API functions, you will very often need to get the error number indicating why a particular API function failed. Most API calls don't return specific error values as their result. Usually, the return value of an API call indicates only that something went wrong, but not specifically what went wrong.  Moreover a return value of 0, for example, may indicate success in one API function but failure in another. You should test the return value of an API call and take the appropriate action based on the meaning of  the return value, as documented in the Microsoft Developer Network (MSDN) on-line documentation.

The documentation in MSDN for the Windows APIs states that you should call the GetLastError API function to get the error number generated by the API function. While GetLastError may well work fine within the world of VC++ programming, its value can get reset to 0 by the time the error number finds its way back to VBA. Thus, instead of using GetLastError , you should use Err.LastDllError instead.

Both  GetLastError and Err.LastDllError  return a Long Integer result. This number isn't directly meaningful. Therefore, Windows supplies an API function called FormatMessage that returns the text description of the supplied error number.

This page contains a function called GetSystemErrorMessageText that is a wrapper function for the FormatMessage  API function.  GetSystemErrorMessageText handles the variables and text buffer processing required to get the error text into a normal VBA String variable.  The function takes a Long error number as input, and returns as its result a string containing the descriptive error text.  Below is the complete VBA code required to use the function, including the Windows API function declaration and the required symbolic constants.  The code can work in any VBA application. There is nothing specific to Excel in the code.

You can download a bas module file here.

Option Explicit
Option Compare Text
' GetSystemErrorMessageText
' By Chip Pearson,,

' used by FormatMessage
Private Const FORMAT_MESSAGE_FROM_HMODULE  As Long = &H800
Private Const FORMAT_MESSAGE_FROM_STRING  As Long = &H400
Private Const FORMAT_MESSAGE_FROM_SYSTEM  As Long = &H1000
Private Const FORMAT_MESSAGE_TEXT_LEN  As Long = &HA0 ' from VC++ ERRORS.H file

' Windows API Declare
Private Declare Function FormatMessage Lib "kernel32" _
    Alias "FormatMessageA" ( _
    ByVal dwFlags As Long, _
    ByVal lpSource As Any, _
    ByVal dwMessageId As Long, _
    ByVal dwLanguageId As Long, _
    ByVal lpBuffer As String, _
    ByVal nSize As Long, _
    ByRef Arguments As Long) As Long

Public Function GetSystemErrorMessageText(ErrorNumber As Long) As String
' GetSystemErrorMessageText
' This function gets the system error message text that corresponds
' to the error code parameter ErrorNumber. This value is the value returned
' by Err.LastDLLError or by GetLastError, or occasionally as the returned
' result of a Windows API function.
' These are NOT the error numbers returned by Err.Number (for these
' errors, use Err.Description to get the description of the error).
' In general, you should use Err.LastDllError rather than GetLastError
' because under some circumstances the value of GetLastError will be
' reset to 0 before the value is returned to VBA. Err.LastDllError will
' always reliably return the last error number raised in an API function.
' The function returns vbNullString is an error occurred or if there is
' no error text for the specified error number.

Dim ErrorText As String
Dim TextLen As Long
Dim FormatMessageResult As Long
Dim LangID As Long

' initialize the variables
LangID = 0& 'default language
ErrorText = String$(FORMAT_MESSAGE_TEXT_LEN, vbNullChar)

' Call FormatMessage to get the text of the error message text
' associated with ErrorNumber.
FormatMessageResult = FormatMessage( _
                        dwFlags:=FORMAT_MESSAGE_FROM_SYSTEM Or _
                                 FORMAT_MESSAGE_IGNORE_INSERTS, _
                        lpSource:=0&, _
                        dwMessageId:=ErrorNumber, _
                        dwLanguageId:=LangID, _
                        lpBuffer:=ErrorText, _
                        nSize:=TextLen, _

If FormatMessageResult = 0& Then
    ' An error occured. Display the error number, but
    ' don't call GetSystemErrorMessageText to get the
    ' text, which would likely cause the error again,
    ' getting us into a loop.
    MsgBox "An error occurred with the FormatMessage" & _
           " API function call." & vbCrLf & _
           "Error: " & CStr(Err.LastDllError) & _
           " Hex(" & Hex(Err.LastDllError) & ")."
    GetSystemErrorMessageText = vbNullString
    Exit Function
End If
' If FormatMessageResult is not zero, it is the number
' of characters placed in the ErrorText variable.
' Take the left FormatMessageResult characters and
' return that text.
ErrorText = Left$(ErrorText, FormatMessageResult)
' Get rid of the trailing vbCrLf, if present.
If Len(ErrorText) >= 2 Then
    If Right$(ErrorText, 2) = vbCrLf Then
        ErrorText = Left$(ErrorText, Len(ErrorText) - 2)
    End If
End If
GetSystemErrorMessageText = ErrorText

End Function

To use the function, use code like

Sub AAATest()
    Dim ErrorNumber As Long
    Dim Description As String
    ' call some API function here
    ErrorNumber = Err.LastDllError
    If ErrorNumber <> 0 Then
        Description = GetSystemErrorMessageText(ErrorNumber)
        MsgBox "An error occurred:" & vbCrLf & _
               "Error Number: " & CStr(ErrorNumber) & " " & _
               "Hex (" & Hex(ErrorNumber) & ")" & vbCrLf & _
               "Description: " & Description
    End If
End Sub

Many of the VBA functions on this web site call Windows API functions, and many of those procedures call upon the
GetSystemErrorMessageText function to get the error text to display. You may want to include GetSystemErrorMessageText in your Personal.xls workbook so that you can easily call it from any project.

You can download a bas module file here containing all the code above.




 Created By Chip Pearson and Pearson Software Consulting, LLC 
This Page:                Updated: November 06, 2013     
MAIN PAGE    About This Site    Consulting    Downloads  
Page Index     Search    Topic Index    What's New   
Links   Legalese And Disclaimers

Copyright 1997-2007  Charles H. Pearson