CODEWITHSUNDEEP
c#vbaexcel
SwimBikeRun
SwimBikeRun

Reputation: 4460

Create object library for Excel vba

I have a GUI I wrote in C#. It's a grid of interactive buttons and text that controls a register map. I would like to create an API for that GUI to use in Excel vba. I have been unable to find any examples or documentation for how to create such a thing, but I have used other companies' object libraries before.

In my attempt to create this, I have ran into some problems, namely it only works on my computer. Also, if I move to another folder on my computer, it also breaks.

Excel vba Code:

Sub Test()
    Dim impclass As New ZZVISIBLE_FROM_EXCEL_CLASS
    MsgBox impclass.DotNetMethod_SQR(5)
End Sub

C# Library Class Code:

using System;
using System.Collections.Generic;
using System.Text;

namespace ZZVISIBLE_FROM_EXCEL_LIBRARY
{
    public class ZZVISIBLE_FROM_EXCEL_CLASS
    {
        public Double DotNetMethod_SQR(Double input)
        {
            return input * input;
        }
    }
}

This foundational example uses C# library class to square a number and Excel VBA to choose the number 5. This is a basic framework for later things I wish to do such as:

grid.writecell(22,1001)

grid.getcell(22)

grid.resetall()

grid.loadfile("C:\griddata\config_default")

enter image description here

Some references for making these object libraries or even what they are called would be most helpful! Googling "custom excel vba references in C#" has led to nothing useful so far, so I decided to post here for help.

Also, I'm early enough in the project that "you're doing it wrong, do this" type answers are welcomed as well!

Upvotes: 0

Views: 4320

Answers (3)

Hany Hassan
Hany Hassan

Reputation: 320

First of all you need to know the dotnet version you are working on so you use either : C:\Windows\Microsoft.NET\Framework\v2.0.50727\regasm.exe or c:\Windows\Microsoft.NET\Framework\v4.0.30319\RegAsm.exe

Second you need to know whether you want to register the dlls as 32 bit or 64 bit so use the same previous path for 32 bit or just use Framework64 instead of Framework in case of 64 bit

once done you can add reference to this assembly in easily in your vba script like that : enter image description here

once added you can instantiate any intance of any class exists in the dll

Upvotes: 1

Govert
Govert

Reputation: 16907

You can wrap your library in an Excel-DNA add-in. (Excel-DNA is a free library I develop for integrating .NET with Excel). This may ease registration issues with COM (allowing you to deploy the COM object mode without requiring administrator permissions).

It will also help if you want to expose some functionality as user-defined function that you can call from the worksheet in Excel, add a ribbon or anything like that.

For using Excel-DNA to expose an object model to VBA, I suggest these posts by Mikael Katajamäki as a starting point:

For general Excel-DNA support I suggest you use the Excel-DNA Google group.

Upvotes: 0

Kevin
Kevin

Reputation: 2631

You have a couple of choices:

1) Create an assembly and make it COM visible using regasm

See article on CodeProject

http://www.codeproject.com/Articles/555660/Extend-your-VBA-code-with-Csharp-VB-NET-or-Cpluspl

2) To make a C# assembly visible to Excel you have to make it COM visible and set the ReferenceAssemblyFromVbaProject to true.

Please see the following MSDN article for more information:

http://msdn.microsoft.com/en-us/library/bb608604.aspx

Thanks, Kevin

Upvotes: 2

Related Questions