Name Range, Name Manager Excel Add-in with code example

Name

In excel Name Range refers to a location or range which can be identified uniquely within workbook or worksheet based on scope. Excel offers Names collection which contains all available Name within specified workbook. A name can be referenced with two main properties:

• Name: name of the named range that you wish to have
• RefersTo: it refers to the pointing location it can be a Shape, a Range etc.

Adding Name

1. Navigate to Formula Ribbon tab
2. Click on Name Manager button under Defined Names group
3. A Name Manager dialog will appear (Please refer below screenshot “Name Manager“)
4. Click on New button on the dialog
5. Another dialog will appear (Please refer Create Name Dialog) asking:
   1. Name : name of your range
   2. Scope : refers to the name availability within (workbook or worksheet) default it is Workbook but you can make it as you want by selecting respective sheet from the dropdown
   3. Comment : its an optional parameter where you can supply any comment to remember what this name was for
   4. Refers To : it provides range selector which takes range as input to bind with the given name
6. Click on OK button and Name Manager dialog will reappear (Please refer Final Name Dialog) with your newly created name listed under Name Range List

Name Manger

Create Name Dialog

Final Name Dialog

C# code example

private void btnAddName_Click(object sender, RibbonControlEventArgs e)
{
	//get active workbook reference
	excel.Workbook oWorkbook = Globals.ThisAddIn.Application.ActiveWorkbook;

	//Input user to supply name
	var oName = Globals.ThisAddIn.Application.InputBox("Enter Name :", "Name Manager", "VBAOVERALL");

	//Input user to select range which he wants to name
	var oSelectRange = Globals.ThisAddIn.Application.InputBox("Select range :", "Select Range", string.Empty,
		System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
		System.Reflection.Missing.Value, 8);

	//Convert var to range
	excel.Range oRange = (excel.Range) oSelectRange;

	//Add name
	oWorkbook.Names.Add(oName.ToString(), oRange.Address);

	//Show dialog to the user
	Globals.ThisAddIn.Application.Dialogs[excel.XlBuiltInDialog.xlDialogNameManager].Show();
}

VB.Net code example

Private Sub AddNameExample_Click(sender As Object, e As RibbonControlEventArgs) Handles AddNameExample.Click
	'get active workbook reference
	dim oWorkbook as excel.Workbook
	oWorkbook=Globals.ThisAddIn.Application.ActiveWorkbook

	'Input user to supply name
	dim oName 
	oName=Globals.ThisAddIn.Application.InputBox("Enter Name :", "Name Manager", "VBAOVERALL")

	'Input user to select range which he wants to name
	dim oSelectRange 
	oSelectRange=Globals.ThisAddIn.Application.InputBox("Select range :", "Select Range", string.Empty,
			System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
			System.Reflection.Missing.Value, 8)

	'Convert var to range
	dim oRange as excel.Range
	oRange=CType(oSelectRange,excel.Range)

	'Add name
	oWorkbook.Names.Add(oName.ToString(), oRange.Address)

	'Show dialog to the user
	Globals.ThisAddIn.Application.Dialogs(XlBuiltInDialog.xlDialogNameManager).Show()
End Sub

VBA code example

Public Sub AddNameCode()
    Dim oRange As Range
    Dim oName As String
    
    'Get user input for name
    oName = Application.InputBox("Enter Name :", "Name Manager", "VBAOVERALL")
    
    'Get Range
    Set oRange = Application.InputBox("Enter Name :", "Name Manager", , , , , , 8)
    
    'Add name
    ActiveWorkbook.Names.Add oName, oRange.Address
    
    'Show name manager
    Application.Dialogs(xlDialogNameManager).Show
End Sub

Next >> Excel Range Object a complete reference

Share on Facebook

Tweet