Version

Accessing Cells and Cell Regions by Name

Topic Overview

Purpose

This topic explains, with code examples, how to access cells and regions within a worksheet in a Microsoft® Excel® workbook by their names or by their named references. For information about how to access cells and regions by their reference strings, refer to the Accessing Cells and Regions by their Reference Strings topic.

Accessing Cells and Regions by Names – Conceptual Overview

Introduction

In Microsoft Excel, individual cells, as well as cell regions can have Names assigned to them, (The Names are assigned and displayed in the Name Box of the spreadsheet.) The Name of a cell or region can be used to reference that cell or region instead of their cell references. The Names of cells and regions can be defined either in Excel or programmatically in code.

Infragistics supports the referencing of cells and regions by Name through the GetCell and GetRegion methods of the Worksheet object. You refer to the cell or region using the NamedReference instance that refers to that cell or region.

Requirements

To be able to access an individual cell or a region of cells, they must be Named, either in MS Excel or programmatically in code.

Naming cells and regions in Excel

To define a Name for a cell or region in an Excel spreadsheet, with the cell or region selected, click the Name Box , type the desired name , and then press Enter .

Naming cells and regions in code

Cell or region Names defined in code are sometimes referred to as “named references”.

To Name a cell or a region in code, use the syntax that follows. In your actual code, replace the variables (cell_Name, range_Name, sheet_name, cell_reference, range_cell_reference, instance_for_cell_NamedReference, and instance_for_range_NamedReference) with specific names.

In Visual Basic:

' Define a Name for a cell.
Dim instance_for_cell_NamedReference As NamedReference = wrkBook.NamedReferences.Add("cell_Name", "=sheet_name!cell reference ", sheet_name)
' Define a Name for a region.
Dim instance_for_range_NamedReference As NamedReference = wrkBook.NamedReferences.Add("range_Name ", "= sheet_name!range cell reference ", sheet_name )

In C#:

// Define a Name for a cell.
NamedReference instance_for_cell_NamedReference = wrkBook.NamedReferences.Add("cell_Name", "=sheet_name!cell reference", sheet_name);
// Define a Name for a region.
NamedReference instance_for_range_NamedReference = wrkBook.NamedReferences.Add("range_Name", "=sheet_name!range cell reference", sheet_name);

Referencing cells and regions by Name

To reference a cell or a region by Name in code, use the following syntax. In your actual code replace the variables (cell_Name, range_Name) with specific names.

In Visual Basic:

' Access a cell by its name.
sampleSheet.GetCell("cell_Name")
' Access a region by its name
sampleSheet.GetRegion("range_Name")

In C#:

// Access a cell by its Name.
sampleSheet.GetCell("cell_Name");
// Access a region by its Name.
sampleSheet.GetRegion("range_Name");

Accessing Cells and Regions by Name – Code Examples

Overview

The following table lists the code examples included in this topic.

Example Description

Names are created in code for cell B5 and for cell region G1 ÷ G10 of Sheet1.

Demonstrates how to access a cell with a Name refNameB3 and a cell region with a Name refNameRegionC1C10 .

Demonstrates how to reference a Named cell and region for displaying their content as a string in a message box.

Demonstrates how to access by their names, a cell and region in the first worksheet of workbook Book1.xls residing on C:\, add a comment to that cell, apply a formula to the region, and then save the workbook.

Demonstrates how to define a NamedReference for a cell and region and get access to them from the NamedReference instance.

Naming Cells and Regions in Code

Description

In the following code, Names are created in code for cell B5 and for cell region G1 ÷ G10 of Sheet1 .

Code

In Visual Basic:

' Define a Name for the cell.
Dim nameRefCell As NamedReference = wrkBook.NamedReferences.Add("NamedRefCell", "=Sheet1!$B$5", sampleSheet)
' Define a Name for the region.
Dim namerefRegion As NamedReference = wrkBook.NamedReferences.Add("NamedRefRegion", "=Sheet1!$G$1:$G$10", sampleSheet)

In C#:

// Define a Name for the cell.
NamedReference nameRefCell = wrkBook.NamedReferences.Add("NamedRefCell", "=Sheet1!$B$5", sampleSheet);
// Define a Name for the region.
NamedReference namerefRegion = wrkBook.NamedReferences.Add("NamedRefRegion", "=Sheet1!$G$1:$G$10", sampleSheet);

Referencing Cells and Regions by Name – Example 1

Description

The following code demonstrates how to access a cell with a Name refNameB3 and a cell region with a Name refNameRegionC1C10 .

Code

In Visual Basic:

' Access a cell by its Name.
sampleSheet.GetCell("refNameB3").Comment = testComment
' Access a region by its Name.
sampleSheet.GetRegion("refNameRegionC1C10").ApplyFormula("=A1*B1")

In C#:

// Access a cell by its Name.
sampleSheet.GetCell("refNameB3").Comment = testComment;
// Access a region by its Name.
sampleSheet.GetRegion("refNameRegionC1C10").ApplyFormula("=A1*B1");

Referencing Cells and Regions by Name – Example 2

Description

The following code demonstrates how to reference a Named cell and region for displaying their content as a string in a message box.

