使用Unmanaged Exports实现VBA调用.NET的字符串参数等技术疑问

Great question set—let's break down each of your concerns, plus address your callback function scenario:

1. Can you use ref string directly as a parameter? #

Absolutely! Unmanaged Exports handles the marshaling between .NET System.String and the COM-compatible BSTR type that VBA natively uses. When you define an exported function with ref string, you can pass a VBA string variable using ByRef, and any changes made to the string in .NET will be reflected back in VBA. For example:

• .NET side:

  [DllExport("UpdateString", CallingConvention = CallingConvention.StdCall)]
  public static void UpdateString(ref string input)
  {
      input = "Modified from .NET";
  }
  

• VBA side:

  Dim myStr As String
  myStr = "Original VBA string"
  Call UpdateString(ByRef myStr)
  ' myStr now equals "Modified from .NET"
  

2. Can you replace it with out string? #

Yes, you can! The key difference between ref and out is that out doesn’t require the VBA variable to be initialized before calling the function—.NET is responsible for assigning a value to the string, which is then marshaled back to VBA. In VBA, you still pass the variable using ByRef, even if it’s uninitialized. Example:

• .NET side:

  [DllExport("GetString", CallingConvention = CallingConvention.StdCall)]
  public static void GetString(out string output)
  {
      output = "Generated from .NET";
  }
  

• VBA side:

  Dim resultStr As String
  Call GetString(ByRef resultStr)
  ' resultStr now equals "Generated from .NET"
  

3. Do you need to manage string length or memory in these cases? #

No manual memory or length management is required—this is one of the biggest perks of using Unmanaged Exports. The library handles all the heavy lifting:

• For ref string: If .NET modifies the string, the marshaler automatically allocates/releases the corresponding BSTR memory. VBA doesn’t need to call SysAllocString or SysFreeString manually.
• For out string: The .NET-created string is marshaled to a VBA-compatible BSTR, with memory managed by the CLR’s marshaling layer. You can use the string in VBA like any other variable without worrying about cleanup.

Handling Callback Functions Passed as int Types #

Using int to pass callbacks is risky because it bypasses type safety. Instead, leverage .NET delegates with Unmanaged Exports to ensure proper marshaling:

1. Define a delegate in .NET that matches your VBA callback’s signature, specifying the StdCall convention (since VBA uses this):

   [UnmanagedFunctionPointer(CallingConvention.StdCall)]
   public delegate void StatusCallback(int progress);
   

2. Use this delegate as a parameter in your exported function:

   [DllExport("RegisterProgressCallback", CallingConvention = CallingConvention.StdCall)]
   public static void RegisterProgressCallback(StatusCallback callback)
   {
       // Store the callback and invoke it as needed, e.g.:
       callback(50); // Send 50% progress to VBA
   }
   

3. In VBA, define a matching function and pass its address using AddressOf:

   Sub TestCallback()
       Call RegisterProgressCallback(AddressOf UpdateProgress)
   End Sub
   
   Sub UpdateProgress(progress As Integer)
       Debug.Print "Progress: " & progress & "%"
   End Sub
   

This approach ensures the callback signature is enforced, avoids pointer errors, and lets Unmanaged Exports handle the marshaling between .NET delegates and VBA function pointers correctly.

内容的提问来源于stack exchange，提问作者Roger Willcocks