Programming Excel with VBA and .NET

Handle Exceptions

Most DLL functions return a value indicating whether they succeeded. A return value of 0 indicates that the function did not complete its task. That type of exception is different from the error in Figure 23-2, which halts everything.

Just as with Visual Basic functions, you should anticipate and handle exceptions from DLL functions in your code, particularly when working with nonmemory resources like hard disks, printers, and other devices. To handle an exception from a DLL:

1. Call the DLL function.

2. Check the value returned by the function.

3. If the returned value is zero, handle the exception and optionally check Err.LastDllError to identify what went wrong.

The following changes to CreateTempFile demonstrate detecting and handling exceptions from a DLL function. If the caller tries to create a temporary file at an invalid path, GetTempFileName returns 0 and the code displays a message in the Immediate window. Optionally, you could uncomment the Error statement to raise a trappable Visual exception:

Function CreateTempFile(Optional path As String = "") As String
    Dim fil As String, res As Long
    ' Initialize the string!
    fil = Space(128)
    If path = "" Then path = ThisWorkbook.path
    ' Pass the string to the DLL function.
    res = GetTempFileName(path, "xl", 0, fil)
    ' If error, return empty string.
    If res = 0 Then
        Debug.Print "Error creating temp file: " & Err.LastDllError
        ' Optionally, raise can't Create temporary file error.
        ' Error 322
    End If ' Trim the excess off the string ...