Create object library for Excel vba

Question

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!

can you not do the same thing in C# using Microsoft.Interop.Excel have you tried to google examples on using Interop..? — MethodMan
– MethodMan, Commented Oct 20, 2014 at 22:08
This is a great comment! Unfortunately the reason I am doing it this way is to maintain compatibility with code written in Excel VBA. — SwimBikeRun
– SwimBikeRun, Commented Oct 20, 2014 at 22:13

Kevin · Accepted Answer · 2014-10-20 22:19:23Z

2

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

edited Oct 20, 2014 at 22:19

answered Oct 20, 2014 at 22:11

Kevin

2,6311 gold badge14 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

SwimBikeRun Over a year ago

Good advice, but it didn't fix the issue of deployment. Only my computer runs it. How am I supposed to deploy this as a tool other people can use?

user2140173 Over a year ago

@SwimBikeRun the answer is right there - use regasm to register the .dll and .tlb on other machines. Before deploying make sure you are targeting the correct platform.

Kevin Over a year ago

As vba4all said you can use regasm, you could also write a .NET Installer stackoverflow.com/questions/2378730/… You will need admin privileges either way.

SwimBikeRun Over a year ago

The problem is that the target machines need this regasm tool, which means they must install visual studio...

SwimBikeRun Over a year ago

Thanks for the .NET installer link

|

Hany Hassan · Accepted Answer · 2014-11-05 12:34:07Z

1

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

answered Nov 5, 2014 at 12:34

Hany Hassan

3202 silver badges10 bronze badges

Comments

Govert · Accepted Answer · 2014-10-20 23:40:39Z

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.

Collectives™ on Stack Overflow

Create object library for Excel vba

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related