; SPDX-License-Identifier: MPL-2.0
; english.vla - the 40 vocabulary rules from SENTENCES.md,
; each shipped with its proof: a "test:" line the loader verifies
; after registration. A vocabulary does not load unless its tests
; pass, so this file is its own regression suite.
;
; Load with:
;   EnglishResetGrammar
;   EnglishLoadVocabulary ThisWorkbook.Path & "\english.vla"
;   Debug.Print EnglishLintReport()
;
; References: bare for cells, columns, sheets, AND ranges (cell B2,
; column C, sheet Data, range A1:C50); quotes only when a reference
; contains spaces ("Q1 Data") or when creating a sheet whose name's
; case matters. Bare references lowercase in transit.
;
; G1: patterns may use alternation slots and optional literals.
;   {d:left|right}  matches one of the words; the MATCHED word binds
;                   into the template ({d} -> left), so xl{d} becomes
;                   xlleft - one rule where surface spellings differ.
;   [key]           optional literal: matched when present, free when
;                   absent, never fails the rule. One word only; for
;                   a multi-word spelling, compose the two features -
;                   "at|under [key]" accepts at / at key / under /
;                   under key, which is exactly how Store reads below.
;   into|in         G10: a BARE alternation - no braces, no name -
;                   matches one branch and binds nothing. The rule
;                   is now simple: BRACES CAPTURE (the matched word
;                   travels into the template, as in {d:left|right}
;                   -> xl{d}); bare words just match. Bare tokens
;                   may carry / surfaces too (sort/ed), and an
;                   optional may hold an alternation ([in|into]).
;   center/ed       G1.1: a branch (or optional) may carry ONE slash
;                   - stem/suffix - accepting both surfaces while the
;                   STEM binds, so xl{d} stays xlcenter whether the
;                   writer typed center or centered.
; The load-time audit expands every branch, surface, and include/omit
; when checking duplicate shapes, so a collapsed rule cannot silently
; shadow (or be shadowed by) a plain one.
;
; G3: dialect layering. Load order is the contract: THIS base file
; first, then dialect phrasebooks alphabetically (E2's plan). A
; dialect rule with the same shape as an earlier rule REFUSES to
; load unless marked - "override: <pattern> => <template>" - and the
; marker must be earned: it must replace exactly one earlier
; phrasebook rule (matching nothing, matching several, or matching
; the built-in core all refuse with directions). Explain names which
; file won every sentence ("[from dialect.vocab (overrides
; english.vla)]").
;
; G2: typed reference slots - :range :cell :column :sheet :color -
; are :text plus a Check-time SHAPE check with a teaching refusal
; ("Sort range banana ..." now refuses at Check instead of dying at
; runtime). A QUOTED reference always asserts past the check: quotes
; are the door for named ranges and spaced sheet names, exactly as
; the quoting doctrine above already reads. :sheet checks no shape
; (any word can name a sheet); it exists so the rule declares what
; it touches - ?EnglishListRefSlots prints that declaration, the
; surface U1's Rehearse will consume.

; ---- 11-20: putting values in and getting them out --------------------
; G10: the template face hides the dot behind a carried macro - the
; dot stays first-class in VLA (it IS member access), but a template
; author writes what the action is called, not how VBA spells it.
(defmacro
    (set-formula r f)
    "write a formula string into a range"
    (set! (. r formula) f))

(english-vla
    "put formula {f:text} into|in cell {r:cell}"
    (set-formula (range {r}) {f}))
(test-success
    "Put formula \"=B2*2\" into cell B3."
    (set-formula (range "b3") "=B2*2"))
(test-success
    "Put formula \"=B2*2\" in cell B3."
    (set-formula (range "b3") "=B2*2"))

; G-FORMULA slice 1: a formula into many cells, through the same
; set-formula (Formula2 on both backends). It is written for the first
; cell and Excel adjusts it for the rest, as Fill Down would - measured
; 2026-09-29: "=B2*C2" into D2:D4 left D3 =B3*C3 and D4 =B4*C4.
(english-vla
    "put formula {f:text} into|in range {r:range}"
    (set-formula (range {r}) {f}))
(test-success
    "Put formula \"=B2*C2\" into range D2:D50."
    (set-formula (range "d2:d50") "=B2*C2"))
(test-success
    "Put formula \"=B2*C2\" in range D2:D50."
    (set-formula (range "d2:d50") "=B2*C2"))

; The rows of a column, counted as the program runs - remember rows'
; own range expression. Rows 2 to 1 (no data rows) write nothing:
; Excel would read D2:D1 as D1:D2 and put the formula on the header row
; (measured 2026-09-29), and a program with no rows to fill has nothing
; to do. "to" is remember rows' word, "through" delete and group rows'.
(defmacro
    (set-formula-rows c a b f)
    "write a formula into rows a to b of column c, adjusted row by row - nothing when b is above a"
    (if (>= b a) (then (set-formula (range (& c a ":" c b)) f))))

(english-vla
    "put formula {f:text} into|in rows {a:expr} to|through {b:expr} of column {c:column}"
    (set-formula-rows {c} {a} {b} {f}))
(test-success
    "Put formula \"=B2*C2\" into rows 2 to last-row of column D."
    (set-formula-rows "d" 2 last-row "=B2*C2"))
(test-success
    "Put formula \"=B2*C2\" in rows 2 through 50 of column D."
    (set-formula-rows "d" 2 50 "=B2*C2"))

; G-FORMULA slice 4: pareto's formula filled down in one sentence, as a
; person types a formula into D2 and fills it down as far as the data
; goes. The sentence names the column that says how far: Excel's own
; double-click reads the column beside it and stops at its first blank,
; where "last filled row" looks up from the bottom, so pareto's "fill
; down to the last row" is refused, expecting 'filled'. It is Excel's
; Fill Down, as "Fill down range" is: the cell's formula goes down
; adjusted row by row, and its format with it. The cell always gets its
; formula; nothing is filled when that row is not below the cell, since
; Fill Down over one cell copies the cell above into it. The rows are
; reached with resize's named row count, as insert-n-rows-at reaches
; them - the form the interpreter has reviewed since IN.2.
(defmacro
    (set-formula-fill-down r k f)
    "write a formula into a cell and fill it down to the last filled row of column k - nothing filled when that row is not below the cell"
    (begin (set-formula r f)
           (if (> (last-filled-row k) (. r row))
               (then (fill-down (. r resize :rowsize (+ (- (last-filled-row k) (. r row)) 1)))))))

(english-vla
    "put formula {f:text} into|in cell {r:cell} and fill down to last filled row of column {k:column}"
    (set-formula-fill-down (range {r}) {k} {f}))
(test-success
    "Put formula \"=B2*C2\" into cell D2 and fill down to the last filled row of column B."
    (set-formula-fill-down (range "d2") "b" "=B2*C2"))
(test-success
    "Put formula \"=B2*C2\" in cell D2 and fill down to the last filled row of column A."
    (set-formula-fill-down (range "d2") "a" "=B2*C2"))
(test-fail
    "Put formula \"=B2*C2\" into cell D2 and fill down to the last row."
    "then I expected 'filled'")

(english-vla
    "put today into|in cell {r:cell}"
    (set! (range {r}) (date)))
(test-success
    "Put today into cell D1."
    (set! (range "d1") (date)))
(test-success
    "Put today in cell D1."
    (set! (range "d1") (date)))

(english-vla
    "put {e:expr} into|in cell {r:cell}"
    (set! (range {r}) {e}))
(test-success
    "Put 5 into cell B2."
    (set! (range "b2") 5))
(test-success
    "Put total plus 1 into cell \"B2\"."
    (set! (range "B2") (+ total 1)))
(test-success
    "Put 1 in cell A2."
    (set! (range "a2") 1))

; G1 day-1: fill a whole range with one value - the range twin the
; cell rule never had.
(english-vla
    "put {e:expr} into|in range {r:range}"
    (set! (range {r}) {e}))
(test-success
    "Put 0 into range A1:B5."
    (set! (range "a1:b5") 0))
(test-success
    "Put 0 in range A1:B5."
    (set! (range "a1:b5") 0))

