Creating custom Excel Function wizard using VSTO Method2
Word Document Of the tutorial-Introduction
Word Document Of the tutorial-Custom Function Wizard
Read Introduction Tutorial About VSTO
Read part1 Of the tutorial For First Aproach
In the steps discussed above the main logic was to write the formula on the selected cell.Instead of this we can do the same thing on this way
1. On click event of button in tab Shows the form.
2. Select the cell(Say A1)
3. Read value of the cell(say 5 , means value in the cell A1 is 5)
4. Calculate the factorial for this value (factorial of 5=1*2*3*4*5=120)
5. Write the result on the selected cell (120 instead of writing =fact(A1)).
Implementation Of Approach 2
Step1
Create a new form and design the form (similar to factorial form used on the first part of this tutorial)
Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin
Add a new button on the ribbon and on the click event shows the newly designed form
Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin
Step2
If we select some cells on excel we need to fill that cellname on the text box. But now we had the chance of opening either form1 or form2.
So the approach to overcome this isdeclare one static variable active formno on the ribbon class and assign it a unique no while showing each form.(say activeformno=1 for myfrm and activeformno=2 for myfrm2)
public static frmFact myfrm;
public static frmFactApproach2 myfrm2;
public static int activeformno;
And on the DoActions function check the active formno first and based on that we shows thecellname on the corresponding textbox
internal void DoActions(string cellValue)
{
try
{
if (activeformno == 1)
{
if (myfrm.ActiveControl.GetType() == typeof(System.Windows.Forms.TextBox))
myfrm.ActiveControl.Text = cellValue;
}
else if (activeformno == 2)
{
if (myfrm2.ActiveControl.GetType() == typeof(System.Windows.Forms.TextBox))
myfrm2.ActiveControl.Text = cellValue;
}
}
catch
{
}
}
Step3 read the value Of selected Cell
On the click event button on the form
string cellName = txtCellName.Text;
string value = readValueOfCell(cellName);
where the function to readvalue of selected cell is
private string readValueOfCell(string cellName)
{
string val = "";
Worksheet activeworksheet = Globals.TesstPAddin.GetActiveWorksheet();
Range FreqRange = activeworksheet.get_Range(cellName, Type.Missing);
val = Convert.ToString(FreqRange.Value);
return val;
}
Step4
Find the factorial based on this no
double factorial = getFactorial(Convert.ToDouble(value));
Step5
and display/write the value in selected cell
Worksheet currentSheet = Globals.TesstPAddin.GetActiveWorksheet();
currentSheet.Cells[Selectedrow, SelectedrCol] = factorial.ToString();
this.Close();
Disadvantage of this approach is if we are change the cell values the result willnot getting updated.But we need this kind of approach in some stages
Here we are discussing the way of reading only singe cell value .But there is a chance that user may selects a range of cells and we need to process this range of values.(
Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin
In this case first we need to identify the cell address in text box is a single cell address(Then read the cell value)
Or a Range Of cell address like A1:A12 like in the last image.In this case we need to read the range of cells using the code.
Worksheet activeworksheet = Globals.TesstPAddin.GetActiveWorksheet();
Range FreqRange = activeworksheet.get_Range(cellnames, Type.Missing);
object[,] ExcelCellvalues = FreqRange.get_Value(System.Reflection.Missing.Value) as object[,];
The 2d array ExcelCellvalues contains all valuesin the selected range of cells.(Both null values,strings,doubles etc).So we process these values and store the needed values on to a List or another array like this
List NeededValues = new List();
if (ExcelCellvalues != null)
{
foreach (object obj in ExcelCellvalues)
{
if (obj != null)
{
/*Do the needed checks on data and store it if they are OK*/
NeededValues.Add(obj.ToString());
}
}
}
If you want to get the address of cell & value of selectd cells
Worksheet activeworksheet = Globals.TesstPAddin.GetActiveWorksheet();
Range FreqRange = activeworksheet.get_Range(cellnames, Type.Missing);
foreach (Range cell in FreqRange)
{
String cellAddress = cell.get_Address(false, false, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, false, false).ToString();
String value= cell.Value;
}
If you want to check the validity of name of cells you enterd
This function checks given string is a range addres or not
A1:A13- valid
A1:B12 valid
A1:A-not valid
A1:aa not valid
1A:b1 –not valid
Check A String is Valid Excel cell Name or not
