Spreadsheet Cell Execution and Editing Reference

The Spreadsheet is analogous to other spreadsheet applications in terms of its standard operations and functionality, such as manipulating blocks of cells, editing cells, referencing cells and inserting functions. Organized into a table of cells arranged in 400 rows (numbered 0 to 399) and 26 columns (labeled A to Z), each cell is identified by its column letter and row number; for example, cell A2 is located at the intersection of column A and row 2.

The spreadsheet is configured one cell at a time. The contents of each cell are defined as a formula, and any piece of information inserted into a cell (whether a single numeric value or a complex vision processing function) is considered a part of the formula. When you highlight a cell, it becomes outlined in black and the information in the cell is displayed in the Formula Bar of the Job Edit toolbar.

Clicking on a cell automatically makes it the active cell. The active cell is the cell that is currently highlighted within the spreadsheet. For example, in the following spreadsheet, B2 is the active cell and is outlined in black.  The headers of both the active column (B) and the active row (2) are highlighted in orange to indicate the address of the active cell.

Tip: Right-clicking within the spreadsheet displays a shortcut menu that allows various spreadsheet operations to be performed. For more information, see Right-Click Menu.

Cell Execution Order

As with other conventional spreadsheets, the execution order of the cells in the In-Sight spreadsheet is dynamically determined by the relationship and dependencies between cell functions. The In-Sight spreadsheet determines the order of cell execution primarily by evaluating cells according to their dependencies, and secondarily by their locations within the spreadsheet.

Cell Dependencies

In the following example, the value of cells B2 and A3 directly depend on the value of A2. Cell A4 indirectly depends on A2 because it references A3. Whenever the value in A2 changes, then the values of B2 and A3 must be re-evaluated, followed by A4. An internal execution tree is automatically created to track cell dependencies; this tree is rebuilt whenever the spreadsheet is modified while the system is Offline.

For example:

A2 = 1

A3 = A2+1

A4 = A3+1

B2 = A2+2

From the Profile Job dialog, we can observe the execution tree for this example:

When a cell's value or expression changes, the In-Sight spreadsheet locates the cell in the tree and determines which cells need to be reevaluated. In this example, if the value in A2 changes, then all dependent cells (B2, A3, and A4) must be reevaluated; however, if the expression in cell B2 changes, then no other cells need to be updated because no cell is dependent upon B2. For more information, see Profile Job Dialog.

Order of Cell Execution

After dependencies have been evaluated, the cells are evaluated by their location within the spreadsheet. Cells are sorted by their row-coordinate (from left to right), and then by their column-coordinate (from top to bottom.) In the preceding example cell A2 is executed first, followed by cells B2, A3, and then A4. If two cells exist in the same row, then the left-most cell is executed first.

From the Profile Job dialog, we can observe that cell A2 is executed first, followed by cells B2, A3, and finally A4.

The following are exceptions to normal cell execution order:

  • ReadResult and WriteResult functions: The ReadResult and WriteResult functions are placed near the bottom of the execution tree regardless of their location in the spreadsheet. Since ReadResult waits for data coming back from the Slave's WriteResult, the function is executed at the end of job so that time is not wasted waiting for the data. WriteResult is also executed near the end of job to make sure that the system is ready to get the next trigger. For more information, see ReadResult and WriteResult.
  • Clocked functions: In the execution tree, clocked functions are always placed at the top of the tree because they need to access the old data in the other cells. For more information, see Clocked Data Storage Functions.
  • Functions with non-dependency cell-references: Several functions take cell references as arguments but do not establish any dependencies. The Row function, which returns the row coordinate of the referenced cell, is one of these functions. For example, Row(A4) always returns 4, even if the value in A4 changes.

For example, the following ReadResult function has been added to the spreadsheet: A1 = ReadResult($A$0,"SYSTEM1",1000).

From the Profiler, we see that cell A1 is at the bottom of the execution tree even though it is near the top of the spreadsheet:

Next, a Clocked Data Storage function is added: A5 = Accumulate($A$0,0,0,0)

Note that even though cell A5 is below the other cells containing data, it is at the top of the execution tree:

What Triggers the Execution?

Cells are executed when:

  • A cell which has dependents has been modified.
  • External events occur, such as AcquireImage triggers, serial data, and incoming packets to TCPDevice. For more information, see AcquireImage and TCPDevice.
  • Graphics Controls (such as Button, Checkbox, EditInt) signal a spreadsheet Event trigger. For more information, see Graphics Functions and Event.

Most Vision Tool functions depend either directly or indirectly on the AcquireImage function in Cell A0. When a new image is acquired, these functions execute and return new values. Also, the spreadsheet updates when an image acquisition or other spreadsheet Event is triggered.

