Getting System Error Message Text

This page contains VBA code to return a text description of a system error message.

Introduction

When you are programming with the Windows API functions, you will very often find that the documentation describes only two return values for an function: the first if the function was successful and the second if the function was not successful. This second return value indicates only that something went wrong, but usually provides no information about what went wrong. Moreover, there is not a lot of consistency in error return codes in the Windows API functions. In some functions, a value of 0 indicates success while in others, 0 indicates a failure.

In C/C++ programming, you would use the GetLastError API function to get a more specific error code. However, GetLastError doesn't always work in VB/VBA programming. By the time the error percolates up to VBA, it has been altered or reset to 0 so you cannot use it to retrieve the error. However, the Err.LastDllError function will reliably get the last error (if any) generated by a Windows API function.

The error numbers and descriptions described on this page are the errors generated by the Windows API functions. They are not the same numbers and descriptions used by the VBA error object Err.Number and Err.Description values. The VBA Err object has nothing to do with errors generated by Windows API calls other than the usage of Err.LastDllError to get the error number generated by the API function.

Error numbers in and of themselves are rarely meaningful, especially if you need to display some information to the user. It is meaningless to display a message like Error -4123750327 occurred.. Instead, you need to translate these errors into descriptive text. Windows supplies an API function called FormatMessage that can be used to get the text description of a Windows error number.

The GetSystemErrorMessageText VBA Module

This page provides a VBA module that takes care of all the Windows API related housekeeping and returns a simple string with the error message for a specified error number. The VBA function GetSystemErrorMessageText takes as input the error value returned by Err.LastDllError and returns as a string the Windows system error message text associated with that error number.

You can download the module file with all the example code on this page.

Many of the code listsings, modules, and projects available for download from this web site use the GetSystemErrorMessageText function, so you may want to store the code module in your standard library directory. The downloadable bas module file contains an entire self-contained Module file, which is shown in its entirity below. Everything in the module file, with the exception of the GetSystemErrorMessageText function itself, is declared Private so that it will not interfere with any other declarations you may have in your project. The module file has no prerequisites. It may be used without any external dependencies.

The contents of the module file are shown below:

Option Explicit
Option Compare Text
''''''''''''''''''''''''''''''''''''''''''''''''''''''
' GetSystemErrorMessageText
' -------------------------
' By Chp Pearson, www.cpearson.com, chip@cpearson.com
' See www.cpearson.com/Excel/FormatMessage.aspx for
' additional information.
''''''''''''''''''''''''''''''''''''''''''''''''''''''

'''''''''''''''''''''''''''''''''''
' Used by FormatMessage
'''''''''''''''''''''''''''''''''''
Private Const FORMAT_MESSAGE_ALLOCATE_BUFFER As Long = &H100
Private Const FORMAT_MESSAGE_ARGUMENT_ARRAY  As Long = &H2000
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_MAX_WIDTH_MASK  As Long = &HFF
Private Const FORMAT_MESSAGE_IGNORE_INSERTS  As Long = &H200
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)
TextLen = FORMAT_MESSAGE_TEXT_LEN

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 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, _
                        Arguments:=0&)

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 = "An internal system error occurred with the" & vbCrLf & _
        "FormatMessage API function: " & CStr(Err.LastDllError) & ". No futher information" & vbCrLf & _
        "is available."
    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

''''''''''''''''''''''''''''''''
' Return the error text as the
' result.
''''''''''''''''''''''''''''''''
GetSystemErrorMessageText = ErrorText

End Function

You can download the module file with all the example code on this page.

This page last updated: 2-November-2008