Corneliu
Corneliu

Reputation: 19

Why is Excel VBA code crashing in other PC with a custom C# DLL?

My point is to share/embbed a C# DLL in Excel VBA in other PC's. The problem is that I don't have admin priveleges in other PC and Visual Studio is not installed aswell.

This is my reg file that than I change the path.

REGEDIT4

[HKEY_CURRENT_USER\Software\Classes\ForVBA2.MyVBAClass]
@="ForVBA2.MyVBAClass"

[HKEY_CURRENT_USER\Software\Classes\ForVBA2.MyVBAClass\CLSID]
@="{F2FD6D35-BC52-4366-94BD-B73741965B4B}"

[HKEY_CURRENT_USER\Software\Classes\CLSID\{F2FD6D35-BC52-4366-94BD-B73741965B4B}]
@="ForVBA2.MyVBAClass"

[HKEY_CURRENT_USER\Software\Classes\CLSID\{F2FD6D35-BC52-4366-94BD-B73741965B4B}\InprocServer32]
@="mscoree.dll"
"ThreadingModel"="Both"
"Class"="ForVBA2.MyVBAClass"
"Assembly"="ForVBA2, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"
"RuntimeVersion"="v4.0.30319"
"CodeBase"="file:///C:/Users/corne/Desktop/Test/ForVBA2.dll"

[HKEY_CURRENT_USER\Software\Classes\CLSID\{F2FD6D35-BC52-4366-94BD-B73741965B4B}\InprocServer32\1.0.0.0]
"Class"="ForVBA2.MyVBAClass"
"Assembly"="ForVBA2, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"
"RuntimeVersion"="v4.0.30319"
"CodeBase"="file:///C:/Users/corne/Desktop/Test/ForVBA2.dll"

[HKEY_CURRENT_USER\Software\Classes\CLSID\{F2FD6D35-BC52-4366-94BD-B73741965B4B}\ProgId]
@="ForVBA2.MyVBAClass"

[HKEY_CURRENT_USER\Software\Classes\CLSID\{F2FD6D35-BC52-4366-94BD-B73741965B4B}\Implemented Categories\{62C8FE65-4EBB-45E7-B440-6E39B2CDBF29}]

This is my C# code:

namespace ForVBA2
{
    [InterfaceType(ComInterfaceType.InterfaceIsIUnknown)]
    [ComVisible(true)]
    [Guid("62E11E9C-890E-4D8A-BB56-26B457F5D2E5")]
    public interface IMyVBAClass
    {
        int MyValue { get; set; }
        void ShowXD();
        void StopTimer();
    }

[ComVisible(true)]
[Guid("B2DA1C3E-F9C2-4A3E-9A1D-85A69F6C1234")]
[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
public interface IExampleLibraryEvents
{
    void OnMessage(string message);
}

[ComVisible(true)]
[Guid("F2FD6D35-BC52-4366-94BD-B73741965B4B")]
[ClassInterface(ClassInterfaceType.None)]
[ComSourceInterfaces(typeof(IExampleLibraryEvents))]
public class MyVBAClass : IMyVBAClass
{
    public delegate void MessageEventHandler(string message);
    public event MessageEventHandler OnMessage;
    private SynchronizationContext _syncContext;

    public int MyValue { get; set; }
    private System.Timers.Timer _timer;

    public MyVBAClass()
    {
        MyValue = 20;
        _timer = new System.Timers.Timer();
        _timer.Elapsed += new System.Timers.ElapsedEventHandler(_timer_Elapsed);
        _timer.Interval = 3000;
        _timer.Enabled = true;
        _timer.Start();
        _syncContext = SynchronizationContext.Current ?? new SynchronizationContext();
        MessageBox.Show("Constructor called");
    }

    [STAThread]
    private void _timer_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
    {
        try
        {
            _syncContext.Post(_ => OnMessage?.Invoke("Hello from .NET!"), null);
        }
        catch (Exception ex)
        {
            MessageBox.Show($"Error invoking event: {ex.Message}");
        }
    }

    public void ShowXD()
    {
        MessageBox.Show("xd");
        MessageBox.Show(MyValue.ToString());
    }

    public void StopTimer()
    {
        _timer.Stop();
    }

    ~MyVBAClass()
    {
        MessageBox.Show("Destructor called");
    }
}

}

The Module1 in the VBA code is :

Dim example As clsExemple

Sub TestEvent()
    Set example = New clsExemple
    example.Initialize
End Sub

The Class Module clsExemple is:

Dim WithEvents obj As ForVBA2.MyVBAClass

Public Sub Initialize()
    Set obj = New ForVBA2.MyVBAClass
End Sub

Public Sub StopTimer()
    obj.StopTimer
End Sub

Private Sub obj_OnMessage(ByVal message As String)
    Debug.Print "Event received: " & message
End Sub

In my PC, I use regasm to create the tlb file. I building the C# project and copying the dll and the pdb file into other folder.I delete in the regedit everything that I search with the GUID and with ForVBA2 string. I then execute the reg file with the correct folder of the copied files. I then run the code in VBA with the correct reference. In my PC it works as intended, but when I do this in other PC, the message "Constructor called" is called, but then the Excel crashes. Why?

Upvotes: 0

Views: 58

Answers (0)

Related Questions