0

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?

1
  • 4
    new SynchronizationContext() is going to randomly crash VBA. Commented Jan 22 at 0:01

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.