VBA Invoke Managed Code

I followed this article and faced a problem in step 2.

I added a class with InteropServices.ComVisible = true, and then RegisterCallback in a VBA module inside the XLS file, but I can't register the code in the Workbook's Open event. An error message:
The customization code cannot be found or could not be loaded.

My code:
[System.Runtime.InteropServices.ComVisible(true)]
public class TiUDF {
    public static string GetTime() {
        return "Testing";
    }
}

In a module of my VBA project:
Public Sub RegisterCallback(callback As Object)
    Set managedObject = callback
End Sub

Public Function GetTime() As Integer
    GetTime = managedObject.GetTime()
End Function

In my workbook's open event:
        private void ThisWorkbook_Open() {
            //this.Application.Run("RegisterCallback", new TiUDF(), null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
            this.Application.Run("RegisterCallback", new TiUDF(), Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        }

Can anyone point me what's wrong in the code above (I'm thinking.. is it something wrong with the namespace and class name only in fact or fully qualified class name is required )

Thanks,

Answer this question

VBA Invoke Managed Code

dalenewman

Here is the correct code.

    private void ThisWorkbook_Startup(object sender, System.EventArgs e)< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

    {

        this.Open += new Microsoft.Office.Interop.Excel.WorkbookEvents_OpenEventHandler(ThisWorkbook_Open);

    }

    void ThisWorkbook_Open()

    {

        //register for the VBA callback (use the built in VSTO missing for optional parameters)

        this.Application.Run("RegisterCallback", new TiUDF(),

            missing,missing,missing,missing,missing,missing,missing,missing

            ,missing,missing,missing,missing,missing,missing,missing,missing

            ,missing,missing,missing,missing,missing,missing,missing,missing

            ,missing,missing,missing,missing,missing);

    }

    [System.Runtime.InteropServices.ComVisible(true)]

    public class TiUDF

    {

        public string GetTime()

        {

            return "Testing";

        }

    }

And the VBA module

    Dim managedObject As Object

    'VSTO calls this on Document Open

    Public Sub RegisterCallback(ByVal callback As Object)

        managedObject = callback

    End Sub

    'Call the VSTO function from VBA

    Public Function GetTime() As String

        GetTime = managedObject.GetTime()

    End Function
Alex Media
There are a couple of things to check. Is your VBA code in a Module The wire up call should happen on open of the document. Also you can test this by clearing the spreadsheet of any of the calls to your udf. When the sheet opens press Alt-F11 to open the VBA ide and try running the function from there. This will show you if there is a race condition with Excel calling the function before VSTO has had a chance to wire it up. Again to emphasize that this is not a supported feature of VSTO for these reasons. So although it should work as you can see it is a fragile solution with limitations.
moontube

You have a type mismatch in converting string to integer. I suggest you to make slight change to your VBA code:

Public Function GetTime() As STRING ' this was Integer in the original code
GetTime = managedObject.GetTime()
End Function
Stoil Pankov
Hi,
Thanks for your pointer, but obviously, my typo to this forum with the return data type have NO relationship with the unreadable error: The customization code cannot be found or could not be loaded

RE: Paul, Thanks for your reply and your article, I tried your suggested solution in C# but it doesn't work neither. I have exactly the same code as yours, but I have a custom Namespace for this class file. Do you think it's the cause of this problem or do you have any other solution in order to prepare UDF in VSTO

Thanks,
Mordt
Any pointer

I tried the code snippet suggested above but no luck, an error message come out once the VSTO application start up.

The customization assembly can not be found or could not be loaded... When I click the "Detail" button, the message area is blank.

Anyone can help
VBA Invoke Managed Code

Answer this question