2.79 FAQ-849 Can I turn off or selectively control use of spreadsheet cell notation in my workbooks?

Last Update: 4/26/2022


Origin 2017 introduced a simplified "Spreadsheet Cell Notation" (SCN) for use in the Set Column Values and F(x)= column formulas. This notation simplifies writing of column formulas and it supports references to other sheets and books but there may be instances where you would prefer to use the older "col()/wcol()" notation (e.g. you have scripts that rename the column Short Name). This FAQ summarizes how to control use of spreadsheet cell notation in your workbooks and projects.

How to turn spreadsheet cell notation off (or on) for a single workbook

Since introduction, SCN has been enabled, by default, for new workbooks. Origin 2017 through Origin 2019 displayed this icon Spreadsheet cell notation mode.png in the upper-left corner of the worksheet to indicate that SCN was enabled. Beginning with Origin 2019b, the icon is hidden by default, to make room for new features. However, as with previous versions, SCN remains enabled, by default, for new workbooks.


To turn off cell notation via the user-interface ...

Right-click on the workbook window title bar, and choose Properties. On the Properties tab, clear the Spreadsheet Cell Notation check box (it will be near the center of the dialog).


To control this via LabTalk, use ...

page.xlcolname = 0; // 0 = off, 1 = on.

Using the @RCN LabTalk System Variable to control spreadsheet cell notation

Origin 2017, @RCN = 7 (default): Spreadsheet cell notation is turned on, by default, for new books created from the New Workbook Button New Workbook.png toolbar button; or new books created on file import, when SCN is on in the active workbook. Additionally, new workbooks created by most X-Function based analysis dialog boxes will also have SCN turned on, regardless of whether SCN is turned on in the active workbook (the workbook containing the data which you are analyzing).

Origin 2019, @RCN = 15 (default): In addition to those cases described for Origin 2017, Origin 2019 and later will have SCN turned ON for windows created by win -t or newbook. This new default behavior may not work for you -- for instance, in situations where you have scripts that change the column Short Name (see, for instance, FAQ-872).

Therefore, if you do not want SCN turned on when creating new windows by win -t or newbook, you should set the value of @RCN=7 in the Set System Variables dialog box. Each time Origin runs, the value of @RCN will be read and Origin will treat windows created by win -t or newbook, as it did in Origin 2017.


Bit Values of @RCN:

  • 0 = Cell notation is turned OFF for all new books.
  • 1 = Cell notation is turned ON for new books created from the New Workbook toolbar button.
  • 2 = When importing, cell notation behavior follows active book; if cell notation is OFF in active book, then OFF in all new books. If ON in active book, ON in all new books.
  • 4 = Most analysis X-Functions have an Auto Spreadsheet Cell Notation on New Book check box that is shown in Tree View mode. When this box is checked and this bit is enabled, any new workbook created for X-Function output will have SCN turned on (Auto On) regardless of the SCN-state of the active book (does not apply to wsplit, wsplit_book, merge_book, wmove_sheet).
  • 8 = Added in 2019. Cell notation is turned ON for all workbooks created by LabTalk win -t command or X-Function newbook.
Note: Bit values are additive (e.g., the default value of @RCN = 7 means cell notation is Auto On for conforming analysis tools (+4), import follows active book (+2), and SCN is ON for all new worksheets from toolbar (+1)), BUT it is OFF for all workbooks created by LabTalk win -t command or X-Function newbook.

Globally turn off spreadsheet cell notation

When you manipulate the value of @RCN, you are controlling use of spreadsheet cell notation for new books. However, there is a method for turning off spreadsheet cell notation for every existing book in the project and, optionally, for turning off spreadsheet cell notation for all new books.

Turn off spreadsheet cell notation for every book in this project:
Open your Script Window or Command Window, type the following and press Enter:

doc -e WW {page.xlcolname=0}

Note that you can turn on cell notation using page.xlcolname=1. See the LT page.xlcolname object property.

Turn off cell notation for every book in this project AND for all new books created in any project:
Open your Script Window or Command Window, type the following and press Enter:

ClrX

Running the ClrX macro, does two things:

  • Runs the doc -e WW {page.xlcolname=0} script described above. This disables spreadsheet cell notation for every workbook in the project.
  • Adds the @RCN LT System Variable to the Set System Variables dialog box and set its value to 0. This disables spreadsheet cell notation for all new workbooks and this applies across all Origin sessions (unless/until you delete @RCN or change its value, in the Set System Variables dialog box).

Note that you can undo the effects of running this macro by:

  1. running the doc -e WW {page.xlcolname=1} command.
  2. Opening the Set System Variables dialog box and either deleting @RCN or setting its value to 1.


For more information, see:


Keywords:short name, Set Values, Set Column Values, column formula, worksheet, new book