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
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
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
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 ItemChoose 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
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
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
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
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
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
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
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
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
