Wednesday, September 5, 2018

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:





Tuesday, April 17, 2018

C#.Net frameworks

Here are C# .NET Framework versions with their main features:
C#.NET framework 1.0 and 2.0 are basic framework which comes in  Visual Studio 2002 and Visual Studio 2003.