Enter Information into the Spreadsheet

Machine vision applications are configured by assembling formulas and inserting them into the cells of the spreadsheet. A formula can contain four types of data:

Note: An In-Sight spreadsheet can contain a maximum of 4,096 active cells. Inserting a function (such as FindBlobs) that, in turn, inserts multiple Vision Data Access functions into the spreadsheet can cause the spreadsheet to attempt to exceed the limit. In this case, no warning dialog is issued; the cells over the 4,096 limit are simply not inserted.
How To... Description
Insert Numbers into a Cell

Select a blank cell and enter the numeric value (for example, 26).

Insert Text into a Cell

Select a blank cell and press the apostrophe (') key. Any text following the apostrophe is inserted into the active cell as a text label rather than a formula.

Insert a Cell Reference into a Cell

There are four main methods for inserting cell references into cells:

  • Double-click on a blank cell, and then click on the cell you want to reference (this creates a relative reference; to create an absolute reference, hold down the SHIFT key).



  • Select a blank cell, and then manually type the cell name to reference (this creates a relative reference; to create an absolute reference, type the $ symbol before the letter and the number [for example, $C$3]).


  • Select a blank cell, and then press CTRL+SHIFT+A or CTRL+SHIFT+R to insert an absolute or relative cell reference, respectively, and enter cell selection mode.
  • Select a blank cell, and then click the Absolute Reference or Relative Reference button on the Job Edit toolbar and enter cell selection mode.
Insert a Function into a Cell

There are six main methods for inserting functions into cells:

  • Drag a function from the Palette to a blank cell. If the function has an associated property sheet, it is automatically displayed.
  • Select a blank cell, and double-click on a function in the Palette. If the function has an associated property sheet, it is automatically displayed.
  • Select a blank cell, and then press the Function button on the Job Edit toolbar. This launches the Insert Function dialog.
  • Select a blank cell, and then press either the ENTER or EQUALS key. Next, press either the UP or DOWN ARROW key. This displays a function box, where you can type or select a function. Press either the TAB or ENTER key to close the function box.



  • Select a blank cell, manually type the name of the function and then either press the ENTER key or manually type an open parenthesis. If the function has an associated property sheet, it is automatically displayed.



  • Select a blank cell, manually type the name of the function into the formula bar and then either press the ENTER key or manually type an open parenthesis. If the function has an associated property sheet, it is automatically displayed.

Note: Functions with a variable-length list of arguments (such as Wizard and WriteEIP) do not have property sheets.
Accept or Reject the Formula

To accept the new formula/value, do one of the following:

  • Press the ENTER key.
  • Click the OK button on the Job Edit toolbar.

To cancel the new formula/value and restore the previous expression, do one of the following:

  • Press the ESC key.
  • Click the Cancel button on the Job Edit toolbar.

Any text highlighted in red indicates an error in your formula that must be corrected before the formula can be accepted.

Use the Formula Bar

The formula bar is located on the Job Edit toolbar. It contains three pieces of information:

  • The active cell (E4)
  • Any formula or value contained in the active cell (442)
  • Cell state information for the active cell (if $A$4)

You can directly edit the formula, value or label in the active cell by simply clicking inside the formula bar and making any desired changes. Click inside a function's parentheses to display a tooltip that assists with syntax.

View Tooltips and Help Information

Tooltips are available in both the spreadsheet and the formula bar. Click inside the function's parentheses to see the syntax.

Tip: When the cursor is positioned inside a function's parentheses, you can press F1 to load the external help topic for the function.

Edit a Spreadsheet Cell

In addition to editing the formulas contained in individual cells, you can cut, copy, paste or delete cells or adjust cell formatting.

Note: During a cut-and-paste operation, the contents of the cut cell may be lost if an error occurs, even if the Cancel button is pressed to stop the operation. In this scenario, use Undo (CTRL+Z) to restore the contents of the cut cell.
How To... Description

Edit Numbers and Text

To edit a numeric value or text in the active cell, press the ENTER or EQUALS key. You can also simply type over the number or text, but this erases the previous number or text.

Edit Cell References To change a cell reference, simply follow the steps for inserting a cell reference.
Edit Functions

Functions can be edited in the property sheet (if available) or in the spreadsheet.

  • If the function has an associated property sheet, press the ENTER or EQUALS key on the active cell to automatically launch it.
  • To edit the function in the active cell, rather than in a property sheet, press F2.

    Press the UP or DOWN ARROW key to display the function box below the formula. Press the RIGHT or LEFT ARROW key to move the cursor within the formula.

    Note: Based on the location of the cursor, only the functions that are valid for that particular expression in the formula are listed in the function box.

    To choose a different function, either type or select the desired function. Press either the TAB or ENTER key to close the function box.