st4-3.0.0

Major Version Update:

This update requires Sublime Text build 4075 or higher.

Explicit support added for the unique syntax used by Google Sheets and LibreOffice Calc.

• See README for full details.

Support for customized separators.

• For locales other than those that use . , and ; for decimal numbers, function arguments/array rows, and array columns, respectively.
• See README for full details.

Support for R1C1-style references in Excel and LibreOffice Calc.

• If you haven't heard of these before, they are great for =PY code in Python for Excel.

Completion snippets for functions found in all three applications.

• Completing functions with Tab places the cursor inside an inserted pair of brackets if the function has valid arguments.
• Completions for functions without valid arguments simply insert a pair of brackets and place the cursor after it.

Full Changelog: 2.5.2...3.0.0

st3-2.5.2

This is the legacy version of Excel formula from before it became Spreadsheet formula in Package Control. It can be used with Sublime builds 3092 - 4074.

Edit (1/6/2025): Logical comparator functions AND, OR, etc. and logical flow functions IF, IFS, etc. that had new scopes applied in 2.5.1 now have both the support.function.excel scope applied to them in addition to the two new scopes applied to said categories of functions: keyword.operator.logical.excel and keyword.control.conditional.if.excel, respectively. The visual effect of this in the default Mariana color scheme is that these two categories of functions additionally show in italics, reflecting the fact that they are built-in functions.

Two relatively major changes from a UX POV:

1. Reverted the previous change explicitly allowing commas as decimal separators. This change made unit testing (QC) too hectic. We have plans to extend this syntax into multiple flavors, including commands that allow users to specify their locale and software of choice (Excel, Sheets, or Libre), at which point this and several other conflicts will be resolved properly.

Apologies for any inconvenience this may cause. I have no idea if anyone other than myself and Michael reads these release notes, but if you run into any locale specific issues or quirks, please submit them here if they are not already mentioned: #19.

2. Formalize naming rules for names defined in LET, name manager, etc. These changes extend to functions. They also extend to Google Sheets and Libre office.

Other changes:

The space intersection operator is now explicity scoped for use between two names as those defined in LET and the name manager, and between a name and a standard cell or range reference.

Fleshed out unit tests for naming rules and uses of the space intersection operator in Excel.

Full Changelog: 2.5.1...2.5.2

2.5.1

README changed to Markdown.

Unit tests updated to account for following changes.

Add special consideration for the use of # with zero-dimensional references like =A1:A1#. These are the only type of cell-range references that can have a trailing # without adding one before the :, like is required in =A1#:A2#. In other words, =A1:A2# is invalid in Excel, but =A1:A1# is valid in Excel.

Added a specific scope (keyword.control.conditional.if.excel) for the following core logical flow functions:
IF
IFS
IFNA
IFERROR
SWITCH

Add a specific scope (keyword.operator.logical.excel) for the following core logical comparator functions:
AND
OR
XOR
NOT

2.5.0

Reworked previous hotfix for name-naming rules.

Complete overhaul of cell and range reference matching to allow for the various usages of the # and @ operators (in the interest of UX when typing something like 1:A1, includes revert to previous update flagging invalid column:row and row:column references).

Update not mentioned previously: files with this syntax enabled can now use ' to write psuedo comments.

2.4.2

Hotfixed oversight in usage of the # operator (was completely wrong before, now it's probably just mostly correct). More polishing to come.

Added an extra pattern to further increase compliance with name-naming rules.

2.4.1

1. Updated builtin function list with Google Sheets functions in an attempt to correct an issue regarding function highlighting potentially stemming from differences in system locale and/or operating system and verified both Excel and Google Sheets functions are up to date as of 12/27/2024 (credit to acid_form).

2. Updated matching for functions not in the builtin list to try to allow for user-defined functions in the above case, and to more closely follow the naming rules for user-defined functions in Excel.

3. Updated matching for names, like those defined in the Name Manager or in LET functions, to more closely follow the naming rules for names in Excel (rules for names are currently set to be identical to functions).

4. Allow commas and semicolons to be used interchangably as separators between function arguments.

5. Flag the following reference formats as invalid:

• A:1
• 1:A

2.3.1

Hotfix:
Fixed oversight in definition metadata (version and release date weren't updated. Syntax definition name was set to the development version's ending in "(LOCAL)").

2.3.0

1. Added support for the following styles of cell references:
   A1:A (Google Sheets specific)
   A:B
   1:2
2. Added some support for the use of commas in place of periods to accomodate more users. This causes periods and commas to not be scoped correctly in certain contexts (e.g., =SUM(0,0)), but it does allow them to be formatted the same color in the default Sublime Text color scheme.

2.2.2

1. Added support for <, >, and their legal combinations with =. (Big oversight 😬)
2. Added error checking for illegal combinations of <, >, and =.
3. Added support for Excel's error types (e.g., #NULL!, #DIV/0!, etc.)
4. Modified scopes for string wildcard and wildcard escapes:

5. Various optimizations and probably some minor stuff I'm missing.

2.1.1

Support for non-English Unicode in table names!

Column names have worked since at least 2.0.0.