xllify

Lua cheat sheet

Lua is a fast, lightweight scripting language. If you know Python, JavaScript, or a similar language, most of it will feel familiar. This page covers the things that most often catch people out.

Remember, xllify can generate Lua from plain English, VBA, or existing Excel formulas, and answer any questions you have about the code.

Comments

Single-line comments use -- (two hyphens). There is no //.

local x = 42  -- this is a comment
↑ Back to top

Not equal

Use ~=, not !=.

if x ~= 0 then
    return 1 / x
end
↑ Back to top

Tables

Lua has one data structure: the table. Arrays, dictionaries, and tuples are all tables. There are no separate list or tuple types.

-- array-style table (1-indexed)
local fruits = { "apple", "banana", "cherry" }
local first = fruits[1]  -- "apple", not fruits[0]

-- dictionary-style table
local person = { name = "Alice", age = 30 }
local name = person.name  -- "Alice"

-- mixed
local row = { "Alice", 30, active = true }

Tables are 1-indexed. The first element is [1], not [0]. This matches Excel ranges, which are also 1-indexed.

↑ Back to top

Length operator

Use # to get the length of a table or string.

local t = { 10, 20, 30 }
local len = #t        -- 3

local s = "hello"
local slen = #s       -- 5

For tables with gaps (sparse arrays), # may not give the result you expect. Use a counter variable if you need precise control.

↑ Back to top

Tables as lists, sets, and dictionaries

Because the table is Lua's only data structure, you use it for everything.

