Syntax highlighting for Excel Formulae - language suggestions

Question

Based on most popular answer:

Tl;dr: to use the Swift language rules, add <!-- language-all: lang-swift --> to the top of your post, or use ```lang-swift at the beginning of each code block where you desire highlighting.

---

Highlight.js doesn't support the Excel formula language and I doubt SE would add it to the list of styles to support on the network. This has been asked for at Meta Stack Overflow too.

What's a good alternative?

For example this code:

=LAMBDA
(
  vArg,
  LET
  (
    v,    INT(ABS(vArg)),
    ev,   ISEVEN(v),
    half, 1/2,
    high, INT(SQRT(v)+half),
    seqLimit, 2^20,
    IF
    (
      AND(ev, v<>2),
      0, 
      IF
      (
        high>seqLimit,
        -1, 
        LET
        (
          high,         INT(SQRT(v)+1/2),
          seq,          v/SEQUENCE(1,high,1,2),
          iSeq,         INT(seq),
          factors,      seq*(seq=iSeq),
          rFactors,     FILTER(factors,(factors>1)*(factors<v),0),
          sumFactors,   SUM(rFactors)
          --(sumFactors=0) 
        )
      ) 
    )
  )
)

is rendered using the tag based (excel or lambda) or auto recognition syntax highlighting like this:

which is not perfect as the --(sumFactors=0) line is rendered like a comment to me which is wrong, and formula names ISEVEN, FILTER etc. are highlighted inconsistently.

Any suggestions of similar supported languages that may look good?

Answer 1

Try using Scala (lang-scala)

Example:

=LAMBDA
(
  vArg,
  LET
  (
    v,    INT(ABS(vArg)),
    ev,   ISEVEN(v),
    half, 1/2,
    high, INT(SQRT(v)+half),
    seqLimit, 2^20,
    IF
    (
      AND(ev, v<>2),
      0, 
      IF
      (
        high>seqLimit,
        -1, 
        LET
        (
          high,         INT(SQRT(v)+1/2),
          seq,          v/SEQUENCE(1,high,1,2),
          iSeq,         INT(seq),
          factors,      seq*(seq=iSeq),
          rFactors,     FILTER(factors,(factors>1)*(factors<v),0),
          sumFactors,   SUM(rFactors)
          --(sumFactors=0) 
        )
      ) 
    )
  )
)

As far as I can tell, this works well with minified formula.

Example for minified formula:

=LET(hello,"Hello World, ",hello & Sheet1!$A2 & "!")

Answer 2

Try swift

=IF($A1>6,"Hello","Goodbye")

---

=LAMBDA
(
  vArg,
  LET
  (
    v,    INT(ABS(vArg)),
    ev,   ISEVEN(v),
    half, 1/2,
    high, INT(SQRT(v)+half),
    seqLimit, 2^20,
    IF
    (
      AND(ev, v<>2),
      0, 
      IF
      (
        high>seqLimit,
        -1, 
        LET
        (
          high,         INT(SQRT(v)+1/2),
          seq,          v/SEQUENCE(1,high,1,2),
          iSeq,         INT(seq),
          factors,      seq*(seq=iSeq),
          rFactors,     FILTER(factors,(factors>1)*(factors<v),0),
          sumFactors,   SUM(rFactors),
          --(sumFactors=0) 
        )
      ) 
    )
  )
)

---

highlight.js actually supports Excel (ref: Supported Languages) but it's not supported by the highlighter version of SE.

I'm not familiar with Excel Lambda functions but it looks that the problem is the formula, not the highlighting. I.E, the following formula taken from another post from Code Review, the highlight is applied correctly there

=IF(OR(ISBLANK(X2),ISBLANK(Y2)),0,X2/VLOOKUP(YEAR(Y2),$EU$3:$FH$57,14,FALSE))*$FH$57

Rendered version of the above formula taken from the source

NOTE: Using the inspect tool, the auto-detect function assigned swift to the above formula and sql to the formula in the question.

I just quickly scanned LAMBDA function. As far I can tell, it looks that the Lambda function in the question is not using the regular spreadsheet formula syntax as there is no separator before --(sumFactors=0). But that is not the unique problem b/c after adding the comma, Excel still shows:

P.S. looking at the source of the formula the OP mentioned that the formula doesn't work.

Related

• What is syntax highlighting and how does it work?

Answer 3

Adding syntax highlighting for Excel Formula would be a good thing. Ideally it would be specific to Excel formula (ie as supported by highlight.js), however if that's unlikely to be supported on SO/SE then utilising another language would be acceptable. The question is: which one.

Whichever is chosen, it should

• give useful highlights to the various components of a formula

• give reasonable results on most (if not all) common use cases

• give unhelpful results on as few as possible edge cases

The two suggested languages are compared below

Swift

=A1+A2
=$A$1+A2
=A$1+A4
=$A1+A5
=$A$1:A10+B10:B100
=SUM(A1:A10)
=SUM(R1C1:R[9]C1+RC[-3]:RC[-3])
=MyRange
=MyUDF(A1:A10,B1,"Hi",-C1+D1,MyRange)
=D1#
=@D1
=@D1:D100
=XLOOKUP(J1,H1:H10,D1:D10,"A1",0,1)
=--(A1:A10=0)
=IF($A1>6,"Hello","Goodbye")
=LAMBDA(base,width,paddingStr, IF(LEN(base)<=width, base, LET(LHS, LEFT(base, width), RHS, RIGHT(base, LEN(base) - width), LHS & paddingStr & PadInternal(RHS,width,paddingStr))))

Scala

=A1+A2
=$A$1+A2
=A$1+A4
=$A1+A5
=$A$1:A10+B10:B100
=SUM(A1:A10)
=SUM(R1C1:R[9]C[-4]+RC[-3]:R[9]C[-3])
=MyRange
=MyUDF(A1:A10,B1,"Hi",-C1+D1,MyRange)
=D1#
=@D1
=@D1:D100
=XLOOKUP(J1,H1:H10,D1:D10,"A1",0,1)
=--(A1:A10=0)
=IF($A1>6,"Hello","Goodbye")
=LAMBDA(base,width,paddingStr, IF(LEN(base)<=width, base, LET(LHS, LEFT(base, width), RHS, RIGHT(base, LEN(base) - width), LHS & paddingStr & PadInternal(RHS,width,paddingStr))))

Based on this, I'd suggest Swift be ruled out, as it gives inconsistent results for range references, and mangles some ranges.

Scala is better, but doesn't handle Implicit Intersection ranges well