Calling VB.Net from VBA in Excel

Question

I am trying to get some code working between Excel VBA and VB.Net. I have the following code in VB.Net in Visual Studio..

Public Class ThisAddIn

    Private Sub ThisAddIn_Startup() Handles Me.Startup

    End Sub

    Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown

    End Sub

    Public Function Multiplication()
        Dim activeWorksheet As Excel.Worksheet = CType(Application.ActiveSheet, Excel.Worksheet)

        Dim Range1 As Excel.Range = activeWorksheet.Range("A1")
        Dim Range2 As Excel.Range = activeWorksheet.Range("A2")

        Dim x As Integer = Range1.Value
        Dim y As Integer = Range2.Value

        Return (x * y)

    End Function

End Class

I also have this code in VBA that I am trying to run

Sub Macro1()

Dim x
x = ThisAddIn.Multiplication
MsgBox (x)

End Sub

From Visual Studio I hit Start and I put this code into the Excel VBA for the new window that pops up.

I get the error '424 Object Required' on the VBA line "x = ThisAddIn.Multiplication". How can I call a VB.Net function from VBA?

Edit: I am using Excel 2013, VB.Net version 2013(VB12), and I guess I am targeting .Net version 4.5+

Edit: I tried adding the following code:

Imports System
Imports System.Collections.Generic
Imports System.Text

and

Private Sub Test()
 Dim testClass As New ThisAddIn
 MsgBox testClass.Multiplication()
End Sub

Based off this guide, but its still not working. I could not find the options to connect the project to Excel mentioned in Step 3 because it is an old guide. Maybe this is my problem?

What versions of Excel and VB.NET are you using, and what version of the .NET Framework are you targeting? — STLDev, Jun 02 '14 at 03:47
In your question you post a link to Richard Newman's "A Beginner's Guide To Calling A .NET Library From Excel". Did you follow the examples laid out there exactly, and if so, did you get that to work? If not, how did it fail? Those steps in step 3 of the guide are essentially registering the component into the OS's COM system. You may want to also take a look at this [past SO question](http://stackoverflow.com/questions/6999230/trying-to-call-a-net-function-from-vba-using-run-in-vba). — STLDev, Jun 06 '14 at 18:24
You should also probably see this SO question/answer: [Calling a .NET Library Method from VBA](http://stackoverflow.com/questions/19954001/calling-a-net-library-method-from-vba). Also, this SO question looks like it's pertinent to your situation. Please have a look: [Difference between : adding a tlb as reference to invoke function VS using 'Run'?](http://stackoverflow.com/questions/7000758/difference-between-adding-a-tlb-as-reference-to-invoke-function-vs-using-run). — STLDev, Jun 06 '14 at 18:34

Govert · Answer 1 · 2014-06-09T13:01:42.690

Instead of using VSTO for making the Excel add-in, another approach is to make an Excel-DNA add-in with the VB.NET code. These are convenient, since you don't need admin access to load and register, and you can create high-performance UDFs and COM servers in a single add-in.

This will allow you to create a user-defined function (UDF) in your VB.NET, which can be used directly form the worksheet. Your UDF code might look like this:

Public Module MyFunctions

    <ExcelFunction(Description:="Useful custom multiplication function")>
    Function MultiplyThem(val1 As Double, val2 As Double) As Double
        Return val1 * val2
    End Function

End Module

And you can call this from a cell as =MultiplyThem(A1, A2).

Or course you can also make macros (Subs) that automate to the Excel object, make custom Ribbon tabs etc.

As an easy start for integrating with VBA, functions and macros in the add-in can be directly called from a VBA project using Application.Run("MultiplyThem", 3, 5).

Going further, you can set up your Excel-DNA add-in as a COM Server, which allows you to Tools->Reference the .xll add-in from a VBA project, and then access your add-in's exported object model from VBA (even with IntelliSense). Two nice articles with step-by-step instruction on making a such a COM Server with Excel-DNA were written by Mikael Katajamäki:

score 0 · Answer 2 · answered Jun 08 '17 at 11:30

if you want to call a function from vb.net first you must reference your dll to vba ftom tools->reference. after reference you must define object from your class into dll file. if your dll file name is mydll the below code is written in vba like below:

dim x as mydll.ThisAddIn
set x =new mydll.ThisAddIn
dim y
y=x.Multiplication

Calling VB.Net from VBA in Excel

2 Answers2