-- as a list (array)
local items = { "a", "b", "c" }
items[#items + 1] = "d"   -- append
table.insert(items, "e")  -- also append
table.remove(items, 1)    -- remove first element

-- as a set (membership check)
local allowed = { admin = true, editor = true, viewer = true }
if allowed["admin"] then
    -- user has access
end

-- as a dictionary (key/value map)
local config = {}
config["timeout"] = 30
config["retries"] = 3
local timeout = config["timeout"]   -- or config.timeout

-- check if a key exists
if config.timeout ~= nil then
    -- key is present
end
↑ Back to top

For loops

Use ipairs to iterate over an array-style table in order. Use pairs to iterate over a dictionary-style table (order not guaranteed).

local scores = { 80, 95, 72, 88 }

-- ipairs: index and value, in order
local total = 0
for i, v in ipairs(scores) do
    total = total + v
end

-- numeric for loop
for i = 1, #scores do
    total = total + scores[i]
end

-- pairs: key and value, any order
local counts = { apples = 3, bananas = 5 }
for key, value in pairs(counts) do
    -- key is "apples" or "bananas"
end
↑ Back to top

Strings

Strings are immutable. Concatenate with .., not +. Lua has its own pattern syntax for matching and substitution, not regular expressions.

local greeting = "Hello, " .. "world"   -- "Hello, world"
local len = #greeting                    -- 13

-- string library
local s = "Hello World"
string.lower(s)          -- "hello world"
string.upper(s)          -- "HELLO WORLD"
string.len(s)            -- 11  (same as #s)
string.sub(s, 1, 5)      -- "Hello"
string.find(s, "World")  -- 7, 11  (start, end positions)
string.rep("ab", 3)      -- "ababab"

-- method syntax also works
s:lower()
s:upper()
s:sub(1, 5)

-- gsub: replace matches (returns newstr, count)
local result = string.gsub("hello world", "(%w+)", function(w)
    return w:upper()
end)
-- "HELLO WORLD"

local clean = string.gsub("  hello  world  ", "%s+", " ")
-- " hello world "

-- common patterns
-- %d  digit       %a  letter      %w  letter or digit
-- %s  whitespace  %p  punctuation  .  any character
-- +   one or more   *  zero or more   ?  zero or one

-- capture groups with ()
local year, month, day = string.match("2026-03-06", "(%d+)-(%d+)-(%d+)")
-- year = "2026", month = "03", day = "06"

Lua patterns are not regular expressions, but they cover most common use cases. See Lua 5.4 patterns for the full syntax.

↑ Back to top

Nil and truthiness

Only nil and false are falsy. Zero and empty string are both truthy.

if 0 then
    -- this runs, 0 is truthy in Lua
end

if "" then
    -- this also runs
end

local x = nil
if not x then
    -- this runs
end
↑ Back to top

Integers and division

Lua 5.4 distinguishes integers from floats. Most of the time you won't notice, but division behaves differently depending on the operator.

local a = 10      -- integer
local b = 10.0    -- float

-- / always returns a float
10 / 3             -- 3.3333333333333
10 / 2             -- 5.0

-- // is integer (floor) division
10 // 3            -- 3
7 // 2             -- 3
-7 // 2            -- -4  (rounds towards negative infinity)

-- check the type
type(42)           -- "number"
math.type(42)      -- "integer"
math.type(42.0)    -- "float"

-- convert between them
math.tointeger(5.0)  -- 5 (returns nil if not exact)
42 + 0.0             -- 42.0 (force to float)
↑ Back to top

Multiple return values

Functions can return more than one value.

local function minmax(t)
    local lo, hi = t[1], t[1]
    for _, v in ipairs(t) do
        if v < lo then lo = v end
        if v > hi then hi = v end
    end
    return lo, hi
end

local lo, hi = minmax({ 3, 1, 4, 1, 5, 9 })
-- lo = 1, hi = 9
↑ Back to top

How Excel ranges arrive in your function

A scalar parameter receives a single cell value directly — a number, string, or boolean.

-- Excel formula: =DOUBLE_IT(A1)
-- 'value' arrives as a plain number, e.g. 42

--- Doubles a number
-- @param value number A single cell
-- @category Math
function DOUBLE_IT(value)
    return value * 2
end

When a parameter accepts a range, Excel passes it as a table of rows, where each row is a table of cell values. A single column looks like a table of single-element rows.

-- Excel range A1:B3 passed as matrix:
-- { { "Alice", 100 }, { "Bob", 200 }, { "Carol", 300 } }
-- access: values[row][col]

-- A single column A1:A5 passed as matrix:
-- { { 10 }, { 20 }, { 30 }, { 40 }, { 50 } }
-- access: col[i][1]

-- A single row A1:E1 passed as matrix:
-- { { 10, 20, 30, 40, 50 } }
-- access: row[1][i]

-- Returning a 2D range from a function:
-- Return a table of rows (table of tables)
return {
    { "Name",  "Score" },
    { "Alice", 95      },
    { "Bob",   82      },
}

-- Returning a single column (Nx1):
return {
    { 10 },
    { 20 },
    { 30 },
}

-- Returning a single value:
return 42

Set result = { type = "any", dimensionality = "matrix" } in your function config when returning a 2D table. For a scalar result the default is fine.

Common patterns

When a matrix parameter might receive a single cell, use xllify.ensure_matrix to normalise it so the rest of your function can always treat it as a table of rows:

values = xllify.ensure_matrix(values)
-- values is now always a 2D table, even if the user passed a single cell

To work with every numeric cell in a range regardless of shape, use xllify.flatten_range:

local flat = xllify.flatten_range(values)
-- flat is a 1D array of numbers, non-numbers skipped
-- works on scalars, 1D arrays, and 2D matrices

Combined, these two cover the most common range-handling boilerplate:

--- Sums a range
-- @param values number Range of numbers
-- @category Math
function SUM_RANGE(values)
    local flat = xllify.flatten_range(values)  -- flatten to 1D, skipping non-numbers
    return functional.fold(flat, function(acc, v) return acc + v end, 0)
end

Or accumulate with a loop and local var:

--- Sums a range
-- @param values number Range of numbers
-- @category Math
function SUM_RANGE(values)
    local flat = xllify.flatten_range(values)
    local total = 0
    for _, v in ipairs(flat) do
        total = total + v
    end
    return total
end
↑ Back to top

The xllify namespace

The xllify global is available in all function code. Do not require it.

Declaring a function

Functions use annotation comments directly above the function declaration:

--- Multiplies two numbers
-- @param a number First number
-- @param b number Second number
-- @category Math
-- @test(3, 4) == 12
-- @test(0, 5) == 0
function MULTIPLY(a, b)
    return a * b
end

These annotations populate Excel's Function Wizard (Insert Function dialog). The --- description appears as the function's help text, each @param description appears next to its argument, and @category controls which group the function appears under. @test lines are run automatically when you build.

Accepting a range

--- Sums a range
-- @param values number Range of numbers
-- @category Math
function SUM_VALUES(values)
    -- values is a table of tables: values[row][col]
    local total = 0
    for _, row in ipairs(values) do
        for _, cell in ipairs(row) do
            total = total + cell
        end
    end
    return total
end

Built-in utilities

These are native primitives available on the xllify global for use inside function code.

See the standard library reference for the full list of ready-made Excel functions.

↑ Back to top

Functional extensions

The functional global is available in all function code with no imports. Full reference at builtins.

functional.map(t, f)           -- transform every element
functional.filter(t, f)        -- keep elements matching a predicate
functional.reduce(t, f)        -- fold from first element as seed
functional.fold(t, f, acc)     -- fold with an explicit seed
functional.zip(a, b, f)        -- combine two tables element-wise
functional.flatten(t, depth)   -- collapse nested tables (depth default 1)
functional.flat_map(t, f)      -- map then flatten one level
functional.compose(...)        -- right-to-left function composition
functional.pipe(...)           -- left-to-right function composition
functional.partial(f, ...)     -- bind leading arguments
functional.every(t, f)         -- true if f is true for all elements
functional.some(t, f)          -- first element for which f is true, or nil
functional.none(t, f)          -- true if f is true for no elements
functional.group_by(t, f)      -- group elements into sub-tables by key
functional.frequencies(t)      -- count occurrences of each value
functional.take_while(t, f)    -- take from front while predicate holds
functional.drop_while(t, f)    -- drop from front while predicate holds
functional.distinct(t)         -- remove duplicates, preserve order
functional.juxt(...)           -- apply multiple functions to same value
functional.const(v)            -- returns a function that always returns v

-- named numeric utilities (pass directly to map, filter, etc.)
functional.double   functional.square   functional.sqrt
functional.negate   functional.abs      functional.round
functional.floor    functional.ceil     functional.inc
functional.dec      functional.half     functional.identity

-- arithmetic combinators (each returns a function)
functional.add(n)   functional.sub(n)   functional.mul(n)
functional.div(n)   functional.pow(n)   functional.mod(n)
↑ Back to top