Accessing C# Method from Excel using VBA

TASK DESCRIPTION:

Excel button click event accessing User Form from Excel which has textboxes and Login button to provide arguments to c# method and retrieve output in excel.

Steps:

1.      Create Class library in C# (create method)

Fig 1.1 create class library

Fig 1.2: create class library

2.      C# Code:

Namespace
using System.Runtime.InteropServices;

[ComVisible(true)]

public class Class1

{

public int Add (int a, int b)

{

  return a + b;

}

}

3.      Create reference of Project library

Fig 1.3:  COM-visible property exposes public methods and functions in machine assembly to other applications not written in .NET.

4.    Enable Register for COM Interop

Fig 1.4: Enable Register for COM interop

 

5.    Excel: Enable Developer bar on top panel. File --> Options (POP Up window will appear)

Fig 1.5: Excel Option 

 

6.    View Developer toolbar

Fig 1.5: Button Draw

7.    View code toolbar in Developer Panel --> sheet window will popup --> tools --> References --> scroll down for your class library exe file and press Ok

8.    Create user form with two textboxes and a Login button and paste below code in Login button click event

VBA Code:

Dim sheet As Worksheet
Dim tools As TestProject_Excel.Class1
Dim value As String
Dim result As String
Dim uiValue As String
Dim uiValue1 As String

Set sheet = ActiveSheet
value = sheet.Cells(1, 1)
Set tools = New TestProject_Excel.Class1

uiValue = UserForm1.TextBox1.Text
uiValue1 = UserForm1.TextBox2.Text
Unload UserForm1

If uiValue = vbNullString Then
 UserForm1.Error = "UserName can not be empty"

ElseIf uiValue1 = vbNullString Then
 UserForm1.Error = "Password can not be empty"

Else
result = tools.Add(uiValue, uiValue1)
sheet.Cells(1, 2) = result
End If

OUTPUT: