Home > Excel Automation Add-in > Creating custom Excel Function wizard using VSTO Method1

Creating custom Excel Function wizard using VSTO Method1


Read Introduction Tutorial About VSTO

Download FullCode From Here
Word Document Of the tutorial-Introduction

Word Document Of the tutorial-Custom Function Wizard

    AIM-

This step by step tutorial describes a way to create custom function wizard and use either builtin formulas or UDF’s with them in 2 approaches.

In this tutorial Generate a function wizard using VSTO project and implement the FACT() function as example.(Function used to find the factorial of a number)
Step1: Read All the instructions in Step by Step Tutorail1.By following them its easy for anyone to start a basic VSTO project. I am using a new project o explain this Tutorial.
Chapter1. Working Of Built-In Excel Function Wizard
1. Open the excel Sheet Fill 5,4,3 in cells A1,A2,A3 and select on the cell C1 and click on the function wizard symbol on the top of excel sheet .
2. Select the function FACT() from Math Trig Category. You can see a windows form like structure for finding the Factorial Functionality. Above things you can see in

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin


3. Now Click On the Cell A1
Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin

4. And click on the OK button on the form what you can see is you get the result on the cell C1.

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin

Chapter2 .Implementation Of the same using VSTO.- Approach1
Step1
Add a windows form item to the project (For this right click on projects Add new ItemChoose Windows Form).Name the form press OK.(I am adding a new form frmFact).and create a design like the Fact() FunctionWizard Design .

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin

Step2
Add Factorial Button to the Ribbon Tab
Open ToolBox .you can see different tools on that .From Office Ribbon Controls tab Choose the button control and drag and drop this button control to the Design Of RibbonTab .Change the propertys Of this button like Text,Name etc by clicking on the property’s(Similarly we are doing for Windows Forms Controls)
Step3
Write click event for the added button-
On theclick event shows the form frmFact and passing the 2 arguments selectedrow and column no and assign them as properties on the form
(These things explain in details on tutorial 1-Step7)
Step 4
Now if we run the project and click on the factorial button we can see the form that you addedon step 2

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin

Step5
Write the OK Button Click event For getting the result on selected cell .
(See step8 On tutorial 1 If need more explanation).
If you write a content like =Fact(A1) On any cell on the excel sheet we will get the result of Factorial Of Number in the cell A1 means. If cell a1 contains a no 5 then we get factorial of 5 in the selected cell.

Using the same concept If we enter the value of cell name manually on the textbox of our form and click OK button then we get the factorial of the written cell value on the selected cell

private void btnOk_Click(object sender, EventArgs e)
{
Worksheet currentSheet = Globals.HelloAddIn.GetActiveWorksheet();
currentSheet.Cells[Selectedrow, SelectedrCol] = "=FACT("+txtCellNAme.Text+")";
this.Close();
}

Output
Working example:
1. Run project and fill A1=10 A2=9 and A3=8 .
2. Select cell c4 and Click on HelloTAb Factorial Button.
3. Write a3 on the cell

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin


4. We get factorial of 8 in cell c4
Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin


Now if you change the value in cell A3 you can see that the value of cell C4 is also getting changed
Note:
So before pressing Ok Button we need to check the cell value enterd is correct or not (if we enter 1a is not correct. Aa is again incorrect..So Check this Code to check a string is valid excel cell Name or not)

Problems with this approach
P1-We need to manually enter the cell value.So chance for error is more.If we want to change the cell name its very complex.
P2- If we are dealing with formulas of range of cells say A1:A3 again this approach is difficult.

What we want is when we select some cells on excel sheet we need to fill the textbox in the form with that selected cell name(either range or a single cell).
Way to overcome the limitation

Add new SheetSelectionChangeEventHandler to the Addin On the HelloAddin.cs Page

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
this.Application.SheetSelectionChange += new Microsoft.Office.Interop.Excel.AppEvents_SheetSelectionChangeEventHandler(Application_SheetSelectionChange);
}

Implementation Of the Event(variable cellvalue contains the name of cell selected).

public void Application_SheetSelectionChange(object Sh, Microsoft.Office.Interop.Excel.Range Target)
{
string cellValue="";

cellValue = Target.get_Address(false, false, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, false, false).ToString();

}

Now we get the value of cell and what is remaining is fill this value in textbox on the currently opend form(frmFact)
.For doing this
Crate a function on the Hello.cs page say DoActions and call it from the HelloAddin.cs page Application_SheetSelectionChange event


Hello do_actions = new Hello();
do_actions.DoActions(cellValue);

This DoActions checks if the currently active control on the form is a textbox then it fills the textbox with the selected cell value

internal void DoActions(string cellValue)
{
try
{
if (myfrm.ActiveControl.GetType() == typeof(System.Windows.Forms.TextBox))
myfrm.ActiveControl.Text = cellValue;
}
catch
{
}
}

Note : Make sure myfrm is a static object like
public static frmFact myfrm;
So in any duration of the application we can use any controls on frmFact.Or we can assume the frmFact as currently active form
On the click event of factorial ribbon button initialize the form frmFactorial like this

Range selectedCell = Globals.HelloAddIn.Application.ActiveCell;
int selectedRow = selectedCell.Row;
int selectedCol = selectedCell.Column;

myfrm = new frmFact(selectedRow,selectedCol);
myfrm.TopMost = true;
myfrm.Show();

Now what we can see is that
The function is behaving like a normal function wizard of excel Sheet.(check the steps mentioned on the beginning of this article under the title Working Of Excel Function Wizard
Output
1. Open the excel Sheet Fill 10,9,8 in cells A1,A2,A3 and select the cell C3 and click on Factorial Button on our designed tab

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin

2. Now Click On theCell A1 .(See image).When you click on the cell A1 the value A1 is filled on the textbox. If you click some other cells like A2, A3 etc and see the difference.

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin

3. And click on the OK button on the form what you can see is you get the result on the cell C3

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin


4. If you select on the cell C3 again then you can see the value is 3628800.But on the top you can see the text as =fact(A1) (
Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin

If you update the contents in cellA1 then you can see the content in cell C3 is also changed
Read Part2 Of this Tutorial for Second Approach

Check A String is Valid Excel cell Name or not

Download FullCode From Here

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: