Insert Cell Method in Excel Add-in with code example

Insert Cell Method in Excel Add-in with code example

Insert Cell

Insert new cell allows user to insert new cell in selected range. Shift cell performs shift operation which impacts other cells based on choice. Range object exposes Insert method in Excel. In this article we will be inserting new cell using, VBA (Visual Basic for Applications), VSTO (Visual Studio Tools for Office) with C# code style.

Syntax

expression.Insert(Shift, CopyOrigin)

Parameters

  1. Shift: its an optional parameter and variant in nature, it determines in which direction the cell will be shifted post Insert operation. The shift direction can be determined by using predefined constants derived from XlInsertShiftDirection as:
    • xlShiftToRight: Shift cells to the right when a new cell insert takes place
    • xlShiftDown: Shift cells to the down when a new cell insert takes place
  2. CopyOrigin: its an optional parameter and variant in nature, determines from where the formatting would be copied for newly inserted cell. It can be determined by Excel predefined constants derived from XlInsertFormatOrigin as:
    • xlFormatFromLeftOrAbove: copy format from left or above cell this is default constant which gets triggered when Insert happens
    • xlFormatFromRightOrBelow: copy format from right or below

Data source

VBA code example

Public Sub InsertCellExample()
    'Range object
    Dim oRange As Range
    
    'Bind selection
    Set oRange = Selection
    
    'Shift Cell down
    oRange.Insert shift:=xlShiftDown, copyOrigin:=xlFormatFromLeftOrAbove
    
    'Cleanup
    Set oRange = Nothing
End Sub

Output

C# code example

private void btnInsertCells_Click(object sender, RibbonControlEventArgs e)
{
	//capture user selection
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;
	const int xlFormatFromLeftOrAbove = 0;
	const int xlFormatFromRightOrBelow = 1;
	oRange.Insert(excel.XlInsertShiftDirection.xlShiftDown, CopyOrigin: xlFormatFromLeftOrAbove);
}

Next: insert new row in Excel using VSTO C#

Leave a Reply

Your email address will not be published.