Code

In Visual Basic:

MessageBox.Show(nameRefCell.ReferencedCell.Value.ToString())
MessageBox.Show(namerefRegion.ReferencedRegion.Worksheet.ToString())

In C#:

MessageBox.Show(nameRefCell.ReferencedCell.Value.ToString());
MessageBox.Show(namerefRegion.ReferencedRegion.Worksheet.ToString());

Referencing Cells and Regions by Name – Example 3

Description

The following example demonstrates how to access by their names, the cell B3 and the region C1:C10 in the first worksheet of workbook named Book1.xls residing on C:. The cell and the region are named refNameB3 and refNameRegionC1C10, respectively earlier in Excel.

The example code loads a Workbook and creates an instance of the Worksheet. Cell B3 of the Worksheet is accessed using the GetCell method by passing in the named reference of the cell. A comment is added to cell B3 .

Similarly RegionC1:C10 is accessed using the GetRegion method by passing in the named reference of the region. A formula is applied to the region C1:C10 .

Prerequisites

This example has the following prerequisites:

  • An Excel file named Book1.xls residing in C:\

  • Arbitrary numbers added in regions A1:A10 and B1:B10 of the Worksheet

  • Names added for Cell B3 and region C1:C10 as explained in Naming cells and regions in Excel

Code

In Visual Basic:

' Load the workbook.
Dim wrkBook As Infragistics.Documents.Excel.Workbook = Infragistics.Documents.Excel.Workbook.Load("C:\\Book1.xls")
Dim sampleSheet As Infragistics.Documents.Excel.Worksheet = wrkBook.Worksheets(0)
Dim testComment As Infragistics.Documents.Excel.WorksheetCellComment = New WorksheetCellComment
Dim formatted As FormattedString = New FormattedString("This cell has a reference name.")
testComment.Text = formatted
' Access the cell by its Name.
sampleSheet.GetCell("refNameB3").Comment = testComment
' Access the region by its Name.
sampleSheet.GetRegion("refNameRegionC1C10").ApplyFormula("=A1*B1")
' Save the workbook.
wrkBook.Save("C:\\Book1.xls")

In C#:

// Load the workbook.
Infragistics.Documents.Excel.Workbook wrkBook = Infragistics.Documents.Excel.Workbook.Load("C:\\Book1.xls");
Infragistics.Documents.Excel.Worksheet sampleSheet = wrkBook.Worksheets[0];
Infragistics.Documents.Excel.WorksheetCellComment testComment = new WorksheetCellComment();
FormattedString formatted = new FormattedString ("This cell has a reference name.");
testComment.Text = formatted;
// Access the cell by its Name.
sampleSheet.GetCell("refNameB3").Comment = testComment;
// Access the region by its Name.
sampleSheet.GetRegion("refNameRegionC1C10").ApplyFormula("=A1*B1");
// Save the workbook.
wrkBook.Save("C:\\Book1.xls");

Defining Names for a Cell and a Region and Referencing Them

Description

The following code demonstrates how to define a NamedReference for a cell and region and get access to them from the NamedReference instance.

Prerequisites

This example has the following prerequisites:

  • An Excel file named Book1.xls residing in C:\

Arbitrary numbers added in Regions A1:A10 and B1:B10 of the Worksheet

Code

In Visual Basic:

' Load the workbook.
Dim wrkBook As Infragistics.Documents.Excel.Workbook = Infragistics.Documents.Excel.Workbook.Load("C:\\Book1.xls")
Dim sampleSheet As Infragistics.Documents.Excel.Worksheet = wrkBook.Worksheets(0)
'Define a Name for the cell.
Dim nameRefCell As NamedReference = wrkBook.NamedReferences.Add("NamedRefCell", "=Sheet1!$B$5", sampleSheet)
MessageBox.Show(nameRefCell.ReferencedCell.Value.ToString())
' Define a Name for the region.
Dim namerefRegion As NamedReference = wrkBook.NamedReferences.Add("NamedRefRegion", "=Sheet1!$G$1:$G$10", sampleSheet)
MessageBox.Show(namerefRegion.ReferencedRegion.Worksheet.ToString())

In C#:

// Load the workbook.
Infragistics.Documents.Excel.Workbook wrkBook = Infragistics.Documents.Excel.Workbook.Load("C:\\Book1.xls");
Infragistics.Documents.Excel.Worksheet sampleSheet = wrkBook.Worksheets[0];
// Define a Name for the cell.
NamedReference nameRefCell = wrkBook.NamedReferences.Add("NamedRefCell", "=Sheet1!$B$5", sampleSheet);
MessageBox.Show(nameRefCell.ReferencedCell.Value.ToString());
// Define a Name for the region.
NamedReference namerefRegion = wrkBook.NamedReferences.Add("NamedRefRegion", "=Sheet1!$G$1:$G$10", sampleSheet);
MessageBox.Show(namerefRegion.ReferencedRegion.Worksheet.ToString());

Related Content

Topics

The following topics provide additional information related to this topic.

Topic Purpose

This topic illustrates saving and loading of Excel files.

This topic demonstrates accessing cells and regions within a worksheet in an Excel® Workbook using their reference strings.