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:
