Excel VBA - Getting Automation Error (430) Using StringBuilder Class from .NET 3.5

Question

I have an Excel VBA module that uses Hashtable and SortedList from the .NET 3.5 mscorlib DLL. I'm able to use these classes with no issues. When I attempted to use the StringBuilder class, I ran into an automation error. Here's the entire function that is in an Excel VBA module:

Function StringBuilderTest() As String
    Dim sb As StringBuilder
    Dim i as Integer
    
    On Error GoTo ErrorLabel
    
    Set sb = New StringBuilder
    
    For i = 1 to 100
        sb.Append "text"
    Next i
    
    StringBuilderTest = sb.ToString
    Exit Function
    
ErrorLabel:
    Debug.Print Err.Description
    Debug.Print Err.Number
    Debug.Print Err.Source
    
    StringBuilderTest = "Error"
    Exit Function
    
End Function

The code blows up on the .Append method call. Error handling outputs the following:

Class does not support Automation or does not support expected interface
 430 
VBAProject

Here are my Excel reference settings:

And here are my Windows Features settings:

Why am I getting this error? It's a mystery, given Hashtable and SortedList from the same library work fine.

UPDATED:

I added a For/Next loop around the Append method call to more accurately represent my intent. Note that this is simplified code (not actually from my Excel project), to illustrate the Automation issue I'm having.

Answer 1

The issue you're encountering I think is because the StringBuilder class from the .NET Framework is not directly accessible in VBA. VBA doesn't have native support for using .NET classes.

However, there are alternative approaches you can use :

    Function StringBuilderTest() As String
    Dim sb As String
    
    On Error GoTo ErrorLabel
    
    sb = "text"
    
    StringBuilderTest = sb
    Exit Function
    
ErrorLabel:
    Debug.Print Err.Description
    Debug.Print Err.Number
    Debug.Print Err.Source
    
    StringBuilderTest = "Error"
    Exit Function
    
End Function

Answer 2

Your issue is related to COM object members can not be overloaded.

StringBuilder.Append has numerous overloads.

So the type library compiler appends an underscore followed by a number for each overload.

Eg. Append(Char, Int32) Which is probably the member attempting to access and causing the automation error as VBA doesn't support the Char data type.

Append_2(Char[], Int32, Int32)

Append_3(System.String) Is what you require. etc..

Enumerating the members of StringBuilder as shown below the Append(System.String) was the third overload listed so its COM object member name is Append_3 which was tested for your example and works. i.e. sb.Append_3 "text"

...

System.Text.StringBuilder Append(Char, Int32)
System.Text.StringBuilder Append(Char[], Int32, Int32)
System.Text.StringBuilder Append(System.String)
System.Text.StringBuilder Append(System.String, Int32, Int32)

etc

The list of members for StringBuilder was obtained from the following code:

Public Sub DisplayStringBuilderMembers()
    Dim sb As mscorlib.StringBuilder
    Set sb = New mscorlib.StringBuilder
    Dim stringBuilderType As mscorlib.Type
    Set stringBuilderType = sb.GetType()
    Debug.Print stringBuilderType.FullName; " members"
    Debug.Print
    Dim members() As mscorlib.MemberInfo
    members = stringBuilderType.GetMembers_2
    Dim i As Long
    For i = 0 To UBound(members)
        Dim pvtMemberInfo As mscorlib.MemberInfo
        Set pvtMemberInfo = members(i)
        Debug.Print pvtMemberInfo.ToString
    Next i
End Sub