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

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

Download FullCode From Here

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

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

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

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

Download FullCode From Here

Check A String is Valid Excel cell Name or not

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: