Programming Excel with VBA and .NET

Work with Strings

The quickest way to end your DLL programming experience is to pass a DLL an uninitialized string. For example, the following code will result in the error shown in Figure 23-2:

Public Declare Function GetTempFileName Lib "kernel32" Alias _
  "GetTempFileNameA" (ByVal lpszPath As String, _
  ByVal lpPrefixString As String, ByVal wUnique As Long, _
  ByVal lpTempFileName As String) As Long
 
Sub Crash(  )
    Dim fil As String, res As Long
    res = GetTempFileName(ThisWorkbook.Path, "xl", 0, fil)
    Debug.Print fil
End Sub

Figure 23-2. You’ll crash if you don’t initialize your strings

Don’t send Microsoft this error report—it was your fault! To avoid this problem, fill a string with spaces before you pass it to a DLL. You need to make the string long enough to fit the passed-in data; 128 characters is usually sufficient:

Function CreateTempFile(  ) As String
    Dim fil As String, res As Long
    ' Initialize the string!
    fil = Space(128)
    ' Pass the string to the DLL function.
    res = GetTempFileName(ThisWorkbook.Path, "xl", 0, fil)
    ' Return the string.
    CreateTempFile = fil
End Function

Warning

Figure 23-2 underscores the risks of working with DLLs—you are leaving the relatively safe world provided by Excel and are performing without a safety net. Mistakes can crash Excel and potentially shut down Windows. Save your work frequently when working with DLLs.

C strings end with a null character (ASCII 0). ...