The most powerful feature of any spreadsheet is the formula reference – the syntax for referring to another cell or range of cells in a formula as a simple variable in the formula expression.
Spread.NET 12 supports all the different kinds of formula references that are supported by Microsoft Excel, and a new special type called, External Variables. This post will review in detail all the types of formula references and explain the power of the new External Variables feature.
Reference Styles
There are two types of reference styles supported in Spread.NET 12 and Microsoft Excel: A1 and R1C1. The default reference style is A1: first the column is specified using the column label, then the row is specified using the row number. Using Spread.NET 12, the ReferenceStyle property specifies the ReferenceStyle:
[VB]
FpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.ReferenceStyle = GrapeCity.Spreadsheet.ReferenceStyle.R1C1
[C#]
fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.ReferenceStyle = GrapeCity.Spreadsheet.ReferenceStyle.R1C1;
Some examples of references using A1 reference style are “A1” which refers to the upper-left cell, and “E5” which refers to the cell in the fifth row and fifth column.
The R1C1 reference style can be easier for programming use cases because it uses numeric indexes for both rows and columns. Using R1C1 reference style, first the row is specified using the row number, and then the column is specified using the column number. When the reference style is changed to R1C1, then you should also change the column header labels to show numbers instead of letters.
Using Spread.NET 12, the ColumnHeaderAutoText property specifies the HeaderAutoText:
[VB]
For Each sv As SheetView In FpSpread1.Sheets
sv.ColumnHeaderAutoText = HeaderAutoText.Numbers
Next
[C#]
foreach (SheetView sv in fpSpread1.Sheets)
sv.ColumnHeaderAutoText = HeaderAutoText.Numbers;
Cell and Range References
The most basic, and most commonly encountered type of reference in formulas, is the cell or range reference. A cell reference specifies a single cell. The syntax for a cell reference in the formula depends on the reference style.
A cell range reference uses a special operator ‘:’ called the range operator – the range operator is used between the upper-left cell reference and the lower-right cell reference to specify the cell range. A reference to a single cell generally does not use the range operator, but it is perfectly legal to specify a cell range reference with the same start and end cells.
Relative References
Relative references are the default type of reference and called relative because they are always relative to the target cell – the cell containing the formula. This type of reference is stored internally using offsets from the target cell. It is best to think of relative references as meaning “the cell X rows above (or below) and Y columns to the left (or right) of this cell.” When a relative reference is copied and pasted, or copied using drag-fill, then those relative references automatically adjust relative to the new location. Some examples of relative references are:
A1 Reference Style
A1
B2:F2
R1C1 Reference Style
R[-2]C
R[-8]C:R[-8]C[4]
Relative references are generally more convenient to use, because the built-in adjustment of the referenced cells is typically very useful when drag-filling formulas to calculate the same formula across a row or down a column. When you want to do the same calculation in a range of cells, and the referenced cells for the calculation are in parallel ranges, then relative references are appropriate.
Examples of typical use cases for relative references include most tabular data tables like monthly sales by division or product, or subtotals and totals in a balance sheet or financial report.
Absolute References
Absolute references are fixed, or “hard coded” references to the cell or cell range that do not depend on the target cell. Absolute references are specified in A1 reference style using the ‘$’ prefix, and in the R1C1 reference style using explicit row and column numbers. Some examples of absolute references are:
A1 Reference Style
$A$1
$B$2:$F$2
R1C1 Reference Style
R1C1
R2C2:R2C5
Absolute references are generally more appropriate to use for use cases where you want to treat a specific cell or cell range like a variable in the formula that always references that cell or cell range. Any time you find yourself using an absolute reference in a formula, you should instead create a custom name to represent that cell or cell range in the formula as a named range, because that makes the formulas much easier to read. Custom names will be discussed further below.
Mixed References
Mixed references are complex references that have some parts that are relative and depend on the target cell, and other parts that are absolute. Each coordinate can independently be either relative or absolute, so for a cell reference there are four cases and for a cell range there are sixteen cases. Mixed references are a very powerful tool for creating complex calculations. Using a mixed reference, you can specify whether you want the row and/or column to adjust or not, for each part of the cell or cell range reference, allowing quick creation of many types of complex calculations such as probability tables. Some examples of mixed references are:
A1 Reference Style
$A1
B$2:$F2
R1C1 Reference Style
R1C[1]
R2C:RC5
Mixed references are very useful for some specific use cases. For example, if you want to create a formula to calculate the cumulative total of the values in another column, you can use the SUM function with a mixed cell range reference which specifies the starting cell reference with an absolute cell reference to the cell containing the first value in the other column, and specifies the end celling cell reference with a relative row and absolute column:
When the formula is copied to other cells in the column using copy/paste or drag-fill, then only the last row reference changes, which computes the cumulative total.
Worksheet References
Worksheet references are references to another worksheet in the same workbook. The worksheet reference uses the name of the worksheet which is displayed in the worksheet’s tab. If the name contains spaces, then it must be enclosed in single-quotes. Worksheet references are required when referencing a cell or cell range in another worksheet, or when referencing a cell or cell range in the formula for a custom name with workbook scope – that will be covered in more detail below in the section about Custom Names.
When referencing a cell or cell range in the same worksheet, it is not necessary to use the worksheet name – you can reference cells and cell ranges within the same worksheet without specifying the worksheet reference.
If you do specify the workbook reference, then when the cell is copied and pasted to another worksheet, then the worksheet reference will be included and refer to the originally specified cell or cell range. For example, if there is a formula “=Sheet1!$A$1” in the cell Sheet1!E1, and this formula is copied and pasted to Sheet2, then the cell in Sheet2 will continue reference Sheet1!$A$1 because the worksheet reference is copied as part of the formula.
If the formula was “=$A$1” then the cell in Sheet2 would instead reference the cell Sheet2!$A$1 because there is no worksheet reference specified in the formula. Some examples of formulas containing worksheet references are:
A1 Reference Style
Sheet1!$A1
‘Monthly Sales’!B$2:$F2
R1C1 Reference Style
Sheet1!R1C[1]
‘Monthly Sales’!R2C:RC5
Note that when the worksheet name contains spaces or other special characters, or when it might be confused with a cell range reference in A1 or R1C1 reference style, then the worksheet name reference must be enclosed in single quotes. If the worksheet name contains single-quote or apostrophes, then those must be doubled inside the formula string – for example, if a worksheet named “test’s results” is used in a formula, then the reference must be as follows:
‘test’’s results’!$A$1
Three-D References
Worksheet references can also use the range operator ‘:’ to create a three-D reference. A three-D reference specifies the starting and ending worksheets and includes the specified cell or cell range in each of those worksheets. Some examples of formulas containing three-D references are:
A1 Reference Style
Sheet1:Sheet3!$A1
January:December!B$2:$F2
R1C1 Reference Style
Sheet1:Sheet3!R1C[1]
January:December!R2C:RC5
Workbook References (External References)
Workbook references are references to another workbook. These are also sometimes called “external references” because the source is external to the workbook and might not be available, so a cached last-known value is kept in the workbook for use in calculations when the source workbook is not open.
External references are also called “links” because the external workbooks are linked to the workbook through the formulas containing the external references to the other workbook. External references use the workbook name or file name enclosed in square-brackets.
Some examples of formulas containing external workbook references are:
A1 Reference Style
[Book1]Sheet1!$A1
[SalesDetail.xlsx]‘Monthly Sales’!B$2:$F2
R1C1 Reference Style
[Book1]Sheet1!R1C[1]
[SalesDetail.xlsx] ‘Monthly Sales’!R2C:RC5
The API’s in Spread.NET 12 related to external references include IWorkbook.LinkSources, IWorkbook.SaveLinkValues, IWorkbook.UpdateLinks, IWorkbook.BreakLink, and IWorkbook.UpdateLink, and full details about external reference support in Spread.NET 12 is here.
Structured References to Table Cells
Structured references are references to table cell or cell ranges which use a special syntax that is easier to read and understand than regular references. Structured references can only be used in formulas for table cells, or formulas which refer to table cells. When a table is created for a cell range in a worksheet, the top row in the cell range becomes the header cells of the table.
The columns in the table must be named, so is you don’t already have column names in the top row of the cell range, then the columns are named using auto-generated names Column1 and Column2:
Structured references use a special syntax that is much easier to read and understand because it uses the column names.
There are also some pre-defined names for accessing the cells in the table header row and totals row.
Some examples of formulas containing structured references are:
[@Column1]+[@Column2]
[Column1]
Table1[[#Headers],[Column1]]
Table1[[#Totals],[Column1]]
Table1[#All]
The first two examples above do not include a table name reference and are only valid inside the table cells referencing its columns. The last three examples do include the table name and can be used in cells outside of the table. The ‘@’ specifies a relative reference to the table cells in “this row” in the specified column. The column names can contain spaces and do not need to be enclosed in single-quotes – for example, if a column is names “January Sales” then you can reference it as “[@January Sales]” in a table cell formula.
Table cells cannot contain array formulas; however, array formulas in non-table cells can use structured references to table cell ranges. Structured references to external workbooks use a special syntax that looks like a reference to a worksheet, but using the table name instead of a worksheet name.
Some examples of formulas containing structured references to external workbooks are:
Book2!Table1[[#Headers],[Column1]]
‘Sales Data.xlsx’!Table1[[#Totals],[Column1]]
‘Tax Tables.xlsx’!Table1[#All]
Custom Names for Cell Ranges and More
You can greatly improve the readability of formulas by replacing explicit references to cell ranges with custom names defined using those ranges instead. Then those formulas can use helpful and descriptive names which can make formula auditing much easier and help to eliminate errors. The name must start with a letter, underscore, or backslash, and must contain only letters, numbers, periods, and underscores.
The name cannot contain embedded space characters and cannot look like a cell reference – for example, “D$5” or “R54C” would not be permitted as these might be confused with range references.
You also cannot use “c” or “r” alone as a name (these are reserved). Names are not case-sensitive in formulas, but you can use naming conventions like camel-casing to initialize the names and improve readability in the Name Manager tool.
Custom names are not limited to named cells and ranges – you can create a custom name for any valid formula expression. All calculation functions are available for use in custom name formulas. Very large and complex calculations can often be broken down into simpler steps that can use custom names, making the formulas much easier to read and understand.
Very complex calculations can be separated into concrete stages using custom names. This is especially helpful in cases where some of those concrete stages can be reused across more than one calculation – for example, using a custom name for tax rate calculations. Custom names also support specifying a comment to document the use and purpose of the name, which the user can see when referencing the name in the formula bar or in the Name Manager tool:
When adding a workbook-scope name, you can use a special syntax with the worksheet reference missing, but still using the ‘!’ operator before the cell or cell range reference. This special syntax will reference cell or cell range in the active sheet when the name is used in a formula.
The Name Manager tool shown above is built into the main spreadsheet control binary and available programmatically using simple code:
[VB]
FpSpread1.ShowListCustomNameForm(Me)
[C#]
fpSpread1.ShowListCustomNameForm(this);
Custom Name Scope: Workbook and Worksheet Names
Custom names can be scoped to either the workbook or the worksheet. This is specified when creating the name – if you are using the user interface then you can select the scope using the drop-down:
In code, you can specify the scope of the name in the last argument when calling the INames.Add method:
[VB]
FpSpread1.AsWorkbook().Names.Add("test", "!$A$1", Nothing, 0, 0, True)
[C#]
fpSpread1.AsWorkbook().Names.Add("test", "!$A$1", null, 0, 0, true);
Workbook-level names are visible to all worksheets in the workbook. In the cell formulas, Worksheet-level names defined for that worksheet take precedence over workbook-level names when the same name is defined for both levels, and worksheet-level names are only visible to the cells of that worksheet.
When a worksheet is copied, all its worksheet-level custom names are also copied to new worksheet-level custom names in the new worksheet.
Evaluation of Custom Name Expressions
When you add or change a custom name, the specified formula is immediately parsed into a logical expression that represents that formula, and any cells containing formulas using that name will recalculate automatically (if automatic calculation isn’t disabled). When a cell formula contains that name, the formula is calculated as if the name expression replaced the name reference, and all references and functions (for example, ROW()) are evaluated in the context of the particular cell being calculated.
Custom name expressions are not actually calculated until some cell formula references that name and that cell is calculated. Custom name expressions are calculated by the Name Manager to display the Value column of the dialog – in that case, the values are calculated using the current active cell as the context for relative references.
Absolute and Relative References in Custom Names
Custom names can contain absolute or relative references just like cell formulas. Generally, it is better to always use absolute references in custom names, because then those names can be used in any cell and always reference the specified absolute references. Custom names that contain relative references are much more complicated to use, because their formulas are necessarily tied to some cell on creation and can only be used in cells where those relative references make sense.
When creating the custom name using the UI “New Name” dialog, the active cell in the active sheet is used to parse the relative references in the formula expression, so all relative references are parsed into relative offsets from that cell – for example, “the cell two rows up and one column to the left” – and when this name is used in a different cell, the referenced cell changes too.
It is sometimes helpful to use relative references in custom names – and I will post such an example later this year – but in general should be avoided unless necessary. When they are used, it is important to note in the comment for the custom name which cell should be active when viewing or editing the formulas using the Name Manager tool, because when other cells are active the formulas will not show or calculate as expected.
External Variables
Spread.NET 12 Windows Forms supports an innovative new feature called “external variables” that is like custom names but much more powerful and flexible. External variables are a new special type of custom name that can be used to push data from some external source, or to pull calculated values to some external source.
That “external source” can be anything the program requires, like a web service or another control – it is up to the application to implement. The external variable has a name and a formula expression which can reference any worksheet like a workbook-scope custom name. The calculation of external variables operates much differently that of from custom names: external variables operate like tiny one-cell worksheets that are separate from the other worksheets.
Relative references are not supported for external variable expressions because there is no context cell when those expressions are evaluated, so all relative references are automatically converted into absolute references.
Since the external variable operates at workbook-scope, all cell range references must specify the worksheet name(s).
External Variable Example
Creating external variables requires implementing a class which inherits from ExternalVariable. This simple example shows how to create an external variable that takes a value from a TextBox control for use in calculations, and then create another external variable that multiples the first value by a factor value in cell and returns it in another TextBox control.
Here is the implementation of the class:
[VB]
Imports GrapeCity.Spreadsheet
Imports GrapeCity.CalcEngine
'Creating and defining the external variable
Public Class TextBoxExternalVariable
Inherits ExternalVariable
Private _textBox As TextBox
Private _asInput As Boolean
Public Sub New(ByVal textBox As TextBox, ByVal asInput As Boolean)
_textBox = textBox
If asInput Then
AddHandler textBox.TextChanged, AddressOf TextBox_TextChanged
End If
End Sub
Private Sub TextBox_TextChanged(ByVal sender As Object, ByVal e As EventArgs)
Dirty()
End Sub
Protected Overrides Function OnDirtying() As Boolean
Return Not _asInput
End Function
Protected Overrides Sub OnDirtied()
Refresh()
End Sub
Protected Overrides Sub EvaluateCore(ByVal context As IEvaluationContext, ByVal result As IValue)
Dim text As String = _textBox.Text
Dim dblValue As Double = Nothing
If Not String.IsNullOrEmpty(text) AndAlso Double.TryParse(text, dblValue) Then
result.SetValue(dblValue)
Else
result.SetValue(text)
End If
End Sub
Public Sub Refresh()
If Not _asInput Then
_textBox.Text = Me.Value.GetText()
End If
End Sub
End Class
[C#]
using System;
using System.Windows.Forms;
using GrapeCity.CalcEngine;
using GrapeCity.Spreadsheet;
namespace ExternalVariablesDemoCS
{
// Creating and defining the external variable
public class TextBoxExternalVariable : ExternalVariable
{
private TextBox _textBox;
private bool _asInput;
public TextBoxExternalVariable(TextBox textBox, bool asInput)
{
_textBox = textBox;
if (asInput)
{
textBox.TextChanged += TextBox_TextChanged;
}
}
private void TextBox_TextChanged(object sender, EventArgs e)
{
Dirty();
}
protected override bool OnDirtying()
{
return !_asInput;
}
protected override void OnDirtied()
{
Refresh();
}
protected override void EvaluateCore(IEvaluationContext context,
IValue result)
{
string text = _textBox.Text;
if (!string.IsNullOrEmpty(text) && double.TryParse(text,
out double dblValue))
{
result.SetValue(dblValue);
}
else
{
result.SetValue(text);
}
}
public void Refresh()
{
if (!_asInput)
{
_textBox.Text = this.Value.GetText();
}
}
}
}
The constructor of TextBoxExternalVariable requires a TextBox control and a flag to indicate whether the text box will be used for input or for output. The TextBox control is kept in a local field and if the input flag is set then an event handler is attached to the TextBox.TextChanged event, which calls the Dirty() method when the text changes.
The code in OnDirtying() returns true when the variable is used for input and ignored otherwise, and the code in Dirtied() calls the Refresh() method, which updates the TextBox.Text with the current value when the input flag is not set.
There is some initialization code required in the form to set up the sample:
[VB]
Public Class Form1
'Using the external variable with text box control
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
Dim workbook = FpSpread1.AsWorkbook()
Dim activeSheet = workbook.ActiveSheet
activeSheet.Cells("A1").Value = "Factor"
activeSheet.Cells("B1").Value = 2
workbook.Names.AddExternalVariable("x", New TextBoxExternalVariable(TextBox1, True))
Dim extVariable2 = New TextBoxExternalVariable(TextBox2, False)
workbook.Names.AddExternalVariable("y", extVariable2, "Sheet1!B1 * x")
extVariable2.Refresh()
End Sub
End Class
[C#]
using System;
using System.Windows.Forms;
namespace ExternalVariablesDemoCS
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
var workbook = fpSpread1.AsWorkbook();
var activeSheet = workbook.ActiveSheet;
activeSheet.Cells["A1"].Value = "Factor";
activeSheet.Cells["B1"].Value = 2;
// Adding Ext. Variable - x referring to textbox1
workbook.Names.AddExternalVariable("x",
new TextBoxExternalVariable(textBox1, true));
// Adding Ext. Variable "y" referring to textbox2 with formula "Sheet1!B1 * x"
var extVariable2 = new TextBoxExternalVariable(textBox2, false);
workbook.Names.AddExternalVariable("y", extVariable2, "Sheet1!B1 * x");
extVariable2.Refresh();
}
}
}
The first external variable named “x” is created using the first text box and used for input. The second external variable names “y” is created using the second text box for output, and calculates the product of the first value and the value in Sheet1!B1.
In run time, when the value in the first text box changes and fires the TextChanged event, then the second text box immediately updates with the calculated product.
VB sample code | C# sample code
Sean Lawyer