Excel Addin dynamic formula insertion in a range

Excel Addin dynamic formula insertion in a range

Formula:

Range object exposes Formula property which can be set through code. I this article we will see an example which will have dynamic formula insertion in entire column.

Prerequisites:

  • Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
  • Create Excel Addin in C# code style (Visual Studio Tools for Office)
  • Create a ribbon designer and put button

Steps 1- Create a form put one combobox, one textbox embedded a button (to launch range selector) and one button to perform execution as shown below:

The dropdown (combobox) I have used to populate formulas which can be placed within range. Pay attention Select range textbox has a small button upon clicking on it, It launches a Range Selector which will allow user to dynamically select range.

Step 2– Code example launch form on ribbon button click event:

private void btnInsertFormula_Click(object sender, RibbonControlEventArgs e)
{
	vbaOverallExercise frmExercise=new vbaOverallExercise();
	frmExercise.Show();
}

Step 3– Code example to grab user input through Range selector:

private string sourceAddress = string.Empty;
private int rowNumber;
private void btnRangeSelctor_Click(object sender, EventArgs e)
{
	var userRange = Globals.ThisAddIn.Application.InputBox("Select range to insert formula:",
		"VBAOVERALL Range Selector", string.Empty, System.Reflection.Missing.Value,
		System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, 8);
	if (userRange!=null)
	{                
		//build range from user input
		excel.Range oRange = (excel.Range) userRange;
		sourceAddress = oRange.Address;
		rowNumber = oRange.Row;
	}
}

Finally code that will process our goal:

private void btnProcess_Click(object sender, EventArgs e)
{
	//retain user choise
	string formulaName = cmbFormulas.Text;
	if (!string.IsNullOrEmpty(formulaName))
	{
		//get current workbook
		excel.Workbook oWorkbook = Globals.ThisAddIn.Application.ActiveWorkbook;
		//get active worksheet
		excel.Worksheet oWorksheet = oWorkbook.ActiveSheet;
		//insert formula
		oWorksheet.Range["H" + rowNumber].Formula = "=" + formulaName + "(" + sourceAddress + ")";
		
	}
}

Step 4- Make selection using range selector

Finally the output should look like below upon clicking on Process button:

Next : Dynamic data sort in Excel C#

Leave a Reply

Your email address will not be published.