; F.4 (shape 1): "set {v:var} to cell {r:cell}" removed here - it was
; dead code, always shadowed by the built-in "set {v:var} to {e:expr}"
; (VLA_English.bas's own prelude), since ParsePrimCore's "cell {ref}"
; expression primary already parses to (range {ref}), byte-identical
; to what this rule's own template produced. The general rule has
; always actually handled "Set price to cell B2." -> (set! price
; (range "b2")); this specific rule never fired even once.

(english-vla
    "copy cell {a:cell} to cell {b:cell}"
    (set! (range {b}) (range {a})))
(test-success
    "Copy cell A1 to cell C1."
    (set! (range "c1") (range "a1")))

(english-vla
    "copy range {a:range} to range {b:range}"
    (copy-range-to (range {a}) (range {b})))
(test-success
    "Copy range \"A1:A5\" to range \"C1:C5\"."
    (copy-range-to (range "A1:A5") (range "C1:C5")))

(english-vla
    "paste values of range {a:range} into range {b:range}"
    (paste-values (range {a}) (range {b})))
(test-success
    "Paste values of range \"A1:A5\" into range \"C1\"."
    (paste-values (range "A1:A5") (range "C1")))

(english-vla
    "clear cell|range {r:range}"
    (clear-contents (range {r})))
(test-success
    "Clear cell B2."
    (clear-contents (range "b2")))
(test-success
    "Clear range \"A1:C10\"."
    (clear-contents (range "A1:C10")))
(test-success
    "Clear range A1:C10."
    (clear-contents (range "a1:c10")))

(english-vla
    "remember range {r:range} as {v:var}"
    (obj-set! {v} (range {r})))
(test-success
    "Remember range \"B2:B4\" as results."
    (obj-set! results (range "B2:B4")))
(test-success
    "Remember range B2:B4 as results."
    (obj-set! results (range "b2:b4")))

; V3.2 (owner revision): keyed memory's store verb. V3.0 shipped
; "Remember ... under ... in ..." beside "Remember ... as ..." and
; defended it as one everyday verb with two tails; the owner's ear
; caught what that defense papered over - a discipline-6 violation:
; Remember-as binds a NAME to a thing, the keyed form puts an entry
; INTO a container, and those are two mechanisms, which one verb may
; never carry. "Store" is idiomatic English's container verb
; (entries are stored in lookups; lookups do not "remember" - they
; lack the sentience), so Remember keeps its single meaning
; (naming) and Store takes the other. Two spellings, one template,
; the Show/Say precedent: the canonical "at key" and the filing
; idiom "under". Storing at an existing key REPLACES (the key keeps
; its first spelling); keys are case-insensitive; the split between
; Excel's two representations lives in VlaDictSet. Reading back is
; expression syntax, not a sentence: "prices for part-code" is a
; value anywhere (a loud step error on a missing key); "keys of
; prices" walks the keys; "For each pair in prices" walks entries.
; The lookup slot is :var deliberately, the Append precedent:
; storing into a name never Created surfaces as a step-numbered
; runtime message at YOUR sentence, not a VBA compile error.
; G1: "at key" and "under" were two rules; the alternation plus an
; optional [key] makes them one, and buys "at" and "under key" free -
; four natural spellings, one template.
(english-vla
    "store {e:expr} at|under [key] {k:expr} in {d:var}"
    (vladictset {d} {k} {e}))
(test-success
    "Store 100 at key \"ax-7\" in prices."
    (vladictset prices "ax-7" 100))
(test-success
    "Store 250 under \"bx-2\" in prices."
    (vladictset prices "bx-2" 250))
(test-success
    "Store 7 at \"cx-1\" in prices."
    (vladictset prices "cx-1" 7))
(test-success
    "Store 9 under key \"dx-4\" in prices."
    (vladictset prices "dx-4" 9))

; ---- 21-30: making it look right --------------------------------------
; G1: the matched style word IS the font property name.
(english-vla
    "make cell {r:cell} {d:bold|italic}"
    (make-{d} (range {r})))
(test-success
    "Make cell A1 bold."
    (make-bold (range "a1")))
(test-success
    "Make cell A1 italic."
    (make-italic (range "a1")))

; G1: the matched color word completes the vb constant. COLOR-FAMILY:
; widened from the original two-branch demo to VBA's full eight named
; colors (BETA_REARVIEW.md's COLOR-FAMILY entry) - no engine change at
; all, since the glued-identifier substitution (vb{d}) and 3+-branch
; alternations both already work elsewhere in this file (format-as's
; own {d:currency|percent|date}); this is pure vocabulary data.
(english-vla
    "make cell {r:cell} {d:red|yellow|black|blue|cyan|green|magenta|white}"
    (set-fill-color (range {r}) vb{d}))
(test-success
    "Make cell C4 red."
    (set-fill-color (range "c4") vbred))
(test-success
    "Make cell C4 yellow."
    (set-fill-color (range "c4") vbyellow))
(test-success
    "Make cell C4 black."
    (set-fill-color (range "c4") vbblack))
(test-success
    "Make cell C4 blue."
    (set-fill-color (range "c4") vbblue))
(test-success
    "Make cell C4 cyan."
    (set-fill-color (range "c4") vbcyan))
(test-success
    "Make cell C4 green."
    (set-fill-color (range "c4") vbgreen))
(test-success
    "Make cell C4 magenta."
    (set-fill-color (range "c4") vbmagenta))
(test-success
    "Make cell C4 white."
    (set-fill-color (range "c4") vbwhite))

(english-vla
    "set font-color of cell {r:cell} to {e:expr}"
    (set-font-color (range {r}) (vlacolor {e})))
(test-success
    "Set font-color of cell A2 to hot-pink."
    (set-font-color (range "a2") (vlacolor hot-pink)))

(english-vla
    "set fill-color of cell {r:cell} to {e:expr}"
    (set-fill-color (range {r}) (vlacolor {e})))
(test-success
    "Set fill-color of cell A2 to \"#FF69B4\"."
    (set-fill-color (range "a2") (vlacolor "#FF69B4")))

(english-vla
    "clear color of cell {r:cell}"
    (clear-color (range {r})))
(test-success
    "Clear color of cell C4."
    (clear-color (range "c4")))

(english-vla
    "set font size of cell {r:cell} to {n:expr}"
    (set-font-size (range {r}) {n}))
(test-success
    "Set font size of cell A1 to 14."
    (set-font-size (range "a1") 14))

; G11r: the matched format word IS the method name (G11's name-
; substitution doctrine, folding what were three separate rules).
(english-vla
    "format cell {r:cell} as {d:currency|percent|date}"
    (format-as-{d} (range {r})))
(test-success
    "Format cell B2 as currency."
    (format-as-currency (range "b2")))
(test-success
    "Format cell B2 as percent."
    (format-as-percent (range "b2")))
(test-success
    "Format cell B2 as date."
    (format-as-date (range "b2")))

(english-vla
    "add border to range {r:range}"
    (add-border (range {r})))
(test-success
    "Add border to range \"A1:C10\"."
    (add-border (range "A1:C10")))

; ---- 31-40: rows, columns, and structure ------------------------------
(english-vla
    "delete row {n:expr}"
    (delete-row {n}))
(test-success
    "Delete row 5."
    (delete-row 5))

; G8: ordinal position, not quantity - "row 5" and "the third row" are
; genuinely different word orders, so this is a second rule sharing
; delete-row, not an alternation on the first. "the" is a dropped noise
; word (never a token either way, present or not in the sentence), so
; it has no place in the pattern itself. {n:expr} picks up ordinal
; words transparently (ParsePrimCore, VLA_English.bas) with no
; vocab-side change of its own.
(english-vla
    "delete {n:expr} row"
    (delete-row {n}))
(test-success
    "Delete the third row."
    (delete-row 3))
(test-success
    "Delete the fifteenth row."
    (delete-row 15))

; G7: {n:expr=1} is an omissible slot - present, its value binds;
; absent, "1" (already valid VLA text) substitutes instead. [at] rides
; G1's existing optional-literal machinery so the whole clause, not
; just the number, can vanish: "Insert a row." means row 1.
(english-vla
    "insert row [at] {n:expr=1}"
    (insert-row-at {n}))
(test-success
    "Insert row at 5."
    (insert-row-at 5))
(test-success
    "Insert a row."
    (insert-row-at 1))

(english-vla
    "delete column {c:column}"
    (delete-column {c}))
(test-success
    "Delete column C."
    (delete-column "c"))

; G11r: the matched verb IS the method name.
(english-vla
    "{d:hide|unhide} column {c:column}"
    ({d}-column {c}))
(test-success
    "Hide column C."
    (hide-column "c"))
(test-success
    "Unhide column C."
    (unhide-column "c"))

(english-vla
    "fit column {c:column}"
    (fit-column {c}))
(test-success
    "Fit column A."
    (fit-column "a"))
(test-success
    "Fit column B."
    (fit-column "b"))

(english-vla
    "fit all columns"
    (fit-all-columns))
(test-success
    "Fit all columns."
    (fit-all-columns))

(english-vla
    "freeze top row"
    (freeze-top-row))
(test-success
    "Freeze top row."
    (freeze-top-row))

(english-vla
    "sort range {r:range} by column {k:cell} descending"
    (sort-range-by (range {r}) (range {k}) xldescending))
(test-success
    "Sort range \"A1:C50\" by column B1 descending."
    (sort-range-by (range "A1:C50") (range "b1") xldescending))

; G1 day-1: the explicit "ascending" was refused before - the word
; is now legal (and optional; the bare spelling stays the default).
(english-vla
    "sort range {r:range} by column {k:cell} [ascending]"
    (sort-range-by (range {r}) (range {k}) xlascending))
(test-success
    "Sort range \"A1:C50\" by column B1 ascending."
    (sort-range-by (range "A1:C50") (range "b1") xlascending))
(test-success
    "Sort range \"A1:C50\" by column B1."
    (sort-range-by (range "A1:C50") (range "b1") xlascending))
(test-success
    "Sort range A1:C50 by column B1."
    (sort-range-by (range "a1:c50") (range "b1") xlascending))

; ---- 41-50: sheets, calculations, and asking the user -----------------
(english-vla
    "work on sheet {s:sheet}"
    (begin (vlaensuresheet {s}) (activate-sheet {s})))
(test-success
    "Work on sheet Results."
    (begin (vlaensuresheet "results") (activate-sheet "results")))

(english-vla
    "go to sheet {s:sheet}"
    (activate-sheet {s}))
(test-success
    "Go to sheet Data."
    (activate-sheet "data"))

(english-vla
    "add [new] sheet called {s:sheet}"
    (add-sheet-called {s}))
(test-success
    "Add sheet called \"Report\"."
    (add-sheet-called "Report"))
(test-success
    "Add new sheet called \"Scratch\"."
    (add-sheet-called "Scratch"))

(english-vla
    "delete sheet {s:sheet}"
    (delete-sheet {s}))
(test-success
    "Delete sheet Old."
    (delete-sheet "old"))

(english-vla
    "save this workbook"
    (save-current-workbook))
(test-success
    "Save this workbook."
    (save-current-workbook))

; LX.14: the put rules for the sum, average, largest, smallest, median and
; standard deviation of a range, and of a range remembered by a name,
; retired. Function phrases (with the function words, below) carry their
; sentences, translated as before, through the put rule above, and their
; proofs went with them. The where rules below carry a second value, so
; they stay sentence rules.

; G-FORMULA slice 4: sum-where and average-where into a cell. Like put
; sum, the cell gets the number, not a live formula.
(english-vla
    "put {d:sum|average} of range {r:range} where range {c:range} matches {e:expr} into|in cell {t:cell}"
    (set! (range {t}) ({d}-of-where (range {r}) (range {c}) {e})))
(test-success
    "Put sum of range B2:B90 where range A2:A90 matches \"West\" into cell D1."
    (set! (range "d1") (sum-of-where (range "b2:b90") (range "a2:a90") "West")))
(test-success
    "Put average of range B2:B90 where range A2:A90 matches \"West\" in cell D2."
    (set! (range "d2") (average-of-where (range "b2:b90") (range "a2:a90") "West")))

; LX.14: the set rules for the same figures retired too, carried by the
; same phrases through the built-in set, their proofs with them.

; B5 decision, blessed: "put answer into <name>" keeps its "into" even
; though Put normally targets places and Set targets names - the whole
; sentence is one ask-and-store idiom, and this is the phrasing people
; reach for when dictating it. Migration would break the natural form
; for a consistency only implementers notice. (Recorded so nobody
; re-litigates it from the Set/Put rule alone.)
(english-vla
    "ask {q:expr} and put answer into {v:var}"
    (set! {v} (inputbox {q})))
(test-success
    "Ask \"Your name?\" and put answer into who."
    (set! who (inputbox "Your name?")))

; G-FORMULA slice 3: calculation mode and recalculate, pareto.txt section
; 11 (the owner's calls, 2026-09-30). "Automatic calculation" is Excel's
; own name for the mode (Formulas, Calculation Options): turning it on
; means Automatic, whatever the mode was before, and a longer "... except
; for data tables" can come later, since this rule stops at "except". A
; Run gives Excel's settings back as it found them when it ends (U.29), so
; a program cannot leave Excel in manual; the CLI, where a setting is meant
; to last, can. While it is off, a formula whose input changes keeps
; its old value until a recalculate or turning it back on, which
; recalculates at once: Excel's manual mode, documented, not refused.
; Registered before the screen-updating pair, so the likelier slip, "Turn
; off calculation.", is refused expecting 'automatic'.
(english-vla
    "turn off automatic calculation"
    (set-calculation xlcalculationmanual))
(test-success
    "Turn off automatic calculation."
    (set-calculation xlcalculationmanual))
(test-fail
    "Turn off calculation."
    "then I expected 'automatic'")

(english-vla
    "turn on automatic calculation"
    (set-calculation xlcalculationautomatic))
(test-success
    "Turn on automatic calculation."
    (set-calculation xlcalculationautomatic))

; Excel has no one-workbook recalculation: F9, Application.Calculate,
; recalculates every open workbook, and a workbook's sheets calculated one
; after another give wrong numbers when a sheet reads one calculated after
; it. So the sentence names what it does, and "Recalculate the workbook."
; is refused expecting 'all'. "This sheet" is the sheet the program is on,
; as in "Protect this sheet" - Excel's Calculate Sheet, which recalculates
; that sheet's formulas from the values the other sheets show now.
(english-vla
    "recalculate all open workbooks"
    (recalculate-all-open-workbooks))
(test-success
    "Recalculate all open workbooks."
    (recalculate-all-open-workbooks))
(test-fail
    "Recalculate the workbook."
    "then I expected 'all'")

(english-vla
    "recalculate this sheet"
    (recalculate-this-sheet))
(test-success
    "Recalculate this sheet."
    (recalculate-this-sheet))

(english-vla
    "turn off screen updating"
    (set-screen-updating false))
(test-success
    "Turn off screen updating."
    (set-screen-updating false))

(english-vla
    "turn on screen updating"
    (set-screen-updating true))
(test-success
    "Turn on screen updating."
    (set-screen-updating true))

; ---- 51-55: computed references ---------------------------------------
; Cells(row, column) is VBA's computed addressing: the column is a
; letter, the row is any expression. These rules make loop-driven
; cell access sayable: "Put total into column B row counter."
(english-vla
    "put {e:expr} into|in column {c:column} row {n:expr}"
    (set! (cells {n} {c}) {e}))
(test-success
    "Put total into column B row counter."
    (set! (cells counter "b") total))
(test-success
    "Put counter times 10 into column E row counter."
    (set! (cells counter "e") (* counter 10)))
(test-success
    "Put 9 in column E row 3."
    (set! (cells 3 "e") 9))

; F.4 (shape 1): two rules removed here, both dead code, both always
; shadowed by built-in prelude rules whose {:expr} slot already parses
; "cell in column X row Y" (ParsePrimCore) to byte-identical output:
;   "set {v:var} to cell in column {c:column} row {n:expr}" - shadowed
;   by "set {v:var} to {e:expr}"; both produced (set! {v} (cells {n}
;   {c})).
;   "show cell in column {c:column} row {n:expr}" - shadowed by "show
;   {e:expr}". NOT byte-identical: this rule's own template said
;   (debug-print ...), but it never once fired - the general rule's
;   (msgbox ...) is what "Show cell in column E row 2." has always
;   actually produced, and this rule's own test-success line already
;   recorded that as its "expected" output (blind-authoring-then-
;   engine-verifies proving the WRONG rule's behavior, undetectable
;   until F.4 shape 1 existed to say which rule actually fired).
;   debug-print was never real user-facing behavior for a "show"
;   sentence anywhere else in this corpus - msgbox is the established,
;   correct meaning of "show", and removing the dead rule lets the
;   general one provide it going forward instead of a shape nobody
;   could ever reach.
(english-vla
    "clear cell in column {c:column} row {n:expr}"
    (clear-contents (cells {n} {c})))
(test-success
    "Clear cell in column E row 9."
    (clear-contents (cells 9 "e")))

(english-vla
    "make cell in column {c:column} row {n:expr} bold"
    (make-bold (cells {n} {c})))
(test-success
    "Make cell in column A row counter bold."
    (make-bold (cells counter "a")))

; ---- 56-68: the expansion pack ----------------------------------------
; Reaching the data: the last-filled-row idiom, computed row ranges.
; LX.14: "set ... to last filled row of column ..." retired - the phrase
; "last filled row of column {c:column}" carries it, and fits anywhere a
; value goes ("last filled row of column B plus 1").
(english-vla
    "remember rows {a:expr} to {b:expr} of column {c:column} as {v:var}"
    (obj-set! {v} (range (& {c} {a} ":" {c} {b}))))
(test-success
    "Remember rows 2 to 9 of column A as data-cells."
    (obj-set! data-cells (range (& "a" 2 ":" "a" 9))))

; Text surgery.
(english-vla
    "set {v:var} to {t:expr} with {a:expr} replaced by {b:expr}"
    (set! {v} (replace {t} {a} {b})))
(test-success
    "Set cleaned to msg with \"x\" replaced by \"y\"."
    (set! cleaned (replace msg "x" "y")))

(english-vla
    "set {v:var} to trimmed {t:expr}"
    (set! {v} (trim {t})))
(test-success
    "Set tidy to trimmed raw-text."
    (set! tidy (trim raw-text)))

(english-vla
    "set {v:var} to first {n:expr} letters of {t:expr}"
    (set! {v} (left {t} {n})))
(test-success
    "Set prefix to first 3 letters of code."
    (set! prefix (left code 3)))

(english-vla
    "set {v:var} to last {n:expr} letters of {t:expr}"
    (set! {v} (right {t} {n})))
(test-success
    "Set suffix to last 2 letters of code."
    (set! suffix (right code 2)))

(english-vla
    "set {v:var} to position of {a:expr} in {t:expr}"
    (set! {v} (instr {t} {a})))
(test-success
    "Set dash-at to position of \"-\" in code."
    (set! dash-at (instr code "-")))

; Finding things (0 means not found - test with: If found-row is 0, ...).
(english-vla
    "set {v:var} to row of {e:expr} in column {c:column}"
    (set! {v} (vlafindrow {e} {c})))
(test-success
    "Set found-row to row of \"Widget\" in column A."
    (set! found-row (vlafindrow "Widget" "a")))

; Reports out the door.
(english-vla
    "export this sheet as pdf {p:expr}"
    (export-sheet-as-pdf {p}))
(test-success
    "Export this sheet as pdf report-path."
    (export-sheet-as-pdf report-path))

(english-vla
    "save a copy as {p:expr}"
    (save-copy-as {p}))
(test-success
    "Save a copy as backup-path."
    (save-copy-as backup-path))

; Email (opens an Outlook DRAFT for review - nothing sends itself).
(english-vla
    "email {who:expr} with subject {s:expr} and message {m:expr} and attachment {p:expr}"
    (vlasendmail {who} {s} {m} {p}))
(test-success
    "Email \"a@b.c\" with subject \"Hi\" and message \"See file.\" and attachment report-path."
    (vlasendmail "a@b.c" "Hi" "See file." report-path))

(english-vla
    "email {who:expr} with subject {s:expr} and message {m:expr}"
    (vlasendmail {who} {s} {m}))
(test-success
    "Email \"boss@co.com\" with subject \"Report\" and message \"Attached.\"."
    (vlasendmail "boss@co.com" "Report" "Attached."))

; Filtering (field is the column's position within the range: 1, 2, 3...).
(english-vla
    "keep only rows of range {r:range} where column {f:expr} is {v:expr}"
    (filter-rows (range {r}) {f} {v}))
(test-success
    "Keep only rows of range \"A1:D50\" where column 3 is \"West\"."
    (filter-rows (range "A1:D50") 3 "West"))

(english-vla
    "show all rows"
    (show-all-rows))
(test-success
    "Show all rows."
    (show-all-rows))

; ---- 69-95: the breadth pass (everyday Excel verbs) -------------------
; Find and replace.
(english-vla
    "replace {a:expr} with {b:expr} in range {r:range}"
    (replace-in {a} {b} (range {r})))
(test-success
    "Replace \"N/A\" with 0 in range A1:D50."
    (replace-in "N/A" 0 (range "a1:d50")))

(english-vla
    "replace {a:expr} with {b:expr} in column {c:column}"
    (replace-in {a} {b} (columns {c})))
(test-success
    "Replace \"N/A\" with 0 in column C."
    (replace-in "N/A" 0 (columns "c")))

; Alignment and layout.
(english-vla
    "center cell|range {r:range}"
    (set-alignment (range {r}) xlcenter))
(test-success
    "Center cell A1."
    (set-alignment (range "a1") xlcenter))
(test-success
    "Center range A1:D1."
    (set-alignment (range "a1:d1") xlcenter))

; G1: four rules folded into one - the matched direction binds into
; the template (xl{d}), and cell/range is one alternation. "center"
; joins the direction list as the day-1 third branch.
(english-vla
    "align cell|range {r:range} {d:left|right|center/ed}"
    (set-alignment (range {r}) xl{d}))
(test-success
    "Align range A2:A9 left."
    (set-alignment (range "a2:a9") xlleft))
(test-success
    "Align range B2:B9 right."
    (set-alignment (range "b2:b9") xlright))
(test-success
    "Align cell A1 left."
    (set-alignment (range "a1") xlleft))
(test-success
    "Align cell D2 center."
    (set-alignment (range "d2") xlcenter))
(test-success
    "Align range C1:C9 centered."
    (set-alignment (range "c1:c9") xlcenter))

; G11r: the matched verb IS the method name.
(english-vla
    "{d:wrap|unwrap} text in range {r:range}"
    ({d}-text (range {r})))
(test-success
    "Wrap text in range A1:A10."
    (wrap-text (range "a1:a10")))
(test-success
    "Unwrap text in range A1:A10."
    (unwrap-text (range "a1:a10")))

; G1: the matched verb IS the method name.
(english-vla
    "{d:merge|unmerge} range {r:range}"
    ({d}-range (range {r})))
(test-success
    "Merge range A1:D1."
    (merge-range (range "a1:d1")))
(test-success
    "Unmerge range A1:D1."
    (unmerge-range (range "a1:d1")))

(english-vla
    "set height of row {n:expr} to {h:expr}"
    (set-row-height {n} {h}))
(test-success
    "Set height of row 2 to 30."
    (set-row-height 2 30))

(english-vla
    "set width of column {c:column} to {w:expr}"
    (set-column-width (columns {c}) {w}))
(test-success
    "Set width of column A to 20."
    (set-column-width (columns "a") 20))

(english-vla
    "set tab-color of sheet {s:sheet} to {e:expr}"
    (set-tab-color {s} (vlacolor {e})))
(test-success
    "Set tab-color of sheet Data to \"#FF69B4\"."
    (set-tab-color "data" (vlacolor "#FF69B4")))

(english-vla
    "unfreeze panes"
    (unfreeze-panes))
(test-success
    "Unfreeze panes."
    (unfreeze-panes))

(english-vla
    "insert column before {c:column}"
    (insert-column {c}))
(test-success
    "Insert column before C."
    (insert-column "c"))

; G11r: the matched verb IS the method name.
(english-vla
    "{d:hide|unhide} row {n:expr}"
    ({d}-row {n}))
(test-success
    "Hide row 5."
    (hide-row 5))
(test-success
    "Unhide row 5."
    (unhide-row 5))

(english-vla
    "clear formatting of range {r:range}"
    (clear-formatting (range {r})))
(test-success
    "Clear formatting of range A1:D50."
    (clear-formatting (range "a1:d50")))

; Moving data around.
(test-success
    "Copy range A1:D10 to range F1:I10."
    (copy-range-to (range "a1:d10") (range "f1:i10")))

(english-vla
    "copy range {r:range} to sheet {s:sheet}"
    (copy-range-to-sheet (range {r}) {s} {r}))
(test-success
    "Copy range A1:D10 to sheet Archive."
    (copy-range-to-sheet (range "a1:d10") "archive" "a1:d10"))

(english-vla
    "convert range {r:range} to values"
    (paste-values (range {r}) (range {r})))
(test-success
    "Convert range B2:B9 to values."
    (paste-values (range "b2:b9") (range "b2:b9")))

; G1: the matched direction completes the method name.
(english-vla
    "fill {d:down|right} range {r:range}"
    (fill-{d} (range {r})))
(test-success
    "Fill down range C2:C90."
    (fill-down (range "c2:c90")))
(test-success
    "Fill right range B2:F2."
    (fill-right (range "b2:f2")))

(english-vla
    "remove duplicates from range {r:range} by column {k:expr}"
    (remove-duplicates (range {r}) {k}))
(test-success
    "Remove duplicates from range A1:C50 by column 2."
    (remove-duplicates (range "a1:c50") 2))

(english-vla
    "remove duplicates from range {r:range}"
    (remove-duplicates (range {r}) 1))
(test-success
    "Remove duplicates from range A1:A50."
    (remove-duplicates (range "a1:a50") 1))

; Lookups and conditional math (errors if not found - by design, so the
; step message names the sentence).
(english-vla
    "set {v:var} to lookup of {e:expr} in range {r:range} column {k:expr}"
    (set! {v} (lookup-of {e} (range {r}) {k})))
(test-success
    "Set price to lookup of part-code in range A2:C90 column 3."
    (set! price (lookup-of part-code (range "a2:c90") 3)))

(english-vla
    "set {v:var} to sum of range {r:range} where range {c:range} matches {e:expr}"
    (set! {v} (sum-of-where (range {r}) (range {c}) {e})))
(test-success
    "Set west-total to sum of range B2:B90 where range A2:A90 matches \"West\"."
    (set! west-total (sum-of-where (range "b2:b90") (range "a2:a90") "West")))

; G-FORMULA slice 2: sum-where's sibling, over Excel's AVERAGEIF. It is
; not sum-where divided by count-matching: COUNTIF counts a matching row
; whose value is blank, and AVERAGEIF skips it - West rows holding 10
; and a blank average 10, where the division says 5. No row matching,
; or only blank values, stops the run at the sentence, as AVERAGEIF has
; no answer.
(english-vla
    "set {v:var} to average of range {r:range} where range {c:range} matches {e:expr}"
    (set! {v} (average-of-where (range {r}) (range {c}) {e})))
(test-success
    "Set west-average to average of range B2:B90 where range A2:A90 matches \"West\"."
    (set! west-average (average-of-where (range "b2:b90") (range "a2:a90") "West")))
(test-success
    "Set big-average to average of range B2:B90 where range B2:B90 matches \">100\"."
    (set! big-average (average-of-where (range "b2:b90") (range "b2:b90") ">100")))

(english-vla
    "set {v:var} to count of range {c:range} matching {e:expr}"
    (set! {v} (count-matching (range {c}) {e})))
(test-success
    "Set west-count to count of range A2:A90 matching \"West\"."
    (set! west-count (count-matching (range "a2:a90") "West")))

; G-FORMULA slice 1: the empty and the filled cells of a range, the
; sentence naming which it counts. Excel's own rule, measured
; 2026-09-29: a formula that shows nothing (="") is counted as empty
; (COUNTBLANK) and as filled (COUNTA) both - COUNTIF with "" and "<>".
(defmacro
    (count-empty-cells r)
    "count a range's cells that show nothing - empty, or a formula that shows nothing"
    (application.worksheetfunction.countif r ""))

(defmacro
    (count-filled-cells r)
    "count a range's cells that hold anything - a formula that shows nothing included"
    (application.worksheetfunction.countif r "<>"))

(english-vla
    "set {v:var} to count of {k:empty|filled} cells in range {r:range}"
    (set! {v} (count-{k}-cells (range {r}))))
(test-success
    "Set blanks to count of empty cells in range A2:A50."
    (set! blanks (count-empty-cells (range "a2:a50"))))
(test-success
    "Set used to count of filled cells in range A2:A50."
    (set! used (count-filled-cells (range "a2:a50"))))

(english-vla
    "set {v:var} to {e:expr} rounded to {n:expr} decimals"
    (set! {v} (round {e} {n})))
(test-success
    "Set price to total rounded to 2 decimals."
    (set! price (round total 2)))
(test-success
    "Set round-check to 3.14159 rounded to 2 decimals."
    (set! round-check (round 3.14159 2)))
(test-success
    "If price is greater than 9.5, log \"expensive\"."
    (if (> price 9.5) (then (debug-print "expensive"))))
(test-success
    "Set budget to 1,000,000."
    (set! budget 1000000))
(test-success
    "Set price to 1,234.56 plus 1."
    (set! price (+ 1234.56 1)))
(test-success
    "If total is greater than 1,000, log \"big\"."
    (if (> total 1000) (then (debug-print "big"))))

; Named ranges (workbook names - usable in formulas afterwards).
(english-vla
    "name range {r:range} as {n:text}"
    (name-range (range {r}) {n}))
(test-success
    "Name range A1:B10 as prices."
    (name-range (range "a1:b10") "prices"))

; G-TABLES rule #1 (BETA_REARVIEW.md F.2's own composite proof): create an
; Excel Table (a ListObject) from a range and name it, in one sentence.
; Surface and target both taken from pareto.txt's own section 9 ("Tables
; (ListObjects) - P1") - "make-table | Turn {r:range} into a table called
; {n:text}. | ListObjects.Add(xlSrcRange, rng, , xlYes)", unmarked there
; (no !/~/^/? - no Tier-2 helper, no new grammar, no bracket, no semantic
; decision needed), matching the plain defmacro built below.
; The nesting - a keyword-argument .Add call nested inside a "." property
; read, nested inside the PLACE of a set! - lives in the macro body below,
; not the phrase-rule template: f1 (VLA_Tests.bas, VlaDotCount) pins this
; file's raw-dot count at ZERO outside macro: lines, on purpose (F.1's own
; "the dots live in prelude.vla / today the phrasebook's own macro:
; definitions" doctrine) - a first, live-caught proof that F.1's discipline
; already keeps every PHRASE-RULE template shallow, whatever raw object-
; model complexity a feature needs living one layer down instead. Unlike
; several existing rules above (sort-range-by, add-border, copy-range-to -
; real names with no defmacro/runtime sub backing them anywhere in this
; repo, the F.1 audit's "41 rules that don't yet reach the object model"),
; this one is fully self-contained and actually compiles - live-verified
; through BOTH backends, not just traced by hand: compile-and-trace built
; the real table first try; interpret-and-trace initially raised "IN.2:
; this interpreter's dynamic dispatch supports up to 4 positional
; arguments; got 6" because the first draft wrote the .Add call as a bare
; dotted-symbol head with keyword args glued on (activesheet.listobjects.
; add :sourcetype ...) - valid to the EMITTER (SymName preserves dots,
; EmitArgs handles the keyword pairs regardless of head shape) but
; invisible to VLA_Interpreter.bas's own keyword-routing (IN2.5,
; WalkMemberNamedCall), which only fires for the EXPLICIT "." form -
; a bare dotted head falls through to positional CallByName dispatch
; instead, where three keyword PAIRS count as six raw positional slots
; against a hand-written 4-argument dynamic-dispatch ceiling. Rewritten
; below as (. activesheet.listobjects add :sourcetype ...) - same final
; VBA text either way (EmitDotText joins obj & "." & member & args
; identically), but now the explicit head the interpreter's own keyword
; path is written to recognize. (VlaDotCount, VLA_Tests.bas, skips
; COMMENT lines too now - this paragraph tripped it once as a plain
; line-scan before that fix landed.)
(defmacro
    (make-table r n)
    "create an Excel Table from a range and name it"
    (set! (. (. activesheet.listobjects
                add
                :sourcetype
                xlsrcrange
                :source
                r
                :xllistobjecthasheaders
                xlyes)
             name)
          n))

(english-vla
    "turn {r:range} into a table called {n:text}"
    (make-table (range {r}) {n}))
(test-success
    "Turn A1:D10 into a table called Sales."
    (make-table (range "a1:d10") "sales"))

; G-TABLES's remaining unmarked pareto.txt section-9 surfaces (table-style,
; table-totals-on/-off, table-to-range) - same shape as make-table above:
; find the table by name on the active sheet ((activesheet.listobjects n),
; the bare-dotted-head-plus-positional-arg call already proven by
; activate-sheet/set-tab-color's own (worksheets s) idiom above), then
; read or write ONE property/method on it. No Tier-2 helper, no new
; grammar slot - the pareto.txt markers already say so.
;
; TABLE-FAMILY: table-style/table-totals-on/table-totals-off are the
; identical shape one level deeper than the paragraph above already
; says - not just "find the table, touch one property," but the exact
; same (set! (. (activesheet.listobjects n) PROPERTY) VALUE) with only
; PROPERTY and VALUE varying (table-style's VALUE is a second
; user-supplied slot, {s:text}; table-totals-on/-off's is a fixed
; true/false, no second slot at all - the three do NOT share one
; arity, so the generator threads it rather than assuming it). One
; generator, table-property-family, produces all three: METAVOCAB
; (VLA_English.bas's DispatchVocabForm/ExpandVocabMacroCall) splices
; its (begin (defmacro ...) (english-vla ...) (test-success ...))
; output back through the loader exactly as if a person had typed the
; defmacro/english-vla/test-success below by hand - same tests, same
; apropos hit, same diff visibility (BETA_REARVIEW.md's TABLE-FAMILY
; entry). `name` (a bare macro-name symbol, e.g. table-style) and
; `property` (a bare member-name symbol, e.g. tablestyle) each
; substitute as a WHOLE value into the nested defmacro's own head
; position and its set!'s member-access position - no gluing anywhere.
; The arity difference rides the macro engine's OWN existing rest-
; parameter splice (already used elsewhere, e.g. with-fast-excel/
; dotimes) - not a new primitive, and never surfaced in any GENERATED
; macro's own signature: table-property-family's trailing `& extras`
; collects zero or more bare param names (here, "s" once, or nothing)
; and splices them, in order, as extra positional params after `n` in
; the generated (defmacro (name n extras) ...) - zero items vanishes
; cleanly, no dangling "&". `value` is separately bound to whatever
; the generator's own call passes for it - the bare symbol `s` (so it
; reads the very extra param `extras` just spliced in) for table-style,
; or the literal true/false for table-totals-on/-off, which never adds
; a param at all. Every generated macro, call template, and test-
; success form below is therefore byte-for-byte what the hand-written
; macros used to emit - scripts/instructions_golden.vla's own
; table-style/table-totals-on/-off lines stay unchanged. table-to-range
; below stays hand-written on purpose: its own shape (a method call,
; `unlist`, no property/value pair at all) does not fit this family.
(defmacro
    (table-property-family name property doc pattern call testsentence testcall value & extras)
    "generate a table-name/value-setting macro, its english-vla rule, and its test-success proof"
    (begin (defmacro
               (name n extras)
               doc
               (set! (. (activesheet.listobjects n) property) value))

           (english-vla
               pattern
               call)
           (test-success
               testsentence
               testcall)))

(table-property-family
    table-style
    tablestyle
    "set an Excel Table's style"
    "set style of table {n:text} to {s:text}"
    (table-style {n} {s})
    "Set style of table Sales to TableStyleMedium2."
    (table-style "sales" "tablestylemedium2")
    s
    s)

(table-property-family
    table-totals-on
    showtotals
    "show an Excel Table's total row"
    "show the total row of table {n:text}"
    (table-totals-on {n})
    "Show the total row of table Sales."
    (table-totals-on "sales")
    true)

(table-property-family
    table-totals-off
    showtotals
    "hide an Excel Table's total row"
    "hide the total row of table {n:text}"
    (table-totals-off {n})
    "Hide the total row of table Sales."
    (table-totals-off "sales")
    false)

(defmacro
    (table-to-range n)
    "convert an Excel Table back into a plain range"
    (. (activesheet.listobjects n) unlist))

(english-vla
    "turn table {n:text} back into a range"
    (table-to-range {n}))
(test-success
    "Turn table Sales back into a range."
    (table-to-range "sales"))

; G-TABLES's last three pareto.txt section-9 surfaces (table-add-row,
; table-delete-row, table-column) - one level deeper than the family
; above: a chained indexed access (.ListObjects(n).ListRows/.ListColumns)
; rather than an indexed access followed by a bare property, but still
; ONE nested (. ...) form each, the same shape last-filled-row already
; proved safe three levels deep. One-off defmacros, not
; table-property-family (none of the three share its one-property/
; one-value arity) - table-to-range above is the closer precedent.
(defmacro
    (table-add-row n)
    "append a blank row to an Excel Table"
    (. (. (activesheet.listobjects n) listrows) add))

(english-vla
    "add a row to table {n:text}"
    (table-add-row {n}))
(test-success
    "Add a row to table Sales."
    (table-add-row "sales"))

; {r:expr} is table-relative - ListRows' own index, not a worksheet row
; number. "row {r:expr} OF TABLE {n:text}" continues past the number,
; so TryPhrase's end-of-sentence gate keeps this distinct from the
; existing worksheet-row "delete row {n:expr}" above (different total
; shape, same disambiguation pivot-create/pivot-full already proved for
; a shared opening clause).
(defmacro
    (table-delete-row n r)
    "delete a row from an Excel Table by its own row number"
    (. (. (activesheet.listobjects n) listrows r) delete))

(english-vla
    "delete row {r:expr} of table {n:text}"
    (table-delete-row {n} {r}))
(test-success
    "Delete row 1 of table Sales."
    (table-delete-row "sales" 1))

(defmacro
    (table-column v n c)
    "bind a variable to one column's data (header excluded) in an Excel Table"
    (obj-set! v (. (. (activesheet.listobjects n) listcolumns c) databodyrange)))

(english-vla
    "set {v:var} to column {c:text} of table {n:text}"
    (table-column {v} {n} {c}))
(test-success
    "Set qty-values to column Qty of table Sales."
    (table-column qty-values "sales" "qty"))

; TABLESPEC (BETA_REARVIEW.md's own entry, THE METAMETAMACRO LINE):
; table-property-family above generates one triple per HAND-TYPED call
; (three calls, three triples); tablespec generalizes that to an
; arbitrary-length WALKED list - one call, N triples, read off a quoted
; spec instead of typed by a person. Not table-property-family's own
; property/value split specifically (that stays table-property-family's
; job) - tablespec-row's BODY field is a whole caller-supplied form, so
; any single-parameter macro shape fits, table properties included, if
; a real table ever needs exactly that shape.
;
; Mechanism, composed entirely from already-shipped pieces - nothing
; new needed once the design was actually traced (an earlier sketch of
; this entry, before code was written, called for quasiquote/unquote;
; that turned out to be unnecessary complexity, dropped once tracing
; showed the plain shape below already suffices, same discipline
; LISTOPS-STDLIB's own append rewrite already used): tablespec-row is
; an ordinary defmacro - table-property-family's own arity (one
; parameter, always literally "n") minus the property/value split,
; generalized to one whole BODY field - so name/doc/body/pattern/call/
; testsentence/testcall substitute via the exact same bound-parameter
; mechanism table-property-family's own name/property already use,
; nothing new there. tablespec itself is walk-rows' own shape
; (BETA_REARVIEW.md's TABLESPEC entry, LISTOPS.0's acceptance test) -
; quote-if/null?/car/cdr driving a self-terminating recursive walk -
; generalized from "one debug-print per row" to "one tablespec-row CALL
; per row," each field pulled off the current row via car/cadr/caddr
; (LISTOPS-STDLIB, already shipped) exactly the way walk-rows already
; pulls (car lst) as an ORDINARY ARGUMENT to another call - never a
; computed value dropped into a head position, which would have been
; new, untested ground. Base case bare (begin), same reason walk-rows'
; own is: quote-if's branches are CODE, not data.
;
; DEPTH CEILING - tested, not assumed (BETA_REARVIEW.md's TABLESPEC
; entry has the full trace of why begin-splice does NOT dodge this,
; contrary to this line's own LISTOPS-BUDGET entry's optimistic
; framing): tablespec is a self-recursive macro, so it inherits
; ExpandMacros's own depth>200 guard exactly like walk-rows already
; does - unchanged, no engine work here. TestTablespecDepthSafety
; (VLA_Tests.bas) proves a 20-row chain (walk-rows' own already-proven
; number, not a larger guessed one - this shape does more work per row
; than walk-rows, so claiming more without evidence would be the same
; mistake LISTOPS-BUDGET's own first-round "raise to 1000" guess
; already made once) expands completely, and a 250-row chain
; (TestListopsBudget's own number) fails LOUDLY, never a silent partial
; result. A table bigger than the tested-safe range is the author's to
; split across multiple (tablespec ...) calls, each within it - see
; TABLESPEC-SCALE, filed directly after TABLESPEC in BETA_REARVIEW.md,
; for why no cleverer internal fix exists today. No real caller in this
; corpus yet (no table in scripts/english.vla is currently big enough
; to need it) - shipped as infrastructure, proven by TestTablespec's
; own synthetic vocab, same as LISTOPS-STDLIB's functions were.
(defmacro
    (tablespec-row name doc body pattern call testsentence testcall)
    "emit one single-parameter defmacro/english-vla/test-success triple - table-property-family's own arity, BODY supplied whole instead of derived from a property/value pair"
    (begin (defmacro
               (name n)
               doc
               body)

           (english-vla
               pattern
               call)
           (test-success
               testsentence
               testcall)))

(defmacro
    (tablespec spec)
    "walk a quoted list of (name doc body pattern call testsentence testcall) rows, calling tablespec-row once per row - see this file's own TABLESPEC comment above for the depth ceiling this inherits"
    (quote-if (null? spec)
              (begin)
              (begin (tablespec-row
                         (car (car spec))
                         (cadr (car spec))
                         (car (cddr (car spec)))
                         (cadr (cddr (car spec)))
                         (caddr (cddr (car spec)))
                         (cadr (cddr (cddr (car spec))))
                         (caddr (cddr (cddr (car spec)))))
                     (tablespec (cdr spec)))))

; G-PIVOT rule #1 (pareto.txt section 10): every surface there is marked
; ! (a Tier-2 helper, VLA_Runtime.VlaPivotCreate - PivotCaches.Create is
; two statements, not one nested expression, so it can't reduce to a
; defmacro's own "." nesting the way make-table's single .Add call did),
; but pareto.txt's own {r:range}/{b:cell}/{n:text} slots for THIS surface
; are all ones the grammar already has - the ~ marker on it turns out to
; describe the SECTION's other verbs (pivot-rows/-columns/-filters, which
; do need G6's {f:field-list}), not this one, the same "the marker was a
; section-wide recommendation, not a per-surface requirement" gap the
; BETA_REARVIEW.md G-FILES correction already found once. pareto.txt's
; own surface text starts with "Create", but that word is the built-in
; Create-declaration keyword's own reserved first word (VLA_English.bas's
; Case "create" always intercepts a sentence starting with it, before
; any phrase rule is even tried, the same collision "Create a button
; called ..." hit earlier) - "Make a pivot table ..." below, one level
; indirect from pareto's own wording, same escape hatch as make-button.
(defmacro
    (pivot-create r b n)
    "create a pivot table from a range at a destination cell"
    (vlapivotcreate r b n))

(english-vla
    "make a pivot table from {r:range} at {b:cell} called {n:text}"
    (pivot-create (range {r}) (range {b}) {n}))
(test-success
    "Make a pivot table from A1:C10 at F1 called SalesPivot."
    (pivot-create (range "a1:c10") (range "f1") "salespivot"))

; G-PIVOT rules #2-4 (pareto.txt section 10): the three field-
; orientation verbs, unblocked now that G6 (BETA_REARVIEW.md) ships
; {name:text-list}. One shared Tier-2 helper, VlaPivotSetOrientation
; (VLA_Runtime.bas) - ".PivotFields(name).Orientation = xl...Field" is
; the same per-field-name loop regardless of which orientation, so each
; DEFMACRO below just bakes in its own "row"/"column"/"filter" literal
; rather than three separate helpers or any new XL-constant plumbing at
; this layer - see that helper's own header note for the full reasoning
; (including why it does NOT reach the interpreter's own constant
; table at all: only the plain kind string crosses the VLA boundary).
(defmacro
    (pivot-rows n f)
    "add field names to a pivot table's row area"
    (vlapivotsetorientation n f "row"))

(defmacro
    (pivot-columns n f)
    "add field names to a pivot table's column area"
    (vlapivotsetorientation n f "column"))

(defmacro
    (pivot-filters n f)
    "add field names to a pivot table's filter (page) area"
    (vlapivotsetorientation n f "filter"))

; G-PIVOT (close-out pass): retrofitted onto the {d:...} functor
; convention on the owner's own instruction - three hand-typed
; english-vla sentences collapsed into one, matching G11r's own
; glued-identifier dispatch (format-as-{d}, {d}-column) rather than
; three separate sentences that only differed by one word. No runtime
; behavior change: pivot-rows/-columns/-filters above are untouched,
; still three separate macros each baking in its own "kind" literal -
; only the ENGLISH sentence that reaches them is now one rule instead
; of three, since the functor and hand-typed-triplet shapes compile to
; the exact same thing (there is no technical difference between them,
; only which one is less text in this file - confirmed with the owner
; before retrofitting already-shipped, already-verified rules).
(english-vla
    "add {d:rows|columns|filters} of {f:text-list} to pivot {n:text}"
    (pivot-{d} {n} {f}))
(test-success
    "Add rows of Region, Product to pivot SalesPivot."
    (pivot-rows "salespivot" (array "region" "product")))
(test-success
    "Add columns of Segment to pivot SalesPivot."
    (pivot-columns "salespivot" (array "segment")))
(test-success
    "Add filters of Region, Segment to pivot SalesPivot."
    (pivot-filters "salespivot" (array "region" "segment")))

; G-PIVOT close-out, round 2 (pivot-remove-field): the missing inverse
; of the three verbs above, found during the CRUD-completeness survey
; and scoped with the owner - there was previously no way to take a
; field back OUT of a pivot's layout at all. Reuses
; VlaPivotSetOrientation unchanged, just a 4th "hidden" kind (that
; helper's own header note has the full reasoning) - not part of the
; {d:rows|columns|filters} functor above, since removing a field names
; no orientation at all.
(defmacro
    (pivot-remove-field n f)
    "remove field names from a pivot table's layout entirely"
    (vlapivotsetorientation n f "hidden"))

(english-vla
    "remove {f:text-list} from pivot {n:text}"
    (pivot-remove-field {n} {f}))
(test-success
    "Remove Product from pivot SalesPivot."
    (pivot-remove-field "salespivot" (array "product")))

; G-PIVOT rule #5, the composite sentence pareto.txt itself names as
; the whole reason for G6 ("the composite sentence is unwritable
; without them, and building it twice is a waste") - built from the
; four parts above via an ordinary begin-splice (TABLE-FAMILY's own
; "three hand-typed calls" precedent, LISTOPS-BUDGET's own finding that
; many independent, never-chained calls carry no depth risk at any
; count), not a new monolithic helper. Its own "values of" clause
; stays sum-only (passing "sum" explicitly to VlaPivotAddValues, which
; gained a real per-call aggregation choice below) - a deliberate v1
; cap on the COMPOSITE sentence specifically, not a limit of the
; underlying mechanism; pivot-values-sum/-count/-avg below expose that
; choice as their own standalone verbs instead.
(defmacro
    (pivot-full r destcell n rowfields colfields valfields)
    "the composite pivot sentence - create, then set rows/columns, then add value fields (sum-only)"
    (begin (pivot-create r destcell n)
           (pivot-rows n rowfields)
           (pivot-columns n colfields)
           (vlapivotaddvalues n valfields "sum")))

(english-vla
    "make a pivot table from {r:range} at {b:cell} called {n:text} with rows of {a:text-list} and columns of {c:text-list} and values of {v:text-list}"
    (pivot-full (range {r}) (range {b}) {n} {a} {c} {v}))
(test-success
    "Make a pivot table from A1:C10 at F1 called SalesPivot with rows of Region, Product and columns of Segment and values of Revenue."
    (pivot-full (range "a1:c10")
                (range "f1")
                "salespivot"
                (array "region" "product")
                (array "segment")
                (array "revenue")))

; G-PIVOT rules #6-8: the per-field aggregation CHOICE pareto.txt's own
; pivot-values-sum/-count/-avg name, unblocked the same way rows/
; columns/filters were - one shared helper, VlaPivotAddValues
; (VLA_Runtime.bas), each DEFMACRO baking in its own function literal.
; Generalized past pareto.txt's own pre-G6 single-field draft to
; {f:text-list} (owner's own call: "collapsing the semantics of two
; sentences into one is always a win for brevity") - "Add Revenue,
; Cost to pivot X as a sum." adds both in one sentence. Three separate
; rules, not one {d:sum|count|average} functor (G11r's own precedent
; would allow it) - owner's own call, matching the shape pivot-rows/
; -columns/-filters already shipped in. No guard against re-adding an
; already-present field/function pair - AddDataField's own native
; behavior (a second, distinctly-captioned data field) is left exactly
; as Excel gives it, owner's own explicit call: users are allowed to
; abuse Excel to their own detriment, same as everywhere else in this
; project.
(defmacro
    (pivot-values-sum n f)
    "add field names to a pivot table's values area, summed"
    (vlapivotaddvalues n f "sum"))

(defmacro
    (pivot-values-count n f)
    "add field names to a pivot table's values area, counted"
    (vlapivotaddvalues n f "count"))

; Renamed from pivot-values-avg (G-PIVOT close-out pass) so the
; matched word ("average") glues cleanly into the macro name below -
; the one real, necessary consequence of the functor retrofit named
; rather than glossed over, since "average" (the English word every
; sentence and pivot-full's own call site already use) and "avg" (the
; old macro name) don't glue to the same identifier. Body unchanged.
(defmacro
    (pivot-values-average n f)
    "add field names to a pivot table's values area, averaged"
    (vlapivotaddvalues n f "average"))

; G-PIVOT (close-out pass): retrofitted onto the {d:...} functor
; convention, same reasoning as pivot-rows/-columns/-filters above.
; FIRST VERSION wrote "as a|an {d:sum|count|average}", explicitly
; spelling out the article as a bare alternation - crashed live
; (run-time error 5, "I expected one of 'a'/'an' but found 'sum'"),
; not caught by tracing. IsNoiseWord strips a bare literal "a"/"an"
; out of a PATTERN entirely at registration (that's how the original
; three rules' own "as a sum"/"as an average" literals always worked -
; the compiled pattern never actually contained "a"), but "a|an" is an
; ALTERNATION token (it has a pipe), so it survives registration as
; real content requiring a real match - and SkipArticles unconditionally
; skips the input's "a"/"an" before ANY literal or alternation is even
; attempted (VLA_English.bas's TryPhrase), consuming the very token the
; alternation needed to see. No article at all belongs in this pattern -
; SkipArticles already skips whatever the user typed (or didn't type)
; immediately before the {d:...} slot below, exactly as it always did
; for the three original literals; "Add Revenue to pivot SalesPivot as
; sum." (no article) matches too, same as it always could have.
(english-vla
    "add {f:text-list} to pivot {n:text} as {d:sum|count|average}"
    (pivot-values-{d} {n} {f}))
(test-success
    "Add Revenue to pivot SalesPivot as a sum."
    (pivot-values-sum "salespivot" (array "revenue")))
(test-success
    "Add Revenue, Units to pivot SalesPivot as a count."
    (pivot-values-count "salespivot" (array "revenue" "units")))
(test-success
    "Add Units to pivot SalesPivot as an average."
    (pivot-values-average "salespivot" (array "units")))

; G-PIVOT rules #9-13: refresh/delete/collapse/expand - VlaPivotRefresh/
; VlaPivotRefreshAll/VlaPivotDelete/VlaPivotSetShowDetail (VLA_Runtime.
; bas). pivot-refresh-all is a genuinely narrower alternative to the
; already-shipped "Refresh everything." (refresh-everything, below) -
; pivots only, never an external data connection - built on that real
; technical distinction, not because pareto.txt happened to name it.
(defmacro
    (pivot-refresh n)
    "refresh one pivot table from its source data"
    (vlapivotrefresh n))

(english-vla
    "refresh pivot {n:text}"
    (pivot-refresh {n}))
(test-success
    "Refresh pivot SalesPivot."
    (pivot-refresh "salespivot"))

(defmacro
    (pivot-refresh-all)
    "refresh every pivot table in the workbook, and nothing else"
    (vlapivotrefreshall))

(english-vla
    "refresh every pivot table"
    (pivot-refresh-all))
(test-success
    "Refresh every pivot table."
    (pivot-refresh-all))

(defmacro
    (pivot-delete n)
    "delete a pivot table by name"
    (vlapivotdelete n))

(english-vla
    "delete pivot {n:text}"
    (pivot-delete {n}))
(test-success
    "Delete pivot SalesPivot."
    (pivot-delete "salespivot"))

; pivot-collapse/pivot-expand: an antonym pair (this grammar's own
; standing convention - hide/unhide, wrap/unwrap, merge/unmerge - any
; action that can be undone should be undoable), not built via
; bool-antonym-family (that generator assumes one directly-addressable
; target/property with no loop and no workbook-wide search; this needs
; both), and named "expand," not the generator's own "un-" prefixing
; convention, since "expand" is the real word for this and
; "uncollapse" is not.
(defmacro
    (pivot-collapse n f)
    "collapse field names in a pivot table, hiding their detail"
    (vlapivotsetshowdetail n f false))

(english-vla
    "collapse {f:text-list} in pivot {n:text}"
    (pivot-collapse {n} {f}))
(test-success
    "Collapse Region, Product in pivot SalesPivot."
    (pivot-collapse "salespivot" (array "region" "product")))

(defmacro
    (pivot-expand n f)
    "expand field names in a pivot table, showing their detail"
    (vlapivotsetshowdetail n f true))

(english-vla
    "expand {f:text-list} in pivot {n:text}"
    (pivot-expand {n} {f}))
(test-success
    "Expand Region in pivot SalesPivot."
    (pivot-expand "salespivot" (array "region")))

; G-PIVOT close-out (pivot-layout): scoped with the owner before
; writing any code. RowAxisLayout is a real, table-WIDE method - unlike
; every other pivot verb here, it names no field at all, matching
; pareto.txt's own "Show pivot {n:text} in tabular form." (only the
; table is named). All three real XlLayoutRowType values are exposed
; (compact/tabular/outline), not just pareto's own "tabular" - the
; owner's own call, since it's the enum's full range at no extra cost.
; Three hand-typed macros, one {d:...} functor dispatch - the same
; authoring convention just retrofitted onto pivot-rows/-columns/
; -filters above, applied fresh here rather than three separate
; sentences differing by one word each.
(defmacro
    (pivot-layout-compact n)
    "show a pivot table's row fields in Excel's default compact form"
    (vlapivotsetrowlayout n "compact"))

(defmacro
    (pivot-layout-tabular n)
    "show a pivot table's row fields in tabular form (one column per field)"
    (vlapivotsetrowlayout n "tabular"))

(defmacro
    (pivot-layout-outline n)
    "show a pivot table's row fields in outline form"
    (vlapivotsetrowlayout n "outline"))

(english-vla
    "show pivot {n:text} in {d:compact|tabular|outline} form"
    (pivot-layout-{d} {n}))
(test-success
    "Show pivot SalesPivot in tabular form."
    (pivot-layout-tabular "salespivot"))
(test-success
    "Show pivot SalesPivot in compact form."
    (pivot-layout-compact "salespivot"))
(test-success
    "Show pivot FullPivot in outline form."
    (pivot-layout-outline "fullpivot"))

; G-PIVOT close-out (pivot-subtotals-hide/-show): a genuine second
; report-layout knob beyond row-axis form, found while surveying the
; real pivot object model and adjudicated with the owner before
; building - PivotField.Subtotals, a per-field property distinct from
; both Orientation and ShowDetail. Antonym pair, not bool-antonym-
; family (same reason pivot-collapse/-expand isn't: this needs a
; workbook-wide table search plus a loop, not one directly-addressable
; target/property), dispatched via the {d:...} functor like every
; other pivot verb this pass touches.
(defmacro
    (pivot-subtotals-hide n f)
    "hide subtotals for field names in a pivot table"
    (vlapivotsetsubtotals n f false))

(defmacro
    (pivot-subtotals-show n f)
    "show (restore automatic) subtotals for field names in a pivot table"
    (vlapivotsetsubtotals n f true))

(english-vla
    "{d:hide|show} subtotals for {f:text-list} in pivot {n:text}"
    (pivot-subtotals-{d} {n} {f}))
(test-success
    "Hide subtotals for Region in pivot SalesPivot."
    (pivot-subtotals-hide "salespivot" (array "region")))
(test-success
    "Show subtotals for Region in pivot SalesPivot."
    (pivot-subtotals-show "salespivot" (array "region")))

; G-PIVOT close-out (pivot-blank-line-add/-remove): a third report-
; layout knob, same discovery and adjudication as pivot-subtotals -
; PivotField.LayoutBlankLine, independent of Orientation, ShowDetail,
; AND Subtotals. Antonym pair, same not-bool-antonym-family reasoning,
; same {d:...} functor dispatch. "a" (not "the") on both sides keeps
; one shared template legal on both branches ("Remove a blank row..."
; reads fine, same deliberate looseness as pivot-values' "a|an").
(defmacro
    (pivot-blank-line-add n f)
    "insert a blank row after each item of field names in a pivot table"
    (vlapivotsetblankline n f true))

(defmacro
    (pivot-blank-line-remove n f)
    "remove the blank row after each item of field names in a pivot table"
    (vlapivotsetblankline n f false))

(english-vla
    "{d:add|remove} a blank row after {f:text-list} in pivot {n:text}"
    (pivot-blank-line-{d} {n} {f}))
(test-success
    "Add a blank row after Region in pivot SalesPivot."
    (pivot-blank-line-add "salespivot" (array "region")))
(test-success
    "Remove a blank row after Region in pivot SalesPivot."
    (pivot-blank-line-remove "salespivot" (array "region")))

; G-PIVOT round 2: pivot-rename/pivot-clear/pivot-source/pivot-sort -
; the CRUD-completeness survey's own findings, scoped and adjudicated
; with the owner (docs/BETA_REARVIEW.md's G-PIVOT entry has the full
; conversation) before writing any of this.
(defmacro
    (pivot-rename n m)
    "rename a pivot table"
    (vlapivotrename n m))

(english-vla
    "rename pivot {n:text} to {m:text}"
    (pivot-rename {n} {m}))
(test-success
    "Rename pivot SalesPivot to RegionalPivot."
    (pivot-rename "salespivot" "regionalpivot"))

(defmacro
    (pivot-clear n)
    "clear a pivot table back to a blank layout, keeping the table itself"
    (vlapivotclear n))

(english-vla
    "clear pivot {n:text}"
    (pivot-clear {n}))
(test-success
    "Clear pivot SalesPivot."
    (pivot-clear "salespivot"))

; "Change the source of pivot ... to ..." rather than pareto.txt-style
; terse phrasing, since this needed a real, human sentence for a real
; two-part action (repoint the cache, then refresh) that VlaPivotChange
; Source's own header note explains doesn't reduce to a single simple
; property set the way it might first look.
(defmacro
    (pivot-source n r)
    "point a pivot table at a new source range and refresh it"
    (vlapivotchangesource n r))

(english-vla
    "change the source of pivot {n:text} to {r:range}"
    (pivot-source {n} (range {r})))
(test-success
    "Change the source of pivot SalesPivot to A1:C20."
    (pivot-source "salespivot" (range "a1:c20")))

; pivot-sort/pivot-sort-by-value: TWO english-vla rules, not one with
; an omissible "by" clause - VlaPivotSort's own header note has the
; full reasoning (G1's [optional] syntax only covers a single bare
; word, never a multi-word literal phrase carrying its own slot).
; FIRST VERSION passed {d} straight through as a bare argument
; ((pivot-sort {n} {f} {d})) - crashed live (run-time error 5, "got
; (pivot-sort ... descending)" - a bare, unquoted symbol, not the
; string "descending" VlaPivotSort actually needs). Wrong: every OTHER
; {d:...} use in this file glues the matched word into an IDENTIFIER
; (pivot-layout-{d}, pivot-{d}, xl{d}) - none of them pass {d} through
; as a plain value argument, and this is the first place that was ever
; tried. Fixed onto the same proven shape pivot-layout-{d} already
; uses: one small macro per branch, each baking in its own QUOTED
; literal, dispatched by gluing {d} into the macro name instead of
; passing it as a value.
(defmacro
    (pivot-sort-ascending n f)
    "sort a pivot field's items alphabetically ascending, by its own labels"
    (vlapivotsort n f "ascending" ""))

(defmacro
    (pivot-sort-descending n f)
    "sort a pivot field's items alphabetically descending, by its own labels"
    (vlapivotsort n f "descending" ""))

(english-vla
    "sort {f:text} in pivot {n:text} {d:ascending|descending}"
    (pivot-sort-{d} {n} {f}))
(test-success
    "Sort Region in pivot SalesPivot descending."
    (pivot-sort-descending "salespivot" "region"))
(test-success
    "Sort Region in pivot SalesPivot ascending."
    (pivot-sort-ascending "salespivot" "region"))

(defmacro
    (pivot-sort-by-value-ascending n f v)
    "sort a pivot field's items ascending by a value field's numbers"
    (vlapivotsort n f "ascending" v))

(defmacro
    (pivot-sort-by-value-descending n f v)
    "sort a pivot field's items descending by a value field's numbers"
    (vlapivotsort n f "descending" v))

(english-vla
    "sort {f:text} in pivot {n:text} {d:ascending|descending} by {v:text}"
    (pivot-sort-by-value-{d} {n} {f} {v}))
(test-success
    "Sort Product in pivot FullPivot descending by Revenue."
    (pivot-sort-by-value-descending "fullpivot" "product" "revenue"))
(test-success
    "Sort Product in pivot FullPivot ascending by Revenue."
    (pivot-sort-by-value-ascending "fullpivot" "product" "revenue"))

; One-sentence cross-sheet reaches (context switching still works too).
(english-vla
    "put {e:expr} into|in cell {r:cell} of sheet {s:sheet}"
    (put-into-cell-of-sheet {e} {r} {s}))
(test-success
    "Put 5 into cell B2 of sheet Data."
    (put-into-cell-of-sheet 5 "b2" "data"))
(test-success
    "Put 5 in cell B2 of sheet Data."
    (put-into-cell-of-sheet 5 "b2" "data"))

(english-vla
    "set {v:var} to cell {r:cell} of sheet {s:sheet}"
    (set! {v} (cell-of-sheet {r} {s})))
(test-success
    "Set remote to cell B2 of sheet Data."
    (set! remote (cell-of-sheet "b2" "data")))

; Columns chosen by the program (numeric column index).
(english-vla
    "put {e:expr} into|in column number {c:expr} row {n:expr}"
    (set! (cells {n} {c}) {e}))
(test-success
    "Put 5 into column number 3 row 2."
    (set! (cells 2 3) 5))
(test-success
    "Put 5 in column number 3 row 2."
    (set! (cells 2 3) 5))

; F.4 (shape 1): "set {v:var} to cell in column number {c:expr} row
; {n:expr}" removed here - dead code, shadowed by "set {v:var} to
; {e:expr}" the same way its sibling above was: ParsePrimCore's "cell
; in column number X row Y" branch already parses to byte-identical
; (set! {v} (cells {n} {c})) output.

; Workbook-level verbs. Close saves by default; Print opens the
; preview (nothing irreversible happens without the user watching).
(english-vla
    "open workbook {p:expr}"
    (open-workbook {p}))
(test-success
    "Open workbook report-path."
    (open-workbook report-path))

(english-vla
    "save this workbook as {p:expr}"
    (save-workbook-as {p}))
(test-success
    "Save this workbook as backup-path."
    (save-workbook-as backup-path))

(english-vla
    "close this workbook"
    (close-workbook))
(test-success
    "Close this workbook."
    (close-workbook))

; G1: the matched verb IS the method name, on both the this-sheet
; and the named-sheet spellings.
(english-vla
    "{d:protect|unprotect} this sheet with password {p:expr}"
    ({d}-sheet activesheet {p}))
(test-success
    "Protect this sheet with password \"abc\"."
    (protect-sheet activesheet "abc"))
(test-success
    "Unprotect this sheet with password \"abc\"."
    (unprotect-sheet activesheet "abc"))

(english-vla
    "{d:protect|unprotect} sheet {s:sheet} with password {p:expr}"
    ({d}-sheet (worksheets {s}) {p}))
(test-success
    "Protect sheet Data with password \"abc\"."
    (protect-sheet (worksheets "data") "abc"))
(test-success
    "Unprotect sheet Data with password \"abc\"."
    (unprotect-sheet (worksheets "data") "abc"))

(english-vla
    "refresh everything"
    (refresh-everything))
(test-success
    "Refresh everything."
    (refresh-everything))

(english-vla
    "print this sheet"
    (print-this-sheet))
(test-success
    "Print this sheet."
    (print-this-sheet))

(english-vla
    "wait {n:expr} seconds"
    (wait-seconds {n}))
(test-success
    "Wait 2 seconds."
    (wait-seconds 2))

(english-vla
    "put {e:expr} in status bar"
    (set-status-bar {e}))
(test-success
    "Put \"working\" in status bar."
    (set-status-bar "working"))

(english-vla
    "clear status bar"
    (set-status-bar false))
(test-success
    "Clear status bar."
    (set-status-bar false))

; ---- G-STRUCT: rows, columns, copy/paste, clearing -------------------
; BETA_REARVIEW.md's G-STRUCT entry, scoped against pareto.txt sections
; 2-5 and signed off with the owner before writing any rule below (four
; real forks; the owner's own choice is named at each). What pareto.txt
; itself already covers under a DIFFERENT surface this file already
; shipped (insert-row, delete-row, insert/delete-column, hide/unhide
; row and column, row-height, fit-column, freeze-top-row,
; unfreeze-panes, copy-range, paste-values, fill-down/right,
; remove-duplicates, clear-formatting) is deliberately NOT re-added
; here under pareto's own wording - two spellings for one operation is
; exactly the promise-proliferation G-ROWLOOP's own entry already
; reverted once (BETA_REARVIEW.md).
;
; copy-style (pareto section 5) and copy-formats (section 3) turned out
; to be the identical .PasteSpecial xlPasteFormats call under two
; natural-English surfaces, not two features - one shared defmacro
; backs both below, no Tier-2 helper despite copy-style's own "!"
; marker (the same "the marker described something else" correction
; G-PIVOT's own pivot-create entry made once already). header-style
; likewise needs no Tier-2 helper - three flat property sets, not a
; stateful/looping COM sequence.

; ---- rows and columns ---------------------------------------------
(defmacro
    (unhide-all-rows-columns)
    "show every hidden row and column on the active sheet"
    (begin (set! (. cells entirerow.hidden) false) (set! (. cells entirecolumn.hidden) false)))

(english-vla
    "unhide all rows and columns"
    (unhide-all-rows-columns))
(test-success
    "Unhide all rows and columns."
    (unhide-all-rows-columns))

(defmacro
    (fit-row n)
    "autofit a row's height to its contents"
    (. (rows n) autofit))

(english-vla
    "fit row {r:expr}"
    (fit-row {r}))
(test-success
    "Fit row 3."
    (fit-row 3))

(defmacro
    (group-rows a b)
    "group a span of rows so they can be collapsed"
    (. (rows (& a ":" b)) group))

(defmacro
    (ungroup-rows a b)
    "ungroup a previously-grouped span of rows"
    (. (rows (& a ":" b)) ungroup))

(english-vla
    "group rows {a:expr} through {b:expr}"
    (group-rows {a} {b}))
(test-success
    "Group rows 2 through 10."
    (group-rows 2 10))

(english-vla
    "ungroup rows {a:expr} through {b:expr}"
    (ungroup-rows {a} {b}))
(test-success
    "Ungroup rows 2 through 10."
    (ungroup-rows 2 10))

; IN.2 (owner-caught live, interpret-and-trace): DynamicNamedCall
; (VLA_Interpreter.bas) had no "resize" Case at all - a genuine gap in
; the interpreter's own named-argument dispatch, not a grammar issue,
; fixed there rather than routed around here. :rowsize now works on
; both backends.
(defmacro
    (insert-n-rows-at r n)
    "insert several blank rows starting at a row number, pushing existing rows down"
    (. (. (rows r) resize :rowsize n) insert))

(english-vla
    "insert {n:expr} rows at row {r:expr}"
    (insert-n-rows-at {r} {n}))
(test-success
    "Insert 3 rows at row 5."
    (insert-n-rows-at 5 3))

(defmacro
    (delete-rows-range a b)
    "delete a contiguous span of whole rows"
    (. (rows (& a ":" b)) delete))

(english-vla
    "delete rows {a:expr} through {b:expr}"
    (delete-rows-range {a} {b}))
(test-success
    "Delete rows 4 through 9."
    (delete-rows-range 4 9))

; move-column (!, pareto section 2): VLA_Runtime.VlaMoveColumn - Cut
; then Insert(Shift:=xlToRight) is a single atomic Excel operation that
; keeps formats and needs no manual index-shift math (that Sub's own
; header note has the full reasoning); self-move is a silent no-op.
(defmacro
    (move-column c d)
    "move a whole column to sit before another column, keeping its formatting"
    (vlamovecolumn c d))

(english-vla
    "move column {c:column} before column {d:column}"
    (move-column {c} {d}))
(test-success
    "Move column B before column D."
    (move-column "b" "d"))

; freeze-panes (?, pareto section 2 - resolved with the owner: follow
; the active window, same shape as the already-shipped freeze-top-row,
; but guard the degenerate n<=0 case (VLA_Runtime.VlaFreezePanes)
; rather than silently doing nothing.
(defmacro
    (freeze-first-rows n)
    "freeze the given number of rows at the top of the active window"
    (vlafreezepanes n))

(english-vla
    "freeze the first {n:expr} rows"
    (freeze-first-rows {n}))
(test-success
    "Freeze the first 3 rows."
    (freeze-first-rows 3))

; ---- copy, paste, and moving data around -----------------------------
(defmacro
    (copy-formats-only a b)
    "copy only the formatting from one range onto another, leaving values and formulas untouched"
    (begin (. a copy) (. b pastespecial :paste xlpasteformats) (set! application.cutcopymode false)))

(english-vla
    "copy formatting of {a:range} to {b:range}"
    (copy-formats-only (range {a}) (range {b})))
(test-success
    "Copy formatting of A1:A5 to C1:C5."
    (copy-formats-only (range "a1:a5") (range "c1:c5")))

; NOTE the swapped argument order versus the rule above: copy-formats-
; only(a, b) copies FROM a TO b, but "Make {a} look like {b}" means a
; is the one being CHANGED and b is the model - a is the destination
; here, not the source, so b must be passed first.
(english-vla
    "make {a:range} look like {b:range}"
    (copy-formats-only (range {b}) (range {a})))
(test-success
    "Make C1:C5 look like A1:A5."
    (copy-formats-only (range "a1:a5") (range "c1:c5")))

(defmacro
    (copy-formulas-only a b)
    "copy only the formulas from one range into another"
    (begin (. a copy)
           (. b pastespecial :paste xlpasteformulas)
           (set! application.cutcopymode false)))

(english-vla
    "copy formulas of {a:range} to {b:cell}"
    (copy-formulas-only (range {a}) (range {b})))
(test-success
    "Copy formulas of B2:B9 to D2."
    (copy-formulas-only (range "b2:b9") (range "d2")))

(defmacro
    (copy-widths-only a b)
    "copy only the column widths from one range onto another"
    (begin (. a copy)
           (. b pastespecial :paste xlpastecolumnwidths)
           (set! application.cutcopymode false)))

(english-vla
    "copy column widths of {a:range} to {b:range}"
    (copy-widths-only (range {a}) (range {b})))
(test-success
    "Copy column widths of A1:C1 to E1:G1."
    (copy-widths-only (range "a1:c1") (range "e1:g1")))

(defmacro
    (paste-transposed a b)
    "copy a range into another location with rows and columns swapped"
    (begin (. a copy) (. b pastespecial :transpose true) (set! application.cutcopymode false)))

(english-vla
    "copy range {a:range} to {b:cell} transposed"
    (paste-transposed (range {a}) (range {b})))
(test-success
    "Copy range A1:A5 to C1 transposed."
    (paste-transposed (range "a1:a5") (range "c1")))

; IN.2: DynamicNamedCall had no "cut" Case either (same underlying gap
; insert-n-rows-at's own note documents) - fixed there.
(defmacro
    (cut-range a b)
    "move a range's cells to another location, leaving the source empty"
    (. a cut :destination b))

(english-vla
    "move range {a:range} to {b:cell}"
    (cut-range (range {a}) (range {b})))
(test-success
    "Move range A1:A5 to C1."
    (cut-range (range "a1:a5") (range "c1")))

(defmacro
    (duplicate-row r n)
    "copy one whole row's contents and formatting onto another row"
    (. (rows r) copy :destination (rows n)))

(english-vla
    "copy row {r:expr} to row {n:expr}"
    (duplicate-row {r} {n}))
(test-success
    "Copy row 5 to row 12."
    (duplicate-row 5 12))

; ---- clearing and removing --------------------------------------------
; clear-contents (pareto section 4) is already shipped under a
; different surface - "Clear cell|range {r:range}." (this file's own
; 11-20 block, line 143) already reaches .ClearContents - not re-added
; here under pareto's own wording, same policy as this section's own
; header comment states.
(defmacro
    (clear-everything-from r)
    "clear a range's values, formulas, and formatting entirely"
    (. r clear))

(english-vla
    "clear everything from {r:range}"
    (clear-everything-from (range {r})))
(test-success
    "Clear everything from A1:D50."
    (clear-everything-from (range "a1:d50")))

; IN.2: DynamicNamedCall had no "delete" Case either (same underlying
; gap insert-n-rows-at's/cut-range's own notes document) - fixed there.
(defmacro
    (delete-shift-up r)
    "delete a range and shift the cells below it up"
    (. r delete :shift xlshiftup))

(defmacro
    (delete-shift-left r)
    "delete a range and shift the cells to its right left"
    (. r delete :shift xlshifttoleft))

(english-vla
    "delete {r:range} and shift cells up"
    (delete-shift-up (range {r})))
(test-success
    "Delete A1:A5 and shift cells up."
    (delete-shift-up (range "a1:a5")))

(english-vla
    "delete {r:range} and shift cells left"
    (delete-shift-left (range {r})))
(test-success
    "Delete A1:A5 and shift cells left."
    (delete-shift-left (range "a1:a5")))

; delete-blank-rows (!, pareto section 4): VLA_Runtime.VlaDeleteBlankRows
; guards SpecialCells' own "no cells were found" error - nothing blank
; is a silent no-op, not a refusal.
(defmacro
    (delete-blank-rows r)
    "delete every row that has a blank cell within a range"
    (vladeleteblankrows r))

(english-vla
    "delete blank rows in {r:range}"
    (delete-blank-rows (range {r})))
(test-success
    "Delete blank rows in A1:D50."
    (delete-blank-rows (range "a1:d50")))

; trim-sheet (!, pareto section 4): VLA_Runtime.VlaTrimSheet. No slot -
; operates on the active sheet, same implicit-target convention as the
; already-shipped "fit all columns"/"refresh everything".
(defmacro
    (trim-this-sheet)
    "remove every trailing empty row and column past the sheet's real used extent"
    (vlatrimsheet))

(english-vla
    "remove trailing empty rows and columns"
    (trim-this-sheet))
(test-success
    "Remove trailing empty rows and columns."
    (trim-this-sheet))

; ---- banded rows and header styling (pareto section 5, swept into
; G-STRUCT's own scoping pass per the roadmap item) ---------------------
; banded-rows (!, owner's choice: a static one-time loop, not a live
; Conditional Formatting rule - no cf-* machinery exists yet, pareto's
; own section 7 is a later, unstarted pass, and a static fill is what
; clear-formats/clear-all already know how to undo).
; VLA_Runtime.VlaBandRows shades every OTHER row starting at the
; range's own 2nd row (leaves row 1 - often a header - unbanded).
; pareto.txt's own {n:text} target is widened here to {n:expr}, the
; same slot category and (vlacolor {e}) wrapping set-fill-color/
; set-font-color/set-tab-color already use and already proved (a bare
; :text slot has no runtime path to a color value - VlaColor only
; resolves a hex string or a numeric constant, never an English color
; name - so a bare color word here would raise rt-color-invalid at
; runtime with no warning at Check time). G-FORMAT slice 2 later taught
; VlaColor the eight named colors, for its {c:color} border sentences;
; this rule keeps {n:expr} (SD-4), where a bare "red" is still read as
; a variable name.
(defmacro
    (band-rows r c)
    "shade every other row of a range with a fill color, starting at the range's own 2nd row"
    (vlabandrows r c))

(english-vla
    "band every other row of {r:range} {n:expr}"
    (band-rows (range {r}) (vlacolor {n})))
(test-success
    "Band every other row of A2:D50 \"#D9D9D9\"."
    (band-rows (range "a2:d50") (vlacolor "#D9D9D9")))

; header-style (!, owner's choice: bold + fill only - "an opinionated
; composite, audition carefully" per pareto's own note. Freezing the
; row and turning on filters are each already their own sentence
; ("Freeze the first {n} rows.", "Add filters to {r}." - the latter
; still unbuilt, filed under G-SORTFILTER) - documented here as the
; two-sentence idiom rather than folded into this verb's own side
; effects. No Tier-2 helper: bold + one fill-color set!, the same
; shape make-bold/set-fill-color already prove independently.
(defmacro
    (make-header-row r)
    "make a row read as a table header: bold text and a light fill - freeze or filter it with their own separate sentences if wanted"
    (begin (make-bold (rows r)) (set-fill-color (rows r) (vlacolor "#D9D9D9"))))

(english-vla
    "make row {r:expr} a header row"
    (make-header-row {r}))
(test-success
    "Make row 1 a header row."
    (make-header-row 1))

; ---- fill-series (?, pareto section 3 - owner's choice: design the
; fuller step/type surface now rather than deferring it). Two axes
; (linear/growth, with/without an explicit step) as four plain rules,
; the same shape sort-range-by's own ascending/descending/bare-default
; trio already uses, rather than a defaulted alternation slot - "with a
; {t:linear|growth} series" would put the noise word "a" directly
; before a slot, exactly the silent-swallow hazard this pass was
; warned to check for (IsNoiseWord strips "a"/"an" before a LITERAL
; safely; before a SLOT it eats the slot's own leftover article
; instead). Four full-sentence patterns of different lengths need no
; such trick and cannot ambiguously overlap - TryPhrase requires a
; rule to consume the WHOLE sentence, so the step-less rules simply
; fail (unconsumed trailing tokens) on a sentence that names a step,
; regardless of registration order.
; DataSeries has no "starting value" argument of its own - the value
; has to be written into the range's first cell before it runs
; (VLA_Runtime.VlaFillSeries's own header note).
(defmacro
    (fill-series r n s kind)
    "fill a range with a linear or growth series starting at a value, with an optional step"
    (vlafillseries r n s kind))

(english-vla
    "fill {r:range} with a series starting at {n:expr}"
    (fill-series (range {r}) {n} 1 "linear"))
(test-success
    "Fill A1:A10 with a series starting at 1."
    (fill-series (range "a1:a10") 1 1 "linear"))

(english-vla
    "fill {r:range} with a series starting at {n:expr} with step {s:expr}"
    (fill-series (range {r}) {n} {s} "linear"))
(test-success
    "Fill A1:A10 with a series starting at 1 with step 2."
    (fill-series (range "a1:a10") 1 2 "linear"))

; owner-caught live: this rule's own default step was wrongly shared
; with the linear rule above (literal 1) - a growth series with ratio
; 1 is degenerate (every cell equals the start value, no growth at
; all), diagnosed but not actually fixed the first time this session -
; caught only when VerifyReports kept reporting it. 2 (doubling) is
; the natural default for "growth" the way +1 is for "linear".
(english-vla
    "fill {r:range} with a growth series starting at {n:expr}"
    (fill-series (range {r}) {n} 2 "growth"))
(test-success
    "Fill A1:A10 with a growth series starting at 1."
    (fill-series (range "a1:a10") 1 2 "growth"))

(english-vla
    "fill {r:range} with a growth series starting at {n:expr} with step {s:expr}"
    (fill-series (range {r}) {n} {s} "growth"))
(test-success
    "Fill A1:A10 with a growth series starting at 2 with step 3."
    (fill-series (range "a1:a10") 2 3 "growth"))

; ---- G-FORMAT slice 1: the cosmetic layer (pareto section 5) ----------
; BETA_REARVIEW.md's G-FORMAT entry, appetite-boxed to pareto.txt section
; 5 only. Section 6 (number formats) waits on EN.3's locale policy - the
; roadmap's own gate table names exactly those rules and nothing else -
; and section 7 (conditional formatting) is its own later pass.
;
; What pareto section 5 lists that this file ALREADY says, and is
; therefore not re-added under pareto's wording (one spelling per
; operation - G-STRUCT's own header policy): wrap/no-wrap, merge/
; unmerge, align left/right/center, copy-style, banded-rows and
; header-style.
;
; SD-19 (BETA_REARVIEW.md's standing-decision register, founded on this
; block): where Excel has neighbouring operations, each sentence names
; the one it performs. The test, at audition: read the sentence to
; someone who has not seen the rule - if a sibling operation is a
; plausible answer to "what will Excel do?", the deciding word is
; missing. A new rule never ships the undecided spelling; a shipped one
; keeps its meaning (SD-4) and gains an explicit sibling.
;
; Three kinds of rule follow.
;   RANGE TWINS - a shipped rule typed {r:cell}, which refuses A1:C3 at
;   Check (G2's shape check), gains a "range" sibling. The twin is the
;   cell sentence with "cell" replaced by "range", word for word, and
;   reuses the cell rule's own macro - the "put ... into range" twin at
;   the top of this file is the precedent. The shipped cell rule is left
;   untouched: widening it to "cell|range" would change a shipped
;   spelling (SD-4), and the duplicate-shape audit would refuse the two
;   side by side anyway. A cell rule SD-19 finds vague gets no twin -
;   copying it would ship the vague spelling a second time.
;   SD-19 SIBLINGS - the explicit spelling beside a vague shipped one:
;   "Add borders to every cell in" beside "Add border to range" (grid,
;   heard as a box), "Clear fill-color of" beside "Clear color of cell"
;   (fill only, font colour untouched). The shipped spelling keeps
;   working; the sibling is the one the corpus uses from here on.
;   NEW VERBS - no shipped predecessor, so they take "cell|range
;   {r:range}" (the breadth pass's center/align/clear convention),
;   saying both at once.
;
; Every antonym pair ("any action that can be undone should be
; undoable", the pivot-collapse/expand note) is shipped together:
; bold/not bold, italic/not italic, underline/remove underline, strike
; through/remove strikethrough, add border/remove borders.

; ---- range twins of the shipped cell-only formatting rules ------------
(english-vla
    "make range {r:range} {d:bold|italic}"
    (make-{d} (range {r})))
(test-success
    "Make range A1:C1 bold."
    (make-bold (range "a1:c1")))
(test-success
    "Make range A1:C1 italic."
    (make-italic (range "a1:c1")))

; The eight vb color constants: before this pass the interpreter
; resolved only vbred and vbyellow (VLA_Interpreter.ResolveVbConstant),
; so the SHIPPED cell rule's other six colors translated fine and then
; raised "nothing stored at key 'vbblack'" under Interpret - instructions.txt
; only ever said red and yellow. Closed there alongside this twin.
(english-vla
    "make range {r:range} {d:red|yellow|black|blue|cyan|green|magenta|white}"
    (set-fill-color (range {r}) vb{d}))
(test-success
    "Make range A1:C1 blue."
    (set-fill-color (range "a1:c1") vbblue))
(test-success
    "Make range A1:C1 white."
    (set-fill-color (range "a1:c1") vbwhite))

(english-vla
    "set font-color of range {r:range} to {e:expr}"
    (set-font-color (range {r}) (vlacolor {e})))
(test-success
    "Set font-color of range A1:C1 to hot-pink."
    (set-font-color (range "a1:c1") (vlacolor hot-pink)))

(english-vla
    "set fill-color of range {r:range} to {e:expr}"
    (set-fill-color (range {r}) (vlacolor {e})))
(test-success
    "Set fill-color of range A1:C1 to \"#FF69B4\"."
    (set-fill-color (range "a1:c1") (vlacolor "#FF69B4")))

; SD-19 sibling, not a twin: the shipped "Clear color of cell" clears
; the FILL and leaves font colour alone, where "color" could mean
; either. Its explicit spelling names the fill, pairing with "Set
; fill-color of", and takes cell|range so the vague cell rule is never
; copied. Same macro as the shipped rule, so the two stay one operation.
(english-vla
    "clear fill-color of cell|range {r:range}"
    (clear-color (range {r})))
(test-success
    "Clear fill-color of range A1:C1."
    (clear-color (range "a1:c1")))
(test-success
    "Clear fill-color of cell A1."
    (clear-color (range "a1")))

(english-vla
    "set font size of range {r:range} to {n:expr}"
    (set-font-size (range {r}) {n}))
(test-success
    "Set font size of range A1:C1 to 14."
    (set-font-size (range "a1:c1") 14))

; ---- font: not bold/italic, underline, strikethrough, typeface --------
(defmacro
    (make-not-bold r)
    "turn a range's bold off"
    (set! (. r font.bold) false))

(defmacro
    (make-not-italic r)
    "turn a range's italic off"
    (set! (. r font.italic) false))

; G11r: the matched style word completes the macro name, the same move
; as "make cell {r:cell} {d:bold|italic}" -> (make-{d} ...).
(english-vla
    "make cell|range {r:range} not {d:bold|italic}"
    (make-not-{d} (range {r})))
(test-success
    "Make range A1:C1 not bold."
    (make-not-bold (range "a1:c1")))
(test-success
    "Make cell A1 not italic."
    (make-not-italic (range "a1")))

; Underline is not a boolean in Excel - Font.Underline takes an
; XlUnderlineStyle - so "single" is the one style said here; double and
; accounting underlines are a later, separate decision if anyone asks.
(defmacro
    (underline-text r)
    "give a range's text a single underline"
    (set! (. r font.underline) xlunderlinestylesingle))

(defmacro
    (strike-through-text r)
    "draw a line through a range's text"
    (set! (. r font.strikethrough) true))

(english-vla
    "underline cell|range {r:range}"
    (underline-text (range {r})))
(test-success
    "Underline range A1:C1."
    (underline-text (range "a1:c1")))
(test-success
    "Underline cell A1."
    (underline-text (range "a1")))

(english-vla
    "strike through cell|range {r:range}"
    (strike-through-text (range {r})))
(test-success
    "Strike through range A1:C1."
    (strike-through-text (range "a1:c1")))

; The undo verb for three different marks, one rule: the matched noun IS
; the macro name (G11r). "borders" is plural only, under SD-19: "Remove
; the border from range B2:D4" reads as taking the box off, but the
; macro removes the inside lines too - the plural says "every line".
(defmacro
    (remove-underline r)
    "take the underline off a range's text"
    (set! (. r font.underline) xlunderlinestylenone))

(defmacro
    (remove-strikethrough r)
    "take the strikethrough line off a range's text"
    (set! (. r font.strikethrough) false))

(defmacro
    (remove-borders r)
    "remove every border line from a range, edges and inside lines alike"
    (set! (. r borders.linestyle) xlnone))

(english-vla
    "remove {d:underline|strikethrough|borders} from cell|range {r:range}"
    (remove-{d} (range {r})))
(test-success
    "Remove underline from range A1:C1."
    (remove-underline (range "a1:c1")))
(test-success
    "Remove the strikethrough from cell A1."
    (remove-strikethrough (range "a1")))
(test-success
    "Remove borders from range B2:D4."
    (remove-borders (range "b2:d4")))
(test-success
    "Remove the borders from cell B2."
    (remove-borders (range "b2")))

; A font name is best quoted ("quotes are the door"): quoting keeps it
; exactly as written, a bare word lowercases in transit, and "Courier
; New" unquoted is two tokens and refuses.
(defmacro
    (set-font-name r n)
    "set a range's typeface by name"
    (set! (. r font.name) n))

(english-vla
    "set font of cell|range {r:range} to {n:text}"
    (set-font-name (range {r}) {n}))
(test-success
    "Set font of range A1:C1 to \"Courier New\"."
    (set-font-name (range "a1:c1") "Courier New"))
(test-success
    "Set font of cell A1 to arial."
    (set-font-name (range "a1") "arial"))

; ---- alignment, indent, rotation --------------------------------------
; Vertical alignment beside the shipped horizontal rule. Not a glued
; xl{d}, unlike the horizontal one: Excel has no xlMiddle - middle is
; xlCenter - so the matched word names a macro instead (G11r), and each
; macro carries its own constant. "the" is dropped from input at
; tokenize time, so "to the top" and "to top" are one sentence.
(defmacro
    (align-top r)
    "align a range's contents to the top of each cell"
    (set! (. r verticalalignment) xltop))

(defmacro
    (align-middle r)
    "align a range's contents to the middle of each cell"
    (set! (. r verticalalignment) xlcenter))

(defmacro
    (align-bottom r)
    "align a range's contents to the bottom of each cell"
    (set! (. r verticalalignment) xlbottom))

(english-vla
    "align cell|range {r:range} to {d:top|middle|bottom}"
    (align-{d} (range {r})))
(test-success
    "Align range A1:C1 to the top."
    (align-top (range "a1:c1")))
(test-success
    "Align cell A1 to the middle."
    (align-middle (range "a1")))
(test-success
    "Align range A1:C1 to bottom."
    (align-bottom (range "a1:c1")))

; Excel refuses an indent outside 0-15 itself, as a step-numbered
; runtime error at this sentence - no guard added in front of it.
(defmacro
    (set-indent r n)
    "indent a range's contents by a number of levels"
    (set! (. r indentlevel) n))

(english-vla
    "indent cell|range {r:range} by {n:expr}"
    (set-indent (range {r}) {n}))
(test-success
    "Indent range A1:C1 by 2."
    (set-indent (range "a1:c1") 2))

; Orientation in degrees, -90 to 90; outside that Excel refuses at
; runtime, same as indent above.
(defmacro
    (set-text-rotation r n)
    "rotate a range's text by an angle in degrees"
    (set! (. r orientation) n))

(english-vla
    "rotate text in cell|range {r:range} by {n:expr} degrees"
    (set-text-rotation (range {r}) {n}))
(test-success
    "Rotate text in range A1:C1 by 45 degrees."
    (set-text-rotation (range "a1:c1") 45))

; ---- borders -----------------------------------------------------------
; Three extents, each named in its sentence (SD-19): every cell's lines,
; the outline ("around"), and one named edge.
;
; Every cell - SD-19's founding case. The shipped "Add border to range"
; draws every line, inside and out, while English hears a box. It keeps
; that meaning (SD-4); this is its explicit sibling, same macro, and the
; spelling the corpus uses from here on. "[range]" is optional because
; "every cell in cell A1" is not English, and a lone cell's borders are
; its outline anyway - "Add a border around cell A1" says that.
(english-vla
    "add borders to every cell in [range] {r:range}"
    (add-border (range {r})))
(test-success
    "Add borders to every cell in range A1:C3."
    (add-border (range "a1:c3")))
(test-success
    "Add borders to every cell in A1:C3."
    (add-border (range "a1:c3")))

; add-edge-border takes the edge as an xlEdge* constant, glued from the
; matched word (xledge{d} -> xledgebottom), the xl{d} move again.
; Borders(index) is the first INDEXED Borders read this corpus makes;
; the interpreter's own "borders" member used to ignore its index and
; hand back all of them, which would have drawn every edge for "Add a
; bottom border" - fixed in VLA_Interpreter.DynamicGet, and the live
; check on the GFormat sheet reads the top edge back to prove it stayed
; off.
(defmacro
    (add-edge-border r edge)
    "draw a continuous line along one edge of a range"
    (set! (. (. r borders edge) linestyle) xlcontinuous))

(english-vla
    "add {d:top|bottom|left|right} border to cell|range {r:range}"
    (add-edge-border (range {r}) xledge{d}))
(test-success
    "Add a bottom border to range B5:D5."
    (add-edge-border (range "b5:d5") xledgebottom))
(test-success
    "Add top border to cell B5."
    (add-edge-border (range "b5") xledgetop))
(test-success
    "Add left border to range B5:B9."
    (add-edge-border (range "b5:b9") xledgeleft))
(test-success
    "Add right border to range D5:D9."
    (add-edge-border (range "d5:d9") xledgeright))

; The outline is the four edges, not Range.BorderAround: one mechanism
; already proved above, four readable lines of emitted VBA, and no new
; method on the interpreter's member allowlist (SEC.1).
(defmacro
    (add-outline-border r)
    "draw a box around the outside of a range, leaving its inside lines alone"
    (begin (add-edge-border r xledgetop)
           (add-edge-border r xledgebottom)
           (add-edge-border r xledgeleft)
           (add-edge-border r xledgeright)))

(english-vla
    "add border around cell|range {r:range}"
    (add-outline-border (range {r})))
(test-success
    "Add a border around range B2:D4."
    (add-outline-border (range "b2:d4")))

; ---- G-FORMAT slice 2: border colour, inside the drawing sentence ------
; Slice 1 withdrew a "Set border-color of ... to ..." rule: SD-19's
; audition failed it live, because Range.Borders.Color DRAWS every edge
; and inside line it colours - an outlined B2:D4 came back with its
; inside lines drawn (the owner's Immediate-window read: D3's bottom
; edge and C4's left edge both LineStyle 1, neither drawn by any
; sentence). The owner's call for its return: the colour rides the
; drawing sentence, so the sentence says it draws. Each colour sentence
; colours only the lines it draws - the outline sets Color edge by edge,
; never Borders.Color, which is what would draw the inside. Only the
; every-cell sentence uses Borders.Color, where every line is drawn
; anyway.
; The wording is the owner's: "colored", placed straight after the
; border it describes. A draft said "in {e:expr}" at the end, which read
; oddly with a code ("in "#FF0000"") and doubled up in "every cell in
; range A1:C3 in ..."; and "colored" at the END would attach to the
; wrong noun - "every cell in range A1:C3 colored blue" reads as a
; filter, the cells that ARE blue (SD-19). Next to "border" it can only
; describe the border.
; {c:color}, not {e:expr}: the typed slot takes the eight named colours
; bare and a code quoted, and refuses anything else at Check with its
; own teaching text - where {e:expr} would have read "colored red" as a
; variable called red. VLA_Runtime.VlaColor learned the eight names in
; the same pass. The cost, accepted: a Defined alias like hot-pink needs
; quotes here.
(defmacro
    (add-edge-border-in r edge c)
    "draw one edge of a range as a continuous line in a color"
    (begin (add-edge-border r edge) (set! (. (. r borders edge) color) c)))

(defmacro
    (add-outline-border-in r c)
    "draw a box around the outside of a range in a color, leaving its inside lines alone"
    (begin (add-edge-border-in r xledgetop c)
           (add-edge-border-in r xledgebottom c)
           (add-edge-border-in r xledgeleft c)
           (add-edge-border-in r xledgeright c)))

(defmacro
    (add-border-in r c)
    "draw every line of every cell in a range, in a color"
    (begin (add-border r) (set! (. r borders.color) c)))

(english-vla
    "add border colored {c:color} around cell|range {r:range}"
    (add-outline-border-in (range {r}) (vlacolor {c})))
(test-success
    "Add a border colored red around range B2:D4."
    (add-outline-border-in (range "b2:d4") (vlacolor "red")))
(test-success
    "Add a border colored \"#FF0000\" around cell B2."
    (add-outline-border-in (range "b2") (vlacolor "#FF0000")))

(english-vla
    "add {d:top|bottom|left|right} border colored {c:color} to cell|range {r:range}"
    (add-edge-border-in (range {r}) xledge{d} (vlacolor {c})))
(test-success
    "Add a bottom border colored green to range B5:D5."
    (add-edge-border-in (range "b5:d5") xledgebottom (vlacolor "green")))

(english-vla
    "add borders colored {c:color} to every cell in [range] {r:range}"
    (add-border-in (range {r}) (vlacolor {c})))
(test-success
    "Add borders colored \"#0000FF\" to every cell in range A1:C3."
    (add-border-in (range "a1:c3") (vlacolor "#0000FF")))
(test-fail
    "Add a border colored hot-pink around range B2:D4."
    "a color (like")

; ---- G-FORMAT slice 2: number formats (pareto section 6) ---------------
; EN.3's policy, the owner's call (2026-09-10): the SYMBOL follows the
; data, the ORDER follows the reader.
;   - A currency is named in the sentence - dollars, euros, pounds -
;     because it is a fact about the numbers, not about the machine
;     reading them. Every sign is ESCAPED in the code, the dollar too:
;     a bare "$" in a format code is the reading machine's own currency
;     symbol, not a literal - a UK-region run showed "as dollars" as
;     "£1,234.56" until VLA_Runtime's CurrencySymbolCode escaped it (owner's run,
;     2026-09-10). The shipped "Format cell ... as currency" is exactly
;     that bare "$#,##0.00", so it has always meant "the reading
;     machine's currency" - a dollar figure reads as pounds in the UK.
;     It keeps that meaning (SD-4) and gets no range twin: under SD-19
;     "currency" does not say which, so it is not copied (CO.7 lists it).
;   - Separators need nothing: Range.NumberFormat always reads US-syntax
;     codes and Excel draws "," and "." from the reading machine.
;   - Dates and times use Excel's own system codes, which follow the
;     reading machine. The shipped "Format cell ... as date" keeps its
;     fixed US order (mm/dd/yyyy, SD-4) and gets no twin either.
; Decimals ride each named format ("with 2 decimals"), never a
; free-standing "Show 2 decimal places in ..." - pareto's own spelling,
; which replaces the whole format and silently drops a currency or a
; percent sign (SD-19, by side effect). "decimal/s" (G1.1) takes "with 1
; decimal" and "with 2 decimals" alike. Every code is built or named in
; VLA_Runtime.VlaNumberFormatCode or written in the macro below - see
; that function's own note for why currency codes are built, not typed.

; Currency - the sign is named, and so the rule says which.
(defmacro
    (format-as-dollars r n)
    "apply a dollar currency format with a number of decimal places"
    (set! (. r numberformat) (vlanumberformatcode "dollars" n)))

(defmacro
    (format-as-euros r n)
    "apply a euro currency format with a number of decimal places"
    (set! (. r numberformat) (vlanumberformatcode "euros" n)))

(defmacro
    (format-as-pounds r n)
    "apply a pound sterling currency format with a number of decimal places"
    (set! (. r numberformat) (vlanumberformatcode "pounds" n)))

(english-vla
    "format cell|range {r:range} as {d:dollars|euros|pounds}"
    (format-as-{d} (range {r}) 2))
(test-success
    "Format range A1:A9 as dollars."
    (format-as-dollars (range "a1:a9") 2))
(test-success
    "Format cell A1 as pounds."
    (format-as-pounds (range "a1") 2))

(english-vla
    "format cell|range {r:range} as {d:dollars|euros|pounds} with {n:expr} decimal/s"
    (format-as-{d} (range {r}) {n}))
(test-success
    "Format range A1:A9 as euros with 0 decimals."
    (format-as-euros (range "a1:a9") 0))
(test-success
    "Format cell A1 as dollars with 1 decimal."
    (format-as-dollars (range "a1") 1))

(defmacro
    (format-as-accounting-dollars r n)
    "apply the accounting layout in dollars: sign at the left, negatives in brackets, zero as a dash"
    (set! (. r numberformat) (vlanumberformatcode "accounting-dollars" n)))

(defmacro
    (format-as-accounting-euros r n)
    "apply the accounting layout in euros: sign at the left, negatives in brackets, zero as a dash"
    (set! (. r numberformat) (vlanumberformatcode "accounting-euros" n)))

(defmacro
    (format-as-accounting-pounds r n)
    "apply the accounting layout in pounds: sign at the left, negatives in brackets, zero as a dash"
    (set! (. r numberformat) (vlanumberformatcode "accounting-pounds" n)))

(english-vla
    "format cell|range {r:range} as accounting in {d:dollars|euros|pounds}"
    (format-as-accounting-{d} (range {r}) 2))
(test-success
    "Format range B2:B9 as accounting in dollars."
    (format-as-accounting-dollars (range "b2:b9") 2))

(english-vla
    "format cell|range {r:range} as accounting in {d:dollars|euros|pounds} with {n:expr} decimal/s"
    (format-as-accounting-{d} (range {r}) {n}))
(test-success
    "Format range B2:B9 as accounting in euros with 0 decimals."
    (format-as-accounting-euros (range "b2:b9") 0))

; Percent. The range twin reuses the shipped cell rule's own macro (one
; decimal, "0.0%"), so "as percent" means one thing whichever it takes;
; the pure suite pins VlaNumberFormatCode("percent", 1) to that same
; code. With decimals it goes through VlaNumberFormatCode.
(english-vla
    "format range {r:range} as percent"
    (format-as-percent (range {r})))
(test-success
    "Format range A1:A9 as percent."
    (format-as-percent (range "a1:a9")))

(defmacro
    (format-as-percent-with-decimals r n)
    "apply a percent format with a number of decimal places"
    (set! (. r numberformat) (vlanumberformatcode "percent" n)))

(english-vla
    "format cell|range {r:range} as percent with {n:expr} decimal/s"
    (format-as-percent-with-decimals (range {r}) {n}))
(test-success
    "Format cell A1 as percent with 2 decimals."
    (format-as-percent-with-decimals (range "a1") 2))
(test-success
    "Format range A1:A9 as percent with 1 decimal."
    (format-as-percent-with-decimals (range "a1:a9") 1))

; Plain numbers: Excel's own Number category - two decimals, no
; separators - and the separators named when wanted, as Excel's own
; "Use 1000 separator" box is. pareto's "Format ... with thousands
; separators" ("#,##0") is not shipped as written: it hides the
; decimals too, which the sentence never said (SD-19). The decimals come
; BEFORE "and thousands separators" on purpose - with them after, the
; plain rule's {n:expr} would meet "thousands separators and 2" and the
; two rules' shapes would overlap.
(defmacro
    (format-as-number r n)
    "apply a plain number format with a number of decimal places and no thousands separators"
    (set! (. r numberformat) (vlanumberformatcode "number" n)))

(defmacro
    (format-as-separated-number r n)
    "apply a number format with thousands separators and a number of decimal places"
    (set! (. r numberformat) (vlanumberformatcode "number-separated" n)))

(english-vla
    "format cell|range {r:range} as number"
    (format-as-number (range {r}) 2))
(test-success
    "Format range A1:A9 as a number."
    (format-as-number (range "a1:a9") 2))

(english-vla
    "format cell|range {r:range} as number with {n:expr} decimal/s"
    (format-as-number (range {r}) {n}))
(test-success
    "Format cell A1 as a number with 3 decimals."
    (format-as-number (range "a1") 3))

(english-vla
    "format cell|range {r:range} as number with thousands separators"
    (format-as-separated-number (range {r}) 2))
(test-success
    "Format range A1:A9 as a number with thousands separators."
    (format-as-separated-number (range "a1:a9") 2))

(english-vla
    "format cell|range {r:range} as number with {n:expr} decimal/s and thousands separators"
    (format-as-separated-number (range {r}) {n}))
(test-success
    "Format range A1:A9 as a number with 0 decimals and thousands separators."
    (format-as-separated-number (range "a1:a9") 0))

; Dates and times - the reading machine's own formats, EN.3's "order
; follows the reader". "m/d/yyyy" looks like a fixed US order and is
; not: it is how Range.NumberFormat spells Excel's built-in short date
; (format 14), the one Excel's own dialog marks as following the
; regional setting. [$-F800] and [$-F400] are Excel's system long date
; and system time; the text after each is ignored. ISO is the one fixed
; order, and the sentence names it. The live checks compare against
; VBA's own Format$ with the machine's named formats, so a region
; change is the test that tells "follows the reader" from "US".
(defmacro
    (format-as-short-date r)
    "apply the reading machine's own short date format"
    (set! (. r numberformat) "m/d/yyyy"))

(defmacro
    (format-as-long-date r)
    "apply the reading machine's own long date format"
    (set! (. r numberformat) "[$-F800]dddd, mmmm dd, yyyy"))

(defmacro
    (format-as-iso-date r)
    "apply the ISO date format, year-month-day, the same on every machine"
    (set! (. r numberformat) "yyyy-mm-dd"))

(defmacro
    (format-as-time r)
    "apply the reading machine's own time format"
    (set! (. r numberformat) "[$-F400]h:mm:ss AM/PM"))

(english-vla
    "format cell|range {r:range} as {d:short|long|iso} date"
    (format-as-{d}-date (range {r})))
(test-success
    "Format range A1:A9 as a short date."
    (format-as-short-date (range "a1:a9")))
(test-success
    "Format cell A1 as a long date."
    (format-as-long-date (range "a1")))
(test-success
    "Format range A1:A9 as an ISO date."
    (format-as-iso-date (range "a1:a9")))

(english-vla
    "format cell|range {r:range} as time"
    (format-as-time (range {r})))
(test-success
    "Format range A1:A9 as a time."
    (format-as-time (range "a1:a9")))

; Text, named for what Excel's Text format actually does (SD-19, the
; owner's call): it changes how values typed LATER are read - "007"
; stays "007" - and leaves a number already there a number, still
; counted by SUM. pareto's bare "Format ... as text" reads as converting
; what is there; this sentence says it does not.
(defmacro
    (format-as-text-for-new-entries r)
    "make a range treat values typed into it later as text; numbers already there stay numbers"
    (set! (. r numberformat) "@"))

(english-vla
    "format cell|range {r:range} as text for new entries"
    (format-as-text-for-new-entries (range {r})))
(test-success
    "Format range A1:A9 as text for new entries."
    (format-as-text-for-new-entries (range "a1:a9")))

(defmacro
    (format-as-general r)
    "remove a range's number format, back to Excel's General"
    (set! (. r numberformat) "General"))

(english-vla
    "format cell|range {r:range} as general"
    (format-as-general (range {r})))
(test-success
    "Format range A1:A9 as general."
    (format-as-general (range "a1:a9")))

; The escape hatch: any Excel format code, quoted, written in Excel's
; own US syntax ("." for decimals, "," for thousands) whatever the
; machine - Excel localises the display, never the code. A format code
; is inert text: it can change what a cell shows, never run anything.
(defmacro
    (format-using r pattern)
    "apply an Excel number format code, written in Excel's own US syntax"
    (set! (. r numberformat) pattern))

(english-vla
    "format cell|range {r:range} using {n:text}"
    (format-using (range {r}) {n}))
(test-success
    "Format range A1:A9 using \"00000\"."
    (format-using (range "a1:a9") "00000"))

; ---- G-SORTFILTER: sorting and filtering (pareto section 8) -------------
; BETA_REARVIEW.md's G-SORTFILTER entry, appetite-boxed to pareto.txt
; section 8 and scoped against what this file already said, with four
; forks the owner decided (2026-09-10), each named where its rules sit.
;
; What already shipped, and keeps its meaning (SD-4):
;   "Sort range {r} by column {k:cell} [ascending]|descending" - the key
;     is a CELL (B1), and :header xlyes means row 1 of the range never
;     moves, a header whether or not it is one.
;   "Keep only rows of range {r} where column {f:expr} is {v:expr}" - a
;     column's POSITION inside the range (3), where every other column
;     slot in this file takes a letter.
;   "Show all rows" - ShowAllData, which clears filter conditions and
;     nothing else.
; Each is now a CO.7 candidate with an explicit sibling below: the sort
; hides its header decision; "keep only" can be heard as deleting the
; other rows, and "column 3" as sheet column C; "show all rows" can be
; heard as unhiding rows hidden with "Hide row", which it does not do.
;
; Columns are letters here ({c:column}), turned into a position inside
; the range by VLA_Runtime.VlaColumnInRange, which refuses a column
; outside the range by name. Sort keys and filter fields share it, so
; "column B" means one thing in both.

; ---- sorting -----------------------------------------------------------
; The header is named in every sentence, with or without (the owner's
; call): a header the sentence does not mention is exactly the shipped
; sort's hidden assumption. It comes last, after the key, on purpose - a
; sort that names no header gets furthest along THESE rules, so its
; refusal asks "with or without" instead of teaching the shipped "by
; column B1" form (CO.7's note on near-miss refusals). "[row]" lets "with
; a header" and "with a header row" both read.
;
; A direction is the bare default (ascending) or named after the key, the
; shipped sort's own shape. With two keys, both directions are named or
; neither is: "by column B descending then by column D" would leave D's
; direction for the reader to guess, so it refuses (a test-fail below).
(defmacro
    (sort-with-header r c order)
    "sort a range by one of its columns, keeping the range's first row in place as its header"
    (. r sort :key1 (. r columns (vlacolumninrange r c)) :order1 order :header xlyes))

(defmacro
    (sort-without-header r c order)
    "sort every row of a range by one of its columns - no header row held back"
    (. r sort :key1 (. r columns (vlacolumninrange r c)) :order1 order :header xlno))

(defmacro
    (sort-two-with-header r c order e order2)
    "sort a range by one column and then another, keeping the range's first row in place as its header"
    (. r sort :key1 (. r columns (vlacolumninrange r c)) :order1 order :key2 (. r columns (vlacolumninrange r e)) :order2 order2 :header xlyes))

(defmacro
    (sort-two-without-header r c order e order2)
    "sort every row of a range by one column and then another - no header row held back"
    (. r sort :key1 (. r columns (vlacolumninrange r c)) :order1 order :key2 (. r columns (vlacolumninrange r e)) :order2 order2 :header xlno))

; The bare rule is registered first so a sentence naming no header is
; refused with "one of 'with'/'without'" - the first rule to reach the
; furthest word leads the refusal.
(english-vla
    "sort [range] {r:range} by column {c:column} {h:with|without} header [row]"
    (sort-{h}-header (range {r}) {c} xlascending))
(test-success
    "Sort range A1:C50 by column B with a header row."
    (sort-with-header (range "a1:c50") "b" xlascending))
(test-success
    "Sort A2:C50 by column B without a header row."
    (sort-without-header (range "a2:c50") "b" xlascending))
(test-success
    "Sort range A1:C50 by column C with a header."
    (sort-with-header (range "a1:c50") "c" xlascending))
(test-fail
    "Sort range A1:C50 by column B."
    "one of 'with'/'without'")

(english-vla
    "sort [range] {r:range} by column {c:column} {d:ascending|descending} {h:with|without} header [row]"
    (sort-{h}-header (range {r}) {c} xl{d}))
(test-success
    "Sort range A1:C50 by column B descending with a header row."
    (sort-with-header (range "a1:c50") "b" xldescending))
(test-success
    "Sort range A2:C50 by column B ascending without a header row."
    (sort-without-header (range "a2:c50") "b" xlascending))

(english-vla
    "sort [range] {r:range} by column {c:column} then [by] column {e:column} {h:with|without} header [row]"
    (sort-two-{h}-header (range {r}) {c} xlascending {e} xlascending))
(test-success
    "Sort range A1:C50 by column B then by column C with a header row."
    (sort-two-with-header (range "a1:c50") "b" xlascending "c" xlascending))
(test-success
    "Sort range A2:C50 by column B then column C without a header row."
    (sort-two-without-header (range "a2:c50") "b" xlascending "c" xlascending))

(english-vla
    "sort [range] {r:range} by column {c:column} {d:ascending|descending} then [by] column {e:column} {f:ascending|descending} {h:with|without} header [row]"
    (sort-two-{h}-header (range {r}) {c} xl{d} {e} xl{f}))
(test-success
    "Sort range A1:C50 by column B descending then by column C ascending with a header row."
    (sort-two-with-header (range "a1:c50") "b" xldescending "c" xlascending))
(test-success
    "Sort range A2:C50 by column B ascending then by column C descending without a header row."
    (sort-two-without-header (range "a2:c50") "b" xlascending "c" xldescending))
(test-fail
    "Sort range A1:C50 by column B descending then by column C with a header row."
    "one of 'ascending'/'descending'")

; "This sheet" is the sheet's used range - every row and column holding
; data or formatting, pareto's own target. The protect sentences'
; "this sheet" is the precedent; "the sheet" reads too, since "the" is
; dropped from input.
(defmacro
    (used-range)
    "the active sheet's used range - every row and column holding data or formatting"
    (. activesheet usedrange))

(english-vla
    "sort [this] sheet by column {c:column} {h:with|without} header [row]"
    (sort-{h}-header (used-range) {c} xlascending))
(test-success
    "Sort this sheet by column B with a header row."
    (sort-with-header (used-range) "b" xlascending))
(test-success
    "Sort the sheet by column B without a header row."
    (sort-without-header (used-range) "b" xlascending))

(english-vla
    "sort [this] sheet by column {c:column} {d:ascending|descending} {h:with|without} header [row]"
    (sort-{h}-header (used-range) {c} xl{d}))
(test-success
    "Sort this sheet by column B descending with a header row."
    (sort-with-header (used-range) "b" xldescending))
(test-success
    "Sort this sheet by column B ascending without a header."
    (sort-without-header (used-range) "b" xlascending))

; ---- filter buttons on, conditions cleared, filters removed ------------
; Clearing and removing are neighbours (SD-19), and each sentence names
; its own (the owner's call): "Clear the filter conditions." brings every
; row back and leaves the buttons - ShowAllData, the shipped "Show all
; rows" macro, so the two stay one operation; "Remove the filters." takes
; the buttons away too. "Add filters to" is the antonym, and the sentence
; G-STRUCT's header-style note already pointed at. Each takes "filter"
; or "filters" - a sheet holds one set, so the two surfaces cannot mean
; different things.
;
; Adding never toggles: Range.AutoFilter with no arguments takes filter
; buttons AWAY when they are already there, so VLA_Runtime.VlaAddFilters
; does nothing when they are, and refuses by name when they are on a
; different range of the same sheet, or when the range is empty (Excel's
; own refusal there tells the writer to select a cell).
(defmacro
    (add-filters r)
    "turn on filter buttons for a range's header row - nothing is hidden until a condition is set"
    (vlaaddfilters r))

; "range" is required, as in every shipped "add ... to" rule: the core's
; own "add {e:expr} to {v:var}" is registered first, and would read "Add
; filters to A1." as adding a variable called filters to one called a1.
; "Add filters to range A1." fails that rule at "a1" and reaches this one.
(english-vla
    "add filter/s to range {r:range}"
    (add-filters (range {r})))
(test-success
    "Add filters to range A1:D50."
    (add-filters (range "a1:d50")))
(test-success
    "Add a filter to range A1:D50."
    (add-filters (range "a1:d50")))

(english-vla
    "clear filter condition/s"
    (show-all-rows))
(test-success
    "Clear the filter conditions."
    (show-all-rows))
(test-success
    "Clear the filter condition."
    (show-all-rows))

(defmacro
    (remove-filters)
    "take the active sheet's filter buttons away, showing every row again"
    (set! (. activesheet autofiltermode) false))

(english-vla
    "remove filter/s"
    (remove-filters))
(test-success
    "Remove the filters."
    (remove-filters))
(test-success
    "Remove the filter."
    (remove-filters))

; ---- filtering by a condition -------------------------------------------
; "Filter ... to show rows where" (the owner's call): "filter" names
; Excel's hide-not-delete, and "to show rows where" says which rows stay
; - pareto's bare "Filter {r} where ..." can be heard as "filter out".
; A condition on a second column narrows the first, Excel's own AND,
; which "filter" reads as; a second condition on the SAME column replaces
; its first.
;
; Each is ONE Range.AutoFilter call, which turns the buttons on itself
; (with a Field it is not a toggle). The Field comes from
; VLA_Runtime.VlaFilterField, which makes VlaAddFilters's own checks - one
; set of buttons per sheet, no empty range - so every refusal a filter
; sentence can meet, the value's included, arrives while the call's
; arguments are worked out, before Excel is touched: a refused sentence
; leaves the sheet as it was. (Found reading the owner's first live run:
; the macros had called VlaAddFilters first, as its own statement, so a
; refused condition would have left the buttons behind.)
;
; "is" is two conditions joined with xlAnd, and the value is written in
; the macro twice - VLA_Runtime.VlaFilterCriterion's note says why: a
; number matched as a number, whatever the cell's format, and text
; matched as written, * and ? included. "is" comes before "is greater
; than" in this file and never shadows it: {v:expr} reads "greater" as a
; name, then stops at "than", so the "is" rule fails on the unconsumed
; words and the next rule matches.
(defmacro
    (filter-to-show-matching r c v)
    "hide every row of a range except those whose column holds a value - the value itself, whatever the cell's number format"
    (. r autofilter :field (vlafilterfield r c) :criteria1 (vlafiltercriterion "at-least" v) :operator xland :criteria2 (vlafiltercriterion "at-most" v)))

(defmacro
    (filter-to-show-condition r c condition)
    "hide every row of a range except those whose column meets one AutoFilter condition"
    (. r autofilter :field (vlafilterfield r c) :criteria1 condition))

(defmacro
    (filter-to-show-containing r c v)
    "hide every row of a range except those whose column contains a piece of text"
    (filter-to-show-condition r c (vlafiltercriterion "contains" v)))

(defmacro
    (filter-to-show-greater-than r c v)
    "hide every row of a range except those whose column holds a number greater than a value"
    (filter-to-show-condition r c (vlafiltercriterion "greater" v)))

(defmacro
    (filter-to-show-less-than r c v)
    "hide every row of a range except those whose column holds a number less than a value"
    (filter-to-show-condition r c (vlafiltercriterion "less" v)))

(english-vla
    "filter [range] {r:range} to show rows where column {c:column} is {v:expr}"
    (filter-to-show-matching (range {r}) {c} {v}))
(test-success
    "Filter range A1:D50 to show rows where column C is \"West\"."
    (filter-to-show-matching (range "a1:d50") "c" "West"))
(test-success
    "Filter A1:D50 to show rows where column D is 100."
    (filter-to-show-matching (range "a1:d50") "d" 100))
(test-fail
    "Filter range A1:D50 where column C is \"West\"."
    "then I expected 'to'")

(english-vla
    "filter [range] {r:range} to show rows where column {c:column} contains {v:expr}"
    (filter-to-show-containing (range {r}) {c} {v}))
(test-success
    "Filter range A1:D50 to show rows where column C contains \"we\"."
    (filter-to-show-containing (range "a1:d50") "c" "we"))

; "greater|less" completes the macro name (G11r), the pivot sort's move.
; pareto lists greater than only; less than is its other half, one
; branch of the same rule.
(english-vla
    "filter [range] {r:range} to show rows where column {c:column} is {d:greater|less} than {v:expr}"
    (filter-to-show-{d}-than (range {r}) {c} {v}))
(test-success
    "Filter range A1:D50 to show rows where column D is greater than 100."
    (filter-to-show-greater-than (range "a1:d50") "d" 100))
(test-success
    "Filter A1:D50 to show rows where column D is less than limit."
    (filter-to-show-less-than (range "a1:d50") "d" limit))

; ---- copying what is visible --------------------------------------------
; "Visible cells" (the owner's call) is Excel's own name for this (Go To
; Special, "Visible cells only"), and it is true of everything
; SpecialCells skips: rows hidden by a filter or by "Hide row", and
; hidden columns too - "visible rows" would have said nothing about the
; columns. "only" marks that the hidden ones stay behind. When nothing in
; the range is visible, Excel's own "No cells were found." is the
; refusal, at this sentence's step.
(defmacro
    (copy-visible-cells a b)
    "copy only a range's visible cells to another place - rows and columns hidden by a filter or by hand stay behind"
    (. (. a specialcells xlcelltypevisible) copy :destination b))

(english-vla
    "copy only visible cells of [range] {a:range} to [cell] {b:cell}"
    (copy-visible-cells (range {a}) (range {b})))
(test-success
    "Copy only the visible cells of range A1:D50 to F1."
    (copy-visible-cells (range "a1:d50") (range "f1")))
(test-success
    "Copy only the visible cells of A1:D50 to cell F1."
    (copy-visible-cells (range "a1:d50") (range "f1")))

; ---- G-TEXT slice 1: text changed in place (pareto section 12) ----------
; BETA_REARVIEW.md's G-TEXT entry. pareto.txt section 12 has sixteen
; entries; this slice is the five that change a range's text where it
; stands (trim-spaces, upper-case, lower-case, proper-case, clean-text),
; each with a column twin. Scoped 2026-09-25 against what already
; shipped, with four forks the owner decided, each named where its rules
; sit.
;
; What already shipped, and keeps its meaning (SD-4): the text of ONE
; value, into a variable - "Set v to trimmed ...", "first|last {n}
; letters of", "position of ... in", "... with ... replaced by ...",
; "uppercase|lowercase|length of", and the conditions "contains",
; "starts with" and "ends with" (the core's). pareto's text-before/-after,
; characters, pad and join are the next slice's, over the same values.
; A CO.7 note, found here and not changed here: "trimmed" is VBA's Trim,
; which trims the two ends only, where "Remove extra spaces from" below
; is Excel's TRIM - a speaker who knows =TRIM() will expect "trimmed" to
; close up the inside of the text too.
;
; Every sentence here changes TEXT and nothing else: formulas, numbers,
; dates, TRUE/FALSE, errors and blank cells are left as they are, and a
; cell that held text still holds text afterwards, so "00123" keeps its
; zeros and "TRUE" stays a word (the owner's call). A column means the
; column's used cells, its header included. All of it is one runtime
; helper, VLA_Runtime.VlaTextInRange, whose header says how.

; ---- upper and lower case ----------------------------------------------
; pareto's "upper case" and "lower case", and the one-word spellings Word
; and Excel's own help use; both reach the same macro, "{k}case" gluing
; the matched word back into one name.
(defmacro
    (make-uppercase r)
    "turn the text in a range into capital letters - numbers, dates and formulas stay as they are"
    (vlatextinrange r "upper"))

(defmacro
    (make-lowercase r)
    "turn the text in a range into small letters - numbers, dates and formulas stay as they are"
    (vlatextinrange r "lower"))

(english-vla
    "make cell|range {r:range} {k:upper|lower} case"
    (make-{k}case (range {r})))
(test-success
    "Make range A2:A50 upper case."
    (make-uppercase (range "a2:a50")))
(test-success
    "Make cell B2 lower case."
    (make-lowercase (range "b2")))

(english-vla
    "make cell|range {r:range} {k:uppercase|lowercase}"
    (make-{k} (range {r})))
(test-success
    "Make range A2:A50 uppercase."
    (make-uppercase (range "a2:a50")))
(test-success
    "Make range A2:C9 lowercase."
    (make-lowercase (range "a2:c9")))

(english-vla
    "make column {c:column} {k:upper|lower} case"
    (make-{k}case (columns {c})))
(test-success
    "Make column B upper case."
    (make-uppercase (columns "b")))

(english-vla
    "make column {c:column} {k:uppercase|lowercase}"
    (make-{k} (columns {c})))
(test-success
    "Make column C lowercase."
    (make-lowercase (columns "c")))

; ---- capitalizing each word ----------------------------------------------
; Two rules are in use for where a word starts, and the sentence names
; the one it means (the owner's call, over pareto's bare "Capitalize
; range", which some readers hear as ALL CAPITALS, and over a default
; that would hide the choice):
;   "after any non-letter" - Excel's PROPER: "o'neil" is "O'Neil",
;     "smith-jones" is "Smith-Jones", and "don't" is "Don'T".
;   "after any space" - a capital only right after a space, a tab or a
;     line break: "don't" is "Don't", "o'neil" is "O'neil".
; Either way every other letter is made small. A sentence naming neither
; refuses, and the refusal shows both.
(defmacro
    (capitalize-after-space r)
    "capitalize each word in a range's text, a word starting after a space - other letters made small"
    (vlatextinrange r "capitalize-after-space"))

(defmacro
    (capitalize-after-non-letter r)
    "capitalize each word in a range's text, a word starting after any character that is not a letter (Excel's PROPER) - other letters made small"
    (vlatextinrange r "capitalize-after-non-letter"))

(english-vla
    "capitalize each word in cell|range {r:range} after any {k:space|non-letter}"
    (capitalize-after-{k} (range {r})))
(test-success
    "Capitalize each word in range A2:A50 after any space."
    (capitalize-after-space (range "a2:a50")))
(test-success
    "Capitalize each word in cell B2 after any non-letter."
    (capitalize-after-non-letter (range "b2")))
(test-fail
    "Capitalize each word in range A2:A50."
    "then I expected 'after'")

(english-vla
    "capitalize each word in column {c:column} after any {k:space|non-letter}"
    (capitalize-after-{k} (columns {c})))
(test-success
    "Capitalize each word in column B after any non-letter."
    (capitalize-after-non-letter (columns "b")))

; ---- extra spaces and non-printing characters --------------------------
; "Remove extra spaces from" is Excel's TRIM - both ends, and every run
; inside closed up to one space - with a non-breaking space (CHAR(160),
; common in text pasted from the web or exported from other systems)
; counted as a space, since it looks like one (the owner's call). "Remove
; non-printing characters from" is Excel's CLEAN: characters 0 to 31,
; tabs and line breaks inside a cell among them.
(defmacro
    (remove-extra-spaces r)
    "remove spaces from both ends of a range's text and close up every run inside it to one space (Excel's TRIM, non-breaking spaces counted)"
    (vlatextinrange r "remove-extra-spaces"))

(defmacro
    (remove-non-printing-characters r)
    "remove tabs, line breaks and the other characters 0 to 31 from a range's text (Excel's CLEAN)"
    (vlatextinrange r "remove-non-printing"))

(english-vla
    "remove extra spaces from cell|range {r:range}"
    (remove-extra-spaces (range {r})))
(test-success
    "Remove extra spaces from range A2:A50."
    (remove-extra-spaces (range "a2:a50")))
(test-success
    "Remove extra spaces from cell B2."
    (remove-extra-spaces (range "b2")))

(english-vla
    "remove extra spaces from column {c:column}"
    (remove-extra-spaces (columns {c})))
(test-success
    "Remove extra spaces from column A."
    (remove-extra-spaces (columns "a")))

(english-vla
    "remove non-printing characters from cell|range {r:range}"
    (remove-non-printing-characters (range {r})))
(test-success
    "Remove non-printing characters from range A2:A50."
    (remove-non-printing-characters (range "a2:a50")))

(english-vla
    "remove non-printing characters from column {c:column}"
    (remove-non-printing-characters (columns {c})))
(test-success
    "Remove non-printing characters from column D."
    (remove-non-printing-characters (columns "d")))

; ---- G-TEXT slice 2: text in a variable (pareto section 12) -------------
; The rest of section 12 that makes a value rather than changing cells:
; text-before, text-after, first- and last-characters, pad-left, and
; join-range, plus the value form of every slice-1 change (SD-19: a
; speaker who can tidy a column expects to tidy the text in hand the
; same way). Four forks the owner decided (2026-09-25), each named where
; its rules sit. Each value reads as text the one way VLA_Runtime's
; TextOfValue says: a number as this machine writes it, a date as its
; short date, TRUE/FALSE as Excel shows them, one cell as its value; a
; list, several cells or an error value refuse by name.

; ---- the characters of a text --------------------------------------------
; pareto's own word. The shipped "first|last {n} letters of" stay (SD-4),
; but a code's characters are not all letters, so "characters" is the
; spelling that says what is counted.
(english-vla
    "set {v:var} to first {n:expr} characters of {t:expr}"
    (set! {v} (left {t} {n})))
(test-success
    "Set prefix to the first 3 characters of code."
    (set! prefix (left code 3)))

(english-vla
    "set {v:var} to last {n:expr} characters of {t:expr}"
    (set! {v} (right {t} {n})))
(test-success
    "Set suffix to the last 2 characters of code."
    (set! suffix (right code 2)))

; ---- the text before or after a marker -----------------------------------
; Excel's TEXTBEFORE and TEXTAFTER. The marker is found in any case,
; exactly as "If code contains ..." finds it, so the two can never
; disagree; and a marker that is not there refuses by name, as Excel's
; #N/A does, instead of letting a whole text or an empty one run on
; (both the owner's calls). A program expecting gaps asks "If code
; contains ..." first. "the last" searches from the end - a file's
; extension, a person's last name.
(defmacro
    (text-before t a)
    "the text before the first place a marker appears in it, found in any case - refuses when the marker is not there"
    (vlatextbeside t a "before" "first"))

(defmacro
    (text-after t a)
    "the text after the first place a marker appears in it, found in any case - refuses when the marker is not there"
    (vlatextbeside t a "after" "first"))

(defmacro
    (text-before-last t a)
    "the text before the last place a marker appears in it, found in any case - refuses when the marker is not there"
    (vlatextbeside t a "before" "last"))

(defmacro
    (text-after-last t a)
    "the text after the last place a marker appears in it, found in any case - refuses when the marker is not there"
    (vlatextbeside t a "after" "last"))

(english-vla
    "set {v:var} to text {d:before|after} {a:expr} in {t:expr}"
    (set! {v} (text-{d} {t} {a})))
(test-success
    "Set part to the text before \"-\" in code."
    (set! part (text-before code "-")))
(test-success
    "Set rest to the text after \": \" in cell B2."
    (set! rest (text-after (range "b2") ": ")))

(english-vla
    "set {v:var} to text {d:before|after} last {a:expr} in {t:expr}"
    (set! {v} (text-{d}-last {t} {a})))
(test-success
    "Set extension to the text after the last \".\" in file-name."
    (set! extension (text-after-last file-name ".")))
(test-success
    "Set folder to the text before the last \"/\" in file-path."
    (set! folder (text-before-last file-path "/")))

; ---- padding ---------------------------------------------------------------
; The side and the character are both named in the sentence (the
; owner's call): 42 padded on the left with "0" to 5 characters is
; "00042". Text already that long is never cut, a pad that is not one
; character refuses, and so does a width that is not a whole number.
(defmacro
    (pad-left t c n)
    "a text lengthened to a number of characters by adding one character on the left - never cut when already that long"
    (vlatextpad t c n "left"))

(defmacro
    (pad-right t c n)
    "a text lengthened to a number of characters by adding one character on the right - never cut when already that long"
    (vlatextpad t c n "right"))

(english-vla
    "set {v:var} to {t:expr} padded on {d:left|right} with {c:expr} to {n:expr} characters"
    (set! {v} (pad-{d} {t} {c} {n})))
(test-success
    "Set code to id padded on the left with \"0\" to 5 characters."
    (set! code (pad-left id "0" 5)))
(test-success
    "Set label to name padded on the right with \" \" to 20 characters."
    (set! label (pad-right name " " 20)))

; ---- a range as one list ---------------------------------------------------
; G-PROLOG slice 5's own words for a list in one cell, so the phrase
; means one thing (the owner's call over pareto's "joined with", which
; already glues two texts together): ", " between items unless the
; sentence names another, row by row, blank cells skipped, and each
; cell's value as text - not its displayed text, which shows "####" in
; a narrow column. A column means its used cells, its header included.
; A cell holding an error refuses, naming the cell.
(defmacro
    (as-one-list r sep)
    "the values of a range's cells as one text, row by row, blank cells skipped, with a separator between them"
    (vlajoinrange r sep))

(english-vla
    "set {v:var} to range {r:range} as one list"
    (set! {v} (as-one-list (range {r}) ", ")))
(test-success
    "Set names to range A2:A9 as one list."
    (set! names (as-one-list (range "a2:a9") ", ")))

(english-vla
    "set {v:var} to range {r:range} as one list separated by {s:expr}"
    (set! {v} (as-one-list (range {r}) {s})))
(test-success
    "Set names to range A2:A9 as one list separated by \"; \"."
    (set! names (as-one-list (range "a2:a9") "; ")))

(english-vla
    "set {v:var} to column {c:column} as one list"
    (set! {v} (as-one-list (columns {c}) ", ")))
(test-success
    "Set regions to column C as one list."
    (set! regions (as-one-list (columns "c") ", ")))

(english-vla
    "set {v:var} to column {c:column} as one list separated by {s:expr}"
    (set! {v} (as-one-list (columns {c}) {s})))
(test-success
    "Set regions to column C as one list separated by \" / \"."
    (set! regions (as-one-list (columns "c") " / ")))

; ---- slice 1's changes, made to a value ----------------------------------
; The same changes the cell sentences make, with the same rules, into a
; variable - so a loop can tidy one value at a time. "Remove extra
; spaces" is Excel's TRIM with non-breaking spaces counted, which the
; shipped "trimmed" (VBA's Trim, the two ends only) is not; CO.7 holds
; that note.
(defmacro
    (without-extra-spaces t)
    "a text with spaces removed from both ends and every run inside closed up to one (Excel's TRIM, non-breaking spaces counted)"
    (vlatextop t "remove-extra-spaces"))

(defmacro
    (without-non-printing-characters t)
    "a text with tabs, line breaks and the other characters 0 to 31 removed (Excel's CLEAN)"
    (vlatextop t "remove-non-printing"))

(defmacro
    (capitalized-after-space t)
    "a text with each word capitalized, a word starting after a space - other letters made small"
    (vlatextop t "capitalize-after-space"))

(defmacro
    (capitalized-after-non-letter t)
    "a text with each word capitalized, a word starting after any character that is not a letter (Excel's PROPER) - other letters made small"
    (vlatextop t "capitalize-after-non-letter"))

(english-vla
    "set {v:var} to {t:expr} with extra spaces removed"
    (set! {v} (without-extra-spaces {t})))
(test-success
    "Set tidy to raw-name with extra spaces removed."
    (set! tidy (without-extra-spaces raw-name)))

(english-vla
    "set {v:var} to {t:expr} with non-printing characters removed"
    (set! {v} (without-non-printing-characters {t})))
(test-success
    "Set tidy to cell B2 with non-printing characters removed."
    (set! tidy (without-non-printing-characters (range "b2"))))

(english-vla
    "set {v:var} to {t:expr} with each word capitalized after any {k:space|non-letter}"
    (set! {v} (capitalized-after-{k} {t})))
(test-success
    "Set proper to raw-name with each word capitalized after any non-letter."
    (set! proper (capitalized-after-non-letter raw-name)))
(test-fail
    "Set proper to raw-name with each word capitalized."
    "then I expected 'after'")

; ---- G-TEXT slice 3: find and replace, and split (pareto 12 and 13) ------
; The rest of both sections: replace on a whole sheet and in formulas,
; split-column, find-cell, count-occurrences, and "contains" over a range
; (a condition, so it lives in the core's condition grammar, not here).
; Already shipped and kept: "Replace ... in range|column" (now values
; only, see replace-in above) and "row of ... in column". Four forks the
; owner decided (2026-09-28), each named where its rules sit. Text is
; found as "If code contains ..." finds it - in any case, and a "*" or a
; "?" is itself, never Excel's wildcard.

; ---- replace: the whole sheet, and formulas ------------------------------
; The shipped sentence changes values, so formulas have a sentence of
; their own that says so (the owner's call): Excel's own Replace, on the
; formula cells only. "this sheet" means the sheet the program is on.
(defmacro
    (replace-in-formulas a b target)
    "find and replace text inside the formulas of a range or column - a match anywhere in a formula, in any case; values are left alone"
    (vlareplaceinrange target a b "formulas"))

(english-vla
    "replace {a:expr} with {b:expr} on this sheet"
    (replace-in {a} {b} (used-range)))
(test-success
    "Replace \"N/A\" with 0 on this sheet."
    (replace-in "N/A" 0 (used-range)))

(english-vla
    "replace {a:expr} with {b:expr} in formulas of range {r:range}"
    (replace-in-formulas {a} {b} (range {r})))
(test-success
    "Replace \"Sheet1\" with \"Data\" in the formulas of range B2:B20."
    (replace-in-formulas "Sheet1" "Data" (range "b2:b20")))

(english-vla
    "replace {a:expr} with {b:expr} in formulas of column {c:column}"
    (replace-in-formulas {a} {b} (columns {c})))
(test-success
    "Replace \"SUM\" with \"AVERAGE\" in the formulas of column D."
    (replace-in-formulas "SUM" "AVERAGE" (columns "d")))

(english-vla
    "replace {a:expr} with {b:expr} in formulas on this sheet"
    (replace-in-formulas {a} {b} (used-range)))
(test-success
    "Replace \"2025\" with \"2026\" in the formulas on this sheet."
    (replace-in-formulas "2025" "2026" (used-range)))

; ---- split a column --------------------------------------------------------
; Excel's Text to Columns, with the owner's two calls. It never writes
; over anything: when a cell the pieces need holds something, nothing
; changes and the refusal names the cell. And the sentence says what the
; pieces are - "as text" keeps each piece text ("0042" keeps its zeros),
; "reading numbers" makes a plain number a number ("42", "-3.5"; never
; "0042", "3/4" or "1E5") - and a date is never made. A sentence naming
; neither refuses at Check. Only text is split; the pieces keep their
; spaces.
(defmacro
    (split-column-as-text c d)
    "split each text cell of a column at a separator, the pieces going into the empty columns to its right, each kept as text - never writing over a cell"
    (vlasplitcolumn c d "text"))

(defmacro
    (split-column-reading-numbers c d)
    "split each text cell of a column at a separator, the pieces going into the empty columns to its right, a plain number read as a number and the rest kept as text - never writing over a cell"
    (vlasplitcolumn c d "numbers"))

(english-vla
    "split column {c:column} by {d:expr} as text"
    (split-column-as-text (columns {c}) {d}))
(test-success
    "Split column C by \",\" as text."
    (split-column-as-text (columns "c") ","))

(english-vla
    "split column {c:column} by {d:expr} reading numbers"
    (split-column-reading-numbers (columns {c}) {d}))
(test-success
    "Split column A by \" - \" reading numbers."
    (split-column-reading-numbers (columns "a") " - "))
(test-fail
    "Split column C by \",\"."
    "then I expected 'as'")

; ---- find ------------------------------------------------------------------
; The owner's call: a search hands back a row or a column number, which
; the "column number ... row ..." sentences can use, and 0 when nothing
; matches - as the shipped "row of ... in column" does. pareto's "the
; first cell" could not be kept in a variable: a variable set to a cell
; holds the cell's value. The search reads each cell's value as text,
; row by row, blank cells and errors skipped. "If range A1:D50 contains
; "x"" asks first.
(defmacro
    (row-of-first-containing r t)
    "the row of the first cell of a range, row by row, whose value contains a text in any case - 0 when none does"
    (vlafindtext r t "row"))

(defmacro
    (column-of-first-containing r t)
    "the column number of the first cell of a range, row by row, whose value contains a text in any case - 0 when none does"
    (vlafindtext r t "column"))

(english-vla
    "set {v:var} to row of first cell in range {r:range} containing {t:expr}"
    (set! {v} (row-of-first-containing (range {r}) {t})))
(test-success
    "Set r to the row of the first cell in range A1:D50 containing \"total\"."
    (set! r (row-of-first-containing (range "a1:d50") "total")))

(english-vla
    "set {v:var} to column of first cell in range {r:range} containing {t:expr}"
    (set! {v} (column-of-first-containing (range {r}) {t})))
(test-success
    "Set k to the column of the first cell in range A1:D50 containing \"total\"."
    (set! k (column-of-first-containing (range "a1:d50") "total")))

(english-vla
    "set {v:var} to row of first cell in column {c:column} containing {t:expr}"
    (set! {v} (row-of-first-containing (columns {c}) {t})))
(test-success
    "Set r to the row of the first cell in column B containing code."
    (set! r (row-of-first-containing (columns "b") code)))

; ---- count -----------------------------------------------------------------
; How many cells hold a text somewhere in their value - the same match as
; find, counted. Not the shipped "count of range ... matching", which
; counts whole cells equal to a value.
(defmacro
    (count-containing r t)
    "how many cells of a range have a value containing a text, in any case - blank cells and errors not counted"
    (vlacounttext r t))

(english-vla
    "set {v:var} to how many cells in range {r:range} contain {t:expr}"
    (set! {v} (count-containing (range {r}) {t})))
(test-success
    "Set n to how many cells in range A2:A99 contain \"late\"."
    (set! n (count-containing (range "a2:a99") "late")))

(english-vla
    "set {v:var} to how many cells in column {c:column} contain {t:expr}"
    (set! {v} (count-containing (columns {c}) {t})))
(test-success
    "Set n to how many cells in column C contain \"*\"."
    (set! n (count-containing (columns "c") "*")))

; ---- G11: the template face --------------------------------------
; Friendly functors hiding the dot (VBA member access) from template
; authors. The dot stays first-class in VLA - these are carried
; macros, one expansion deep, each probe-validated at load. Where a
; rule binds a style word ({d:bold|italic}), the ENGLISH layer
; computes the macro NAME by text substitution - (make-{d} ...)
; becomes (make-bold ...) before the reader ever sees it, the same
; move as xl{d}. Emitted VBA is unchanged to the byte.
(defmacro
    (clear-contents r)
    "erase a range's values, keep its formatting"
    (. r clearcontents))

(defmacro
    (fill-down r)
    "copy the top row of a range down through it"
    (. r filldown))

(defmacro
    (fill-right r)
    "copy the left column of a range right through it"
    (. r fillright))

(defmacro
    (make-bold r)
    "turn a range's font bold"
    (set! (. r font.bold) true))

(defmacro
    (make-italic r)
    "turn a range's font italic"
    (set! (. r font.italic) true))

; ANTONYM-SWEEP: method-antonym-family is bool-antonym-family's twin
; (defined near hide-column, scripts/english.vla) for the shape a
; boolean flip doesn't fit - the antonym pair IS two distinct method
; names on one target, not one property's two values. protect-sheet/
; unprotect-sheet below stay hand-written on purpose, table-to-range's
; own precedent (BETA_REARVIEW.md's TABLE-FAMILY entry): their shape
; adds a keyword-argument tail (:password pw) AND an extra parameter -
; neither family can splice into a signature and a call site with two
; different subsets of the same rest-bound name at once without real
; new complexity, so this stays a genuine exception, not a shortcut
; forced onto a shape that doesn't fit it.
(defmacro
    (method-antonym-family name param target pos-method neg-method doc-pos doc-neg)
    "generate a method-call antonym pair sharing one target-and-method shape, no extra arguments"
    (begin (defmacro
               (name param)
               doc-pos
               (. target pos-method))

           (defmacro
               ((symbol "un" name) param)
               doc-neg
               (. target neg-method))))

(method-antonym-family
    merge-range
    r
    r
    merge
    unmerge
    "merge a range into one cell"
    "split a merged range back into cells")
(defmacro
    (protect-sheet ws pw)
    "protect a worksheet with a password"
    (. ws protect :password pw))

(defmacro
    (set-alignment r a)
    "set a range's horizontal alignment (xl constant)"
    (set! (. r horizontalalignment) a))

(defmacro
    (set-column-width c w)
    "set a column's width in characters"
    (set! (. c columnwidth) w))

(defmacro
    (set-fill-color r c)
    "set a range's background fill color"
    (set! (. r interior.color) c))

(defmacro
    (set-font-color r c)
    "set a range's font color"
    (set! (. r font.color) c))

(defmacro
    (set-font-size r n)
    "set a range's font size in points"
    (set! (. r font.size) n))

(defmacro
    (unprotect-sheet ws pw)
    "remove a worksheet's password protection"
    (. ws unprotect :password pw))

; ---- F.1: the grammar/emitter ABI --------------------------------------
; The dot count. Every rule below used to reach the object model
; directly; each now targets one of these named verbs instead, so a
; second backend (IN.2) has a table of verbs to implement rather than
; a phrasebook of raw dot-forms to reverse-engineer one at a time.
; Behavior-preserving by construction: each macro's body is the exact
; dot-form the rule used to emit inline, so expansion produces
; byte-identical VBA - this is why the golden diff for this pass is
; expected to be non-empty ONLY in the .vla file (template text
; changed) and empty in the .vba file (emitted code did not).
(defmacro
    (copy-range-to a b)
    "copy a range's cells into another range"
    (. a copy :destination b))

(defmacro
    (paste-values a b)
    "copy a range's values only - no formatting or formulas - into another range"
    (begin (. a copy) (. b pastespecial :paste xlpastevalues) (set! application.cutcopymode false)))

(defmacro
    (clear-color r)
    "remove a range's fill color"
    (set! (. r interior.colorindex) xlnone))

(defmacro
    (format-as-currency r)
    "apply a currency number-format to a range"
    (set! (. r numberformat) "$#,##0.00"))

(defmacro
    (format-as-percent r)
    "apply a percent number-format to a range"
    (set! (. r numberformat) "0.0%"))

(defmacro
    (format-as-date r)
    "apply a date number-format to a range"
    (set! (. r numberformat) "mm/dd/yyyy"))

(defmacro
    (add-border r)
    "add a continuous-line border around a range"
    (set! (. r borders.linestyle) xlcontinuous))

(defmacro
    (delete-row n)
    "delete a whole row"
    (. (rows n) delete))

(defmacro
    (insert-row-at n)
    "insert a blank row"
    (. (rows n) insert))

(defmacro
    (delete-column c)
    "delete a whole column"
    (. (columns c) delete))

; ANTONYM-SWEEP: bool-antonym-family generates a boolean-property
; on/off macro pair from one shared target-and-property shape - `name`
; sets `property` true on `target`; `(symbol "un" name)` sets it false.
; `target` is a whole caller-supplied FORM (e.g. (columns c) below, or
; the bare param itself with no wrapper call at all, as wrap-text
; further down needs) - substituted as one whole value, unexamined,
; the same way TABLE-FAMILY's own `call`/`testcall` arguments already
; work; `param` is written by the caller in two places (once naming
; the generated macro's own parameter, once inside `target`
; referencing it) by convention, not magic - Substitute never touches
; the symbol inside an already-whole-value-substituted form a second
; time. Every generated macro is therefore byte-for-byte what the
; hand-written pair used to emit - the english-vla rule and
; test-success proofs for each antonym pair stay exactly where they
; already were, untouched: they were ALREADY maximally compressed via
; G11r's own {d:hide|unhide}-style glued-identifier dispatch
; (({d}-column {c}) below), which reaches whichever macro name results
; regardless of whether it was hand-written or generated - only the
; macro PAIR itself was hand-duplicated, and that's what this
; generator removes. Used again below for wrap-text/unwrap-text and
; hide-row/unhide-row.
(defmacro
    (bool-antonym-family name param target property doc-pos doc-neg)
    "generate a boolean-property on/off macro pair sharing one target-and-property shape"
    (begin (defmacro
               (name param)
               doc-pos
               (set! (. target property) true))

           (defmacro
               ((symbol "un" name) param)
               doc-neg
               (set! (. target property) false))))

(bool-antonym-family hide-column c (columns c) hidden "hide a column" "show a hidden column")
(defmacro
    (insert-column c)
    "insert a blank column"
    (. (columns c) insert))

(defmacro
    (fit-column c)
    "autofit a column's width to its contents"
    (. (columns c) autofit))

(defmacro
    (fit-all-columns)
    "autofit every column's width to its contents"
    (. cells entirecolumn.autofit))

(defmacro
    (freeze-top-row)
    "freeze the sheet's header row"
    (begin (. (rows 2) select) (set! activewindow.freezepanes true)))

(defmacro
    (unfreeze-panes)
    "remove any frozen rows or columns"
    (set! (. activewindow freezepanes) false))

(defmacro
    (sort-range-by r k order)
    "sort a range by one column"
    (. r sort :key1 k :order1 order :header xlyes))

(defmacro
    (activate-sheet s)
    "make a sheet the active sheet - fails if it does not exist"
    (. (worksheets s) activate))

(defmacro
    (add-sheet-called s)
    "add a new sheet with a given name"
    (begin (vlachecksheetname s) (vlachecksheetabsent s) (worksheets.add) (set! activesheet.name s)))

(defmacro
    (delete-sheet s)
    "delete a sheet without the confirmation prompt - a sheet that is not there, Frazaro's own, or the only one showing is refused in words"
    (vladeletesheet s))

; L-SHEET-HELPERS (2026-10-07): the sheet helpers G-TABS's sentences will
; call, one macro each over VLA_Runtime's own (VlaAddSheetAt and the rest,
; its L-SHEET-HELPERS block). A position is always explicit: "after" or
; "before" a named sheet, or "last" with "" for the name; a bare sentence's
; default is G-TABS's to decide. Copy and Move always pass Before or After,
; since either alone makes a new workbook. The sheet that was active stays
; active (add-sheet-called above keeps activating its new sheet, as
; shipped). Frazaro's own sheets and very-hidden sheets are refused by
; name; hide is xlSheetHidden only, and very hidden is never offered.
(defmacro
    (add-sheet-at n w a)
    "add a new sheet with a given name after or before a named sheet, or last"
    (vlaaddsheetat n w a))

(defmacro
    (copy-sheet s w a)
    "copy a sheet to after or before a named sheet, or last - Excel names the copy, and the name comes back"
    (vlacopysheet s w a ""))

(defmacro
    (copy-sheet-named s n w a)
    "copy a sheet and call the copy by a name, to after or before a named sheet, or last"
    (vlacopysheet s w a n))

(defmacro
    (move-sheet s w a)
    "move a sheet to after or before a named sheet, or last"
    (vlamovesheet s w a))

(defmacro
    (rename-sheet s n)
    "give a sheet a new name"
    (vlarenamesheet s n))

(defmacro
    (hide-sheet s)
    "hide a sheet (show-sheet brings it back)"
    (vlahidesheet s))

(defmacro
    (show-sheet s)
    "show a hidden sheet"
    (vlashowsheet s))

(defmacro
    (clear-sheet s)
    "clear every cell of a sheet - values, formulas and formatting"
    (vlaclearsheet s))

(defmacro
    (sheet-exists s)
    "whether a sheet of that name is in this workbook"
    (vlasheetexists s))

(defmacro
    (sheet-count)
    "how many sheets this workbook has"
    (. worksheets count))

(defmacro
    (sheet-name)
    "the name of the sheet the program is working on"
    (. activesheet name))

(defmacro
    (open-workbook p)
    "open a workbook file"
    (. workbooks open p))

(defmacro
    (save-workbook-as p)
    "save this workbook to a new path"
    (. thisworkbook saveas p))

(defmacro
    (close-workbook)
    "close this workbook, saving changes"
    (. thisworkbook close :savechanges true))

(defmacro
    (refresh-everything)
    "refresh every data connection and pivot table"
    (. activeworkbook refreshall))

(defmacro
    (print-this-sheet)
    "open the print preview for this sheet"
    (. activesheet printout :preview true))

(defmacro
    (wait-seconds n)
    "pause for a number of seconds"
    (. application wait (+ (now) (timeserial 0 0 n))))

; ANTONYM-SWEEP: bool-antonym-family (scripts/english.vla, defined near
; hide-column above) - target is the bare param `r` itself, no wrapper
; call, unlike hide-column's (columns c).
(bool-antonym-family
    wrap-text
    r
    r
    wraptext
    "turn on a range's text wrapping"
    "turn off a range's text wrapping")
; G-TEXT (2026-09-25): a match anywhere in a cell, in any case - Excel's
; own starting settings, pinned, so no earlier Find or Replace (the
; dialog's, or "row of ... in column", a whole-cell Find) changes what
; the sentence means. G-TEXT slice 3 (2026-09-28, the owner's call):
; values only. A formula is never touched - Excel's Replace used to turn
; =A1*2 into =B1*2 - and a changed value that would begin with = is kept
; as text, never made a formula (SEC.4). "N/A" replaced by 0 is still the
; number 0. VLA_Runtime's VlaReplaceInRange has the whole account;
; "... in the formulas of ..." (replace-in-formulas) changes formulas.
(defmacro
    (replace-in a b target)
    "find and replace text in the values of a range or column - a match anywhere in a cell, in any case; formulas are left alone"
    (vlareplaceinrange target a b "values"))

(defmacro
    (clear-formatting r)
    "remove all formatting from a range, keeping its values"
    (. r clearformats))

(defmacro
    (set-row-height n h)
    "set a row's height in points"
    (set! (. (rows n) rowheight) h))

; ANTONYM-SWEEP: bool-antonym-family (scripts/english.vla, defined near
; hide-column above) - same shape as hide-column, (rows n) instead of
; (columns c).
(bool-antonym-family hide-row n (rows n) hidden "hide a row" "show a hidden row")
(defmacro
    (set-tab-color s c)
    "set a sheet tab's color"
    (set! (. (worksheets s) tab.color) c))

(defmacro
    (copy-range-to-sheet rExpr s rRef)
    "copy a range into the same address on another sheet"
    (. rExpr copy :destination (. (worksheets s) range rRef)))

(defmacro
    (remove-duplicates r cols)
    "remove duplicate rows from a range, keyed by one or more columns"
    (. r removeduplicates :columns cols :header xlyes))

(defmacro
    (name-range r n)
    "give a range a workbook-level name, usable in formulas"
    (begin (vlacheckrangename n) (set! (. r name) n)))

(defmacro
    (put-into-cell-of-sheet e r s)
    "write a value into a cell on another sheet"
    (set! (. (worksheets s) range r) e))

(defmacro
    (cell-of-sheet r s)
    "read a cell's value from another sheet"
    (. (worksheets s) range r))

(defmacro
    (filter-rows r field criteria)
    "hide every row of a range whose column doesn't match"
    (. r autofilter :field field :criteria1 criteria))

(defmacro
    (show-all-rows)
    "remove any active filter, showing every row again"
    (begin (on-error resume-next) (. activesheet showalldata) (on-error goto 0)))

(defmacro
    (last-filled-row c)
    "the row number of the last non-empty cell in a column"
    (. (. (cells rows.count c) end xlup) row))

(defmacro
    (export-sheet-as-pdf p)
    "export this sheet as a PDF file"
    (. activesheet exportasfixedformat :type xltypepdf :filename p))

(defmacro
    (save-copy-as p)
    "save a copy of this workbook without changing the open file"
    (. activeworkbook savecopyas p))

(defmacro
    (save-current-workbook)
    "save this workbook"
    (. thisworkbook save))

(defmacro
    (set-screen-updating on)
    "turn Excel's screen redraw on or off"
    (set! application.screenupdating on))

; G-FORMULA slice 3. set-calculation takes Excel's own constant, as
; with-fast-excel does; the interpreter resolves both. calculate is one
; member on two objects: the active sheet (Calculate Sheet) and Excel
; itself (Calculate Now, every open workbook).
(defmacro
    (set-calculation mode)
    "turn Excel's automatic calculation off (xlcalculationmanual) or on (xlcalculationautomatic)"
    (set! application.calculation mode))

(defmacro
    (recalculate-this-sheet)
    "recalculate the formulas on the sheet the program is on, from the values the other sheets show now"
    (. activesheet calculate))

(defmacro
    (recalculate-all-open-workbooks)
    "recalculate the formulas whose inputs changed, in every open workbook - Excel's F9"
    (. application calculate))

(defmacro
    (set-status-bar text)
    "show text in the status bar, or false to clear it"
    (set! application.statusbar text))

; F.1 residual: the seven WorksheetFunction calls (sum/average/max/min/
; vlookup/sumif/countif) - a distinct, lower-risk category than the
; above (computing a value, not mutating sheet/range state), left out
; of the main pass and closed here. Same rule as everywhere else in
; this block: each macro's body is the exact bare-dotted call the rule
; or function: word used to emit inline, so expansion is byte-
; identical VBA. Parameter order matches the calling rule's English
; sentence, not the WorksheetFunction signature (sum-of-where reorders
; internally, same move as put-into-cell-of-sheet above).
(defmacro
    (sum-of r)
    "the sum of a range or list of numbers"
    (application.worksheetfunction.sum r))

(defmacro
    (average-of r)
    "the average of a range or list of numbers"
    (application.worksheetfunction.average r))

(defmacro
    (largest-of r)
    "the largest value in a range or list"
    (application.worksheetfunction.max r))

(defmacro
    (smallest-of r)
    "the smallest value in a range or list"
    (application.worksheetfunction.min r))

(defmacro
    (lookup-of e r k)
    "look up a value in a range's first column, reading across to another column - fails if not found"
    (application.worksheetfunction.vlookup e r k false))

(defmacro
    (sum-of-where r c e)
    "the sum of one range's cells where a matching range meets a condition"
    (application.worksheetfunction.sumif c e r))

(defmacro
    (count-matching c e)
    "count a range's cells that match a condition"
    (application.worksheetfunction.countif c e))

; G-FORMULA slice 2: four more, each with its own native interpreter arm
; beside the seven above (VLA_Interpreter's EvalDynamicHead). The two
; standard deviations call Excel's current STDEV.S and STDEV.P, not the
; older STDEV and STDEVP, which Excel keeps for compatibility only; the
; numbers are the same. average-of-where hands AVERAGEIF its own order,
; as sum-of-where hands SUMIF its.
(defmacro
    (median-of r)
    "the median of a range's numbers - the middle one, or the average of the middle two"
    (application.worksheetfunction.median r))

(defmacro
    (sample-standard-deviation-of r)
    "the standard deviation of a range's numbers taken as a sample (Excel's STDEV.S)"
    (application.worksheetfunction.stdev_s r))

(defmacro
    (population-standard-deviation-of r)
    "the standard deviation of a range's numbers taken as the whole population (Excel's STDEV.P)"
    (application.worksheetfunction.stdev_p r))

(defmacro
    (average-of-where r c e)
    "the average of one range's cells where a matching range meets a condition - a blank value is skipped"
    (application.worksheetfunction.averageif c e r))

; ---- L4: a phrasebook-carried macro ----------------------------------
; macro: carries a (defmacro ...) form, validated at load (probe-
; transpiled through the engine's own gates) and APPENDED to every
; translation - bottom, not top, so the transpiler's ' vla:N source
; map never shifts when macros change (L4.2). The rule below emits
; the macro CALL; the transpiler expands it. NOTE: this block
; changes the .vla golden by exactly the appended defmacro at the
; tail - an intentional, reviewed contract change; the .vba golden
; must not move at all (defmacros emit nothing, no corpus sentence
; calls this one, and appending shifts no line numbers).
(defmacro
    (vla-stamp-cell r v)
    "write a value into a cell and make it bold - the phrasebook demo macro"
    (begin (set! (range r) v) (make-bold (range r))))

(english-vla
    "stamp {e:expr} into|in cell {r:cell}"
    (vla-stamp-cell {r} {e}))
(test-success
    "Stamp 5 into cell B2."
    (vla-stamp-cell "b2" 5))
(test-success
    "Stamp total in cell C3."
    (vla-stamp-cell "c3" total))

; ---- IN.7 (button-click half): interpreter-native UI ------------------
; make-button is NOT a macro to a dot-form like every rule above it -
; it is a native interpreter primitive (VLA_Interpreter.bas's own
; EvalDynamicHead, the same tier "range"/"cells" are dispatched at and
; for the same reason: no CallByName path reaches a Form Control
; button's .Add/.Name/.OnAction any more than it reaches Application's
; own parameterized default members). Check and Interpret both accept
; this rule; Compile/Compile and Trace now compile it for real too
; (compiled parity, a later pass - EmitStmt's own "make-button" case
; wires straight to the matching "When ... is clicked:" handler, no
; registry involved). The caption doubles as the button's reference
; name (IN.7's own naming decision) - see "When ... is clicked:" below.
; [called] is optional (G1's own bracket syntax) - "add sheet called
; {s:sheet}" above already reads that way, and an owner-typed sentence
; ("Make a button called ""Clicky"" at cell C3.") reached for the same
; shape by analogy and got a confusing near-miss instead: without the
; bracket, {c:text} greedily accepted the bare word "called" itself as
; the caption (a :text slot's own documented permissiveness, G2 above -
; nothing wrong with it in isolation), then failed one token later
; expecting "at" - a hard-to-diagnose error that named the wrong
; problem. Both phrasings now mean the same thing.
(english-vla
    "make a button [called] {c:text} at cell {r:cell}"
    (make-button {c} (range {r})))
(test-success
    "Make a button \"Recalculate Totals\" at cell D3."
    (make-button "Recalculate Totals" (range "d3")))
(test-success
    "Make a button called \"Recalculate Totals\" at cell D3."
    (make-button "Recalculate Totals" (range "d3")))

; ---- G4: negative proofs ---------------------------------------------
; fail: asserts a sentence REFUSES and that the refusal keeps its
; wording (fragment match, case- and whitespace-blind). These protect
; this file's own G1/G2 refusal decisions and two hardest-won engine
; refusals its sentences rely on; the load dies if any sentence below
; starts translating, or any message drifts.
(test-fail
    "Sort range banana by column B1."
    "a range (like")
(test-fail
    "Put 1 onto cell A2."
    "one of 'into'/'in'")
(test-fail
    "Fit column banana."
    "a column letter")
(test-fail
    "Increase total by 5 plus 5%."
    "mixes % into a longer amount")
(test-fail
    "Set label to don't."
    "I don't understand the character")
; LX.13: a word shaped like a cell is never a name - refused where a name
; is made and where a value is read; "cell A1" is how a cell is said.
(test-fail
    "Set A1 to 5."
    "'a1' is shaped like a cell")
(test-fail
    "Put A1 plus B1 into cell C1."
    "'a1' is shaped like a cell")


; ---- function words: expression-level vocabulary ----------------------
; V3: the walkable key list of a lookup (a plain Collection under
; either representation, insertion-ordered - deterministic walks).
(english-function
    "keys of"
    vladictkeys)
(english-function
    "largest of"
    largest-of)
(english-function
    "smallest of"
    smallest-of)
(english-function
    "sum of"
    sum-of)
(english-function
    "average of"
    average-of)

; LX.14: function phrases - a value said in several words. Each is matched
; where a value is read, longest first, so it binds before arithmetic and
; fits anywhere a value goes: "If median of revenues is more than 100, ...",
; "median of revenues plus 5". The words before the hole are fixed; the hole
; says what it takes - {x:value}, a value, or a reference, {r:range} or
; {c:column}; a closing clause may follow, with one choice of words. Once
; its words match, a phrase completes or is refused, saying what it
; expected. These carry the sentences of the fourteen figure rules over
; "range ..." and over a name that retired with them (G-FORMULA slices 1, 2
; and 4, and "set ... to last filled row of column ..."), each translated
; exactly as before; their proofs are here now. "median of" is a one-word
; head, so a program's own "To median of ...:" masks it, with a note at
; Check; the phrases after it are the phrasebook's in every program.
(english-function
    "median of"
    median-of)
(english-function
    "median of range {r:range}"
    (median-of (range {r})))
(english-function
    "standard deviation of {x:value} as {k:sample|population}"
    ({k}-standard-deviation-of {x}))
(english-function
    "standard deviation of range {r:range} as {k:sample|population}"
    ({k}-standard-deviation-of (range {r})))
(english-function
    "sum of range {r:range}"
    (sum-of (range {r})))
(english-function
    "average of range {r:range}"
    (average-of (range {r})))
(english-function
    "largest of range {r:range}"
    (largest-of (range {r})))
(english-function
    "smallest of range {r:range}"
    (smallest-of (range {r})))
(english-function
    "last filled row of column {c:column}"
    (last-filled-row {c}))
(test-success
    "Set middle to median of revenues."
    (set! middle (median-of revenues)))
(test-success
    "Set middle to median of units-sold."
    (set! middle (median-of units-sold)))
(test-success
    "Set middle to median of revenues plus 5."
    (set! middle (+ (median-of revenues) 5)))
(test-success
    "If median of revenues is more than 100, show \"high\"."
    (if (> (median-of revenues) 100) (then (msgbox "high"))))
(test-success
    "Put median of revenues into cell B54."
    (set! (range "b54") (median-of revenues)))
(test-success
    "Put median of units-sold in cell B54."
    (set! (range "b54") (median-of units-sold)))
(test-success
    "Set middle to median of range B2:B50."
    (set! middle (median-of (range "b2:b50"))))
(test-success
    "Set middle to median of range \"B2:B50\"."
    (set! middle (median-of (range "B2:B50"))))
(test-success
    "Put median of range B2:B50 into cell B54."
    (set! (range "b54") (median-of (range "b2:b50"))))
(test-success
    "Put median of range B2:B50 in cell B54."
    (set! (range "b54") (median-of (range "b2:b50"))))
(test-fail
    "Set middle to median of range."
    "then I expected a range")
(test-success
    "Set spread to standard deviation of revenues as a sample."
    (set! spread (sample-standard-deviation-of revenues)))
(test-success
    "Set spread to standard deviation of revenues as the population."
    (set! spread (population-standard-deviation-of revenues)))
(test-success
    "Put standard deviation of revenues as a sample in cell B55."
    (set! (range "b55") (sample-standard-deviation-of revenues)))
(test-success
    "Put standard deviation of revenues as the population into cell B55."
    (set! (range "b55") (population-standard-deviation-of revenues)))
(test-fail
    "Set spread to standard deviation of revenues."
    "then I expected 'as'")
(test-fail
    "Put standard deviation of revenues into cell B55."
    "then I expected 'as'")
(test-success
    "Set spread to standard deviation of range B2:B50 as a sample."
    (set! spread (sample-standard-deviation-of (range "b2:b50"))))
(test-success
    "Set spread to standard deviation of range B2:B50 as the population."
    (set! spread (population-standard-deviation-of (range "b2:b50"))))
(test-success
    "Show standard deviation of range B2:B50 as a sample."
    (msgbox (sample-standard-deviation-of (range "b2:b50"))))
(test-success
    "Put standard deviation of range B2:B50 as a sample in cell B55."
    (set! (range "b55") (sample-standard-deviation-of (range "b2:b50"))))
(test-success
    "Put standard deviation of range B2:B50 as the population into cell B55."
    (set! (range "b55") (population-standard-deviation-of (range "b2:b50"))))
(test-fail
    "Set spread to standard deviation of range B2:B50."
    "then I expected 'as'")
(test-fail
    "Put standard deviation of range B2:B50 into cell B55."
    "then I expected 'as'")
(test-success
    "Set grand to sum of range \"B2:B3\"."
    (set! grand (sum-of (range "B2:B3"))))
(test-success
    "Put sum of range \"B2:B3\" into cell B4."
    (set! (range "b4") (sum-of (range "B2:B3"))))
(test-success
    "Put sum of range \"B2:B3\" in cell B5."
    (set! (range "b5") (sum-of (range "B2:B3"))))
(test-success
    "Set avg to average of range \"A1:A9\"."
    (set! avg (average-of (range "A1:A9"))))
(test-success
    "Put average of range B2:B50 into cell B51."
    (set! (range "b51") (average-of (range "b2:b50"))))
(test-success
    "Set top to largest of range B2:B50."
    (set! top (largest-of (range "b2:b50"))))
(test-success
    "Put largest of range B2:B50 into cell B52."
    (set! (range "b52") (largest-of (range "b2:b50"))))
(test-success
    "Set bottom to smallest of range B2:B50."
    (set! bottom (smallest-of (range "b2:b50"))))
(test-success
    "Put smallest of range B2:B50 in cell B53."
    (set! (range "b53") (smallest-of (range "b2:b50"))))
(test-success
    "Set gap to largest of range B2:B50 minus smallest of range B2:B50."
    (set! gap (- (largest-of (range "b2:b50")) (smallest-of (range "b2:b50")))))
(test-success
    "Set last-row to last filled row of column A."
    (set! last-row (last-filled-row "a")))
(test-success
    "Set next-row to last filled row of column B plus 1."
    (set! next-row (+ (last-filled-row "b") 1)))

; count of moved into the core language in B1: it is a built-in word
; backed by VlaCount, which answers lists (Collections) as well as
; ranges (where it still counts numbers, exactly as before). Do not
; re-add it here - a redefinition would silently break lists.

; ---- core-grammar regression tests (no rules: structural + prelude) ---
(test-success
    "Show total."
    (msgbox total))
(test-success
    "Show cell B2."
    (msgbox (range "b2")))
(test-success
    "Log total."
    (debug-print total))
(test-success
    "Log cell B2."
    (debug-print (range "b2")))
(test-success
    "Log \"step \" joined with counter."
    (debug-print (& "step " counter)))
(test-success
    "Set total to 0."
    (set! total 0))
(test-success
    "Increase total by 5."
    (add! total 5))
(test-success
    "Create a number called total."
    (dim total Double))
(test-success
    "If total is greater than 5, show \"big\"."
    (if (> total 5) (then (msgbox "big"))))
(test-success
    "If counter is divisible by 2, show \"even\"."
    (if (zero? (mod counter 2)) (then (msgbox "even"))))
(test-success
    "Repeat 2 times, show counter."
    (dotimes counter 2 (msgbox counter)))
(test-success
    "Count k from 2 to 9, show k."
    (for (k 2 9) (msgbox k)))
(test-success
    "Count k down from 9 to 2, show k."
    (for (k 9 2 -1) (msgbox k)))
(test-success
    "For each r in results, show r."
    (for-each (r results) (msgbox r)))
(test-success
    "While countdown is greater than 0, decrease countdown by 1."
    (while (> countdown 0) (set! countdown (- countdown 1))))
(test-success
    "If code contains \"x\", show \"hit\"."
    (if (positive? (instr 1 code "x" vbtextcompare)) (then (msgbox "hit"))))
(test-success
    "If code does not contain \"x\", show \"clean\"."
    (if (zero? (instr 1 code "x" vbtextcompare)) (then (msgbox "clean"))))
(test-success
    "If code starts with \"AB\", show \"ab\"."
    (if (= (instr 1 code "AB" vbtextcompare) 1) (then (msgbox "ab"))))
(test-success
    "If code ends with \"z\", show \"zed\"."
    (if (= (lcase (right code (len "z"))) (lcase "z")) (then (msgbox "zed"))))
(test-success
    "If cell B2 is empty, show \"blank\"."
    (if (blank? (range "b2")) (then (msgbox "blank"))))
(test-success
    "If cell in column C row k contains \"West\", make cell in column A row k bold."
    (if (positive? (instr 1 (cells k "c") "West" vbtextcompare)) (then (make-bold (cells k "a")))))
(test-success
    "Set v to cell B2 plus 1."
    (set! v (+ (range "b2") 1)))
(test-success
    "Repeat 9 times, stop the loop."
    (dotimes counter 9 (exit-for)))
(test-success
    "While total is greater than 0, stop the loop."
    (while (> total 0) (exit-do)))
(test-success
    "Count k from 1 to 5, stop the loop."
    (for (k 1 5) (exit-for)))
(test-success
    "Set n to length of code."
    (set! n (len code)))
(test-success
    "Set shout to uppercase of name-part."
    (set! shout (ucase name-part)))
(test-success
    "Set m to month of today."
    (set! m (month (date))))
(test-success
    "Set stamp-day to day of now."
    (set! stamp-day (day (now))))
(test-success
    "Set l to length of cell B2."
    (set! l (len (range "b2"))))
(test-success
    "If length of code is greater than 5, show \"long\"."
    (if (> (len code) 5) (then (msgbox "long"))))
(test-success
    "Put now into cell B1."
    (set! (range "b1") (now)))
(test-success
    "Set big to largest of data-cells."
    (set! big (largest-of data-cells)))
(test-success
    "Set small to smallest of data-cells."
    (set! small (smallest-of data-cells)))
(test-success
    "Set s to sum of results."
    (set! s (sum-of results)))
(test-success
    "Set c to count of results."
    (set! c (vlacount results)))
(test-success
    "Create a list called found-items."
    (begin (dim found-items Collection) (obj-set! found-items (new Collection))))
(test-success
    "Append 5 to found-items."
    (. found-items add 5))
(test-success
    "Append counter times 100 to found-items."
    (. found-items add (* counter 100)))
(test-success
    "Set n to count of found-items."
    (set! n (vlacount found-items)))
(test-success
    "For each f in found-items, log f."
    (for-each (f found-items) (debug-print f)))
(test-success
    "Repeat until countdown is 0, decrease countdown by 1."
    (do-until (= countdown 0) (set! countdown (- countdown 1))))
(test-success
    "Repeat until fuel is 5, stop the loop."
    (do-until (= fuel 5) (exit-do)))
(test-success
    "If cell in column number 3 row 2 is 5, log \"hit\"."
    (if (= (cells 2 3) 5) (then (debug-print "hit"))))
; The row expression is greedy - "row 2 plus 1" is row 3, matching the
; spoken reading "the cell in row two-plus-one". To add to the VALUE,
; name it first (Set v to ...; Increase v by 1.). Blessed after the
; golden asked; the lettered form has always bound the same way.
(test-success
    "Set x to cell in column number 3 row 2 plus 1."
    (set! x (cells (+ 2 1) 3)))
(test-success
    "If value in column number 3 row 2 is 5, log \"hit\"."
    (if (= (cells 2 3) 5) (then (debug-print "hit"))))
(test-success
    "Set v to value in cell B2."
    (set! v (range "b2")))
(test-success
    "Put 5 into cell Data!B2."
    (set! (range "data!b2") 5))
(test-success
    "Put 5 into cell 'Q1 Data'!B2."
    (set! (range "'Q1 Data'!B2") 5))
(test-success
    "Set x to 15%."
    (set! x (/ 15 100)))
(test-success
    "Set x to 10 percent."
    (set! x (/ 10 100)))
(test-success
    "Grow total by 10%."
    (set! total (* total (+ 1 (/ 10 100)))))
(test-success
    "Shrink total by 25%."
    (set! total (* total (- 1 (/ 25 100)))))
(test-success
    "Set x to first of found-items."
    (set! x (vlafirst found-items)))
(test-success
    "Set x to last of found-items."
    (set! x (vlalast found-items)))
(test-success
    "Set x to item 3 of found-items."
    (set! x (vlaitem found-items 3)))
(test-success
    "Set msg to \"total is \" joined with total."
    (set! msg (& "total is " total)))
(test-success
    "Tidy-up."
    (tidy-up))
(test-success
    "Stamp with row of 2 and value of \"beta\"."
    (stamp :row 2 :value "beta"))
(test-success
    "Fill with width of counter plus 1."
    (fill :width (+ counter 1)))

(english-vla
    "paint cell {r:cell} {e:expr}"
    (set-fill-color (range {r}) (vlacolor {e})))
(test-success
    "Paint cell A1 hot-pink."
    (set-fill-color (range "a1") (vlacolor hot-pink)))

; ---- G-DATALOG: sentence templates targeting DATALOG(rulesText, tables) --
; BETA_ROADMAP.md's own SQL.1 "Two refinements" text settled this whole
; query family's architecture already: these rows are RECOGNIZERS, not
; generators - an English sentence is matched and parsed INTO a target
; query representation (here, DATALOG's own S-expression program text,
; canonical and auditor-visible), never the reverse. Two ordinary
; {slot:type} pattern/template pairs below, nothing DCG-shaped.
;
; Two spellings, one template, G1's own precedent (Store's "at key" /
; "under" reused, one level up): the owner weighed a terser, query-like
; register ("every Name in Staffing with Salary over 80000") against the
; original's more conversational one ("every Staffing name whose salary
; is over 80000") and found neither objectively better. Rather than pick
; a winner, both patterns below compile through the SAME defmacro -
; datalog-filter-place is the one template; the two english-vla rows are
; its two spellings, exactly as "at|under [key]" is one rule rather than
; two for Store.
;
; Bare (unquoted) {..:text} words fold to lowercase in transit (this
; file's own header note) - which is exactly right here, never a loss:
; DATALOG's own predicate/column names are matched through the identical
; fold (VLA_Identity.Fold, DATALOG.5's own header), so a folded "Staffing"
; and the live Table's own ListObject name resolve identically.
; L-INTERPOLATE: was ten-plus alternating literal/variable pieces
; through bare "&" (legible only because that session hand-traced every
; piece character-by-character against the target string before
; trusting it) - now one named-hole template, read straight against the
; =DATALOG(...) shape it produces. alias and table each appear twice
; in the template; interpolate's own named holes give that back for
; free (each value supplied once, referenced as often as the template
; needs) - the exact reason this primitive uses named holes rather
; than positional {0}/{1}. Named "interpolate", not CL's "format" -
; this file's own format-as-currency/-percent/-date (above) already
; own "format" for visual cell styling.
(defmacro
    (datalog-filter-place table shown filtercol op val alias r)
    "write a =DATALOG(...) named-column filter query as a live formula"
    (set-formula r
                 (interpolate "=DATALOG(\"(rule ({alias} X) ({table} ({shown} X) ({filtercol} S)) ({op} S {val})) (query {alias})\", {table})"
                              :alias
                              alias
                              :table
                              table
                              :shown
                              shown
                              :filtercol
                              filtercol
                              :op
                              op
                              :val
                              val)))

(english-vla
    "show every {table:text} {shown:text} whose {filtercol:text} is over {val:expr} as {alias:text} in cell {r:cell}"
    (datalog-filter-place {table} {shown} {filtercol} ">" {val} {alias} (range {r})))
(test-success
    "Show every Staffing name whose salary is over 80000 as rich in cell E2."
    (datalog-filter-place "staffing" "name" "salary" ">" 80000 "rich" (range "e2")))

(english-vla
    "show every {shown:text} in {table:text} with {filtercol:text} over {val:expr} as {alias:text} in cell {r:cell}"
    (datalog-filter-place {table} {shown} {filtercol} ">" {val} {alias} (range {r})))
(test-success
    "Show every Name in Staffing with Salary over 80000 as Rich in cell E2."
    (datalog-filter-place "staffing" "name" "salary" ">" 80000 "rich" (range "e2")))

; Reachability - Datalog's own differentiator (BETA_ROADMAP.md's
; G-DATALOG entry names this the natural second wave). One template,
; one anchored transitive-closure shape: a two-column table read
; positionally (X reports to Y - DATALOG's own header comment's own
; "(rule (reports_to X Y) (Employees X Y))" precedent for a plain
; positional rename), closed transitively over an internal predicate
; (vlachain - a name the sentence never surfaces, so it cannot collide
; with a real predicate the phrasebook itself chose), then narrowed to
; just the rows reaching one named anchor.
;
; The anchor MUST be quoted ({person:expr}, not a bare {..:text}): a
; bare word would fold to lowercase like every other bare reference in
; this file, but DATALOG's own tuple comparison is case-SENSITIVE
; (VLA_Relation's tuple-identity discipline, DATALOG.4/.5's own header
; notes) - "alice" would silently never match a real "Alice" row. This
; is G2's own quoting doctrine ("quotes are the door") applied to a
; query constant instead of a reference. Because the anchor's own quotes
; must survive two more layers of quoting - Excel's formula-string
; escape (a literal " doubles to "") wrapping DATALOG's own quoted-
; constant syntax - the macro below doubles them itself; a phrasebook
; author never has to.
; L-INTERPOLATE: same hand-spliced-"&" -> interpolate refactor as
; datalog-filter-place, above - table appears three times and alias
; twice in this one template. person's own doubled quotes (this
; comment block's own "the macro below doubles them itself" line,
; just above) are plain template TEXT here, not a hole - interpolate's
; "{{"/"}}" escape is for a literal brace, never needed for a quote
; character, so \"\" passes through unescaped exactly as it did in the
; original "&" chain.
(defmacro
    (datalog-chain-place table person alias r)
    "write a =DATALOG(...) transitive two-column reachability query as a live formula"
    (set-formula r
                 (interpolate "=DATALOG(\"(rule (vlachain X Y) ({table} X Y)) (rule (vlachain X Y) ({table} X Z) (vlachain Z Y)) (rule ({alias} X) (vlachain X \"\"{person}\"\")) (query {alias})\", {table})"
                              :table
                              table
                              :person
                              person
                              :alias
                              alias)))

(english-vla
    "show everyone who reports to {person:expr} directly or not in {table:text} as {alias:text} in cell {r:cell}"
    (datalog-chain-place {table} {person} {alias} (range {r})))
(test-success
    "Show everyone who reports to \"Alice\" directly or not in Reports as team in cell E2."
    (datalog-chain-place "reports" "Alice" "team" (range "e2")))

; ---- OPTIMIZE.3 slice 5: an OPTIMIZE formula run as a command -----------
; A formula refuses a program too large for a cell and stops a search
; after ten seconds; the command runs the cell's own =OPTIMIZE(...) call
; again with neither - a step may lay out 500,000 rows, and Esc stops it -
; and writes the answer as values on a new sheet named after the cell,
; leaving the sheet the program is on active. The same command as
; Frazaro > Logic Engines > Optimize Selected Cell. It runs in the
; interpreter and the console; a compiled program has no OPTIMIZE engine
; beside it, so Compile refuses the sentence by name. The header of
; VLA_Optimize.VlaOptimizeCell has the whole account.
(defmacro
    (optimize-cell r)
    "run the =OPTIMIZE(...) formula in a cell again as a command - larger ceilings, no ten-second limit, Esc stops it - and put its answer on a new sheet"
    (vlaoptimizecell r))

(english-vla
    "optimize cell {r:cell}"
    (optimize-cell (range {r})))
(test-success
    "Optimize cell C1."
    (optimize-cell (range "c1")))
(test-success
    "Optimize cell F12."
    (optimize-cell (range "f12")))

; ---- G-PROLOG: flat Logical English ------------------------------------
; One sentence writes ONE clause, role nouns are the variables, and the
; body is a flat and-list of condition shapes parsed by the BUILT-IN
; `conditions` sub-grammar. SD-16's 2026-09-11 precision amendment is what
; permits these slots; nothing here is DCG-shaped, and no rule below
; recurses into another. VLA_SentenceEngine.bas's two G-PROLOG sections
; have the grammars and the reasoning.
;
; SLICE 2 (the owner's calls, 2026-09-13): everything after "that" is ONE
; built-in slot, {:clause}, and everything between a question's cell and
; "by applying" is ONE, {:question}. Only a sub-grammar that sees a rule's
; head and body together can refuse a head role its conditions never bind,
; and only one that sees a question's whole shape can route it. Slice 1's
; four rows became these two; every sentence they accepted reads the same
; way, and 0.6.0 had not been tagged, so no released spelling moved.
;
; A rule or a fact PLACES ITSELF FIRST - "Write in cell H2 that ..." - so
; a long body never buries the address, and a question places itself the
; same way, which is the pair a reader meets side by side.
;
; {..:clause} binds the whole cell text: a (fact ...), or a (rule ...)
; with any projection rules a negated Table row generates written before
; it, OUTSIDE the rule.
(defmacro
    (prolog-clause-place r text)
    "write one Logical English rule or fact, and any projection rules it needs, as text in a cell"
    (set! r text))

(english-vla
    "write in cell {r:cell} that {h:clause}"
    (prolog-clause-place (range {r}) {h}))
(test-success
    "Write in cell H2 that a person can-cover a shift if Shifts lists the shift as Shift, the cert as Needs, and the min as MinLevel, and Staff lists the person as Name, the cert as Cert, and the level as Level, and the level is at least the min, and not Leave lists the person as Name and the shift as Shift."
    (prolog-clause-place (range "h2") "(rule (vla-not-leave-name-shift Person Shift) (leave (name Person) (shift Shift))) (rule (can-cover Person Shift) (shifts (shift Shift) (needs Cert) (minlevel Min)) (staff (name Person) (cert Cert) (level Level)) (>= Level Min) (not (vla-not-leave-name-shift Person Shift)))"))
(test-success
    "Write in cell H2 that a bill is big if Bills lists the bill as Bill and the amount as Amount, and the amount is greater than 10000."
    (prolog-clause-place (range "h2") "(rule (big Bill) (bills (bill Bill) (amount Amount)) (> Amount 10000))"))
(test-success
    "Write in cell H7 that \"B3\" is flagged."
    (prolog-clause-place (range "h7") "(fact (flagged \"B3\"))"))
; SLICE 3 (the owner's calls, 2026-09-14): "directly or not" after a
; relation asks for one step along it or more, and writes two generated
; rules, vla-any-<relation>, always right-recursive, before the rule.
(test-success
    "Write in cell H3 that a person is-under a boss if the person reports-to the boss directly or not."
    (prolog-clause-place (range "h3") "(rule (vla-any-reports--to X Y) (reports-to X Y)) (rule (vla-any-reports--to X Y) (reports-to X Z) (vla-any-reports--to Z Y)) (rule (is-under Person Boss) (vla-any-reports--to Person Boss))"))
; SLICE 5 (the owner's calls, 2026-09-14): a text test - starts with, ends with,
; contains - checks two values and binds neither; "otherwise" chooses the first
; branch that holds, per subject, through generated vla-first- guards.
(test-success
    "Write in cell H2 that a code is revenue if Accounts lists the code as Code, and the code starts with \"GL-4\"."
    (prolog-clause-place (range "h2") "(rule (revenue Code) (accounts (code Code)) (text-starts-with Code \"GL-4\"))"))
(test-success
    "Write in cell H2 that a customer has-tier \"Gold\" if Customers lists the customer as Customer and the spend as Spend, and the spend is at least 10000, otherwise \"Silver\" if Customers lists the customer as Customer and the spend as Spend, and the spend is at least 5000, otherwise \"Bronze\" if Customers lists the customer as Customer."
    (prolog-clause-place (range "h2") "(rule (vla-first-has--tier-1 Customer) (customers (customer Customer) (spend Spend)) (>= Spend 10000)) (rule (vla-first-has--tier-2 Customer) (customers (customer Customer) (spend Spend)) (>= Spend 5000)) (rule (has-tier Customer \"Gold\") (customers (customer Customer) (spend Spend)) (>= Spend 10000)) (rule (has-tier Customer \"Silver\") (customers (customer Customer) (spend Spend)) (>= Spend 5000) (not (vla-first-has--tier-1 Customer))) (rule (has-tier Customer \"Bronze\") (customers (customer Customer)) (not (vla-first-has--tier-1 Customer)) (not (vla-first-has--tier-2 Customer)))"))

; The questions. {..:question} binds two things: the program text the
; question adds after the rules - its quotes already doubled for Excel's
; string literal, since only the sub-grammar knows where a typed constant's
; quotes are - and, under {..-engine}, the engine the question's SHAPE
; routes it to, G-PROLOG's decision 5 made literal. "whether" is a ground
; TRUE or FALSE, and since slice 3 goes to DATALOG as one fact written out
; whole (DATALOG.9), which answers even where data loops back on itself. A
; who, what or which unknown goes to
; DATALOG (set answers, termination on cycles), through a narrowing rule
; named vla-ask-<relation> - a prefix no writer's name may start with -
; whose head is the unknowns, so an answer's headers are the question's
; own words: Who, What, or the noun after "which".
;
; The rules text is read LIVE out of its cells with TEXTJOIN - the idiom
; VLA_Datalog.bas's own header recommends for a multi-cell rule set - so
; editing a rule recalculates every question over it. A text constant MUST
; be quoted, since a bare word folds to lowercase in transit and a Table's
; own text keeps its case; a number is written bare, and a quoted number is
; refused, because a number cell is a number in both engines.
(defmacro
    (prolog-ask-place engine question rules tables r)
    "write a =DATALOG(...) or =PROLOG(...) question over rules held in cells as a live formula"
    (set-formula r
                 (interpolate "={engine}(TEXTJOIN(\" \",TRUE,{rules})&\" {question}\",{tables})"
                              :engine
                              engine
                              :question
                              question
                              :rules
                              rules
                              :tables
                              tables)))

(english-vla
    "show in cell {r:cell} {q:question} by applying the rules in {rules:range} to the data tables {t:text-list}"
    (prolog-ask-place {q-engine} {q} {rules} (vlatablearguments {t}) (range {r})))
(test-success
    "Show in cell E2 who can-cover \"Night\" by applying the rules in H2:H4 to the data tables Staff, Shifts, and Leave."
    (prolog-ask-place "DATALOG" "(rule (vla-ask-can-cover Who) (can-cover Who \"\"Night\"\")) (query vla-ask-can-cover)" "h2:h4" (vlatablearguments (array "staff" "shifts" "leave")) (range "e2")))
(test-success
    "Show in cell E3 what \"Bob\" can-cover by applying the rules in H2:H4 to the data tables Staff, Shifts, and Leave."
    (prolog-ask-place "DATALOG" "(rule (vla-ask-can-cover What) (can-cover \"\"Bob\"\" What)) (query vla-ask-can-cover)" "h2:h4" (vlatablearguments (array "staff" "shifts" "leave")) (range "e3")))
(test-success
    "Show in cell E4 whether \"Bob\" can-cover \"Night\" by applying the rules in H2:H4 to the data tables Staff, Shifts, and Leave."
    (prolog-ask-place "DATALOG" "(query (can-cover \"\"Bob\"\" \"\"Night\"\"))" "h2:h4" (vlatablearguments (array "staff" "shifts" "leave")) (range "e4")))
(test-success
    "Show in cell E6 which person can-cover which shift by applying the rules in H2:H4 to the data tables Staff, Shifts, and Leave."
    (prolog-ask-place "DATALOG" "(rule (vla-ask-can-cover Person Shift) (can-cover Person Shift)) (query vla-ask-can-cover)" "h2:h4" (vlatablearguments (array "staff" "shifts" "leave")) (range "e6")))
(test-success
    "Show in cell E2 which bill is a violation by applying the rules in H2:H8 to the data tables Bills, Approvals, and Preferred."
    (prolog-ask-place "DATALOG" "(rule (vla-ask-violation Bill) (violation Bill)) (query vla-ask-violation)" "h2:h8" (vlatablearguments (array "bills" "approvals" "preferred")) (range "e2")))
(test-success
    "Show in cell E7 who reports-to \"Alice\" directly or not by applying the rules in H2:H2 to the data tables Reports."
    (prolog-ask-place "DATALOG" "(rule (vla-any-reports--to X Y) (reports-to X Y)) (rule (vla-any-reports--to X Y) (reports-to X Z) (vla-any-reports--to Z Y)) (rule (vla-ask-reports-to Who) (vla-any-reports--to Who \"\"Alice\"\")) (query vla-ask-reports-to)" "h2:h2" (vlatablearguments (array "reports")) (range "e7")))
(test-success
    "Show in cell E8 whether \"Eve\" reports-to \"Alice\" directly or not by applying the rules in H2:H2 to the data tables Reports."
    (prolog-ask-place "DATALOG" "(rule (vla-any-reports--to X Y) (reports-to X Y)) (rule (vla-any-reports--to X Y) (reports-to X Z) (vla-any-reports--to Z Y)) (query (vla-any-reports--to \"\"Eve\"\" \"\"Alice\"\"))" "h2:h2" (vlatablearguments (array "reports")) (range "e8")))
; SLICE 4 (the owner's calls, 2026-09-14): four answer shapes, each named by
; its own opening words, all through DATALOG. "how many" counts the set and
; answers one number in one cell; "each ... that is <set>" counts per member,
; zero included; "which ... that is <set> is not <set>" lists what is outside;
; "whether every ... that is <set> is <set>" asks that nothing is (DATALOG.10);
; and "alone" after the subject asks that exactly one holds. "are" reads as
; "is" in these shapes.
(test-success
    "Show in cell E2 how many people can-cover \"Night\" by applying the rules in H2:H4 to the data tables Staff, Shifts, and Leave."
    (prolog-ask-place "DATALOG" "(headless) (rule (vla-count-can--cover People) (count People (can-cover VlaCounted \"\"Night\"\"))) (query vla-count-can--cover)" "h2:h4" (vlatablearguments (array "staff" "shifts" "leave")) (range "e2")))
(test-success
    "Show in cell E3 how many shifts \"Bob\" can-cover by applying the rules in H2:H4 to the data tables Staff, Shifts, and Leave."
    (prolog-ask-place "DATALOG" "(headless) (rule (vla-count-can--cover Shifts) (count Shifts (can-cover \"\"Bob\"\" VlaCounted))) (query vla-count-can--cover)" "h2:h4" (vlatablearguments (array "staff" "shifts" "leave")) (range "e3")))
(test-success
    "Show in cell E4 how many people can-cover each shift that is listed by applying the rules in H2:H5 to the data tables Staff, Shifts, and Leave."
    (prolog-ask-place "DATALOG" "(rule (vla-each-can--cover Shift People) (listed Shift) (count People (can-cover VlaCounted Shift))) (query vla-each-can--cover)" "h2:h5" (vlatablearguments (array "staff" "shifts" "leave")) (range "e4")))
(test-success
    "Show in cell E5 which shift that is listed is not covered by applying the rules in H2:H6 to the data tables Staff, Shifts, and Leave."
    (prolog-ask-place "DATALOG" "(rule (vla-none-listed-covered Shift) (listed Shift) (not (covered Shift))) (query vla-none-listed-covered)" "h2:h6" (vlatablearguments (array "staff" "shifts" "leave")) (range "e5")))
(test-success
    "Show in cell E6 whether every shift that is listed is covered by applying the rules in H2:H6 to the data tables Staff, Shifts, and Leave."
    (prolog-ask-place "DATALOG" "(rule (vla-none-listed-covered Shift) (listed Shift) (not (covered Shift))) (query (not (vla-none-listed-covered Shift)))" "h2:h6" (vlatablearguments (array "staff" "shifts" "leave")) (range "e6")))
(test-success
    "Show in cell E7 who alone can-cover \"Night\" by applying the rules in H2:H4 to the data tables Staff, Shifts, and Leave."
    (prolog-ask-place "DATALOG" "(rule (vla-alone-can--cover Who) (can-cover Who \"\"Night\"\") (count VlaCount (can-cover VlaCounted \"\"Night\"\")) (= VlaCount 1)) (query vla-alone-can--cover)" "h2:h4" (vlatablearguments (array "staff" "shifts" "leave")) (range "e7")))
(test-success
    "Show in cell E8 whether \"Bob\" alone can-cover \"Night\" by applying the rules in H2:H4 to the data tables Staff, Shifts, and Leave."
    (prolog-ask-place "DATALOG" "(rule (vla-alone-can--cover Who) (can-cover Who \"\"Night\"\") (count VlaCount (can-cover VlaCounted \"\"Night\"\")) (= VlaCount 1)) (query (vla-alone-can--cover \"\"Bob\"\"))" "h2:h4" (vlatablearguments (array "staff" "shifts" "leave")) (range "e8")))
; SLICE 5: "as one list" joins one unknown's answers in one cell, or gives
; one list for each member of a set named after "that is" - DATALOG.11's textjoin.
(test-success
    "Show in cell E2 who can-cover \"Night\" as one list by applying the rules in H2:H4 to the data tables Staff, Shifts, and Leave."
    (prolog-ask-place "DATALOG" "(headless) (rule (vla-list-can--cover Who) (textjoin Who \"\", \"\" (can-cover VlaListed \"\"Night\"\"))) (query vla-list-can--cover)" "h2:h4" (vlatablearguments (array "staff" "shifts" "leave")) (range "e2")))
(test-success
    "Show in cell E3 which people can-cover each shift that is listed as one list by applying the rules in H2:H5 to the data tables Staff, Shifts, and Leave."
    (prolog-ask-place "DATALOG" "(rule (vla-list-can--cover Shift People) (listed Shift) (textjoin People \"\", \"\" (can-cover VlaListed Shift))) (query vla-list-can--cover)" "h2:h5" (vlatablearguments (array "staff" "shifts" "leave")) (range "e3")))