UPDATE[docs]: Compatibility and other engines

Sheet1!$D$1 rather than Sheet1!D1

There reason is that if we extend a formula that has those will behave in
surprising ways.

.github/workflows/test-coverage.yaml

@@ -17,8 +17,9 @@ jobs:
       - name: Generate code coverage
         run: cargo llvm-cov --all-features --workspace --exclude pyroncalc --exclude wasm --lcov --output-path lcov.info
       - name: Upload coverage to Codecov
-        uses: codecov/codecov-action@v3
+        uses: codecov/codecov-action@v5
+        env:
+          CODECOV_TOKEN: ${{ secrets.CODECOV_TOKEN }}
         with:
-          token: ${{ secrets.CODECOV_TOKEN }}
           files: lcov.info
           fail_ci_if_error: true

CHANGELOG.md

@@ -7,6 +7,7 @@
 - New function UNICODE ([#128](https://github.com/ironcalc/IronCalc/pull/128))
 - New document server (Thanks Dani!)
 - New function FORMULATEXT
+- Name Manager ([#212](https://github.com/ironcalc/IronCalc/pull/212) [#220](https://github.com/ironcalc/IronCalc/pull/220))
 
 ### Fixed
 

base/src/calc_result.rs

@@ -2,6 +2,7 @@ use std::cmp::Ordering;
 
 use crate::expressions::{token::Error, types::CellReferenceIndex};
 
+#[derive(Clone)]
 pub struct Range {
     pub left: CellReferenceIndex,
     pub right: CellReferenceIndex,

base/src/constants.rs

@@ -16,3 +16,10 @@ pub(crate) const LAST_ROW: i32 = 1_048_576;
 // NaiveDate::from_ymd(1900, 1, 1).num_days_from_ce() - 2
 // The 2 days offset is because of Excel 1900 bug
 pub(crate) const EXCEL_DATE_BASE: i32 = 693_594;
+
+// We do not support dates before 1899-12-31.
+pub(crate) const MINIMUM_DATE_SERIAL_NUMBER: i32 = 1;
+
+// Excel can handle dates until the year 9999-12-31
+// 2958465 is the number of days from 1900-01-01 to 9999-12-31
+pub(crate) const MAXIMUM_DATE_SERIAL_NUMBER: i32 = 2_958_465;

base/src/expressions/parser/mod.rs

@@ -164,7 +164,9 @@ pub enum Node {
         args: Vec<Node>,
     },
     ArrayKind(Vec<Node>),
-    VariableKind(String),
+    DefinedNameKind((String, Option<u32>)),
+    TableNameKind(String),
+    WrongVariableKind(String),
     CompareKind {
         kind: OpCompare,
         left: Box<Node>,
@@ -187,12 +189,17 @@ pub enum Node {
 pub struct Parser {
     lexer: lexer::Lexer,
     worksheets: Vec<String>,
-    context: Option<CellReferenceRC>,
+    defined_names: Vec<(String, Option<u32>)>,
+    context: CellReferenceRC,
     tables: HashMap<String, Table>,
 }
 
 impl Parser {
-    pub fn new(worksheets: Vec<String>, tables: HashMap<String, Table>) -> Parser {
+    pub fn new(
+        worksheets: Vec<String>,
+        defined_names: Vec<(String, Option<u32>)>,
+        tables: HashMap<String, Table>,
+    ) -> Parser {
         let lexer = lexer::Lexer::new(
             "",
             lexer::LexerMode::A1,
@@ -201,10 +208,16 @@ impl Parser {
             #[allow(clippy::expect_used)]
             get_language("en").expect(""),
         );
+        let context = CellReferenceRC {
+            sheet: worksheets.first().map_or("", |v| v).to_string(),
+            column: 1,
+            row: 1,
+        };
         Parser {
             lexer,
             worksheets,
-            context: None,
+            defined_names,
+            context,
             tables,
         }
     }
@@ -212,13 +225,18 @@ impl Parser {
         self.lexer.set_lexer_mode(mode)
     }
 
-    pub fn set_worksheets(&mut self, worksheets: Vec<String>) {
+    pub fn set_worksheets_and_names(
+        &mut self,
+        worksheets: Vec<String>,
+        defined_names: Vec<(String, Option<u32>)>,
+    ) {
         self.worksheets = worksheets;
+        self.defined_names = defined_names;
     }
 
-    pub fn parse(&mut self, formula: &str, context: &Option<CellReferenceRC>) -> Node {
+    pub fn parse(&mut self, formula: &str, context: &CellReferenceRC) -> Node {
         self.lexer.set_formula(formula);
-        self.context.clone_from(context);
+        self.context = context.clone();
         self.parse_expr()
     }
 
@@ -232,6 +250,24 @@ impl Parser {
         None
     }
 
+    // Returns:
+    //  * None: If there is no defined name by that name
+    //  * Some(Some(index)): If there is a defined name local to that sheet
+    //  * Some(None): If there is a global defined name
+    fn get_defined_name(&self, name: &str, sheet: u32) -> Option<Option<u32>> {
+        for (df_name, df_scope) in &self.defined_names {
+            if name.to_lowercase() == df_name.to_lowercase() && df_scope == &Some(sheet) {
+                return Some(*df_scope);
+            }
+        }
+        for (df_name, df_scope) in &self.defined_names {
+            if name.to_lowercase() == df_name.to_lowercase() && df_scope.is_none() {
+                return Some(None);
+            }
+        }
+        None
+    }
+
     fn parse_expr(&mut self) -> Node {
         let mut t = self.parse_concat();
         if let Node::ParseErrorKind { .. } = t {
@@ -446,16 +482,7 @@ impl Parser {
                 absolute_column,
                 absolute_row,
             } => {
-                let context = match &self.context {
-                    Some(c) => c,
-                    None => {
-                        return Node::ParseErrorKind {
-                            formula: self.lexer.get_formula(),
-                            position: self.lexer.get_position() as usize,
-                            message: "Expected context for the reference".to_string(),
-                        }
-                    }
-                };
+                let context = &self.context;
                 let sheet_index = match &sheet {
                     Some(name) => self.get_sheet_index_by_name(name),
                     None => self.get_sheet_index_by_name(&context.sheet),
@@ -490,16 +517,7 @@ impl Parser {
                 }
             }
             TokenType::Range { sheet, left, right } => {
-                let context = match &self.context {
-                    Some(c) => c,
-                    None => {
-                        return Node::ParseErrorKind {
-                            formula: self.lexer.get_formula(),
-                            position: self.lexer.get_position() as usize,
-                            message: "Expected context for the reference".to_string(),
-                        }
-                    }
-                };
+                let context = &self.context;
                 let sheet_index = match &sheet {
                     Some(name) => self.get_sheet_index_by_name(name),
                     None => self.get_sheet_index_by_name(&context.sheet),
@@ -585,11 +603,33 @@ impl Parser {
                             kind: function_kind,
                             args,
                         };
-                    } else {
-                        return Node::InvalidFunctionKind { name, args };
+                    }
+                    return Node::InvalidFunctionKind { name, args };
+                }
+                let context = &self.context;
+
+                let context_sheet_index = match self.get_sheet_index_by_name(&context.sheet) {
+                    Some(i) => i,
+                    None => {
+                        return Node::ParseErrorKind {
+                            formula: self.lexer.get_formula(),
+                            position: 0,
+                            message: "sheet not found".to_string(),
+                        };
+                    }
+                };
+
+                // Could be a defined name or a table
+                if let Some(scope) = self.get_defined_name(&name, context_sheet_index) {
+                    return Node::DefinedNameKind((name, scope));
+                }
+                let name_lower = name.to_lowercase();
+                for table_name in self.tables.keys() {
+                    if table_name.to_lowercase() == name_lower {
+                        return Node::TableNameKind(name);
                     }
                 }
-                Node::VariableKind(name)
+                Node::WrongVariableKind(name)
             }
             TokenType::Error(kind) => Node::ErrorKind(kind),
             TokenType::Illegal(error) => Node::ParseErrorKind {
@@ -602,7 +642,38 @@ impl Parser {
                 position: 0,
                 message: "Unexpected end of input.".to_string(),
             },
-            TokenType::Boolean(value) => Node::BooleanKind(value),
+            TokenType::Boolean(value) => {
+                // Could be a function call "TRUE()"
+                let next_token = self.lexer.peek_token();
+                if next_token == TokenType::LeftParenthesis {
+                    self.lexer.advance_token();
+                    // We parse all the arguments, although technically this is moot
+                    // But is has the upside of transforming `=TRUE( 4 )` into `=TRUE(4)`
+                    let args = match self.parse_function_args() {
+                        Ok(s) => s,
+                        Err(e) => return e,
+                    };
+                    if let Err(err) = self.lexer.expect(TokenType::RightParenthesis) {
+                        return Node::ParseErrorKind {
+                            formula: self.lexer.get_formula(),
+                            position: err.position,
+                            message: err.message,
+                        };
+                    }
+                    if value {
+                        return Node::FunctionKind {
+                            kind: Function::True,
+                            args,
+                        };
+                    } else {
+                        return Node::FunctionKind {
+                            kind: Function::False,
+                            args,
+                        };
+                    }
+                }
+                Node::BooleanKind(value)
+            }
             TokenType::Compare(_) => {
                 // A primary Node cannot start with an operator
                 Node::ParseErrorKind {
@@ -661,187 +732,177 @@ impl Parser {
                 // We will try to convert to a normal reference
                 // table_name[column_name] => cell1:cell2
                 // table_name[[#This Row], [column_name]:[column_name]] => cell1:cell2
-                if let Some(context) = &self.context {
-                    let context_sheet_index = match self.get_sheet_index_by_name(&context.sheet) {
-                        Some(i) => i,
-                        None => {
-                            return Node::ParseErrorKind {
-                                formula: self.lexer.get_formula(),
-                                position: 0,
-                                message: "sheet not found".to_string(),
-                            };
-                        }
-                    };
-                    // table-name => table
-                    let table = match self.tables.get(&table_name) {
-                        Some(t) => t,
-                        None => {
-                            let message = format!(
-                                "Table not found: '{table_name}' at '{}!{}{}'",
-                                context.sheet,
-                                number_to_column(context.column)
-                                    .unwrap_or(format!("{}", context.column)),
-                                context.row
-                            );
-                            return Node::ParseErrorKind {
-                                formula: self.lexer.get_formula(),
-                                position: 0,
-                                message,
-                            };
-                        }
-                    };
-                    let table_sheet_index = match self.get_sheet_index_by_name(&table.sheet_name) {
-                        Some(i) => i,
-                        None => {
-                            return Node::ParseErrorKind {
-                                formula: self.lexer.get_formula(),
-                                position: 0,
-                                message: "sheet not found".to_string(),
-                            };
-                        }
-                    };
+                let context = &self.context;
+                let context_sheet_index = match self.get_sheet_index_by_name(&context.sheet) {
+                    Some(i) => i,
+                    None => {
+                        return Node::ParseErrorKind {
+                            formula: self.lexer.get_formula(),
+                            position: 0,
+                            message: "sheet not found".to_string(),
+                        };
+                    }
+                };
+                // table-name => table
+                let table = match self.tables.get(&table_name) {
+                    Some(t) => t,
+                    None => {
+                        let message = format!(
+                            "Table not found: '{table_name}' at '{}!{}{}'",
+                            context.sheet,
+                            number_to_column(context.column)
+                                .unwrap_or(format!("{}", context.column)),
+                            context.row
+                        );
+                        return Node::ParseErrorKind {
+                            formula: self.lexer.get_formula(),
+                            position: 0,
+                            message,
+                        };
+                    }
+                };
+                let table_sheet_index = match self.get_sheet_index_by_name(&table.sheet_name) {
+                    Some(i) => i,
+                    None => {
+                        return Node::ParseErrorKind {
+                            formula: self.lexer.get_formula(),
+                            position: 0,
+                            message: "sheet not found".to_string(),
+                        };
+                    }
+                };
 
-                    let sheet_name = if table_sheet_index == context_sheet_index {
-                        None
-                    } else {
-                        Some(table.sheet_name.clone())
-                    };
+                let sheet_name = if table_sheet_index == context_sheet_index {
+                    None
+                } else {
+                    Some(table.sheet_name.clone())
+                };
 
-                    // context must be with tables.reference
-                    #[allow(clippy::expect_used)]
-                    let (column_start, mut row_start, column_end, mut row_end) =
-                        parse_range(&table.reference).expect("Failed parsing range");
+                // context must be with tables.reference
+                #[allow(clippy::expect_used)]
+                let (column_start, mut row_start, column_end, mut row_end) =
+                    parse_range(&table.reference).expect("Failed parsing range");
 
-                    let totals_row_count = table.totals_row_count as i32;
-                    let header_row_count = table.header_row_count as i32;
-                    row_end -= totals_row_count;
+                let totals_row_count = table.totals_row_count as i32;
+                let header_row_count = table.header_row_count as i32;
+                row_end -= totals_row_count;
 
-                    match specifier {
-                        Some(token::TableSpecifier::ThisRow) => {
-                            row_start = context.row;
-                            row_end = context.row;
-                        }
-                        Some(token::TableSpecifier::Totals) => {
-                            if totals_row_count != 0 {
-                                row_start = row_end + 1;
-                                row_end = row_start;
-                            } else {
-                                // Table1[#Totals] is #REF! if Table1 does not have totals
-                                return Node::ErrorKind(token::Error::REF);
-                            }
-                        }
-                        Some(token::TableSpecifier::Headers) => {
+                match specifier {
+                    Some(token::TableSpecifier::ThisRow) => {
+                        row_start = context.row;
+                        row_end = context.row;
+                    }
+                    Some(token::TableSpecifier::Totals) => {
+                        if totals_row_count != 0 {
+                            row_start = row_end + 1;
                             row_end = row_start;
-                        }
-                        Some(token::TableSpecifier::Data) => {
-                            row_start += header_row_count;
-                        }
-                        Some(token::TableSpecifier::All) => {
-                            if totals_row_count != 0 {
-                                row_end += 1;
-                            }
-                        }
-                        None => {
-                            // skip the headers
-                            row_start += header_row_count;
+                        } else {
+                            // Table1[#Totals] is #REF! if Table1 does not have totals
+                            return Node::ErrorKind(token::Error::REF);
                         }
                     }
-                    match table_reference {
-                        None => {
-                            return Node::RangeKind {
-                                sheet_name,
-                                sheet_index: table_sheet_index,
-                                absolute_row1: true,
-                                absolute_column1: true,
-                                row1: row_start,
-                                column1: column_start,
-                                absolute_row2: true,
-                                absolute_column2: true,
-                                row2: row_end,
-                                column2: column_end,
-                            };
-                        }
-                        Some(TableReference::ColumnReference(s)) => {
-                            let column_index = match get_table_column_by_name(&s, table) {
-                                Some(s) => s + column_start,
-                                None => {
-                                    return Node::ParseErrorKind {
-                                        formula: self.lexer.get_formula(),
-                                        position: self.lexer.get_position() as usize,
-                                        message: format!(
-                                            "Expecting column: {s} in table {table_name}"
-                                        ),
-                                    };
-                                }
-                            };
-                            if row_start == row_end {
-                                return Node::ReferenceKind {
-                                    sheet_name,
-                                    sheet_index: table_sheet_index,
-                                    absolute_row: true,
-                                    absolute_column: true,
-                                    row: row_start,
-                                    column: column_index,
-                                };
-                            }
-                            return Node::RangeKind {
-                                sheet_name,
-                                sheet_index: table_sheet_index,
-                                absolute_row1: true,
-                                absolute_column1: true,
-                                row1: row_start,
-                                column1: column_index,
-                                absolute_row2: true,
-                                absolute_column2: true,
-                                row2: row_end,
-                                column2: column_index,
-                            };
-                        }
-                        Some(TableReference::RangeReference((left, right))) => {
-                            let left_column_index = match get_table_column_by_name(&left, table) {
-                                Some(f) => f + column_start,
-                                None => {
-                                    return Node::ParseErrorKind {
-                                        formula: self.lexer.get_formula(),
-                                        position: self.lexer.get_position() as usize,
-                                        message: format!(
-                                            "Expecting column: {left} in table {table_name}"
-                                        ),
-                                    };
-                                }
-                            };
-
-                            let right_column_index = match get_table_column_by_name(&right, table) {
-                                Some(f) => f + column_start,
-                                None => {
-                                    return Node::ParseErrorKind {
-                                        formula: self.lexer.get_formula(),
-                                        position: self.lexer.get_position() as usize,
-                                        message: format!(
-                                            "Expecting column: {right} in table {table_name}"
-                                        ),
-                                    };
-                                }
-                            };
-                            return Node::RangeKind {
-                                sheet_name,
-                                sheet_index: table_sheet_index,
-                                absolute_row1: true,
-                                absolute_column1: true,
-                                row1: row_start,
-                                column1: left_column_index,
-                                absolute_row2: true,
-                                absolute_column2: true,
-                                row2: row_end,
-                                column2: right_column_index,
-                            };
+                    Some(token::TableSpecifier::Headers) => {
+                        row_end = row_start;
+                    }
+                    Some(token::TableSpecifier::Data) => {
+                        row_start += header_row_count;
+                    }
+                    Some(token::TableSpecifier::All) => {
+                        if totals_row_count != 0 {
+                            row_end += 1;
                         }
                     }
+                    None => {
+                        // skip the headers
+                        row_start += header_row_count;
+                    }
                 }
-                Node::ParseErrorKind {
-                    formula: self.lexer.get_formula(),
-                    position: 0,
-                    message: "Structured references not supported in R1C1 mode".to_string(),
+                match table_reference {
+                    None => Node::RangeKind {
+                        sheet_name,
+                        sheet_index: table_sheet_index,
+                        absolute_row1: true,
+                        absolute_column1: true,
+                        row1: row_start,
+                        column1: column_start,
+                        absolute_row2: true,
+                        absolute_column2: true,
+                        row2: row_end,
+                        column2: column_end,
+                    },
+                    Some(TableReference::ColumnReference(s)) => {
+                        let column_index = match get_table_column_by_name(&s, table) {
+                            Some(s) => s + column_start,
+                            None => {
+                                return Node::ParseErrorKind {
+                                    formula: self.lexer.get_formula(),
+                                    position: self.lexer.get_position() as usize,
+                                    message: format!("Expecting column: {s} in table {table_name}"),
+                                };
+                            }
+                        };
+                        if row_start == row_end {
+                            return Node::ReferenceKind {
+                                sheet_name,
+                                sheet_index: table_sheet_index,
+                                absolute_row: true,
+                                absolute_column: true,
+                                row: row_start,
+                                column: column_index,
+                            };
+                        }
+                        Node::RangeKind {
+                            sheet_name,
+                            sheet_index: table_sheet_index,
+                            absolute_row1: true,
+                            absolute_column1: true,
+                            row1: row_start,
+                            column1: column_index,
+                            absolute_row2: true,
+                            absolute_column2: true,
+                            row2: row_end,
+                            column2: column_index,
+                        }
+                    }
+                    Some(TableReference::RangeReference((left, right))) => {
+                        let left_column_index = match get_table_column_by_name(&left, table) {
+                            Some(f) => f + column_start,
+                            None => {
+                                return Node::ParseErrorKind {
+                                    formula: self.lexer.get_formula(),
+                                    position: self.lexer.get_position() as usize,
+                                    message: format!(
+                                        "Expecting column: {left} in table {table_name}"
+                                    ),
+                                };
+                            }
+                        };
+
+                        let right_column_index = match get_table_column_by_name(&right, table) {
+                            Some(f) => f + column_start,
+                            None => {
+                                return Node::ParseErrorKind {
+                                    formula: self.lexer.get_formula(),
+                                    position: self.lexer.get_position() as usize,
+                                    message: format!(
+                                        "Expecting column: {right} in table {table_name}"
+                                    ),
+                                };
+                            }
+                        };
+                        Node::RangeKind {
+                            sheet_name,
+                            sheet_index: table_sheet_index,
+                            absolute_row1: true,
+                            absolute_column1: true,
+                            row1: row_start,
+                            column1: left_column_index,
+                            absolute_row2: true,
+                            absolute_column2: true,
+                            row2: row_end,
+                            column2: right_column_index,
+                        }
+                    }
                 }
             }
         }

base/src/expressions/parser/move_formula.rs

@@ -375,7 +375,9 @@ fn to_string_moved(node: &Node, move_context: &MoveContext) -> String {
             }
             format!("{{{}}}", arguments)
         }
-        VariableKind(value) => value.to_string(),
+        DefinedNameKind((name, _)) => name.to_string(),
+        TableNameKind(name) => name.to_string(),
+        WrongVariableKind(name) => name.to_string(),
         CompareKind { kind, left, right } => format!(
             "{}{}{}",
             to_string_moved(left, move_context),

base/src/expressions/parser/stringify.rs

@@ -464,7 +464,9 @@ fn stringify(
                 | ReferenceKind { .. }
                 | RangeKind { .. }
                 | WrongReferenceKind { .. }
-                | VariableKind(_)
+                | DefinedNameKind(_)
+                | TableNameKind(_)
+                | WrongVariableKind(_)
                 | WrongRangeKind { .. } => {
                     stringify(left, context, displace_data, use_original_name)
                 }
@@ -492,7 +494,9 @@ fn stringify(
                 | ReferenceKind { .. }
                 | RangeKind { .. }
                 | WrongReferenceKind { .. }
-                | VariableKind(_)
+                | DefinedNameKind(_)
+                | TableNameKind(_)
+                | WrongVariableKind(_)
                 | WrongRangeKind { .. } => {
                     stringify(right, context, displace_data, use_original_name)
                 }
@@ -543,7 +547,9 @@ fn stringify(
             }
             format!("{{{}}}", arguments)
         }
-        VariableKind(value) => value.to_string(),
+        TableNameKind(value) => value.to_string(),
+        DefinedNameKind((name, _)) => name.to_string(),
+        WrongVariableKind(name) => name.to_string(),
         UnaryKind { kind, right } => match kind {
             OpUnary::Minus => {
                 format!(
@@ -660,7 +666,90 @@ pub(crate) fn rename_sheet_in_node(node: &mut Node, sheet_index: u32, new_name:
         Node::ErrorKind(_) => {}
         Node::ParseErrorKind { .. } => {}
         Node::ArrayKind(_) => {}
-        Node::VariableKind(_) => {}
+        Node::DefinedNameKind(_) => {}
+        Node::TableNameKind(_) => {}
+        Node::WrongVariableKind(_) => {}
         Node::EmptyArgKind => {}
     }
 }
+
+pub(crate) fn rename_defined_name_in_node(
+    node: &mut Node,
+    name: &str,
+    scope: Option<u32>,
+    new_name: &str,
+) {
+    match node {
+        // Rename
+        Node::DefinedNameKind((n, s)) => {
+            if name.to_lowercase() == n.to_lowercase() && *s == scope {
+                *n = new_name.to_string();
+            }
+        }
+        // Go next level
+        Node::OpRangeKind { left, right } => {
+            rename_defined_name_in_node(left, name, scope, new_name);
+            rename_defined_name_in_node(right, name, scope, new_name);
+        }
+        Node::OpConcatenateKind { left, right } => {
+            rename_defined_name_in_node(left, name, scope, new_name);
+            rename_defined_name_in_node(right, name, scope, new_name);
+        }
+        Node::OpSumKind {
+            kind: _,
+            left,
+            right,
+        } => {
+            rename_defined_name_in_node(left, name, scope, new_name);
+            rename_defined_name_in_node(right, name, scope, new_name);
+        }
+        Node::OpProductKind {
+            kind: _,
+            left,
+            right,
+        } => {
+            rename_defined_name_in_node(left, name, scope, new_name);
+            rename_defined_name_in_node(right, name, scope, new_name);
+        }
+        Node::OpPowerKind { left, right } => {
+            rename_defined_name_in_node(left, name, scope, new_name);
+            rename_defined_name_in_node(right, name, scope, new_name);
+        }
+        Node::FunctionKind { kind: _, args } => {
+            for arg in args {
+                rename_defined_name_in_node(arg, name, scope, new_name);
+            }
+        }
+        Node::InvalidFunctionKind { name: _, args } => {
+            for arg in args {
+                rename_defined_name_in_node(arg, name, scope, new_name);
+            }
+        }
+        Node::CompareKind {
+            kind: _,
+            left,
+            right,
+        } => {
+            rename_defined_name_in_node(left, name, scope, new_name);
+            rename_defined_name_in_node(right, name, scope, new_name);
+        }
+        Node::UnaryKind { kind: _, right } => {
+            rename_defined_name_in_node(right, name, scope, new_name);
+        }
+
+        // Do nothing
+        Node::BooleanKind(_) => {}
+        Node::NumberKind(_) => {}
+        Node::StringKind(_) => {}
+        Node::ErrorKind(_) => {}
+        Node::ParseErrorKind { .. } => {}
+        Node::ArrayKind(_) => {}
+        Node::EmptyArgKind => {}
+        Node::ReferenceKind { .. } => {}
+        Node::RangeKind { .. } => {}
+        Node::WrongReferenceKind { .. } => {}
+        Node::WrongRangeKind { .. } => {}
+        Node::TableNameKind(_) => {}
+        Node::WrongVariableKind(_) => {}
+    }
+}

base/src/expressions/parser/tests/test_general.rs

@@ -17,7 +17,7 @@ struct Formula<'a> {
 #[test]
 fn test_parser_reference() {
     let worksheets = vec!["Sheet1".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Reference cell is Sheet1!A1
     let cell_reference = CellReferenceRC {
@@ -25,14 +25,14 @@ fn test_parser_reference() {
         row: 1,
         column: 1,
     };
-    let t = parser.parse("A2", &Some(cell_reference));
+    let t = parser.parse("A2", &cell_reference);
     assert_eq!(to_rc_format(&t), "R[1]C[0]");
 }
 
 #[test]
 fn test_parser_absolute_column() {
     let worksheets = vec!["Sheet1".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Reference cell is Sheet1!A1
     let cell_reference = CellReferenceRC {
@@ -40,14 +40,14 @@ fn test_parser_absolute_column() {
         row: 1,
         column: 1,
     };
-    let t = parser.parse("$A1", &Some(cell_reference));
+    let t = parser.parse("$A1", &cell_reference);
     assert_eq!(to_rc_format(&t), "R[0]C1");
 }
 
 #[test]
 fn test_parser_absolute_row_col() {
     let worksheets = vec!["Sheet1".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Reference cell is Sheet1!A1
     let cell_reference = CellReferenceRC {
@@ -55,14 +55,14 @@ fn test_parser_absolute_row_col() {
         row: 1,
         column: 1,
     };
-    let t = parser.parse("$C$5", &Some(cell_reference));
+    let t = parser.parse("$C$5", &cell_reference);
     assert_eq!(to_rc_format(&t), "R5C3");
 }
 
 #[test]
 fn test_parser_absolute_row_col_1() {
     let worksheets = vec!["Sheet1".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Reference cell is Sheet1!A1
     let cell_reference = CellReferenceRC {
@@ -70,14 +70,14 @@ fn test_parser_absolute_row_col_1() {
         row: 1,
         column: 1,
     };
-    let t = parser.parse("$A$1", &Some(cell_reference));
+    let t = parser.parse("$A$1", &cell_reference);
     assert_eq!(to_rc_format(&t), "R1C1");
 }
 
 #[test]
 fn test_parser_simple_formula() {
     let worksheets = vec!["Sheet1".to_string(), "Sheet2".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Reference cell is Sheet1!A1
     let cell_reference = CellReferenceRC {
@@ -86,14 +86,14 @@ fn test_parser_simple_formula() {
         column: 1,
     };
 
-    let t = parser.parse("C3+Sheet2!D4", &Some(cell_reference));
+    let t = parser.parse("C3+Sheet2!D4", &cell_reference);
     assert_eq!(to_rc_format(&t), "R[2]C[2]+Sheet2!R[3]C[3]");
 }
 
 #[test]
 fn test_parser_boolean() {
     let worksheets = vec!["Sheet1".to_string(), "Sheet2".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Reference cell is Sheet1!A1
     let cell_reference = CellReferenceRC {
@@ -102,14 +102,14 @@ fn test_parser_boolean() {
         column: 1,
     };
 
-    let t = parser.parse("true", &Some(cell_reference));
+    let t = parser.parse("true", &cell_reference);
     assert_eq!(to_rc_format(&t), "TRUE");
 }
 
 #[test]
 fn test_parser_bad_formula() {
     let worksheets = vec!["Sheet1".to_string(), "Sheet2".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Reference cell is Sheet1!A1
     let cell_reference = CellReferenceRC {
@@ -117,7 +117,7 @@ fn test_parser_bad_formula() {
         row: 1,
         column: 1,
     };
-    let t = parser.parse("#Value", &Some(cell_reference));
+    let t = parser.parse("#Value", &cell_reference);
     match &t {
         Node::ParseErrorKind {
             formula,
@@ -138,7 +138,7 @@ fn test_parser_bad_formula() {
 #[test]
 fn test_parser_bad_formula_1() {
     let worksheets = vec!["Sheet1".to_string(), "Sheet2".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Reference cell is Sheet1!A1
     let cell_reference = CellReferenceRC {
@@ -146,7 +146,7 @@ fn test_parser_bad_formula_1() {
         row: 1,
         column: 1,
     };
-    let t = parser.parse("<5", &Some(cell_reference));
+    let t = parser.parse("<5", &cell_reference);
     match &t {
         Node::ParseErrorKind {
             formula,
@@ -167,7 +167,7 @@ fn test_parser_bad_formula_1() {
 #[test]
 fn test_parser_bad_formula_2() {
     let worksheets = vec!["Sheet1".to_string(), "Sheet2".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Reference cell is Sheet1!A1
     let cell_reference = CellReferenceRC {
@@ -175,7 +175,7 @@ fn test_parser_bad_formula_2() {
         row: 1,
         column: 1,
     };
-    let t = parser.parse("*5", &Some(cell_reference));
+    let t = parser.parse("*5", &cell_reference);
     match &t {
         Node::ParseErrorKind {
             formula,
@@ -196,7 +196,7 @@ fn test_parser_bad_formula_2() {
 #[test]
 fn test_parser_bad_formula_3() {
     let worksheets = vec!["Sheet1".to_string(), "Sheet2".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Reference cell is Sheet1!A1
     let cell_reference = CellReferenceRC {
@@ -204,7 +204,7 @@ fn test_parser_bad_formula_3() {
         row: 1,
         column: 1,
     };
-    let t = parser.parse("SUM(#VALVE!)", &Some(cell_reference));
+    let t = parser.parse("SUM(#VALVE!)", &cell_reference);
     match &t {
         Node::ParseErrorKind {
             formula,
@@ -225,7 +225,7 @@ fn test_parser_bad_formula_3() {
 #[test]
 fn test_parser_formulas() {
     let worksheets = vec!["Sheet1".to_string(), "Sheet2".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     let formulas = vec![
         Formula {
@@ -259,11 +259,11 @@ fn test_parser_formulas() {
     for formula in formulas {
         let t = parser.parse(
             formula.initial,
-            &Some(CellReferenceRC {
+            &CellReferenceRC {
                 sheet: "Sheet1".to_string(),
                 row: 1,
                 column: 1,
-            }),
+            },
         );
         assert_eq!(to_rc_format(&t), formula.expected);
         assert_eq!(to_string(&t, &cell_reference), formula.initial);
@@ -273,7 +273,7 @@ fn test_parser_formulas() {
 #[test]
 fn test_parser_r1c1_formulas() {
     let worksheets = vec!["Sheet1".to_string(), "Sheet2".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
     parser.set_lexer_mode(LexerMode::R1C1);
 
     let formulas = vec![
@@ -324,11 +324,11 @@ fn test_parser_r1c1_formulas() {
     for formula in formulas {
         let t = parser.parse(
             formula.initial,
-            &Some(CellReferenceRC {
+            &CellReferenceRC {
                 sheet: "Sheet1".to_string(),
                 row: 1,
                 column: 1,
-            }),
+            },
         );
         assert_eq!(to_string(&t, &cell_reference), formula.expected);
         assert_eq!(to_rc_format(&t), formula.initial);
@@ -338,7 +338,7 @@ fn test_parser_r1c1_formulas() {
 #[test]
 fn test_parser_quotes() {
     let worksheets = vec!["Sheet1".to_string(), "Second Sheet".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Reference cell is Sheet1!A1
     let cell_reference = CellReferenceRC {
@@ -347,14 +347,14 @@ fn test_parser_quotes() {
         column: 1,
     };
 
-    let t = parser.parse("C3+'Second Sheet'!D4", &Some(cell_reference));
+    let t = parser.parse("C3+'Second Sheet'!D4", &cell_reference);
     assert_eq!(to_rc_format(&t), "R[2]C[2]+'Second Sheet'!R[3]C[3]");
 }
 
 #[test]
 fn test_parser_escape_quotes() {
     let worksheets = vec!["Sheet1".to_string(), "Second '2' Sheet".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Reference cell is Sheet1!A1
     let cell_reference = CellReferenceRC {
@@ -363,14 +363,14 @@ fn test_parser_escape_quotes() {
         column: 1,
     };
 
-    let t = parser.parse("C3+'Second ''2'' Sheet'!D4", &Some(cell_reference));
+    let t = parser.parse("C3+'Second ''2'' Sheet'!D4", &cell_reference);
     assert_eq!(to_rc_format(&t), "R[2]C[2]+'Second ''2'' Sheet'!R[3]C[3]");
 }
 
 #[test]
 fn test_parser_parenthesis() {
     let worksheets = vec!["Sheet1".to_string(), "Second2".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Reference cell is Sheet1!A1
     let cell_reference = CellReferenceRC {
@@ -379,14 +379,14 @@ fn test_parser_parenthesis() {
         column: 1,
     };
 
-    let t = parser.parse("(C3=\"Yes\")*5", &Some(cell_reference));
+    let t = parser.parse("(C3=\"Yes\")*5", &cell_reference);
     assert_eq!(to_rc_format(&t), "(R[2]C[2]=\"Yes\")*5");
 }
 
 #[test]
 fn test_parser_excel_xlfn() {
     let worksheets = vec!["Sheet1".to_string(), "Second2".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Reference cell is Sheet1!A1
     let cell_reference = CellReferenceRC {
@@ -395,7 +395,7 @@ fn test_parser_excel_xlfn() {
         column: 1,
     };
 
-    let t = parser.parse("_xlfn.CONCAT(C3)", &Some(cell_reference));
+    let t = parser.parse("_xlfn.CONCAT(C3)", &cell_reference);
     assert_eq!(to_rc_format(&t), "CONCAT(R[2]C[2])");
 }
 
@@ -407,9 +407,9 @@ fn test_to_string_displaced() {
         column: 1,
     };
     let worksheets = vec!["Sheet1".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
-    let node = parser.parse("C3", &Some(context.clone()));
+    let node = parser.parse("C3", context);
     let displace_data = DisplaceData::Column {
         sheet: 0,
         column: 1,
@@ -427,9 +427,9 @@ fn test_to_string_displaced_full_ranges() {
         column: 1,
     };
     let worksheets = vec!["Sheet1".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
-    let node = parser.parse("SUM(3:3)", &Some(context.clone()));
+    let node = parser.parse("SUM(3:3)", context);
     let displace_data = DisplaceData::Column {
         sheet: 0,
         column: 1,
@@ -440,7 +440,7 @@ fn test_to_string_displaced_full_ranges() {
         "SUM(3:3)".to_string()
     );
 
-    let node = parser.parse("SUM(D:D)", &Some(context.clone()));
+    let node = parser.parse("SUM(D:D)", context);
     let displace_data = DisplaceData::Row {
         sheet: 0,
         row: 3,
@@ -460,9 +460,9 @@ fn test_to_string_displaced_too_low() {
         column: 1,
     };
     let worksheets = vec!["Sheet1".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
-    let node = parser.parse("C3", &Some(context.clone()));
+    let node = parser.parse("C3", context);
     let displace_data = DisplaceData::Column {
         sheet: 0,
         column: 1,
@@ -480,9 +480,9 @@ fn test_to_string_displaced_too_high() {
         column: 1,
     };
     let worksheets = vec!["Sheet1".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
-    let node = parser.parse("C3", &Some(context.clone()));
+    let node = parser.parse("C3", context);
     let displace_data = DisplaceData::Column {
         sheet: 0,
         column: 1,

base/src/expressions/parser/tests/test_issue_155.rs

@@ -9,7 +9,7 @@ use crate::expressions::types::CellReferenceRC;
 #[test]
 fn issue_155_parser() {
     let worksheets = vec!["Sheet1".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Reference cell is Sheet1!A1
     let cell_reference = CellReferenceRC {
@@ -17,14 +17,14 @@ fn issue_155_parser() {
         row: 2,
         column: 2,
     };
-    let t = parser.parse("A$1:A2", &Some(cell_reference.clone()));
+    let t = parser.parse("A$1:A2", &cell_reference);
     assert_eq!(to_string(&t, &cell_reference), "A$1:A2");
 }
 
 #[test]
 fn issue_155_parser_case_2() {
     let worksheets = vec!["Sheet1".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Reference cell is Sheet1!A1
     let cell_reference = CellReferenceRC {
@@ -32,14 +32,14 @@ fn issue_155_parser_case_2() {
         row: 20,
         column: 20,
     };
-    let t = parser.parse("C$1:D2", &Some(cell_reference.clone()));
+    let t = parser.parse("C$1:D2", &cell_reference);
     assert_eq!(to_string(&t, &cell_reference), "C$1:D2");
 }
 
 #[test]
 fn issue_155_parser_only_row() {
     let worksheets = vec!["Sheet1".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Reference cell is Sheet1!A1
     let cell_reference = CellReferenceRC {
@@ -48,14 +48,14 @@ fn issue_155_parser_only_row() {
         column: 20,
     };
     // This is tricky, I am not sure what to do in these cases
-    let t = parser.parse("A$2:B1", &Some(cell_reference.clone()));
+    let t = parser.parse("A$2:B1", &cell_reference);
     assert_eq!(to_string(&t, &cell_reference), "A1:B$2");
 }
 
 #[test]
 fn issue_155_parser_only_column() {
     let worksheets = vec!["Sheet1".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Reference cell is Sheet1!A1
     let cell_reference = CellReferenceRC {
@@ -64,6 +64,6 @@ fn issue_155_parser_only_column() {
         column: 20,
     };
     // This is tricky, I am not sure what to do in these cases
-    let t = parser.parse("D1:$A3", &Some(cell_reference.clone()));
+    let t = parser.parse("D1:$A3", &cell_reference);
     assert_eq!(to_string(&t, &cell_reference), "$A1:D3");
 }

base/src/expressions/parser/tests/test_move_formula.rs

@@ -15,7 +15,7 @@ fn test_move_formula() {
         column,
     };
     let worksheets = vec!["Sheet1".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Area is C2:F6
     let area = &Area {
@@ -27,7 +27,7 @@ fn test_move_formula() {
     };
 
     // formula AB31 will not change
-    let node = parser.parse("AB31", &Some(context.clone()));
+    let node = parser.parse("AB31", context);
     let t = move_formula(
         &node,
         &MoveContext {
@@ -43,7 +43,7 @@ fn test_move_formula() {
     assert_eq!(t, "AB31");
 
     // formula $AB$31 will not change
-    let node = parser.parse("AB31", &Some(context.clone()));
+    let node = parser.parse("AB31", context);
     let t = move_formula(
         &node,
         &MoveContext {
@@ -59,7 +59,7 @@ fn test_move_formula() {
     assert_eq!(t, "AB31");
 
     // but formula D5 will change to N15  (N = D + 10)
-    let node = parser.parse("D5", &Some(context.clone()));
+    let node = parser.parse("D5", context);
     let t = move_formula(
         &node,
         &MoveContext {
@@ -75,7 +75,7 @@ fn test_move_formula() {
     assert_eq!(t, "N15");
 
     // Also formula $D$5 will change to N15  (N = D + 10)
-    let node = parser.parse("$D$5", &Some(context.clone()));
+    let node = parser.parse("$D$5", context);
     let t = move_formula(
         &node,
         &MoveContext {
@@ -102,7 +102,7 @@ fn test_move_formula_context_offset() {
         column,
     };
     let worksheets = vec!["Sheet1".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Area is C2:F6
     let area = &Area {
@@ -113,7 +113,7 @@ fn test_move_formula_context_offset() {
         height: 5,
     };
 
-    let node = parser.parse("-X9+C2%", &Some(context.clone()));
+    let node = parser.parse("-X9+C2%", context);
     let t = move_formula(
         &node,
         &MoveContext {
@@ -140,7 +140,7 @@ fn test_move_formula_area_limits() {
         column,
     };
     let worksheets = vec!["Sheet1".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Area is C2:F6
     let area = &Area {
@@ -152,7 +152,7 @@ fn test_move_formula_area_limits() {
     };
 
     // Outside of the area. Not moved
-    let node = parser.parse("B2+B3+C1+G6+H5", &Some(context.clone()));
+    let node = parser.parse("B2+B3+C1+G6+H5", context);
     let t = move_formula(
         &node,
         &MoveContext {
@@ -168,7 +168,7 @@ fn test_move_formula_area_limits() {
     assert_eq!(t, "B2+B3+C1+G6+H5");
 
     // In the area. Moved
-    let node = parser.parse("C2+F4+F5+F6", &Some(context.clone()));
+    let node = parser.parse("C2+F4+F5+F6", context);
     let t = move_formula(
         &node,
         &MoveContext {
@@ -195,7 +195,7 @@ fn test_move_formula_ranges() {
         column,
     };
     let worksheets = vec!["Sheet1".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     let area = &Area {
         sheet: 0,
@@ -205,7 +205,7 @@ fn test_move_formula_ranges() {
         height: 5,
     };
     // Ranges inside the area are fully displaced (absolute or not)
-    let node = parser.parse("SUM(C2:F5)", &Some(context.clone()));
+    let node = parser.parse("SUM(C2:F5)", context);
     let t = move_formula(
         &node,
         &MoveContext {
@@ -220,7 +220,7 @@ fn test_move_formula_ranges() {
     );
     assert_eq!(t, "SUM(M12:P15)");
 
-    let node = parser.parse("SUM($C$2:$F$5)", &Some(context.clone()));
+    let node = parser.parse("SUM($C$2:$F$5)", context);
     let t = move_formula(
         &node,
         &MoveContext {
@@ -236,7 +236,7 @@ fn test_move_formula_ranges() {
     assert_eq!(t, "SUM($M$12:$P$15)");
 
     // Ranges completely outside of the area are not touched
-    let node = parser.parse("SUM(A1:B3)", &Some(context.clone()));
+    let node = parser.parse("SUM(A1:B3)", context);
     let t = move_formula(
         &node,
         &MoveContext {
@@ -251,7 +251,7 @@ fn test_move_formula_ranges() {
     );
     assert_eq!(t, "SUM(A1:B3)");
 
-    let node = parser.parse("SUM($A$1:$B$3)", &Some(context.clone()));
+    let node = parser.parse("SUM($A$1:$B$3)", context);
     let t = move_formula(
         &node,
         &MoveContext {
@@ -267,7 +267,7 @@ fn test_move_formula_ranges() {
     assert_eq!(t, "SUM($A$1:$B$3)");
 
     // Ranges that overlap with the area are also NOT displaced
-    let node = parser.parse("SUM(A1:F5)", &Some(context.clone()));
+    let node = parser.parse("SUM(A1:F5)", context);
     let t = move_formula(
         &node,
         &MoveContext {
@@ -283,7 +283,7 @@ fn test_move_formula_ranges() {
     assert_eq!(t, "SUM(A1:F5)");
 
     // Ranges that contain the area are also NOT displaced
-    let node = parser.parse("SUM(A1:X50)", &Some(context.clone()));
+    let node = parser.parse("SUM(A1:X50)", context);
     let t = move_formula(
         &node,
         &MoveContext {
@@ -318,10 +318,10 @@ fn test_move_formula_wrong_reference() {
         height: 5,
     };
     let worksheets = vec!["Sheet1".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Wrong formulas will NOT be displaced
-    let node = parser.parse("Sheet3!AB31", &Some(context.clone()));
+    let node = parser.parse("Sheet3!AB31", context);
     let t = move_formula(
         &node,
         &MoveContext {
@@ -335,7 +335,7 @@ fn test_move_formula_wrong_reference() {
         },
     );
     assert_eq!(t, "Sheet3!AB31");
-    let node = parser.parse("Sheet3!$X$9", &Some(context.clone()));
+    let node = parser.parse("Sheet3!$X$9", context);
     let t = move_formula(
         &node,
         &MoveContext {
@@ -350,7 +350,7 @@ fn test_move_formula_wrong_reference() {
     );
     assert_eq!(t, "Sheet3!$X$9");
 
-    let node = parser.parse("SUM(Sheet3!D2:D3)", &Some(context.clone()));
+    let node = parser.parse("SUM(Sheet3!D2:D3)", context);
     let t = move_formula(
         &node,
         &MoveContext {
@@ -377,7 +377,7 @@ fn test_move_formula_misc() {
         column,
     };
     let worksheets = vec!["Sheet1".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Area is C2:F6
     let area = &Area {
@@ -387,7 +387,7 @@ fn test_move_formula_misc() {
         width: 4,
         height: 5,
     };
-    let node = parser.parse("X9^C2-F4*H2", &Some(context.clone()));
+    let node = parser.parse("X9^C2-F4*H2", context);
     let t = move_formula(
         &node,
         &MoveContext {
@@ -402,7 +402,7 @@ fn test_move_formula_misc() {
     );
     assert_eq!(t, "X9^M12-P14*H2");
 
-    let node = parser.parse("F5*(-D5)*SUM(A1, X9, $D$5)", &Some(context.clone()));
+    let node = parser.parse("F5*(-D5)*SUM(A1, X9, $D$5)", context);
     let t = move_formula(
         &node,
         &MoveContext {
@@ -417,7 +417,7 @@ fn test_move_formula_misc() {
     );
     assert_eq!(t, "P15*(-N15)*SUM(A1,X9,$N$15)");
 
-    let node = parser.parse("IF(F5 < -D5, X9 & F5, FALSE)", &Some(context.clone()));
+    let node = parser.parse("IF(F5 < -D5, X9 & F5, FALSE)", context);
     let t = move_formula(
         &node,
         &MoveContext {
@@ -445,7 +445,7 @@ fn test_move_formula_another_sheet() {
     };
     // we add two sheets and we cut/paste from Sheet1 to Sheet2
     let worksheets = vec!["Sheet1".to_string(), "Sheet2".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Area is C2:F6
     let area = &Area {
@@ -457,10 +457,7 @@ fn test_move_formula_another_sheet() {
     };
 
     // Formula AB31 and JJ3:JJ4 refers to original Sheet1!AB31 and Sheet1!JJ3:JJ4
-    let node = parser.parse(
-        "AB31*SUM(JJ3:JJ4)+SUM(Sheet2!C2:F6)*SUM(C2:F6)",
-        &Some(context.clone()),
-    );
+    let node = parser.parse("AB31*SUM(JJ3:JJ4)+SUM(Sheet2!C2:F6)*SUM(C2:F6)", context);
     let t = move_formula(
         &node,
         &MoveContext {

base/src/expressions/parser/tests/test_ranges.rs

@@ -14,7 +14,7 @@ struct Formula<'a> {
 #[test]
 fn test_parser_formulas_with_full_ranges() {
     let worksheets = vec!["Sheet1".to_string(), "Second Sheet".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     let formulas = vec![
         Formula {
@@ -52,11 +52,11 @@ fn test_parser_formulas_with_full_ranges() {
     for formula in &formulas {
         let t = parser.parse(
             formula.formula_a1,
-            &Some(CellReferenceRC {
+            &CellReferenceRC {
                 sheet: "Sheet1".to_string(),
                 row: 1,
                 column: 1,
-            }),
+            },
         );
         assert_eq!(to_rc_format(&t), formula.formula_r1c1);
         assert_eq!(to_string(&t, &cell_reference), formula.formula_a1);
@@ -67,11 +67,11 @@ fn test_parser_formulas_with_full_ranges() {
     for formula in &formulas {
         let t = parser.parse(
             formula.formula_r1c1,
-            &Some(CellReferenceRC {
+            &CellReferenceRC {
                 sheet: "Sheet1".to_string(),
                 row: 1,
                 column: 1,
-            }),
+            },
         );
         assert_eq!(to_rc_format(&t), formula.formula_r1c1);
         assert_eq!(to_string(&t, &cell_reference), formula.formula_a1);
@@ -81,7 +81,7 @@ fn test_parser_formulas_with_full_ranges() {
 #[test]
 fn test_range_inverse_order() {
     let worksheets = vec!["Sheet1".to_string(), "Sheet2".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Reference cell is Sheet1!A1
     let cell_reference = CellReferenceRC {
@@ -93,7 +93,7 @@ fn test_range_inverse_order() {
     // D4:C2 => C2:D4
     let t = parser.parse(
         "SUM(D4:C2)*SUM(Sheet2!D4:C20)*SUM($C$20:D4)",
-        &Some(cell_reference.clone()),
+        &cell_reference,
     );
     assert_eq!(
         to_string(&t, &cell_reference),

base/src/expressions/parser/tests/test_stringify.rs

@@ -9,7 +9,7 @@ use crate::expressions::types::CellReferenceRC;
 #[test]
 fn exp_order() {
     let worksheets = vec!["Sheet1".to_string()];
-    let mut parser = Parser::new(worksheets, HashMap::new());
+    let mut parser = Parser::new(worksheets, vec![], HashMap::new());
 
     // Reference cell is Sheet1!A1
     let cell_reference = CellReferenceRC {
@@ -17,18 +17,18 @@ fn exp_order() {
         row: 1,
         column: 1,
     };
-    let t = parser.parse("(1 + 2)^3  + 4", &Some(cell_reference.clone()));
+    let t = parser.parse("(1 + 2)^3  + 4", &cell_reference);
     assert_eq!(to_string(&t, &cell_reference), "(1+2)^3+4");
 
-    let t = parser.parse("(C5 + 3)^R4", &Some(cell_reference.clone()));
+    let t = parser.parse("(C5 + 3)^R4", &cell_reference);
     assert_eq!(to_string(&t, &cell_reference), "(C5+3)^R4");
 
-    let t = parser.parse("(C5 + 3)^(R4*6)", &Some(cell_reference.clone()));
+    let t = parser.parse("(C5 + 3)^(R4*6)", &cell_reference);
     assert_eq!(to_string(&t, &cell_reference), "(C5+3)^(R4*6)");
 
-    let t = parser.parse("(C5)^(R4)", &Some(cell_reference.clone()));
+    let t = parser.parse("(C5)^(R4)", &cell_reference);
     assert_eq!(to_string(&t, &cell_reference), "C5^R4");
 
-    let t = parser.parse("(5)^(4)", &Some(cell_reference.clone()));
+    let t = parser.parse("(5)^(4)", &cell_reference);
     assert_eq!(to_string(&t, &cell_reference), "5^4");
 }

base/src/expressions/parser/tests/test_tables.rs

@@ -63,7 +63,7 @@ fn simple_table() {
     let row_count = 3;
     let tables = create_test_table("tblIncome", &column_names, "A1", row_count);
 
-    let mut parser = Parser::new(worksheets, tables);
+    let mut parser = Parser::new(worksheets, vec![], tables);
     // Reference cell is 'Sheet One'!F2
     let cell_reference = CellReferenceRC {
         sheet: "Sheet One".to_string(),
@@ -72,7 +72,7 @@ fn simple_table() {
     };
 
     let formula = "SUM(tblIncome[[#This Row],[Jan]:[Dec]])";
-    let t = parser.parse(formula, &Some(cell_reference.clone()));
+    let t = parser.parse(formula, &cell_reference);
     assert_eq!(to_string(&t, &cell_reference), "SUM($A$2:$E$2)");
 
     // Cell A3
@@ -82,7 +82,7 @@ fn simple_table() {
         column: 1,
     };
     let formula = "SUBTOTAL(109, tblIncome[Jan])";
-    let t = parser.parse(formula, &Some(cell_reference.clone()));
+    let t = parser.parse(formula, &cell_reference);
     assert_eq!(to_string(&t, &cell_reference), "SUBTOTAL(109,$A$2:$A$3)");
 
     // Cell A3 in 'Second Sheet'
@@ -92,7 +92,7 @@ fn simple_table() {
         column: 1,
     };
     let formula = "SUBTOTAL(109, tblIncome[Jan])";
-    let t = parser.parse(formula, &Some(cell_reference.clone()));
+    let t = parser.parse(formula, &cell_reference);
     assert_eq!(
         to_string(&t, &cell_reference),
         "SUBTOTAL(109,'Sheet One'!$A$2:$A$3)"

base/src/expressions/parser/walk.rs

@@ -263,7 +263,9 @@ pub(crate) fn forward_references(
         // TODO: Not implemented
         Node::ArrayKind(_) => {}
         // Do nothing. Note: we could do a blanket _ => {}
-        Node::VariableKind(_) => {}
+        Node::DefinedNameKind(_) => {}
+        Node::TableNameKind(_) => {}
+        Node::WrongVariableKind(_) => {}
         Node::ErrorKind(_) => {}
         Node::ParseErrorKind { .. } => {}
         Node::EmptyArgKind => {}

base/src/formatter/dates.rs

@@ -1,18 +1,158 @@
 use chrono::Datelike;
+use chrono::Days;
 use chrono::Duration;
+use chrono::Months;
 use chrono::NaiveDate;
 
 use crate::constants::EXCEL_DATE_BASE;
+use crate::constants::MAXIMUM_DATE_SERIAL_NUMBER;
+use crate::constants::MINIMUM_DATE_SERIAL_NUMBER;
 
-pub fn from_excel_date(days: i64) -> NaiveDate {
+#[inline]
+fn convert_to_serial_number(date: NaiveDate) -> i32 {
+    date.num_days_from_ce() - EXCEL_DATE_BASE
+}
+
+fn is_date_within_range(date: NaiveDate) -> bool {
+    convert_to_serial_number(date) >= MINIMUM_DATE_SERIAL_NUMBER
+        && convert_to_serial_number(date) <= MAXIMUM_DATE_SERIAL_NUMBER
+}
+
+pub fn from_excel_date(days: i64) -> Result<NaiveDate, String> {
+    if days < MINIMUM_DATE_SERIAL_NUMBER as i64 {
+        return Err(format!(
+            "Excel date must be greater than {}",
+            MINIMUM_DATE_SERIAL_NUMBER
+        ));
+    };
+    if days > MAXIMUM_DATE_SERIAL_NUMBER as i64 {
+        return Err(format!(
+            "Excel date must be less than {}",
+            MAXIMUM_DATE_SERIAL_NUMBER
+        ));
+    };
     #[allow(clippy::expect_used)]
     let dt = NaiveDate::from_ymd_opt(1900, 1, 1).expect("problem with chrono::NaiveDate");
-    dt + Duration::days(days - 2)
+    Ok(dt + Duration::days(days - 2))
 }
 
 pub fn date_to_serial_number(day: u32, month: u32, year: i32) -> Result<i32, String> {
     match NaiveDate::from_ymd_opt(year, month, day) {
-        Some(native_date) => Ok(native_date.num_days_from_ce() - EXCEL_DATE_BASE),
+        Some(native_date) => Ok(convert_to_serial_number(native_date)),
         None => Err("Out of range parameters for date".to_string()),
     }
 }
+
+pub fn permissive_date_to_serial_number(day: i32, month: i32, year: i32) -> Result<i32, String> {
+    // Excel parses `DATE` very permissively. It allows not just for valid date values, but it
+    // allows for invalid dates as well. If you for example enter `DATE(1900, 1, 32)` it will
+    // return the date `1900-02-01`. Despite giving a day that is out of range it will just
+    // wrap the month and year around.
+    //
+    // This function applies that same logic to dates. And does it in the most compatible way as
+    // possible.
+
+    // Special case for the minimum date
+    if year == 1899 && month == 12 && day == 31 {
+        return Ok(MINIMUM_DATE_SERIAL_NUMBER);
+    }
+    let Some(mut date) = NaiveDate::from_ymd_opt(year, 1, 1) else {
+        return Err("Out of range parameters for date".to_string());
+    };
+
+    // One thing to note for example is that even if you started with a year out of range
+    // but tried to increment the months so that it wraps around into within range, excel
+    // would still return an error.
+    //
+    // I.E. DATE(0,13,-1) will return an error, despite it being equivalent to DATE(1,1,0) which
+    // is within range.
+    //
+    // As a result, we have to run range checks as we parse the date from the biggest unit to the
+    // smallest unit.
+    if !is_date_within_range(date) {
+        return Err("Out of range parameters for date".to_string());
+    }
+
+    date = {
+        let month_diff = month - 1;
+        let abs_month = month_diff.unsigned_abs();
+        if month_diff <= 0 {
+            date = date - Months::new(abs_month);
+        } else {
+            date = date + Months::new(abs_month);
+        }
+        if !is_date_within_range(date) {
+            return Err("Out of range parameters for date".to_string());
+        }
+        date
+    };
+
+    date = {
+        let day_diff = day - 1;
+        let abs_day = day_diff.unsigned_abs() as u64;
+        if day_diff <= 0 {
+            date = date - Days::new(abs_day);
+        } else {
+            date = date + Days::new(abs_day);
+        }
+        if !is_date_within_range(date) {
+            return Err("Out of range parameters for date".to_string());
+        }
+        date
+    };
+
+    Ok(convert_to_serial_number(date))
+}
+
+#[cfg(test)]
+mod tests {
+    use super::*;
+
+    #[test]
+    fn test_permissive_date_to_serial_number() {
+        assert_eq!(
+            permissive_date_to_serial_number(42, 42, 2002),
+            date_to_serial_number(12, 7, 2005)
+        );
+        assert_eq!(
+            permissive_date_to_serial_number(1, 42, 2002),
+            date_to_serial_number(1, 6, 2005)
+        );
+        assert_eq!(
+            permissive_date_to_serial_number(1, 15, 2000),
+            date_to_serial_number(1, 3, 2001)
+        );
+        assert_eq!(
+            permissive_date_to_serial_number(1, 49, 2000),
+            date_to_serial_number(1, 1, 2004)
+        );
+        assert_eq!(
+            permissive_date_to_serial_number(1, 49, 2000),
+            date_to_serial_number(1, 1, 2004)
+        );
+        assert_eq!(
+            permissive_date_to_serial_number(31, 49, 2000),
+            date_to_serial_number(31, 1, 2004)
+        );
+        assert_eq!(
+            permissive_date_to_serial_number(256, 49, 2000),
+            date_to_serial_number(12, 9, 2004)
+        );
+        assert_eq!(
+            permissive_date_to_serial_number(256, 1, 2004),
+            date_to_serial_number(12, 9, 2004)
+        );
+    }
+
+    #[test]
+    fn test_max_and_min_dates() {
+        assert_eq!(
+            permissive_date_to_serial_number(31, 12, 9999),
+            Ok(MAXIMUM_DATE_SERIAL_NUMBER),
+        );
+        assert_eq!(
+            permissive_date_to_serial_number(31, 12, 1899),
+            Ok(MINIMUM_DATE_SERIAL_NUMBER),
+        );
+    }
+}

base/src/formatter/format.rs

@@ -154,15 +154,16 @@ pub fn format_number(value_original: f64, format: &str, locale: &Locale) -> Form
         ParsePart::Date(p) => {
             let tokens = &p.tokens;
             let mut text = "".to_string();
-            if !(1.0..=2_958_465.0).contains(&value) {
-                // 2_958_465 is 31 December 9999
-                return Formatted {
-                    text: "#VALUE!".to_owned(),
-                    color: None,
-                    error: Some("Date negative or too long".to_owned()),
-                };
-            }
-            let date = from_excel_date(value as i64);
+            let date = match from_excel_date(value as i64) {
+                Ok(d) => d,
+                Err(e) => {
+                    return Formatted {
+                        text: "#VALUE!".to_owned(),
+                        color: None,
+                        error: Some(e),
+                    }
+                }
+            };
             for token in tokens {
                 match token {
                     TextToken::Literal(c) => {

base/src/functions/date_and_time.rs

@@ -3,8 +3,11 @@ use chrono::Datelike;
 use chrono::Months;
 use chrono::Timelike;
 
+use crate::constants::MAXIMUM_DATE_SERIAL_NUMBER;
+use crate::constants::MINIMUM_DATE_SERIAL_NUMBER;
 use crate::expressions::types::CellReferenceIndex;
 use crate::formatter::dates::date_to_serial_number;
+use crate::formatter::dates::permissive_date_to_serial_number;
 use crate::model::get_milliseconds_since_epoch;
 use crate::{
     calc_result::CalcResult, constants::EXCEL_DATE_BASE, expressions::parser::Node,
@@ -18,20 +21,19 @@ impl Model {
             return CalcResult::new_args_number_error(cell);
         }
         let serial_number = match self.get_number(&args[0], cell) {
-            Ok(c) => {
-                let t = c.floor() as i64;
-                if t < 0 {
-                    return CalcResult::Error {
-                        error: Error::NUM,
-                        origin: cell,
-                        message: "Function DAY parameter 1 value is negative. It should be positive or zero.".to_string(),
-                    };
-                }
-                t
-            }
+            Ok(c) => c.floor() as i64,
             Err(s) => return s,
         };
-        let date = from_excel_date(serial_number);
+        let date = match from_excel_date(serial_number) {
+            Ok(date) => date,
+            Err(_) => {
+                return CalcResult::Error {
+                    error: Error::NUM,
+                    origin: cell,
+                    message: "Out of range parameters for date".to_string(),
+                }
+            }
+        };
         let day = date.day() as f64;
         CalcResult::Number(day)
     }
@@ -42,20 +44,19 @@ impl Model {
             return CalcResult::new_args_number_error(cell);
         }
         let serial_number = match self.get_number(&args[0], cell) {
-            Ok(c) => {
-                let t = c.floor() as i64;
-                if t < 0 {
-                    return CalcResult::Error {
-                        error: Error::NUM,
-                        origin: cell,
-                        message: "Function MONTH parameter 1 value is negative. It should be positive or zero.".to_string(),
-                    };
-                }
-                t
-            }
+            Ok(c) => c.floor() as i64,
             Err(s) => return s,
         };
-        let date = from_excel_date(serial_number);
+        let date = match from_excel_date(serial_number) {
+            Ok(date) => date,
+            Err(_) => {
+                return CalcResult::Error {
+                    error: Error::NUM,
+                    origin: cell,
+                    message: "Out of range parameters for date".to_string(),
+                }
+            }
+        };
         let month = date.month() as f64;
         CalcResult::Number(month)
     }
@@ -79,6 +80,23 @@ impl Model {
             }
             Err(s) => return s,
         };
+        let date = match from_excel_date(serial_number) {
+            Ok(date) => date,
+            Err(_) => {
+                return CalcResult::Error {
+                    error: Error::NUM,
+                    origin: cell,
+                    message: "Out of range parameters for date".to_string(),
+                }
+            }
+        };
+        if serial_number > MAXIMUM_DATE_SERIAL_NUMBER as i64 {
+            return CalcResult::Error {
+                error: Error::NUM,
+                origin: cell,
+                message: "Function DAY parameter 1 value is too large.".to_string(),
+            };
+        }
 
         let months = match self.get_number_no_bools(&args[1], cell) {
             Ok(c) => {
@@ -91,9 +109,9 @@ impl Model {
         let months_abs = months.unsigned_abs();
 
         let native_date = if months > 0 {
-            from_excel_date(serial_number) + Months::new(months_abs)
+            date + Months::new(months_abs)
         } else {
-            from_excel_date(serial_number) - Months::new(months_abs)
+            date - Months::new(months_abs)
         };
 
         // Instead of calculating the end of month we compute the first day of the following month
@@ -137,32 +155,18 @@ impl Model {
         let month = match self.get_number(&args[1], cell) {
             Ok(c) => {
                 let t = c.floor();
-                if t < 0.0 {
-                    return CalcResult::Error {
-                        error: Error::NUM,
-                        origin: cell,
-                        message: "Out of range parameters for date".to_string(),
-                    };
-                }
-                t as u32
+                t as i32
             }
             Err(s) => return s,
         };
         let day = match self.get_number(&args[2], cell) {
             Ok(c) => {
                 let t = c.floor();
-                if t < 0.0 {
-                    return CalcResult::Error {
-                        error: Error::NUM,
-                        origin: cell,
-                        message: "Out of range parameters for date".to_string(),
-                    };
-                }
-                t as u32
+                t as i32
             }
             Err(s) => return s,
         };
-        match date_to_serial_number(day, month, year) {
+        match permissive_date_to_serial_number(day, month, year) {
             Ok(serial_number) => CalcResult::Number(serial_number as f64),
             Err(message) => CalcResult::Error {
                 error: Error::NUM,
@@ -178,20 +182,19 @@ impl Model {
             return CalcResult::new_args_number_error(cell);
         }
         let serial_number = match self.get_number(&args[0], cell) {
-            Ok(c) => {
-                let t = c.floor() as i64;
-                if t < 0 {
-                    return CalcResult::Error {
-                        error: Error::NUM,
-                        origin: cell,
-                        message: "Function YEAR parameter 1 value is negative. It should be positive or zero.".to_string(),
-                    };
-                }
-                t
-            }
+            Ok(c) => c.floor() as i64,
             Err(s) => return s,
         };
-        let date = from_excel_date(serial_number);
+        let date = match from_excel_date(serial_number) {
+            Ok(date) => date,
+            Err(_) => {
+                return CalcResult::Error {
+                    error: Error::NUM,
+                    origin: cell,
+                    message: "Out of range parameters for date".to_string(),
+                }
+            }
+        };
         let year = date.year() as f64;
         CalcResult::Number(year)
     }
@@ -203,20 +206,19 @@ impl Model {
             return CalcResult::new_args_number_error(cell);
         }
         let serial_number = match self.get_number(&args[0], cell) {
-            Ok(c) => {
-                let t = c.floor() as i64;
-                if t < 0 {
-                    return CalcResult::Error {
-                        error: Error::NUM,
-                        origin: cell,
-                        message: "Parameter 1 value is negative. It should be positive or zero."
-                            .to_string(),
-                    };
-                }
-                t
-            }
+            Ok(c) => c.floor() as i64,
             Err(s) => return s,
         };
+        let date = match from_excel_date(serial_number) {
+            Ok(date) => date,
+            Err(_) => {
+                return CalcResult::Error {
+                    error: Error::NUM,
+                    origin: cell,
+                    message: "Out of range parameters for date".to_string(),
+                }
+            }
+        };
 
         let months = match self.get_number(&args[1], cell) {
             Ok(c) => {
@@ -229,13 +231,13 @@ impl Model {
         let months_abs = months.unsigned_abs();
 
         let native_date = if months > 0 {
-            from_excel_date(serial_number) + Months::new(months_abs)
+            date + Months::new(months_abs)
         } else {
-            from_excel_date(serial_number) - Months::new(months_abs)
+            date - Months::new(months_abs)
         };
 
         let serial_number = native_date.num_days_from_ce() - EXCEL_DATE_BASE;
-        if serial_number < 0 {
+        if serial_number < MINIMUM_DATE_SERIAL_NUMBER {
             return CalcResult::Error {
                 error: Error::NUM,
                 origin: cell,

base/src/functions/financial.rs

@@ -2,7 +2,7 @@ use chrono::Datelike;
 
 use crate::{
     calc_result::CalcResult,
-    constants::{LAST_COLUMN, LAST_ROW},
+    constants::{LAST_COLUMN, LAST_ROW, MAXIMUM_DATE_SERIAL_NUMBER, MINIMUM_DATE_SERIAL_NUMBER},
     expressions::{parser::Node, token::Error, types::CellReferenceIndex},
     formatter::dates::from_excel_date,
     model::Model,
@@ -13,37 +13,38 @@ use super::financial_util::{compute_irr, compute_npv, compute_rate, compute_xirr
 // See:
 // https://github.com/apache/openoffice/blob/c014b5f2b55cff8d4b0c952d5c16d62ecde09ca1/main/scaddins/source/analysis/financial.cxx
 
-// FIXME: Is this enough?
-fn is_valid_date(date: f64) -> bool {
-    date > 0.0
-}
-
-fn is_less_than_one_year(start_date: i64, end_date: i64) -> bool {
+fn is_less_than_one_year(start_date: i64, end_date: i64) -> Result<bool, String> {
+    let end = match from_excel_date(end_date) {
+        Ok(s) => s,
+        Err(s) => return Err(s),
+    };
+    let start = match from_excel_date(start_date) {
+        Ok(s) => s,
+        Err(s) => return Err(s),
+    };
     if end_date - start_date < 365 {
-        return true;
+        return Ok(true);
     }
-    let end = from_excel_date(end_date);
-    let start = from_excel_date(start_date);
     let end_year = end.year();
     let start_year = start.year();
     if end_year == start_year {
-        return true;
+        return Ok(true);
     }
     if end_year != start_year + 1 {
-        return false;
+        return Ok(false);
     }
     let start_month = start.month();
     let end_month = end.month();
     if end_month < start_month {
-        return true;
+        return Ok(true);
     }
     if end_month > start_month {
-        return false;
+        return Ok(false);
     }
     // we are one year later same month
     let start_day = start.day();
     let end_day = end.day();
-    end_day <= start_day
+    Ok(end_day <= start_day)
 }
 
 fn compute_payment(
@@ -436,7 +437,7 @@ impl Model {
         }
         if rate == -1.0 {
             return CalcResult::Error {
-                error: Error::NUM,
+                error: Error::DIV,
                 origin: cell,
                 message: "Rate must be != -1".to_string(),
             };
@@ -923,7 +924,9 @@ impl Model {
         }
         let first_date = dates[0];
         for date in &dates {
-            if !is_valid_date(*date) {
+            if *date < MINIMUM_DATE_SERIAL_NUMBER as f64
+                || *date > MAXIMUM_DATE_SERIAL_NUMBER as f64
+            {
                 // Excel docs claim that if any number in dates is not a valid date,
                 // XNPV returns the #VALUE! error value, but it seems to return #VALUE!
                 return CalcResult::new_error(
@@ -989,7 +992,9 @@ impl Model {
         }
         let first_date = dates[0];
         for date in &dates {
-            if !is_valid_date(*date) {
+            if *date < MINIMUM_DATE_SERIAL_NUMBER as f64
+                || *date > MAXIMUM_DATE_SERIAL_NUMBER as f64
+            {
                 return CalcResult::new_error(
                     Error::NUM,
                     cell,
@@ -1373,9 +1378,10 @@ impl Model {
             Ok(f) => f,
             Err(s) => return s,
         };
-        if !is_valid_date(settlement) || !is_valid_date(maturity) {
-            return CalcResult::new_error(Error::NUM, cell, "Invalid date".to_string());
-        }
+        let less_than_one_year = match is_less_than_one_year(settlement as i64, maturity as i64) {
+            Ok(f) => f,
+            Err(_) => return CalcResult::new_error(Error::NUM, cell, "Invalid date".to_string()),
+        };
         if settlement > maturity {
             return CalcResult::new_error(
                 Error::NUM,
@@ -1383,7 +1389,7 @@ impl Model {
                 "settlement should be <= maturity".to_string(),
             );
         }
-        if !is_less_than_one_year(settlement as i64, maturity as i64) {
+        if !less_than_one_year {
             return CalcResult::new_error(
                 Error::NUM,
                 cell,
@@ -1437,9 +1443,10 @@ impl Model {
             Ok(f) => f,
             Err(s) => return s,
         };
-        if !is_valid_date(settlement) || !is_valid_date(maturity) {
-            return CalcResult::new_error(Error::NUM, cell, "Invalid date".to_string());
-        }
+        let less_than_one_year = match is_less_than_one_year(settlement as i64, maturity as i64) {
+            Ok(f) => f,
+            Err(_) => return CalcResult::new_error(Error::NUM, cell, "Invalid date".to_string()),
+        };
         if settlement > maturity {
             return CalcResult::new_error(
                 Error::NUM,
@@ -1447,7 +1454,7 @@ impl Model {
                 "settlement should be <= maturity".to_string(),
             );
         }
-        if !is_less_than_one_year(settlement as i64, maturity as i64) {
+        if !less_than_one_year {
             return CalcResult::new_error(
                 Error::NUM,
                 cell,
@@ -1487,9 +1494,10 @@ impl Model {
             Ok(f) => f,
             Err(s) => return s,
         };
-        if !is_valid_date(settlement) || !is_valid_date(maturity) {
-            return CalcResult::new_error(Error::NUM, cell, "Invalid date".to_string());
-        }
+        let less_than_one_year = match is_less_than_one_year(settlement as i64, maturity as i64) {
+            Ok(f) => f,
+            Err(_) => return CalcResult::new_error(Error::NUM, cell, "Invalid date".to_string()),
+        };
         if settlement > maturity {
             return CalcResult::new_error(
                 Error::NUM,
@@ -1497,7 +1505,7 @@ impl Model {
                 "settlement should be <= maturity".to_string(),
             );
         }
-        if !is_less_than_one_year(settlement as i64, maturity as i64) {
+        if !less_than_one_year {
             return CalcResult::new_error(
                 Error::NUM,
                 cell,

base/src/functions/information.rs

@@ -247,45 +247,67 @@ impl Model {
             return CalcResult::Number(cell.sheet as f64 + 1.0);
         }
         // The arg could be a defined name or a table
-        let arg = &args[0];
-        if let Node::VariableKind(name) = arg {
-            // Let's see if it is a defined name
-            if let Some(defined_name) = self.parsed_defined_names.get(&(None, name.to_lowercase()))
-            {
-                match defined_name {
-                    ParsedDefinedName::CellReference(reference) => {
-                        return CalcResult::Number(reference.sheet as f64 + 1.0)
+        // let  = &args[0];
+        match &args[0] {
+            Node::DefinedNameKind((name, scope)) => {
+                // Let's see if it is a defined name
+                if let Some(defined_name) = self
+                    .parsed_defined_names
+                    .get(&(*scope, name.to_lowercase()))
+                {
+                    match defined_name {
+                        ParsedDefinedName::CellReference(reference) => {
+                            return CalcResult::Number(reference.sheet as f64 + 1.0)
+                        }
+                        ParsedDefinedName::RangeReference(range) => {
+                            return CalcResult::Number(range.left.sheet as f64 + 1.0)
+                        }
+                        ParsedDefinedName::InvalidDefinedNameFormula => {
+                            return CalcResult::Error {
+                                error: Error::ERROR,
+                                origin: cell,
+                                message: "Invalid name".to_string(),
+                            };
+                        }
                     }
-                    ParsedDefinedName::RangeReference(range) => {
-                        return CalcResult::Number(range.left.sheet as f64 + 1.0)
-                    }
-                    ParsedDefinedName::InvalidDefinedNameFormula => {
-                        return CalcResult::Error {
-                            error: Error::NA,
-                            origin: cell,
-                            message: "Invalid name".to_string(),
-                        };
+                } else {
+                    // This should never happen
+                    return CalcResult::Error {
+                        error: Error::ERROR,
+                        origin: cell,
+                        message: "Invalid name".to_string(),
+                    };
+                }
+            }
+            Node::TableNameKind(name) => {
+                // Now let's see if it is a table
+                for (table_name, table) in &self.workbook.tables {
+                    if table_name == name {
+                        if let Some(sheet_index) = self.get_sheet_index_by_name(&table.sheet_name) {
+                            return CalcResult::Number(sheet_index as f64 + 1.0);
+                        } else {
+                            break;
+                        }
                     }
                 }
             }
-            // Now let's see if it is a table
-            for (table_name, table) in &self.workbook.tables {
-                if table_name == name {
-                    if let Some(sheet_index) = self.get_sheet_index_by_name(&table.sheet_name) {
-                        return CalcResult::Number(sheet_index as f64 + 1.0);
-                    } else {
-                        break;
-                    }
+            Node::WrongVariableKind(name) => {
+                return CalcResult::Error {
+                    error: Error::NAME,
+                    origin: cell,
+                    message: format!("Name not found: {name}"),
+                }
+            }
+            arg => {
+                // Now it should be the name of a sheet
+                let sheet_name = match self.get_string(arg, cell) {
+                    Ok(s) => s,
+                    Err(e) => return e,
+                };
+                if let Some(sheet_index) = self.get_sheet_index_by_name(&sheet_name) {
+                    return CalcResult::Number(sheet_index as f64 + 1.0);
                 }
             }
-        }
-        // Now it should be the name of a sheet
-        let sheet_name = match self.get_string(arg, cell) {
-            Ok(s) => s,
-            Err(e) => return e,
-        };
-        if let Some(sheet_index) = self.get_sheet_index_by_name(&sheet_name) {
-            return CalcResult::Number(sheet_index as f64 + 1.0);
         }
         CalcResult::Error {
             error: Error::NA,

base/src/functions/logical.rs

@@ -7,6 +7,22 @@ use crate::{
 use super::util::compare_values;
 
 impl Model {
+    pub(crate) fn fn_true(&mut self, args: &[Node], cell: CellReferenceIndex) -> CalcResult {
+        if args.is_empty() {
+            CalcResult::Boolean(true)
+        } else {
+            CalcResult::new_args_number_error(cell)
+        }
+    }
+
+    pub(crate) fn fn_false(&mut self, args: &[Node], cell: CellReferenceIndex) -> CalcResult {
+        if args.is_empty() {
+            CalcResult::Boolean(false)
+        } else {
+            CalcResult::new_args_number_error(cell)
+        }
+    }
+
     pub(crate) fn fn_if(&mut self, args: &[Node], cell: CellReferenceIndex) -> CalcResult {
         if args.len() == 2 || args.len() == 3 {
             let cond_result = self.get_boolean(&args[0], cell);
@@ -66,91 +82,61 @@ impl Model {
     }
 
     pub(crate) fn fn_and(&mut self, args: &[Node], cell: CellReferenceIndex) -> CalcResult {
-        let mut true_count = 0;
-        for arg in args {
-            match self.evaluate_node_in_context(arg, cell) {
-                CalcResult::Boolean(b) => {
-                    if !b {
-                        return CalcResult::Boolean(false);
-                    }
-                    true_count += 1;
-                }
-                CalcResult::Number(value) => {
-                    if value == 0.0 {
-                        return CalcResult::Boolean(false);
-                    }
-                    true_count += 1;
-                }
-                CalcResult::String(_value) => {
-                    true_count += 1;
-                }
-                CalcResult::Range { left, right } => {
-                    if left.sheet != right.sheet {
-                        return CalcResult::new_error(
-                            Error::VALUE,
-                            cell,
-                            "Ranges are in different sheets".to_string(),
-                        );
-                    }
-                    for row in left.row..(right.row + 1) {
-                        for column in left.column..(right.column + 1) {
-                            match self.evaluate_cell(CellReferenceIndex {
-                                sheet: left.sheet,
-                                row,
-                                column,
-                            }) {
-                                CalcResult::Boolean(b) => {
-                                    if !b {
-                                        return CalcResult::Boolean(false);
-                                    }
-                                    true_count += 1;
-                                }
-                                CalcResult::Number(value) => {
-                                    if value == 0.0 {
-                                        return CalcResult::Boolean(false);
-                                    }
-                                    true_count += 1;
-                                }
-                                CalcResult::String(_value) => {
-                                    true_count += 1;
-                                }
-                                error @ CalcResult::Error { .. } => return error,
-                                CalcResult::Range { .. } => {}
-                                CalcResult::EmptyCell | CalcResult::EmptyArg => {}
-                            }
-                        }
-                    }
-                }
-                error @ CalcResult::Error { .. } => return error,
-                CalcResult::EmptyCell | CalcResult::EmptyArg => {}
-            };
-        }
-        if true_count == 0 {
-            return CalcResult::new_error(
-                Error::VALUE,
-                cell,
-                "Boolean values not found".to_string(),
-            );
-        }
-        CalcResult::Boolean(true)
+        self.logical_nary(
+            args,
+            cell,
+            |acc, value| acc.unwrap_or(true) && value,
+            Some(false),
+        )
     }
 
     pub(crate) fn fn_or(&mut self, args: &[Node], cell: CellReferenceIndex) -> CalcResult {
+        self.logical_nary(
+            args,
+            cell,
+            |acc, value| acc.unwrap_or(false) || value,
+            Some(true),
+        )
+    }
+
+    pub(crate) fn fn_xor(&mut self, args: &[Node], cell: CellReferenceIndex) -> CalcResult {
+        self.logical_nary(args, cell, |acc, value| acc.unwrap_or(false) ^ value, None)
+    }
+
+    /// Base function for AND, OR, XOR. These are all n-ary functions that perform a boolean operation on a series of
+    /// boolean values. These boolean values are sourced from `args`. Note that there is not a 1-1 relationship between
+    /// arguments and boolean values evaluated (see how Ranges are handled for example).
+    ///
+    /// Each argument in `args` is evaluated and the resulting value is interpreted as a boolean as follows:
+    /// - Boolean: The value is used directly.
+    /// - Number: 0 is FALSE, all other values are TRUE.
+    /// - Range: Each cell in the range is evaluated as if they were individual arguments with some caveats
+    /// - Empty arg: FALSE
+    /// - Empty cell & String: Ignored, behaves exactly like the argument wasn't passed in at all
+    /// - Error: Propagated
+    ///
+    /// If no arguments are provided, or all arguments are ignored, the function returns a #VALUE! error
+    ///
+    /// **`fold_fn`:** The function that combines the running result with the next value boolean value. The running result
+    /// starts as `None`.
+    ///
+    /// **`short_circuit_value`:** If the running result reaches `short_circuit_value`, the function returns early.
+    fn logical_nary(
+        &mut self,
+        args: &[Node],
+        cell: CellReferenceIndex,
+        fold_fn: fn(Option<bool>, bool) -> bool,
+        short_circuit_value: Option<bool>,
+    ) -> CalcResult {
         if args.is_empty() {
             return CalcResult::new_args_number_error(cell);
         }
-        let mut result = false;
+
+        let mut result = None;
         for arg in args {
             match self.evaluate_node_in_context(arg, cell) {
-                CalcResult::Boolean(value) => result = value || result,
-                CalcResult::Number(value) => {
-                    if value != 0.0 {
-                        return CalcResult::Boolean(true);
-                    }
-                }
-                CalcResult::String(_value) => {
-                    return CalcResult::Boolean(true);
-                }
+                CalcResult::Boolean(value) => result = Some(fold_fn(result, value)),
+                CalcResult::Number(value) => result = Some(fold_fn(result, value != 0.0)),
                 CalcResult::Range { left, right } => {
                     if left.sheet != right.sheet {
                         return CalcResult::new_error(
@@ -166,94 +152,58 @@ impl Model {
                                 row,
                                 column,
                             }) {
-                                CalcResult::Boolean(value) => {
-                                    result = value || result;
-                                }
+                                CalcResult::Boolean(value) => result = Some(fold_fn(result, value)),
                                 CalcResult::Number(value) => {
-                                    if value != 0.0 {
-                                        return CalcResult::Boolean(true);
-                                    }
-                                }
-                                CalcResult::String(_value) => {
-                                    return CalcResult::Boolean(true);
+                                    result = Some(fold_fn(result, value != 0.0))
                                 }
                                 error @ CalcResult::Error { .. } => return error,
-                                CalcResult::Range { .. } => {}
-                                CalcResult::EmptyCell | CalcResult::EmptyArg => {}
+                                CalcResult::EmptyArg => {} // unreachable
+                                CalcResult::Range { .. }
+                                | CalcResult::String { .. }
+                                | CalcResult::EmptyCell => {}
+                            }
+                            if let (Some(current_result), Some(short_circuit_value)) =
+                                (result, short_circuit_value)
+                            {
+                                if current_result == short_circuit_value {
+                                    return CalcResult::Boolean(current_result);
+                                }
                             }
                         }
                     }
                 }
                 error @ CalcResult::Error { .. } => return error,
-                CalcResult::EmptyCell | CalcResult::EmptyArg => {}
-            };
-        }
-        CalcResult::Boolean(result)
-    }
-
-    /// XOR(logical1, [logical]*,...)
-    /// Logical1 is required, subsequent logical values are optional. Can be logical values, arrays, or references.
-    /// The result of XOR is TRUE when the number of TRUE inputs is odd and FALSE when the number of TRUE inputs is even.
-    pub(crate) fn fn_xor(&mut self, args: &[Node], cell: CellReferenceIndex) -> CalcResult {
-        let mut true_count = 0;
-        let mut false_count = 0;
-        for arg in args {
-            match self.evaluate_node_in_context(arg, cell) {
-                CalcResult::Boolean(b) => {
-                    if b {
-                        true_count += 1;
-                    } else {
-                        false_count += 1;
-                    }
-                }
-                CalcResult::Number(value) => {
-                    if value != 0.0 {
-                        true_count += 1;
-                    } else {
-                        false_count += 1;
-                    }
-                }
-                CalcResult::Range { left, right } => {
-                    if left.sheet != right.sheet {
-                        return CalcResult::new_error(
-                            Error::VALUE,
-                            cell,
-                            "Ranges are in different sheets".to_string(),
-                        );
-                    }
-                    for row in left.row..(right.row + 1) {
-                        for column in left.column..(right.column + 1) {
-                            match self.evaluate_cell(CellReferenceIndex {
-                                sheet: left.sheet,
-                                row,
-                                column,
-                            }) {
-                                CalcResult::Boolean(b) => {
-                                    if b {
-                                        true_count += 1;
-                                    } else {
-                                        false_count += 1;
-                                    }
-                                }
-                                CalcResult::Number(value) => {
-                                    if value != 0.0 {
-                                        true_count += 1;
-                                    } else {
-                                        false_count += 1;
-                                    }
-                                }
-                                _ => {}
-                            }
+                CalcResult::EmptyArg => result = Some(result.unwrap_or(false)),
+                // Strings are ignored unless they are "TRUE" or "FALSE" (case insensitive). EXCEPT if the string value
+                // comes from a reference, in which case it is always ignored regardless of its value.
+                CalcResult::String(..) => {
+                    if !matches!(arg, Node::ReferenceKind { .. }) {
+                        if let Ok(f) = self.get_boolean(arg, cell) {
+                            result = Some(fold_fn(result, f));
                         }
                     }
                 }
-                _ => {}
-            };
+                // References to empty cells are ignored. If all args are ignored the result is #VALUE!
+                CalcResult::EmptyCell => {}
+            }
+
+            if let (Some(current_result), Some(short_circuit_value)) = (result, short_circuit_value)
+            {
+                if current_result == short_circuit_value {
+                    return CalcResult::Boolean(current_result);
+                }
+            }
         }
-        if true_count == 0 && false_count == 0 {
-            return CalcResult::new_error(Error::VALUE, cell, "No booleans found".to_string());
+
+        if let Some(result) = result {
+            CalcResult::Boolean(result)
+        } else {
+            CalcResult::new_error(
+                Error::VALUE,
+                cell,
+                "No logical values in argument list".to_string(),
+            )
         }
-        CalcResult::Boolean(true_count % 2 == 1)
     }
 
     /// =SWITCH(expression, case1, value1, [case, value]*, [default])

base/src/functions/mod.rs

@@ -949,7 +949,7 @@ impl Model {
         match kind {
             // Logical
             Function::And => self.fn_and(args, cell),
-            Function::False => CalcResult::Boolean(false),
+            Function::False => self.fn_false(args, cell),
             Function::If => self.fn_if(args, cell),
             Function::Iferror => self.fn_iferror(args, cell),
             Function::Ifna => self.fn_ifna(args, cell),
@@ -957,7 +957,7 @@ impl Model {
             Function::Not => self.fn_not(args, cell),
             Function::Or => self.fn_or(args, cell),
             Function::Switch => self.fn_switch(args, cell),
-            Function::True => CalcResult::Boolean(true),
+            Function::True => self.fn_true(args, cell),
             Function::Xor => self.fn_xor(args, cell),
             // Math and trigonometry
             Function::Sin => self.fn_sin(args, cell),

base/src/lib.rs

@@ -25,6 +25,8 @@
 #![doc = include_str!("../examples/formulas_and_errors.rs")]
 //! ```
 
+#![warn(clippy::print_stdout)]
+
 pub mod calc_result;
 pub mod cell;
 pub mod expressions;

base/src/model.rs

@@ -8,14 +8,15 @@ use crate::{
     cell::CellValue,
     constants::{self, LAST_COLUMN, LAST_ROW},
     expressions::{
+        lexer::LexerMode,
         parser::{
             move_formula::{move_formula, MoveContext},
-            stringify::{to_rc_format, to_string},
+            stringify::{rename_defined_name_in_node, to_rc_format, to_string},
             Node, Parser,
         },
         token::{get_error_by_name, Error, OpCompare, OpProduct, OpSum, OpUnary},
         types::*,
-        utils::{self, is_valid_column_number, is_valid_row},
+        utils::{self, is_valid_column_number, is_valid_identifier, is_valid_row},
     },
     formatter::{
         format::{format_number, parse_formatted_number},
@@ -72,6 +73,7 @@ pub(crate) enum CellState {
 }
 
 /// A parsed formula for a defined name
+#[derive(Clone)]
 pub(crate) enum ParsedDefinedName {
     /// CellReference (`=C4`)
     CellReference(CellReferenceIndex),
@@ -79,9 +81,6 @@ pub(crate) enum ParsedDefinedName {
     RangeReference(Range),
     /// `=SomethingElse`
     InvalidDefinedNameFormula,
-    // TODO: Support constants in defined names
-    // TODO: Support formulas in defined names
-    // TODO: Support tables in defined names
 }
 
 /// A dynamical IronCalc model.
@@ -417,38 +416,40 @@ impl Model {
                 // TODO: NOT IMPLEMENTED
                 CalcResult::new_error(Error::NIMPL, cell, "Arrays not implemented".to_string())
             }
-            VariableKind(defined_name) => {
-                let parsed_defined_name = self
-                    .parsed_defined_names
-                    .get(&(Some(cell.sheet), defined_name.to_lowercase())) // try getting local defined name
-                    .or_else(|| {
-                        self.parsed_defined_names
-                            .get(&(None, defined_name.to_lowercase()))
-                    }); // fallback to global
-
-                if let Some(parsed_defined_name) = parsed_defined_name {
+            DefinedNameKind((name, scope)) => {
+                if let Ok(Some(parsed_defined_name)) = self.get_parsed_defined_name(name, *scope) {
                     match parsed_defined_name {
                         ParsedDefinedName::CellReference(reference) => {
-                            self.evaluate_cell(*reference)
+                            self.evaluate_cell(reference)
                         }
                         ParsedDefinedName::RangeReference(range) => CalcResult::Range {
                             left: range.left,
                             right: range.right,
                         },
                         ParsedDefinedName::InvalidDefinedNameFormula => CalcResult::new_error(
-                            Error::NIMPL,
+                            Error::NAME,
                             cell,
-                            format!("Defined name \"{}\" is not a reference.", defined_name),
+                            format!("Defined name \"{}\" is not a reference.", name),
                         ),
                     }
                 } else {
                     CalcResult::new_error(
                         Error::NAME,
                         cell,
-                        format!("Defined name \"{}\" not found.", defined_name),
+                        format!("Defined name \"{}\" not found.", name),
                     )
                 }
             }
+            TableNameKind(s) => CalcResult::new_error(
+                Error::NAME,
+                cell,
+                format!("table name \"{}\" not supported.", s),
+            ),
+            WrongVariableKind(s) => CalcResult::new_error(
+                Error::NAME,
+                cell,
+                format!("Variable name \"{}\" not found.", s),
+            ),
             CompareKind { kind, left, right } => {
                 let l = self.evaluate_node_in_context(left, cell);
                 if l.is_error() {
@@ -682,6 +683,13 @@ impl Model {
         Err(format!("Invalid color: {}", color))
     }
 
+    /// Changes the visibility of a sheet
+    pub fn set_sheet_state(&mut self, sheet: u32, state: SheetState) -> Result<(), String> {
+        let worksheet = self.workbook.worksheet_mut(sheet)?;
+        worksheet.state = state;
+        Ok(())
+    }
+
     /// Makes the grid lines in the sheet visible (`true`) or hidden (`false`)
     pub fn set_show_grid_lines(&mut self, sheet: u32, show_grid_lines: bool) -> Result<(), String> {
         let worksheet = self.workbook.worksheet_mut(sheet)?;
@@ -857,6 +865,11 @@ impl Model {
 
         let worksheet_names = worksheets.iter().map(|s| s.get_name()).collect();
 
+        let defined_names = workbook
+            .get_defined_names_with_scope()
+            .iter()
+            .map(|s| (s.0.to_owned(), s.1))
+            .collect();
         // add all tables
         // let mut tables = Vec::new();
         // for worksheet in worksheets {
@@ -866,7 +879,7 @@ impl Model {
         //     }
         //     tables.push(tables_in_sheet);
         // }
-        let parser = Parser::new(worksheet_names, workbook.tables.clone());
+        let parser = Parser::new(worksheet_names, defined_names, workbook.tables.clone());
         let cells = HashMap::new();
         let locale = get_locale(&workbook.settings.locale)
             .map_err(|_| "Invalid locale".to_string())?
@@ -1027,7 +1040,7 @@ impl Model {
                 column: source.column,
             };
             let formula_str = move_formula(
-                &self.parser.parse(formula, &Some(cell_reference)),
+                &self.parser.parse(formula, &cell_reference),
                 &MoveContext {
                     source_sheet_name: &source_sheet_name,
                     row: source.row,
@@ -1135,7 +1148,7 @@ impl Model {
                 row: source.row,
                 column: source.column,
             };
-            let formula = &self.parser.parse(formula_str, &Some(cell_reference));
+            let formula = &self.parser.parse(formula_str, &cell_reference);
             let cell_reference = CellReferenceRC {
                 sheet: target_sheet_name,
                 row: target.row,
@@ -1511,13 +1524,11 @@ impl Model {
             column,
         };
         let shared_formulas = &mut worksheet.shared_formulas;
-        let mut parsed_formula = self.parser.parse(formula, &Some(cell_reference.clone()));
+        let mut parsed_formula = self.parser.parse(formula, &cell_reference);
         // If the formula fails to parse try adding a parenthesis
         // SUM(A1:A3  => SUM(A1:A3)
         if let Node::ParseErrorKind { .. } = parsed_formula {
-            let new_parsed_formula = self
-                .parser
-                .parse(&format!("{})", formula), &Some(cell_reference));
+            let new_parsed_formula = self.parser.parse(&format!("{})", formula), &cell_reference);
             match new_parsed_formula {
                 Node::ParseErrorKind { .. } => {}
                 _ => parsed_formula = new_parsed_formula,
@@ -1596,6 +1607,42 @@ impl Model {
             .set_cell_with_number(row, column, value, style)
     }
 
+    // Helper function that returns a defined name given the name and scope
+    fn get_parsed_defined_name(
+        &self,
+        name: &str,
+        scope: Option<u32>,
+    ) -> Result<Option<ParsedDefinedName>, String> {
+        let name_upper = name.to_uppercase();
+
+        for (key, df) in &self.parsed_defined_names {
+            if key.1.to_uppercase() == name_upper && key.0 == scope {
+                return Ok(Some(df.clone()));
+            }
+        }
+        Ok(None)
+    }
+
+    // Returns the formula for a defined name
+    pub(crate) fn get_defined_name_formula(
+        &self,
+        name: &str,
+        scope: Option<u32>,
+    ) -> Result<String, String> {
+        let name_upper = name.to_uppercase();
+        let defined_names = &self.workbook.defined_names;
+        let sheet_id = match scope {
+            Some(index) => Some(self.workbook.worksheet(index)?.sheet_id),
+            None => None,
+        };
+        for df in defined_names {
+            if df.name.to_uppercase() == name_upper && df.sheet_id == sheet_id {
+                return Ok(df.formula.clone());
+            }
+        }
+        Err("Defined name not found".to_string())
+    }
+
     /// Gets the Excel Value (Bool, Number, String) of a cell
     ///
     /// See also:
@@ -1986,6 +2033,137 @@ impl Model {
             .worksheet_mut(sheet)?
             .set_row_height(column, height)
     }
+
+    /// Adds a new defined name
+    pub fn new_defined_name(
+        &mut self,
+        name: &str,
+        scope: Option<u32>,
+        formula: &str,
+    ) -> Result<(), String> {
+        if !is_valid_identifier(name) {
+            return Err("Invalid defined name".to_string());
+        };
+        let name_upper = name.to_uppercase();
+        let defined_names = &self.workbook.defined_names;
+        let sheet_id = match scope {
+            Some(index) => Some(self.workbook.worksheet(index)?.sheet_id),
+            None => None,
+        };
+        // if the defined name already exist return error
+        for df in defined_names {
+            if df.name.to_uppercase() == name_upper && df.sheet_id == sheet_id {
+                return Err("Defined name already exists".to_string());
+            }
+        }
+        self.workbook.defined_names.push(DefinedName {
+            name: name.to_string(),
+            formula: formula.to_string(),
+            sheet_id,
+        });
+        self.reset_parsed_structures();
+
+        Ok(())
+    }
+
+    /// Delete defined name of name and scope
+    pub fn delete_defined_name(&mut self, name: &str, scope: Option<u32>) -> Result<(), String> {
+        let name_upper = name.to_uppercase();
+        let defined_names = &self.workbook.defined_names;
+        let sheet_id = match scope {
+            Some(index) => Some(self.workbook.worksheet(index)?.sheet_id),
+            None => None,
+        };
+        let mut index = None;
+        for (i, df) in defined_names.iter().enumerate() {
+            if df.name.to_uppercase() == name_upper && df.sheet_id == sheet_id {
+                index = Some(i);
+            }
+        }
+        if let Some(i) = index {
+            self.workbook.defined_names.remove(i);
+            self.reset_parsed_structures();
+            Ok(())
+        } else {
+            Err("Defined name not found".to_string())
+        }
+    }
+
+    /// Update defined name
+    pub fn update_defined_name(
+        &mut self,
+        name: &str,
+        scope: Option<u32>,
+        new_name: &str,
+        new_scope: Option<u32>,
+        new_formula: &str,
+    ) -> Result<(), String> {
+        if !is_valid_identifier(new_name) {
+            return Err("Invalid defined name".to_string());
+        };
+        let name_upper = name.to_uppercase();
+        let new_name_upper = new_name.to_uppercase();
+
+        if name_upper != new_name_upper || scope != new_scope {
+            for key in self.parsed_defined_names.keys() {
+                if key.1.to_uppercase() == new_name_upper && key.0 == new_scope {
+                    return Err("Defined name already exists".to_string());
+                }
+            }
+        }
+        let defined_names = &self.workbook.defined_names;
+        let sheet_id = match scope {
+            Some(index) => Some(self.workbook.worksheet(index)?.sheet_id),
+            None => None,
+        };
+
+        let new_sheet_id = match new_scope {
+            Some(index) => Some(self.workbook.worksheet(index)?.sheet_id),
+            None => None,
+        };
+
+        let mut index = None;
+        for (i, df) in defined_names.iter().enumerate() {
+            if df.name.to_uppercase() == name_upper && df.sheet_id == sheet_id {
+                index = Some(i);
+            }
+        }
+        if let Some(i) = index {
+            if let Some(df) = self.workbook.defined_names.get_mut(i) {
+                if new_name != df.name {
+                    // We need to rename the name in every formula:
+
+                    // Parse all formulas with the old name
+                    // All internal formulas are R1C1
+                    self.parser.set_lexer_mode(LexerMode::R1C1);
+                    let worksheets = &mut self.workbook.worksheets;
+                    for worksheet in worksheets {
+                        let cell_reference = CellReferenceRC {
+                            sheet: worksheet.get_name(),
+                            row: 1,
+                            column: 1,
+                        };
+                        let mut formulas = Vec::new();
+                        for formula in &worksheet.shared_formulas {
+                            let mut t = self.parser.parse(formula, &cell_reference);
+                            rename_defined_name_in_node(&mut t, name, scope, new_name);
+                            formulas.push(to_rc_format(&t));
+                        }
+                        worksheet.shared_formulas = formulas;
+                    }
+                    // Se the mode back to A1
+                    self.parser.set_lexer_mode(LexerMode::A1);
+                }
+                df.name = new_name.to_string();
+                df.sheet_id = new_sheet_id;
+                df.formula = new_formula.to_string();
+                self.reset_parsed_structures();
+            }
+            Ok(())
+        } else {
+            Err("Defined name not found".to_string())
+        }
+    }
 }
 
 #[cfg(test)]

base/src/new_empty.rs

@@ -85,14 +85,14 @@ impl Model {
         let worksheets = &self.workbook.worksheets;
         for worksheet in worksheets {
             let shared_formulas = &worksheet.shared_formulas;
-            let cell_reference = &Some(CellReferenceRC {
+            let cell_reference = CellReferenceRC {
                 sheet: worksheet.get_name(),
                 row: 1,
                 column: 1,
-            });
+            };
             let mut parse_formula = Vec::new();
             for formula in shared_formulas {
-                let t = self.parser.parse(formula, cell_reference);
+                let t = self.parser.parse(formula, &cell_reference);
                 parse_formula.push(t);
             }
             self.parsed_formulas.push(parse_formula);
@@ -144,8 +144,14 @@ impl Model {
 
     /// Reparses all formulas and defined names
     pub(crate) fn reset_parsed_structures(&mut self) {
+        let defined_names = self
+            .workbook
+            .get_defined_names_with_scope()
+            .iter()
+            .map(|s| (s.0.to_owned(), s.1))
+            .collect();
         self.parser
-            .set_worksheets(self.workbook.get_worksheet_names());
+            .set_worksheets_and_names(self.workbook.get_worksheet_names(), defined_names);
         self.parsed_formulas = vec![];
         self.parse_formulas();
         self.parsed_defined_names = HashMap::new();
@@ -262,11 +268,11 @@ impl Model {
         // We use iter because the default would be a mut_iter and we don't need a mutable reference
         let worksheets = &mut self.workbook.worksheets;
         for worksheet in worksheets {
-            let cell_reference = &Some(CellReferenceRC {
+            let cell_reference = &CellReferenceRC {
                 sheet: worksheet.get_name(),
                 row: 1,
                 column: 1,
-            });
+            };
             let mut formulas = Vec::new();
             for formula in &worksheet.shared_formulas {
                 let mut t = self.parser.parse(formula, cell_reference);
@@ -388,7 +394,7 @@ impl Model {
         let parsed_formulas = Vec::new();
         let worksheets = &workbook.worksheets;
         let worksheet_names = worksheets.iter().map(|s| s.get_name()).collect();
-        let parser = Parser::new(worksheet_names, HashMap::new());
+        let parser = Parser::new(worksheet_names, vec![], HashMap::new());
         let cells = HashMap::new();
 
         // FIXME: Add support for display languages

base/src/test/mod.rs

@@ -13,12 +13,14 @@ mod test_fn_averageifs;
 mod test_fn_choose;
 mod test_fn_concatenate;
 mod test_fn_count;
+mod test_fn_day;
 mod test_fn_exact;
 mod test_fn_financial;
 mod test_fn_formulatext;
 mod test_fn_if;
 mod test_fn_maxifs;
 mod test_fn_minifs;
+mod test_fn_or_xor;
 mod test_fn_product;
 mod test_fn_rept;
 mod test_fn_sum;
@@ -40,13 +42,13 @@ mod test_sheet_markup;
 mod test_sheets;
 mod test_styles;
 mod test_trigonometric;
+mod test_true_false;
 mod test_workbook;
 mod test_worksheet;
 pub(crate) mod util;
 
 mod engineering;
 mod test_fn_offset;
-mod test_fn_or;
 mod test_number_format;
 
 mod test_escape_quotes;

base/src/test/test_date_and_time.rs

@@ -37,12 +37,12 @@ fn test_fn_date_arguments() {
     assert_eq!(model._get_text("A3"), *"#ERROR!");
     assert_eq!(model._get_text("A4"), *"#ERROR!");
 
-    assert_eq!(model._get_text("A5"), *"#NUM!");
-    assert_eq!(model._get_text("A6"), *"#NUM!");
-    assert_eq!(model._get_text("A7"), *"#NUM!");
-    assert_eq!(model._get_text("A8"), *"#NUM!");
+    assert_eq!(model._get_text("A5"), *"10/10/1974");
+    assert_eq!(model._get_text("A6"), *"21/01/1975");
+    assert_eq!(model._get_text("A7"), *"10/02/1976");
+    assert_eq!(model._get_text("A8"), *"02/03/1975");
 
-    assert_eq!(model._get_text("A9"), *"#NUM!");
+    assert_eq!(model._get_text("A9"), *"01/03/1975");
     assert_eq!(model._get_text("A10"), *"29/02/1976");
     assert_eq!(
         model.get_cell_value_by_ref("Sheet1!A10"),
@@ -64,15 +64,18 @@ fn test_date_out_of_range() {
 
     // year (actually years < 1900 don't really make sense)
     model._set("C1", "=DATE(-1, 5, 5)");
+    // excel is not compatible with years past 9999
+    model._set("C2", "=DATE(10000, 5, 5)");
 
     model.evaluate();
 
-    assert_eq!(model._get_text("A1"), *"#NUM!");
-    assert_eq!(model._get_text("A2"), *"#NUM!");
-    assert_eq!(model._get_text("B1"), *"#NUM!");
-    assert_eq!(model._get_text("B2"), *"#NUM!");
+    assert_eq!(model._get_text("A1"), *"10/12/2021");
+    assert_eq!(model._get_text("A2"), *"10/01/2023");
+    assert_eq!(model._get_text("B1"), *"30/04/2042");
+    assert_eq!(model._get_text("B2"), *"01/06/2025");
 
     assert_eq!(model._get_text("C1"), *"#NUM!");
+    assert_eq!(model._get_text("C2"), *"#NUM!");
 }
 
 #[test]
@@ -129,8 +132,7 @@ fn test_day_small_serial() {
     model.evaluate();
 
     assert_eq!(model._get_text("A1"), *"#NUM!");
-    // This agrees with Google Docs and disagrees with Excel
-    assert_eq!(model._get_text("A2"), *"30");
+    assert_eq!(model._get_text("A2"), *"#NUM!");
     // Excel thinks is Feb 29, 1900
     assert_eq!(model._get_text("A3"), *"28");
 
@@ -150,8 +152,7 @@ fn test_month_small_serial() {
     model.evaluate();
 
     assert_eq!(model._get_text("A1"), *"#NUM!");
-    // This agrees with Google Docs and disagrees with Excel
-    assert_eq!(model._get_text("A2"), *"12");
+    assert_eq!(model._get_text("A2"), *"#NUM!");
     // We agree with Excel here (We are both in Feb)
     assert_eq!(model._get_text("A3"), *"2");
 
@@ -171,8 +172,7 @@ fn test_year_small_serial() {
     model.evaluate();
 
     assert_eq!(model._get_text("A1"), *"#NUM!");
-    // This agrees with Google Docs and disagrees with Excel
-    assert_eq!(model._get_text("A2"), *"1899");
+    assert_eq!(model._get_text("A2"), *"#NUM!");
 
     assert_eq!(model._get_text("A3"), *"1900");
 
@@ -204,7 +204,10 @@ fn test_date_early_dates() {
         model.get_cell_value_by_ref("Sheet1!A2"),
         Ok(CellValue::Number(60.0))
     );
-    assert_eq!(model._get_text("B2"), *"#NUM!");
+
+    // This does not agree with Excel, instead of mistakenly allowing
+    // for Feb 29, it will auto-wrap to the next day after Feb 28.
+    assert_eq!(model._get_text("B2"), *"01/03/1900");
 
     // This agrees with Excel from he onward
     assert_eq!(model._get_text("A3"), *"01/03/1900");

base/src/test/test_fn_day.rs

@@ -0,0 +1,15 @@
+#![allow(clippy::unwrap_used)]
+
+use crate::test::util::new_empty_model;
+
+#[test]
+fn test_fn_date_arguments() {
+    let mut model = new_empty_model();
+
+    model._set("A1", "=DAY(95051806)");
+    model._set("A2", "=DAY(2958465)");
+    model.evaluate();
+
+    assert_eq!(model._get_text("A1"), *"#NUM!");
+    assert_eq!(model._get_text("A2"), *"31");
+}

base/src/test/test_fn_or.rs

@@ -1,36 +0,0 @@
-#![allow(clippy::unwrap_used)]
-
-use crate::test::util::new_empty_model;
-
-#[test]
-fn fn_or() {
-    let mut model = new_empty_model();
-    model._set("A1", "=OR(1, 0)");
-    model._set("A2", "=OR(0, 0)");
-    model._set("A3", "=OR(true, false)");
-    model._set("A4", "=OR(false, false)");
-
-    model.evaluate();
-    assert_eq!(model._get_text("A1"), *"TRUE");
-    assert_eq!(model._get_text("A2"), *"FALSE");
-    assert_eq!(model._get_text("A3"), *"TRUE");
-    assert_eq!(model._get_text("A4"), *"FALSE");
-}
-
-#[test]
-fn fn_or_no_arguments() {
-    let mut model = new_empty_model();
-    model._set("A1", "=OR()");
-    model.evaluate();
-    assert_eq!(model._get_text("A1"), *"#ERROR!");
-}
-
-#[test]
-fn fn_or_missing_arguments() {
-    let mut model = new_empty_model();
-    model._set("A1", "=OR(,)");
-    model._set("A2", "=OR(,1)");
-    model.evaluate();
-    assert_eq!(model._get_text("A1"), *"FALSE");
-    assert_eq!(model._get_text("A2"), *"TRUE");
-}

base/src/test/test_fn_or_xor.rs

@@ -0,0 +1,204 @@
+#![allow(clippy::unwrap_used)]
+#![allow(clippy::print_stdout)]
+use crate::test::util::new_empty_model;
+
+// These tests are grouped because in many cases XOR and OR have similar behaviour.
+
+// Test specific to xor
+#[test]
+fn fn_xor() {
+    let mut model = new_empty_model();
+
+    model._set("A1", "=XOR(1, 1, 1, 0, 0)");
+    model._set("A2", "=XOR(1, 1, 0, 0, 0)");
+    model._set("A3", "=XOR(TRUE, TRUE, TRUE, FALSE, FALSE)");
+    model._set("A4", "=XOR(TRUE, TRUE, FALSE, FALSE, FALSE)");
+    model._set("A5", "=XOR(FALSE, FALSE, FALSE, FALSE, FALSE)");
+    model._set("A6", "=XOR(TRUE, TRUE)");
+    model._set("A7", "=XOR(0,0,0)");
+    model._set("A8", "=XOR(0,0,1)");
+    model._set("A9", "=XOR(0,1,0)");
+    model._set("A10", "=XOR(0,1,1)");
+    model._set("A11", "=XOR(1,0,0)");
+    model._set("A12", "=XOR(1,0,1)");
+    model._set("A13", "=XOR(1,1,0)");
+    model._set("A14", "=XOR(1,1,1)");
+
+    model.evaluate();
+
+    assert_eq!(model._get_text("A1"), *"TRUE");
+    assert_eq!(model._get_text("A2"), *"FALSE");
+    assert_eq!(model._get_text("A3"), *"TRUE");
+    assert_eq!(model._get_text("A4"), *"FALSE");
+    assert_eq!(model._get_text("A5"), *"FALSE");
+    assert_eq!(model._get_text("A6"), *"FALSE");
+    assert_eq!(model._get_text("A7"), *"FALSE");
+    assert_eq!(model._get_text("A8"), *"TRUE");
+    assert_eq!(model._get_text("A9"), *"TRUE");
+    assert_eq!(model._get_text("A10"), *"FALSE");
+    assert_eq!(model._get_text("A11"), *"TRUE");
+    assert_eq!(model._get_text("A12"), *"FALSE");
+    assert_eq!(model._get_text("A13"), *"FALSE");
+    assert_eq!(model._get_text("A14"), *"TRUE");
+}
+
+#[test]
+fn fn_or() {
+    let mut model = new_empty_model();
+
+    model._set("A1", "=OR(1, 1, 1, 0, 0)");
+    model._set("A2", "=OR(1, 1, 0, 0, 0)");
+    model._set("A3", "=OR(TRUE, TRUE, TRUE, FALSE, FALSE)");
+    model._set("A4", "=OR(TRUE, TRUE, FALSE, FALSE, FALSE)");
+    model._set("A5", "=OR(FALSE, FALSE, FALSE, FALSE, FALSE)");
+    model._set("A6", "=OR(TRUE, TRUE)");
+
+    model.evaluate();
+
+    assert_eq!(model._get_text("A1"), *"TRUE");
+    assert_eq!(model._get_text("A2"), *"TRUE");
+    assert_eq!(model._get_text("A3"), *"TRUE");
+    assert_eq!(model._get_text("A4"), *"TRUE");
+    assert_eq!(model._get_text("A5"), *"FALSE");
+    assert_eq!(model._get_text("A6"), *"TRUE");
+}
+
+#[test]
+fn fn_or_xor() {
+    inner("or");
+    inner("xor");
+
+    fn inner(func: &str) {
+        println!("Testing function: {func}");
+
+        let mut model = new_empty_model();
+
+        // Text args
+        model._set("A1", &format!(r#"={func}("")"#));
+        model._set("A2", &format!(r#"={func}("", "")"#));
+        model._set("A3", &format!(r#"={func}("", TRUE)"#));
+        model._set("A4", &format!(r#"={func}("", FALSE)"#));
+
+        model._set("A5", &format!("={func}(FALSE, TRUE)"));
+        model._set("A6", &format!("={func}(FALSE, FALSE)"));
+        model._set("A7", &format!("={func}(TRUE, FALSE)"));
+
+        // Reference to empty cell, plus true argument
+        model._set("A8", &format!("={func}(Z99, 1)"));
+
+        // Reference to empty cell/range
+        model._set("A9", &format!("={func}(Z99)"));
+        model._set("A10", &format!("={func}(X99:Z99"));
+
+        // Reference to cell with reference to empty range
+        model._set("B11", "=X99:Z99");
+        model._set("A11", &format!("={func}(B11)"));
+
+        // Reference to cell with non-empty range
+        model._set("X12", "1");
+        model._set("B12", "=X12:Z12");
+        model._set("A12", &format!("={func}(B12)"));
+
+        // Reference to text cell
+        model._set("B13", "some_text");
+        model._set("A13", &format!("={func}(B13)"));
+        model._set("A14", &format!("={func}(B13, 0)"));
+        model._set("A15", &format!("={func}(B13, 1)"));
+
+        // Reference to Implicit intersection
+        model._set("X16", "1");
+        model._set("B16", "=@X15:X16");
+        model._set("A16", &format!("={func}(B16)"));
+
+        // Non-empty range
+        model._set("B17", "1");
+        model._set("A17", &format!("={func}(B17:C17)"));
+
+        // Non-empty range with text
+        model._set("B18", "text");
+        model._set("A18", &format!("={func}(B18:C18)"));
+
+        // Non-empty range with text and number
+        model._set("B19", "text");
+        model._set("C19", "1");
+        model._set("A19", &format!("={func}(B19:C19)"));
+
+        // range with error
+        model._set("B20", "=1/0");
+        model._set("A20", &format!("={func}(B20:C20)"));
+
+        model.evaluate();
+
+        assert_eq!(model._get_text("A1"), *"#VALUE!");
+        assert_eq!(model._get_text("A2"), *"#VALUE!");
+        assert_eq!(model._get_text("A3"), *"TRUE");
+        assert_eq!(model._get_text("A4"), *"FALSE");
+
+        assert_eq!(model._get_text("A5"), *"TRUE");
+        assert_eq!(model._get_text("A6"), *"FALSE");
+        assert_eq!(model._get_text("A7"), *"TRUE");
+
+        assert_eq!(model._get_text("A8"), *"TRUE");
+
+        assert_eq!(model._get_text("A9"), *"#VALUE!");
+        assert_eq!(model._get_text("A10"), *"#VALUE!");
+
+        assert_eq!(model._get_text("A11"), *"#VALUE!");
+
+        // TODO: This one depends on spill behaviour which isn't implemented yet
+        // assert_eq!(model._get_text("A12"), *"TRUE");
+
+        assert_eq!(model._get_text("A13"), *"#VALUE!");
+        assert_eq!(model._get_text("A14"), *"FALSE");
+        assert_eq!(model._get_text("A15"), *"TRUE");
+
+        // TODO: This one depends on @ implicit intersection behaviour which isn't implemented yet
+        // assert_eq!(model._get_text("A16"), *"TRUE");
+
+        assert_eq!(model._get_text("A17"), *"TRUE");
+
+        assert_eq!(model._get_text("A18"), *"#VALUE!");
+
+        assert_eq!(model._get_text("A19"), *"TRUE");
+
+        assert_eq!(model._get_text("A20"), *"#DIV/0!");
+    }
+}
+
+#[test]
+fn fn_or_xor_no_arguments() {
+    inner("or");
+    inner("xor");
+
+    fn inner(func: &str) {
+        println!("Testing function: {func}");
+
+        let mut model = new_empty_model();
+        model._set("A1", &format!("={}()", func));
+        model.evaluate();
+        assert_eq!(model._get_text("A1"), *"#ERROR!");
+    }
+}
+
+#[test]
+fn fn_or_xor_missing_arguments() {
+    inner("or");
+    inner("xor");
+
+    fn inner(func: &str) {
+        println!("Testing function: {func}");
+
+        let mut model = new_empty_model();
+        model._set("A1", &format!("={func}(,)"));
+        model._set("A2", &format!("={func}(,1)"));
+        model._set("A3", &format!("={func}(1,)"));
+        model._set("A4", &format!("={func}(,B1)"));
+        model._set("A5", &format!("={func}(,B1:B4)"));
+        model.evaluate();
+        assert_eq!(model._get_text("A1"), *"FALSE");
+        assert_eq!(model._get_text("A2"), *"TRUE");
+        assert_eq!(model._get_text("A3"), *"TRUE");
+        assert_eq!(model._get_text("A4"), *"FALSE");
+        assert_eq!(model._get_text("A5"), *"FALSE");
+    }
+}

base/src/test/test_true_false.rs

@@ -0,0 +1,25 @@
+#![allow(clippy::unwrap_used)]
+
+use crate::test::util::new_empty_model;
+
+#[test]
+fn true_false_arguments() {
+    let mut model = new_empty_model();
+    model._set("A1", "=TRUE( )");
+    model._set("A2", "=FALSE( )");
+    model._set("A3", "=TRUE( 4 )");
+    model._set("A4", "=FALSE( 4 )");
+
+    model.evaluate();
+
+    assert_eq!(model._get_text("A1"), *"TRUE");
+    assert_eq!(model._get_text("A2"), *"FALSE");
+
+    assert_eq!(model._get_formula("A1"), *"=TRUE()");
+    assert_eq!(model._get_formula("A2"), *"=FALSE()");
+
+    assert_eq!(model._get_text("A3"), *"#ERROR!");
+    assert_eq!(model._get_text("A4"), *"#ERROR!");
+    assert_eq!(model._get_formula("A3"), *"=TRUE(4)");
+    assert_eq!(model._get_formula("A4"), *"=FALSE(4)");
+}

base/src/test/user_model/mod.rs

@@ -3,6 +3,7 @@ mod test_autofill_columns;
 mod test_autofill_rows;
 mod test_border;
 mod test_clear_cells;
+mod test_defined_names;
 mod test_diff_queue;
 mod test_evaluation;
 mod test_general;
@@ -14,6 +15,7 @@ mod test_on_paste_styles;
 mod test_paste_csv;
 mod test_rename_sheet;
 mod test_row_column;
+mod test_sheet_state;
 mod test_styles;
 mod test_to_from_bytes;
 mod test_undo_redo;

base/src/test/user_model/test_defined_names.rs

@@ -0,0 +1,398 @@
+#![allow(clippy::unwrap_used)]
+
+use crate::UserModel;
+
+#[test]
+fn create_defined_name() {
+    let mut model = UserModel::new_empty("model", "en", "UTC").unwrap();
+    model.set_user_input(0, 1, 1, "42").unwrap();
+    model
+        .new_defined_name("myName", None, "Sheet1!$A$1")
+        .unwrap();
+    model.set_user_input(0, 5, 7, "=myName").unwrap();
+    assert_eq!(
+        model.get_formatted_cell_value(0, 5, 7),
+        Ok("42".to_string())
+    );
+
+    assert_eq!(
+        model.get_defined_name_list(),
+        vec![("myName".to_string(), None, "Sheet1!$A$1".to_string())]
+    );
+
+    // delete it
+    model.delete_defined_name("myName", None).unwrap();
+    assert_eq!(
+        model.get_formatted_cell_value(0, 5, 7),
+        Ok("#NAME?".to_string())
+    );
+
+    assert_eq!(model.get_defined_name_list().len(), 0);
+
+    model.undo().unwrap();
+    assert_eq!(
+        model.get_formatted_cell_value(0, 5, 7),
+        Ok("42".to_string())
+    );
+}
+
+#[test]
+fn scopes() {
+    let mut model = UserModel::new_empty("model", "en", "UTC").unwrap();
+    model.set_user_input(0, 1, 1, "42").unwrap();
+
+    // Global
+    model
+        .new_defined_name("myName", None, "Sheet1!$A$1")
+        .unwrap();
+    model.set_user_input(0, 5, 7, "=myName").unwrap();
+
+    // Local to Sheet2
+    model.new_sheet().unwrap();
+    model.set_user_input(1, 2, 1, "145").unwrap();
+    model
+        .new_defined_name("myName", Some(1), "Sheet2!$A$2")
+        .unwrap();
+    model.set_user_input(1, 8, 8, "=myName").unwrap();
+
+    // Sheet 3
+    model.new_sheet().unwrap();
+    model.set_user_input(2, 2, 2, "=myName").unwrap();
+
+    // Global
+    assert_eq!(
+        model.get_formatted_cell_value(0, 5, 7),
+        Ok("42".to_string())
+    );
+
+    assert_eq!(
+        model.get_formatted_cell_value(1, 8, 8),
+        Ok("145".to_string())
+    );
+
+    assert_eq!(
+        model.get_formatted_cell_value(2, 2, 2),
+        Ok("42".to_string())
+    );
+}
+
+#[test]
+fn delete_sheet() {
+    let mut model = UserModel::new_empty("model", "en", "UTC").unwrap();
+    model.set_user_input(0, 1, 1, "Hello").unwrap();
+    model
+        .set_user_input(0, 2, 1, r#"=CONCATENATE(MyName, " world!")"#)
+        .unwrap();
+    model.new_sheet().unwrap();
+    model
+        .new_defined_name("myName", Some(1), "Sheet1!$A$1")
+        .unwrap();
+    model
+        .set_user_input(1, 2, 1, r#"=CONCATENATE(MyName, " my world!")"#)
+        .unwrap();
+
+    assert_eq!(
+        model.get_formatted_cell_value(0, 2, 1),
+        Ok("#NAME?".to_string())
+    );
+
+    assert_eq!(
+        model.get_formatted_cell_value(1, 2, 1),
+        Ok("Hello my world!".to_string())
+    );
+
+    model.delete_sheet(0).unwrap();
+
+    assert_eq!(
+        model.get_formatted_cell_value(0, 2, 1),
+        Ok("#NAME?".to_string())
+    );
+
+    assert_eq!(
+        model.get_cell_content(0, 2, 1),
+        Ok(r#"=CONCATENATE(MyName," my world!")"#.to_string())
+    );
+}
+
+#[test]
+fn change_scope() {
+    let mut model = UserModel::new_empty("model", "en", "UTC").unwrap();
+    model.set_user_input(0, 1, 1, "Hello").unwrap();
+    model
+        .set_user_input(0, 2, 1, r#"=CONCATENATE(MyName, " world!")"#)
+        .unwrap();
+    model.new_sheet().unwrap();
+    model
+        .new_defined_name("myName", Some(1), "Sheet1!$A$1")
+        .unwrap();
+
+    assert_eq!(
+        model.get_formatted_cell_value(0, 2, 1),
+        Ok("#NAME?".to_string())
+    );
+
+    model
+        .update_defined_name("myName", Some(1), "myName", None, "Sheet1!$A$1")
+        .unwrap();
+
+    assert_eq!(
+        model.get_formatted_cell_value(0, 2, 1),
+        Ok("Hello world!".to_string())
+    );
+}
+
+#[test]
+fn rename_defined_name() {
+    let mut model = UserModel::new_empty("model", "en", "UTC").unwrap();
+    model.set_user_input(0, 1, 1, "Hello").unwrap();
+    model
+        .set_user_input(0, 2, 1, r#"=CONCATENATE(MyName, " world!")"#)
+        .unwrap();
+    model.new_sheet().unwrap();
+    model
+        .new_defined_name("myName", None, "Sheet1!$A$1")
+        .unwrap();
+
+    assert_eq!(
+        model.get_formatted_cell_value(0, 2, 1),
+        Ok("Hello world!".to_string())
+    );
+
+    model
+        .update_defined_name("myName", None, "newName", None, "Sheet1!$A$1")
+        .unwrap();
+
+    assert_eq!(
+        model.get_formatted_cell_value(0, 2, 1),
+        Ok("Hello world!".to_string())
+    );
+
+    assert_eq!(
+        model.get_cell_content(0, 2, 1),
+        Ok(r#"=CONCATENATE(newName," world!")"#.to_string())
+    );
+}
+
+#[test]
+fn rename_defined_name_operations() {
+    let mut model = UserModel::new_empty("model", "en", "UTC").unwrap();
+    model.set_user_input(0, 1, 1, "42").unwrap();
+    model.set_user_input(0, 1, 2, "123").unwrap();
+
+    model
+        .new_defined_name("answer", None, "Sheet1!$A$1")
+        .unwrap();
+
+    model
+        .set_user_input(0, 2, 1, "=IF(answer<2, answer*2, answer^2)")
+        .unwrap();
+
+    model
+        .set_user_input(0, 3, 1, "=badDunction(-answer)")
+        .unwrap();
+
+    model.new_sheet().unwrap();
+    model.set_user_input(1, 1, 1, "78").unwrap();
+    model
+        .new_defined_name("answer", Some(1), "Sheet1!$A$1")
+        .unwrap();
+
+    model.set_user_input(1, 3, 1, "=answer").unwrap();
+
+    model
+        .update_defined_name("answer", None, "respuesta", None, "Sheet1!$A$1")
+        .unwrap();
+
+    assert_eq!(
+        model.get_cell_content(0, 2, 1),
+        Ok("=IF(respuesta<2,respuesta*2,respuesta^2)".to_string())
+    );
+
+    assert_eq!(
+        model.get_cell_content(0, 3, 1),
+        Ok("=badDunction(-respuesta)".to_string())
+    );
+
+    // A defined name with the same name but different scope
+    assert_eq!(model.get_cell_content(1, 3, 1), Ok("=answer".to_string()));
+}
+
+#[test]
+fn rename_defined_name_string_operations() {
+    let mut model = UserModel::new_empty("model", "en", "UTC").unwrap();
+    model.set_user_input(0, 1, 1, "Hello").unwrap();
+    model.set_user_input(0, 1, 2, "World").unwrap();
+
+    model
+        .new_defined_name("hello", None, "Sheet1!$A$1")
+        .unwrap();
+
+    model
+        .new_defined_name("world", None, "Sheet1!$B$1")
+        .unwrap();
+
+    model.set_user_input(0, 2, 1, "=hello&world").unwrap();
+
+    model
+        .update_defined_name("hello", None, "HolaS", None, "Sheet1!$A$1")
+        .unwrap();
+
+    assert_eq!(
+        model.get_cell_content(0, 2, 1),
+        Ok("=HolaS&world".to_string())
+    );
+}
+
+#[test]
+fn invalid_names() {
+    let mut model = UserModel::new_empty("model", "en", "UTC").unwrap();
+    model.set_user_input(0, 1, 1, "Hello").unwrap();
+    model
+        .new_defined_name("MyName", None, "Sheet1!$A$1")
+        .unwrap();
+
+    // spaces
+    assert_eq!(
+        model.new_defined_name("A real", None, "Sheet1!$A$1"),
+        Err("Invalid defined name".to_string())
+    );
+
+    // Starts with number
+    assert_eq!(
+        model.new_defined_name("2real", None, "Sheet1!$A$1"),
+        Err("Invalid defined name".to_string())
+    );
+
+    // Updating also fails
+    assert_eq!(
+        model.update_defined_name("MyName", None, "My Name", None, "Sheet1!$A$1"),
+        Err("Invalid defined name".to_string())
+    );
+}
+
+#[test]
+fn already_existing() {
+    let mut model = UserModel::new_empty("model", "en", "UTC").unwrap();
+
+    model
+        .new_defined_name("MyName", None, "Sheet1!$A$1")
+        .unwrap();
+    model
+        .new_defined_name("Another", None, "Sheet1!$A$1")
+        .unwrap();
+
+    // Can't create a new name with the same name
+    assert_eq!(
+        model.new_defined_name("MyName", None, "Sheet1!$A$2"),
+        Err("Defined name already exists".to_string())
+    );
+
+    // Can't update one into an existing
+    assert_eq!(
+        model.update_defined_name("Another", None, "MyName", None, "Sheet1!$A$1"),
+        Err("Defined name already exists".to_string())
+    );
+}
+
+#[test]
+fn invalid_sheet() {
+    let mut model = UserModel::new_empty("model", "en", "UTC").unwrap();
+    model.set_user_input(0, 1, 1, "Hello").unwrap();
+    model
+        .new_defined_name("MyName", None, "Sheet1!$A$1")
+        .unwrap();
+
+    assert_eq!(
+        model.new_defined_name("Mything", Some(2), "Sheet1!$A$1"),
+        Err("Invalid sheet index".to_string())
+    );
+
+    assert_eq!(
+        model.update_defined_name("MyName", None, "MyName", Some(2), "Sheet1!$A$1"),
+        Err("Invalid sheet index".to_string())
+    );
+
+    assert_eq!(
+        model.update_defined_name("MyName", Some(9), "YourName", None, "Sheet1!$A$1"),
+        Err("Invalid sheet index".to_string())
+    );
+}
+
+#[test]
+fn invalid_formula() {
+    let mut model = UserModel::new_empty("model", "en", "UTC").unwrap();
+    model.set_user_input(0, 1, 1, "Hello").unwrap();
+    model.new_defined_name("MyName", None, "A1").unwrap();
+
+    model.set_user_input(0, 1, 2, "=MyName").unwrap();
+
+    assert_eq!(
+        model.get_formatted_cell_value(0, 1, 2),
+        Ok("#NAME?".to_string())
+    );
+}
+
+#[test]
+fn undo_redo() {
+    let mut model = UserModel::new_empty("model", "en", "UTC").unwrap();
+    model.set_user_input(0, 1, 1, "Hello").unwrap();
+    model.set_user_input(0, 2, 1, "Hola").unwrap();
+    model.set_user_input(0, 1, 2, r#"=MyName&"!""#).unwrap();
+
+    model
+        .new_defined_name("MyName", None, "Sheet1!$A$1")
+        .unwrap();
+
+    assert_eq!(
+        model.get_formatted_cell_value(0, 1, 2),
+        Ok("Hello!".to_string())
+    );
+    model.undo().unwrap();
+    assert_eq!(model.get_defined_name_list().len(), 0);
+    assert_eq!(
+        model.get_formatted_cell_value(0, 1, 2),
+        Ok("#NAME?".to_string())
+    );
+    model.redo().unwrap();
+
+    assert_eq!(model.get_defined_name_list().len(), 1);
+    assert_eq!(
+        model.get_formatted_cell_value(0, 1, 2),
+        Ok("Hello!".to_string())
+    );
+
+    model
+        .update_defined_name("MyName", None, "MyName", None, "Sheet1!$A$2")
+        .unwrap();
+    assert_eq!(model.get_defined_name_list().len(), 1);
+    assert_eq!(
+        model.get_formatted_cell_value(0, 1, 2),
+        Ok("Hola!".to_string())
+    );
+    model.undo().unwrap();
+
+    assert_eq!(model.get_defined_name_list().len(), 1);
+    assert_eq!(
+        model.get_formatted_cell_value(0, 1, 2),
+        Ok("Hello!".to_string())
+    );
+
+    model.redo().unwrap();
+
+    assert_eq!(model.get_defined_name_list().len(), 1);
+    assert_eq!(
+        model.get_formatted_cell_value(0, 1, 2),
+        Ok("Hola!".to_string())
+    );
+
+    let send_queue = model.flush_send_queue();
+
+    let mut model2 = UserModel::new_empty("model", "en", "UTC").unwrap();
+    model2.apply_external_diffs(&send_queue).unwrap();
+
+    assert_eq!(model2.get_defined_name_list().len(), 1);
+    assert_eq!(
+        model2.get_formatted_cell_value(0, 1, 2),
+        Ok("Hola!".to_string())
+    );
+}

base/src/test/user_model/test_general.rs

@@ -91,7 +91,6 @@ fn insert_remove_columns() {
     let mut model = UserModel::from_model(model);
     // column E
     let column_width = model.get_column_width(0, 5).unwrap();
-    println!("{column_width}");
 
     // Insert some data in row 5 (and change the style) in E1
     assert!(model.set_user_input(0, 1, 5, "100$").is_ok());

base/src/test/user_model/test_sheet_state.rs

@@ -0,0 +1,57 @@
+#![allow(clippy::unwrap_used)]
+
+use crate::test::util::new_empty_model;
+use crate::UserModel;
+
+#[test]
+fn basic_tests() {
+    let model = new_empty_model();
+    let mut model = UserModel::from_model(model);
+
+    // add three more sheets
+    model.new_sheet().unwrap();
+    model.new_sheet().unwrap();
+    model.new_sheet().unwrap();
+
+    let info = model.get_worksheets_properties();
+    assert_eq!(info.len(), 4);
+    for sheet in &info {
+        assert_eq!(sheet.state, "visible".to_string());
+    }
+
+    model.set_selected_sheet(2).unwrap();
+    assert_eq!(info.get(2).unwrap().name, "Sheet3".to_string());
+
+    model.hide_sheet(2).unwrap();
+
+    let info = model.get_worksheets_properties();
+    assert_eq!(model.get_selected_sheet(), 3);
+    assert_eq!(info.get(2).unwrap().state, "hidden".to_string());
+
+    model.undo().unwrap();
+    let info = model.get_worksheets_properties();
+    assert_eq!(info.get(2).unwrap().state, "visible".to_string());
+    model.redo().unwrap();
+    let info = model.get_worksheets_properties();
+    assert_eq!(info.get(2).unwrap().state, "hidden".to_string());
+
+    model.set_selected_sheet(3).unwrap();
+    model.hide_sheet(3).unwrap();
+    assert_eq!(model.get_selected_sheet(), 0);
+
+    model.unhide_sheet(2).unwrap();
+    model.unhide_sheet(3).unwrap();
+
+    let info = model.get_worksheets_properties();
+    assert_eq!(info.len(), 4);
+    for sheet in &info {
+        assert_eq!(sheet.state, "visible".to_string());
+    }
+
+    model.undo().unwrap();
+    let info = model.get_worksheets_properties();
+    assert_eq!(info.get(3).unwrap().state, "hidden".to_string());
+    model.redo().unwrap();
+    let info = model.get_worksheets_properties();
+    assert_eq!(info.get(3).unwrap().state, "visible".to_string());
+}

base/src/units.rs

@@ -293,7 +293,9 @@ impl Model {
             Node::EmptyArgKind => None,
             Node::InvalidFunctionKind { .. } => None,
             Node::ArrayKind(_) => None,
-            Node::VariableKind(_) => None,
+            Node::DefinedNameKind(_) => None,
+            Node::TableNameKind(_) => None,
+            Node::WrongVariableKind(_) => None,
             Node::CompareKind { .. } => None,
             Node::OpPowerKind { .. } => None,
         }

base/src/user_model/common.rs

@@ -13,8 +13,8 @@ use crate::{
     },
     model::Model,
     types::{
-        Alignment, BorderItem, CellType, Col, HorizontalAlignment, SheetProperties, Style,
-        VerticalAlignment,
+        Alignment, BorderItem, CellType, Col, HorizontalAlignment, SheetProperties, SheetState,
+        Style, VerticalAlignment,
     },
     utils::is_valid_hex_color,
 };
@@ -440,6 +440,48 @@ impl UserModel {
         Ok(())
     }
 
+    /// Hides sheet by index
+    ///
+    /// See also:
+    /// * [Model::set_sheet_state]
+    /// * [UserModel::unhide_sheet]
+    pub fn hide_sheet(&mut self, sheet: u32) -> Result<(), String> {
+        let sheet_count = self.model.workbook.worksheets.len() as u32;
+        for index in 1..sheet_count {
+            let sheet_index = (sheet + index) % sheet_count;
+            if self.model.workbook.worksheet(sheet_index)?.state == SheetState::Visible {
+                if let Some(view) = self.model.workbook.views.get_mut(&self.model.view_id) {
+                    view.sheet = sheet_index;
+                };
+                break;
+            }
+        }
+        let old_value = self.model.workbook.worksheet(sheet)?.state.clone();
+        self.push_diff_list(vec![Diff::SetSheetState {
+            index: sheet,
+            new_value: SheetState::Hidden,
+            old_value,
+        }]);
+        self.model.set_sheet_state(sheet, SheetState::Hidden)?;
+        Ok(())
+    }
+
+    /// Un hides sheet by index
+    ///
+    /// See also:
+    /// * [Model::set_sheet_state]
+    /// * [UserModel::hide_sheet]
+    pub fn unhide_sheet(&mut self, sheet: u32) -> Result<(), String> {
+        let old_value = self.model.workbook.worksheet(sheet)?.state.clone();
+        self.push_diff_list(vec![Diff::SetSheetState {
+            index: sheet,
+            new_value: SheetState::Visible,
+            old_value,
+        }]);
+        self.model.set_sheet_state(sheet, SheetState::Visible)?;
+        Ok(())
+    }
+
     /// Sets sheet color
     ///
     /// Note: an empty string will remove the color
@@ -1692,6 +1734,68 @@ impl UserModel {
         Ok(())
     }
 
+    /// Returns the list of defined names
+    pub fn get_defined_name_list(&self) -> Vec<(String, Option<u32>, String)> {
+        self.model.workbook.get_defined_names_with_scope()
+    }
+
+    /// Delete an existing defined name
+    pub fn delete_defined_name(&mut self, name: &str, scope: Option<u32>) -> Result<(), String> {
+        let old_value = self.model.get_defined_name_formula(name, scope)?;
+        let diff_list = vec![Diff::DeleteDefinedName {
+            name: name.to_string(),
+            scope,
+            old_value,
+        }];
+        self.push_diff_list(diff_list);
+        self.model.delete_defined_name(name, scope)?;
+        self.evaluate_if_not_paused();
+        Ok(())
+    }
+
+    /// Create a new defined name
+    pub fn new_defined_name(
+        &mut self,
+        name: &str,
+        scope: Option<u32>,
+        formula: &str,
+    ) -> Result<(), String> {
+        self.model.new_defined_name(name, scope, formula)?;
+        let diff_list = vec![Diff::CreateDefinedName {
+            name: name.to_string(),
+            scope,
+            value: formula.to_string(),
+        }];
+        self.push_diff_list(diff_list);
+        self.evaluate_if_not_paused();
+        Ok(())
+    }
+
+    /// Updates a defined name
+    pub fn update_defined_name(
+        &mut self,
+        name: &str,
+        scope: Option<u32>,
+        new_name: &str,
+        new_scope: Option<u32>,
+        new_formula: &str,
+    ) -> Result<(), String> {
+        let old_formula = self.model.get_defined_name_formula(name, scope)?;
+        let diff_list = vec![Diff::UpdateDefinedName {
+            name: name.to_string(),
+            scope,
+            old_formula: old_formula.to_string(),
+            new_name: new_name.to_string(),
+            new_scope,
+            new_formula: new_formula.to_string(),
+        }];
+        self.push_diff_list(diff_list);
+        self.model
+            .update_defined_name(name, scope, new_name, new_scope, new_formula)?;
+        self.evaluate_if_not_paused();
+        Ok(())
+    }
+
     // **** Private methods ****** //
 
     fn push_diff_list(&mut self, diff_list: DiffList) {
@@ -1862,6 +1966,41 @@ impl UserModel {
                 } => {
                     self.model.set_show_grid_lines(*sheet, *old_value)?;
                 }
+                Diff::CreateDefinedName {
+                    name,
+                    scope,
+                    value: _,
+                } => {
+                    self.model.delete_defined_name(name, *scope)?;
+                }
+                Diff::DeleteDefinedName {
+                    name,
+                    scope,
+                    old_value,
+                } => {
+                    self.model.new_defined_name(name, *scope, old_value)?;
+                }
+                Diff::UpdateDefinedName {
+                    name,
+                    scope,
+                    old_formula,
+                    new_name,
+                    new_scope,
+                    new_formula: _,
+                } => {
+                    self.model.update_defined_name(
+                        new_name,
+                        *new_scope,
+                        name,
+                        *scope,
+                        old_formula,
+                    )?;
+                }
+                Diff::SetSheetState {
+                    index,
+                    old_value,
+                    new_value: _,
+                } => self.model.set_sheet_state(*index, old_value.clone())?,
             }
         }
         if needs_evaluation {
@@ -1989,6 +2128,33 @@ impl UserModel {
                 } => {
                     self.model.set_show_grid_lines(*sheet, *new_value)?;
                 }
+                Diff::CreateDefinedName { name, scope, value } => {
+                    self.model.new_defined_name(name, *scope, value)?
+                }
+                Diff::DeleteDefinedName {
+                    name,
+                    scope,
+                    old_value: _,
+                } => self.model.delete_defined_name(name, *scope)?,
+                Diff::UpdateDefinedName {
+                    name,
+                    scope,
+                    old_formula: _,
+                    new_name,
+                    new_scope,
+                    new_formula,
+                } => self.model.update_defined_name(
+                    name,
+                    *scope,
+                    new_name,
+                    *new_scope,
+                    new_formula,
+                )?,
+                Diff::SetSheetState {
+                    index,
+                    old_value: _,
+                    new_value,
+                } => self.model.set_sheet_state(*index, new_value.clone())?,
             }
         }
 

base/src/user_model/history.rs

@@ -2,7 +2,7 @@ use std::collections::HashMap;
 
 use bitcode::{Decode, Encode};
 
-use crate::types::{Cell, Col, Row, Style};
+use crate::types::{Cell, Col, Row, SheetState, Style};
 
 #[derive(Clone, Encode, Decode)]
 pub(crate) struct RowData {
@@ -104,11 +104,35 @@ pub(crate) enum Diff {
         old_value: String,
         new_value: String,
     },
+    SetSheetState {
+        index: u32,
+        old_value: SheetState,
+        new_value: SheetState,
+    },
     SetShowGridLines {
         sheet: u32,
         old_value: bool,
         new_value: bool,
-    }, // FIXME: we are missing SetViewDiffs
+    },
+    CreateDefinedName {
+        name: String,
+        scope: Option<u32>,
+        value: String,
+    },
+    DeleteDefinedName {
+        name: String,
+        scope: Option<u32>,
+        old_value: String,
+    },
+    UpdateDefinedName {
+        name: String,
+        scope: Option<u32>,
+        old_formula: String,
+        new_name: String,
+        new_scope: Option<u32>,
+        new_formula: String,
+    },
+    // FIXME: we are missing SetViewDiffs
 }
 
 pub(crate) type DiffList = Vec<Diff>;

base/src/workbook.rs

@@ -27,4 +27,27 @@ impl Workbook {
             .get_mut(worksheet_index as usize)
             .ok_or_else(|| "Invalid sheet index".to_string())
     }
+
+    /// Returns the a list of defined names in the workbook with their scope
+    pub(crate) fn get_defined_names_with_scope(&self) -> Vec<(String, Option<u32>, String)> {
+        let sheet_id_index: Vec<u32> = self.worksheets.iter().map(|s| s.sheet_id).collect();
+
+        let defined_names = self
+            .defined_names
+            .iter()
+            .map(|dn| {
+                let index = dn
+                    .sheet_id
+                    .and_then(|sheet_id| {
+                        // returns an Option<usize>
+                        sheet_id_index.iter().position(|&x| x == sheet_id)
+                    })
+                    // convert Option<usize> to Option<u32>
+                    .map(|pos| pos as u32);
+
+                (dn.name.clone(), index, dn.formula.clone())
+            })
+            .collect::<Vec<_>>();
+        defined_names
+    }
 }

bindings/wasm/Makefile

@@ -3,6 +3,7 @@ all:
 	cp README.pkg.md pkg/README.md
 	tsc types.ts --target esnext --module esnext
 	python fix_types.py
+	rm -f types.js
 
 tests:
 	wasm-pack build --target nodejs && node tests/test.mjs

bindings/wasm/fix_types.py

@@ -187,6 +187,20 @@ paste_from_clipboard_types = r"""
   pasteFromClipboard(source_sheet: number, source_range: [number, number, number, number], clipboard: ClipboardData, is_cut: boolean): void;
 """
 
+defined_name_list = r"""
+/**
+* @returns {any}
+*/
+  getDefinedNameList(): any;
+"""
+
+defined_name_list_types = r"""
+/**
+* @returns {DefinedName[]}
+*/
+  getDefinedNameList(): DefinedName[];
+"""
+
 def fix_types(text):
     text = text.replace(get_tokens_str, get_tokens_str_types)
     text = text.replace(update_style_str, update_style_str_types)
@@ -200,6 +214,7 @@ def fix_types(text):
     text = text.replace(paste_csv_string, paste_csv_string_types)
     text = text.replace(clipboard, clipboard_types)
     text = text.replace(paste_from_clipboard, paste_from_clipboard_types)
+    text = text.replace(defined_name_list, defined_name_list_types)
     with open("types.ts") as f:
         types_str = f.read()
         header_types = "{}\n\n{}".format(header, types_str)

bindings/wasm/src/lib.rs

@@ -1,3 +1,4 @@
+use serde::Serialize;
 use wasm_bindgen::{
     prelude::{wasm_bindgen, JsError},
     JsValue,
@@ -29,6 +30,13 @@ pub fn column_name_from_number(column: i32) -> Result<String, JsError> {
     }
 }
 
+#[derive(Serialize)]
+struct DefinedName {
+    name: String,
+    scope: Option<u32>,
+    formula: String,
+}
+
 #[wasm_bindgen]
 pub struct Model {
     model: BaseModel,
@@ -106,6 +114,16 @@ impl Model {
         self.model.delete_sheet(sheet).map_err(to_js_error)
     }
 
+    #[wasm_bindgen(js_name = "hideSheet")]
+    pub fn hide_sheet(&mut self, sheet: u32) -> Result<(), JsError> {
+        self.model.hide_sheet(sheet).map_err(to_js_error)
+    }
+
+    #[wasm_bindgen(js_name = "unhideSheet")]
+    pub fn unhide_sheet(&mut self, sheet: u32) -> Result<(), JsError> {
+        self.model.unhide_sheet(sheet).map_err(to_js_error)
+    }
+
     #[wasm_bindgen(js_name = "renameSheet")]
     pub fn rename_sheet(&mut self, sheet: u32, name: &str) -> Result<(), JsError> {
         self.model.rename_sheet(sheet, name).map_err(to_js_error)
@@ -542,4 +560,52 @@ impl Model {
             .paste_csv_string(&range, csv)
             .map_err(|e| to_js_error(e.to_string()))
     }
+
+    #[wasm_bindgen(js_name = "getDefinedNameList")]
+    pub fn get_defined_name_list(&self) -> Result<JsValue, JsError> {
+        let data: Vec<DefinedName> = self
+            .model
+            .get_defined_name_list()
+            .iter()
+            .map(|s| DefinedName {
+                name: s.0.to_owned(),
+                scope: s.1,
+                formula: s.2.to_owned(),
+            })
+            .collect();
+        serde_wasm_bindgen::to_value(&data).map_err(|e| to_js_error(e.to_string()))
+    }
+
+    #[wasm_bindgen(js_name = "newDefinedName")]
+    pub fn new_defined_name(
+        &mut self,
+        name: &str,
+        scope: Option<u32>,
+        formula: &str,
+    ) -> Result<(), JsError> {
+        self.model
+            .new_defined_name(name, scope, formula)
+            .map_err(|e| to_js_error(e.to_string()))
+    }
+
+    #[wasm_bindgen(js_name = "updateDefinedName")]
+    pub fn update_defined_name(
+        &mut self,
+        name: &str,
+        scope: Option<u32>,
+        new_name: &str,
+        new_scope: Option<u32>,
+        new_formula: &str,
+    ) -> Result<(), JsError> {
+        self.model
+            .update_defined_name(name, scope, new_name, new_scope, new_formula)
+            .map_err(|e| to_js_error(e.to_string()))
+    }
+
+    #[wasm_bindgen(js_name = "deleteDefinedName")]
+    pub fn delete_definedname(&mut self, name: &str, scope: Option<u32>) -> Result<(), JsError> {
+        self.model
+            .delete_defined_name(name, scope)
+            .map_err(|e| to_js_error(e.to_string()))
+    }
 }

bindings/wasm/types.ts

@@ -113,6 +113,7 @@ export interface WorksheetProperties {
   name: string;
   color: string;
   sheet_id: number;
+  state: string;
 }
 
 interface CellStyleFill {
@@ -226,4 +227,10 @@ export interface Clipboard {
   csv: string;
   data: ClipboardData;
   range: [number, number, number, number];
+}
+
+export interface DefinedName {
+  name: string;
+  scope?: number;
+  formula: string;
 }

docs/src/.vitepress/config.mts

@@ -45,6 +45,10 @@ export default defineConfig({
           { text: "About the web application", link: "/web-application/about" },
           { text: "Importing Files", link: "/web-application/importing-files" },
           { text: "Sharing Files", link: "/web-application/sharing-files" },
+          {
+            text: "Name Manager",
+            link: "/web-application/name-manager",
+          }
         ],
       },
       {
@@ -58,6 +62,26 @@ export default defineConfig({
             text: "Error Types",
             link: "/features/error-types",
           },
+          {
+            text: "Value Types",
+            link: "/features/value-types",
+          },
+          {
+            text: "Optional Arguments",
+            link: "/features/optional-arguments",
+          },
+          {
+            text: "Units",
+            link: "/features/units",
+          },		  
+          {
+            text: "Dates and serial numbers",
+            link: "/features/serial-numbers",
+          },
+          {
+            text: "Numbers in IronCalc",
+            link: "/features/numbers-in-ironcalc",
+          },		  
           {
             text: "Unsupported Features",
             link: "/features/unsupported-features",
@@ -2008,6 +2032,16 @@ export default defineConfig({
           },
         ],
       },
+      {
+        text: "Spreadhsheet Engines",
+        collapsed: true,
+        items: [
+          {
+            text: "Spreadsheet Engines",
+            link: "/other-spreadsheets/index",
+          },
+        ],
+      },
       {
         text: "Contributing",
         collapsed: true,

docs/src/features/error-types.md

@@ -7,31 +7,39 @@ lang: en-US
 # Error Types
 
 ::: warning
-**Note:** This page is in construction 🚧
+**Note:** This draft page is under construction 🚧
 :::
 
-When working with formulas, you may encounter these common errors:
+The result of a formula is sometimes an _error_. In some situations those errors are expected and your formulas might be dealing with them.
+The error `#N/A` might signal that there is no data to evaluate the formula yet. Maybe the payroll has not been introduced for that month just yet.
 
----
+Some other errors like `#SPILL!`, `#CIRC!` or `#ERROR!` signal an error in your spreadsheet logic and must be corrected.
 
-### **`#ERROR!`**
+The first kind of errors or 'common errors' are found in other spreadsheet engines like Excel while other errors like `#ERROR!` or `#N/IMPL` are particular to IronCalc.
 
-**Cause:** General formula issue, like syntax errors or invalid references.  
-**Fix:** Check the formula for mistakes or invalid cell references.
-
----
+## Common Errors
 
 ### **`#VALUE!`**
 
-**Cause:** Mismatched data types (e.g., text used where numbers are expected).  
-**Fix:** Ensure input types are correct; convert text to numbers if needed.
+It might be caused by mismatched data types (e.g., text used where numbers are expected):
 
----
+```
+=5+"two"
+```
+
+The engine doesn't know how to add the number `5` to the string `two` resulting in a `#VALUE!`.
+
+It is an actual error in your spreadsheet. It indicates that the formula isn’t working as intended.
 
 ### **`#DIV/0!`**
 
-**Cause:** Division by zero or an empty cell.  
-**Fix:** Ensure the denominator isn’t zero or blank. Use `IF` to handle such cases:
+Division by zero or an empty cell:
+
+```
+=1/0
+```
+
+Usually this is an error. However, in cases where a denominator might be blank (e.g., data not yet filled in), this could be expected. Use `IFERROR` or `IF` to handle it:
 
 ```
 =IF(B1=0, "N/A", A1/B1)
@@ -39,23 +47,42 @@ When working with formulas, you may encounter these common errors:
 
 ### **`#NAME?`**
 
-**Cause:** Unrecognized text in the formula (e.g., misspelled function names or undefined named ranges).  
-**Fix:** Correct spelling or define the missing name.
+Found when a name is not recognized. Maybe a misspelled name for a function or a reference to a previously defined name that has since been deleted:
+
+```
+=UNKNOWN_FUNCTION(A1)
+```
+
+This indicates an error in your spreadsheet logic.
 
 ### **`#REF!`**
 
-**Cause:** Invalid cell reference, often from deleting cells used in a formula.  
-**Fix:** Update the formula with correct references.
+Indicates an invalid cell reference, often from deleting cells used in a formula.
+
+They can appear as a result of a computation or in a formula. Example:
+
+```
+=Sheet34!A1
+```
+
+If `Sheet34` doesn't exist it will return `#REF!`
+
+This is a genuine error. It indicates that part of your formula references a cell or range that is missing.
 
 ### **`#NUM!`**
 
-**Cause:** Invalid numeric operation (e.g., calculating a square root of a negative number).  
-**Fix:** Adjust the formula to ensure valid numeric operations.
+Invalid numeric operation (e.g., calculating the square root of a negative number).  
+Adjust the formula to ensure valid numeric operations.
+
+Sometimes a `#NUM!` error might be expected, signalling to the user that some parameter is out of scope.
 
 ### **`#N/A`**
 
-**Cause:** A value is not available, often in lookup functions like VLOOKUP.  
-**Fix:** Ensure the lookup value exists or use IFNA() to handle missing values:
+A value is not available, often in lookup functions like VLOOKUP.
+
+This is frequently not an error in your spreadsheet logic.
+
+You can produce a prettier answer using the [`IFNA`](/functions/information/isna) formula:
 
 ```
 =IFNA(VLOOKUP(A1, B1:C10, 2, FALSE), "Not Found")
@@ -63,17 +90,62 @@ When working with formulas, you may encounter these common errors:
 
 ### **`#NULL!`**
 
-**Cause:** Incorrect range operator in a formula (e.g., missing a colon between cell references).  
-**Fix:** Use correct range operators (e.g., A1:A10).
+Incorrect range operator in a formula (e.g., missing a colon between cell references).
 
+### **`#SPILL!`**
+
+A cell in a formula will overwrite content in other cells.
+This cannot happen right now in IronCalc as formulas don't spill yet.
 
 ### **`#CIRC!`**
 
-**Cause:** Circular reference.  
-**Fix:** Remove the circular reference.
+Circular reference. This is an error in your spreadsheet and must be fixed.
+It means that during the course of a computation, a circular dependency was found.
+
+A circular dependency is a dependency of a formula on itself.
+
+For instance, in the cell `A1` the formula `=A1*2` is a circular dependency.
+
+Other spreadsheet engines use circular dependencies to do "loop computations", run "sensitivity analysis" or "goal seek".
+
+IronCalc doesn't support any of those at the moment.
+
+## IronCalc specific errors
+
+### **`#ERROR!`**
+
+General formula issue, like syntax errors or invalid references.
+In general, Excel does not let you enter incorrect formulas, but IronCalc will.
+
+This will make your workbook imcompatible with Excel.
+
+Typical examples might be an incomplete formula, such as `=A1+`, or a function call with too few arguments, such as `=FV(1,2)`.
+
+### **`#N/IMPL!`**
+
+A particular feature is not yet implemented in IronCalc
+
+Check if there is a [Github](https://github.com/ironcalc) ticket or contact us via [email](mailto:hello@ironcalc.com) or [Discord](https://discord.com/invite/zZYWfh3RHJ).
+
+## Error propagation
+
+Some errors are created by some formulas. For instance, the function `SQRT` can create the error `#NUM!`, but can't ceate the error `#DIV/0`.
+
+Once an error is created it is normally _propagated_ by all the formulas. So if cell `C3` evaluates to `#ERROR!`, then the formula
+`=SQRT(C3)` will return `#ERROR!`.
+
+Not all functions propagate errors in their arguments. For instance the function `IF(condition, if_true, if_false)` will only propagate an error in the `if_false` argument if the `condition` is `FALSE`. This is called _lazy evaluation_ - the function `IF` is _lazy_ because it only evaluates the arguments when needed. The opposite of lazy evaulaution is called _eager evaluation_.
+
+Some functions also expect an error as an argument like [`ERROR.TYPE`](/functions/information/error.type) and will not propagate the error.
 
 
-### **`#####`**
+## See also
 
-**Cause:** The column isn’t wide enough to display the value.  
-**Fix:** Resize the column width to fit the content.
+The following functions are convenient when working with errors
+
+- [`ISERR(ref)`](/functions/information/iserr), `TRUE` if `ref` is any error type except the `#N/A` error.
+- [`ISERROR(ref)`](/functions/information/iserror), `TRUE` if `ref` is any error.
+- [`ISNA(ref)`](/functions/information/isna), `TRUE` if ref is `#N/A`.
+- [`ERROR.TYPE`](/functions/information/error.type) returns the numeric code for a given error.
+- [`IFERROR(ref, value)`](/functions/logical/iferror) returns `value` if the content of `ref` is an error.
+- [`IFNA(ref, value)`](/functions/logical/ifna) returns `value` only if the content of `ref` is the `#N/A` error.

docs/src/features/numbers-in-ironcalc.md

@@ -0,0 +1,38 @@
+---
+layout: doc
+outline: deep
+lang: en-US
+---
+
+# Numbers in IronCalc
+::: warning
+**Note:** This draft page is under construction 🚧
+
+::: warning
+**Note:** This page contains technical documentation
+
+Numbers in IronCalc are [IEE 754](https://en.wikipedia.org/wiki/IEEE_754) doubles (64 bit) and are displayed uo to 15 decimal digits.
+
+## Integers
+
+Some Integers are well represented by IEEE 754 doubles. The largest integer that can be stored perfectly as a double is:
+
+$$
+2^53 = 9,007,199,254,740,992
+$$
+
+## Floating points
+
+The reader should be aware that numbers like 0.1 or 0.3 are not stored perfectly by computers, _only an approximation to them_ is stored.
+This results in imperfect operations like the famous `0.1 + 0.2 != 0.3`.
+
+When comparing numbers we also compare up to 15 significant figures. With this 'trick' `=IF(0.2+0.1=0.3,TRUE,FALSE)` is actually `TRUE`.
+
+
+
+## Compatibility issues
+
+Excel [mostly follows IEEE 754](https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result). Like IronCalc displays numbers with 15 significant digits. Excel does a few other undisclosed 'hacks'.
+If the result of an addition (or subtraction) of two non very small numbers is a number close to EPS and it is the end of the calculation then it is zero.
+
+That's is how it gets `=0.3-0.2-0.1` as `0`. However `=1*(0.3-0.2-0.1)` in Excel is `-2.77556E-17`

docs/src/features/optional-arguments.md

@@ -0,0 +1,49 @@
+---
+layout: doc
+outline: deep
+lang: en-US
+---
+
+# Optional Arguments
+
+::: warning
+**Note:** This draft page is under construction 🚧
+:::
+
+Any IronCalc function may accept zero, one or more arguments, which are values passed to the function when it is called from a spreadsheet formula.
+
+Many function arguments are _required_. For such arguments, always pass a suitable value in the function call.
+
+Some function arguments are _optional_. Optional arguments need not be passed in the function call and, in such cases, the function instead uses a predefined default value.
+
+Consider a notional function called _FN\_NAME_, with the following syntax:
+
+<p style="font-weight:bold;text-align:center;">FN_NAME(<span title="Number" style="color:#1E88E5">arg1</span>, <span title="Number" style="color:#1E88E5">arg2</span>, <span title="Number" style="color:#1E88E5">arg3</span>, <span title="Number" style="color:#1E88E5">arg4</span>=def1, <span title="Number" style="color:#1E88E5">arg5</span>=def2,  <span title="Number" style="color:#1E88E5">arg6</span>=def3) => <span title="Number" style="color:#1E88E5" >fn_name</span></p>
+
+Notes about this syntax:
+
+* _FN_NAME_ is a function that takes six arguments (_arg1_, _arg2_, _arg3_, _arg4_, _arg5_ and _arg6_) and returns a value referred to as _fn_name_.
+* For convenience in this case, all arguments and the returned value are colour-coded to indicate that they are numbers.
+* Arguments _arg1_, _arg2_ and _arg3_ are <u>required</u> arguments and this would normally be stated in the **Argument descriptions** section of the function's description page.
+* Arguments _arg4_, _arg5_ and _arg6_ are <u>optional</u> arguments and again this would normally be stated in the **Argument descriptions** section of the function's description page. In addition, optional arguments are usually indicated by the specification of a default value in the syntax. 
+   * If _arg4_ is omitted, then the value _def1_ is assumed.
+   * If _arg5_ is omitted, then the value _def2_ is assumed. 
+   * If _arg6_ is omitted, then the value _def3_ is assumed.
+
+With this syntax, the following would all be valid calls to the _FN_NAME_ function:
+
+**=FN\_NAME(1,2,3)**. All optional arguments omitted.
+
+**=FN\_NAME(1,2,3,4)**. _arg4_ set to 4; optional arguments _arg5_ and _arg6_ assume default values.
+
+**=FN\_NAME(1,2,3,,5)**.  _arg5_ set to 5; optional arguments _arg4_ and _arg6_ assume  default values.
+
+**=FN\_NAME(1,2,3,,,6)**. _arg6_ set to 6; optional arguments _arg4_ and _arg5_ assume  default values.
+
+**=FN\_NAME(1,2,3,4,5)**. _arg4_ and  _arg5_ set to 4 and 5 respectively; optional argument _arg6_ assumes default value.
+
+**=FN\_NAME(1,2,3,4,,6)**. _arg4_ and _arg6_ set to 4 and 6 respectively; optional argument _arg5_ assumes default value.
+
+**=FN\_NAME(1,2,3,,5,6)**. _arg5_ and _arg6_ set to 5 and 6 respectively; optional argument _arg4_ assumes default value.
+
+**=FN\_NAME(1,2,3,4,5,6)**. Values passed for all optional arguments.

docs/src/features/serial-numbers.md

@@ -0,0 +1,50 @@
+---
+layout: doc
+outline: deep
+lang: en-US
+---
+
+# Serial Numbers
+::: warning
+**Note:** This draft page is under construction 🚧
+:::
+**Note**:	For convenience, dates presented on this page are formatted in accordance with the ISO 8601 international standard. IronCalc can recognize and display dates in other formats.
+
+IronCalc stores dates and times as positive numbers, referred to as *serial numbers*. Serial numbers can be formatted to display the date and time. 
+
+The integer part of a serial number represents the date, as a count of the days since the fixed starting date of 1899-12-30. Hence dates are represented by a unique, sequential integer value, for example:
+* 1 corresponds to 1899-12-31.
+* 2 corresponds to 1900-01-01.
+* 36,526 corresponds to 2000-01-01.
+* 45,658 corresponds to 2025-01-01.
+* 2,958,465 corresponds to 9999-12-31.
+
+To illustrate the concept, type the value 2 into an empty cell that is initially formatted as a number. When you subsequently change the cell to a date format, it will update to show the date 1900-01-01.
+
+The fractional part of a serial number represents time, as a fraction of the day. For example:
+* 0.0 corresponds to 00:00:00 (midnight)
+* 0.041666667 corresponds to 01:00:00.
+* 0.5 corresponds to 12:00:00 (noon)
+* 0.75 corresponds to 18:00:00.
+* 0.99 corresponds to 23:45:36. 
+
+Since date-times are stored as numbers, they can be used for arithmetic operations in formulas. For example, it is possible to determine the difference between two dates by subtracting one serial number from the other.
+
+**Note**: A #VALUE! error is reported if a date-formatted cell contains a number less than 1 or greater than 2,958,465.
+
+## Compatibility Notes
+
+Excel has an infamous [feature](https://learn.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year) that was ported from a bug in Lotus 1-2-3 that assumes that the year 1900 is a leap year.
+
+That means that serial numbers 1 to 60 in IronCalc are different than Excel.
+
+In IronCalc, Google Sheets, Libre Office and Zoho Date(1900,1,1) returns 2
+
+In Excel Date(1900,1,1) returns 1.
+
+Gnumeric solves the problem in yet another way. It follows Excel from 1 to 59, skips 60, and it follows Excel (and all other engines from there on).
+A formula like `=DAY(60)` produces `#NUM!` in Gnumeric.
+
+Serial number 61 corresponds to 1 March 1900, and from there on most spreadsheet engines agree.
+
+IronCalc, like Excel, doesn't deal with serial numbers outside of the range [1, 2,958,465]. Other engines like Google sheets, do not have an upper limit.

docs/src/features/units.md

@@ -0,0 +1,13 @@
+---
+layout: doc
+outline: deep
+lang: en-US
+---
+
+# Units
+
+::: warning
+**Note:** This draft page is under construction 🚧
+:::
+
+Some IronCalc functions return values that have units like currencies, percentage or dates.

docs/src/features/value-types.md

@@ -0,0 +1,69 @@
+---
+layout: doc
+outline: deep
+lang: en-US
+---
+
+# Value Types
+
+::: warning
+**Note:** This draft page is under construction 🚧
+:::
+
+In IronCalc a value, a result of a calculation, can be one of the following.
+
+## Numbers
+
+Numbers in IronCalc are [IEEE 754 double-precision](https://en.wikipedia.org/wiki/Double-precision_floating-point_format).
+
+Numbers are only displayed up to 15 significant figures. That's why `=0.1+0.2` gives  `0.3`.
+
+Also, numbers are compared up to 15 significant figures. So `=IF(0.1+0.2=0.3, "Valid", "Invalid")` gives `Valid`.
+
+However, `=0.3-0.2-0.1` will not give exactly `0` in IronCalc.
+
+### Casting into numbers
+
+Strings and booleans are sometimes converted to numbers:
+
+`=1+"2"` => `3`
+
+Some functions cast in weird ways:
+
+`=SUM(1,TRUE)` => `1` and `=SUM(1,"1")` => `1`
+
+And `=SUM(1,A1)` => `1` (where A1 contains `TRUE` or `"1"`)
+
+
+Sometimes the conversion happens as might be expected. For example, `="123"+1` is `124`, `=SQRT("4")` is `2` and `=SQRT(TRUE)` is `1`.
+
+Some functions, however, are more strict. For example, `=BIN2DEC(TRUE)` gives the #VALUE! error.
+
+### Dates and times
+
+IronCalc uses numbers to represent dates and times.
+
+The integer part of the number represents the date, as a count of days since the fixed starting date of December 30, 1899.
+
+The fractional part of the number represents the time of day. 0.0 corresponds to 00:00:00 (midnight) and 0.5 corresponds to 12:00:00 (noon).
+
+## Strings
+
+
+### Complex numbers
+
+Using IronCalc, a complex number is a string of the form "1+j3".
+
+
+## Booleans
+
+### Casting from numbers
+
+## Errors
+
+
+### Casting from strings
+
+"#N/A" => #N/A
+
+## Arrays

docs/src/functions/date-and-time.md

@@ -14,7 +14,7 @@ You can track the progress in this [GitHub issue](https://github.com/ironcalc/Ir
 | DATE             | <Badge type="tip" text="Available" />          | –             |
 | DATEDIF          | <Badge type="info" text="Not implemented yet" /> | –             |
 | DATEVALUE        | <Badge type="info" text="Not implemented yet" /> | –             |
-| DAY              | <Badge type="tip" text="Available" />          | –             |
+| DAY              | <Badge type="tip" text="Available" />          | [DAY](date_and_time/day) |
 | DAYS             | <Badge type="info" text="Not implemented yet" /> | –             |
 | DAYS360          | <Badge type="info" text="Not implemented yet" /> | –             |
 | EDATE            | <Badge type="tip" text="Available" />          | –             |

docs/src/functions/date_and_time/day.md

@@ -3,9 +3,49 @@ layout: doc
 outline: deep
 lang: en-US
 ---
-
-# DAY
-
+# DAY function
 ::: warning
-🚧 This function is implemented but currently lacks detailed documentation. For guidance, you may refer to the equivalent functionality in [Microsoft Excel documentation](https://support.microsoft.com/en-us/office/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb).
-:::
+**Note:** This draft page is under construction 🚧
+:::
+
+## Overview
+DAY is a function of the Date and Time category that extracts the day of the month from a valid date [serial number](/features/serial-numbers.md), returning a number in the range [1, 31].
+
+## Usage
+### Syntax
+**DAY(<span title="Number" style="color:#1E88E5">date</span>) => <span title="Number" style="color:#1E88E5">day</span>**
+
+### Argument descriptions
+* *date* ([number](/features/value-types#numbers), required). The date for which the day of the month is to be calculated, expressed as a [serial number](/features/serial-numbers.md) in the range [1, 2958465]. The value  corresponds to the date 1899-12-31, while 2958465 corresponds to 9999-12-31.
+
+### Additional guidance
+If the supplied _date_ argument has a fractional part, DAY uses its [floor value](https://en.wikipedia.org/wiki/Floor_and_ceiling_functions).
+
+### Returned value
+DAY returns an integer [number](/features/value-types#numbers) in the range [1, 31], that is the day of the month according to the [Gregorian calendar](https://en.wikipedia.org/wiki/Gregorian_calendar).
+
+### Error conditions
+* In common with many other IronCalc functions, DAY propagates errors that are found in its argument.
+* If no argument, or more than one argument, is supplied, then DAY returns the [`#ERROR!`](/features/error-types.md#error) error.
+* If the value of the *date* argument is not (or cannot be converted to) a [number](/features/value-types#numbers), then DAY returns the [`#VALUE!`](/features/error-types.md#value) error.
+* For some argument values, DAY may return the [`#DIV/0!`](/features/error-types.md#div-0) error.
+* If date is less than 1, or greater than 2,958,465, then DAY returns the [`#NUM!`](/features/error-types.md#num) error.
+* At present, DAY does not accept a string representation of a date literal as an argument. For example, the formula `=DAY("2024-12-31")` returns the [`#VALUE!`](/features/error-types.md#value) error.
+<!--@include: ../markdown-snippets/error-type-details.txt-->
+
+## Details
+IronCalc utilizes Rust's [chrono](https://docs.rs/chrono/latest/chrono/) crate to implement the DAY function.
+
+## Compatibility Notes
+
+In IronCalc the argument of DAY cannot be text. This is confusing, and error prone, as in "10/12/2026" it is not clear if the day is 10 or 12.
+Most of all other spreadsheet engines like Excel, Google Sheets, Libre Office and Gnumeric accept text as input.
+
+## Examples
+
+[See some examples in IronCalc](https://app.ironcalc.com/?example=day).
+
+## Links
+* See also IronCalc's [MONTH](/functions/date_and_time/month.md) and [YEAR](/functions/date_and_time/year.md) functions.
+* Visit Microsoft Excel's [DAY function](https://support.microsoft.com/en-gb/office/day-function-8a7d1cbb-6c7d-4ba1-8aea-25c134d03101) page.
+* Both [Google Sheets](https://support.google.com/docs/answer/3093040) and [LibreOffice Calc](https://wiki.documentfoundation.org/Documentation/Calc_Functions/DAY) provide versions of the DAY function.

docs/src/functions/financial.md

@@ -51,7 +51,7 @@ You can track the progress in this [GitHub issue](https://github.com/ironcalc/Ir
 | PRICE      | <Badge type="info" text="Not implemented yet" /> | –                  |
 | PRICEDISC  | <Badge type="info" text="Not implemented yet" /> | –                  |
 | PRICEMAT   | <Badge type="info" text="Not implemented yet" /> | –                  |
-| PV         | <Badge type="tip" text="Available" />          | –                  |
+| PV         | <Badge type="tip" text="Available" />          | [PV](financial/pv) |
 | RATE       | <Badge type="tip" text="Available" />          | –                  |
 | RECEIVED   | <Badge type="info" text="Not implemented yet" /> | –                  |
 | RRI        | <Badge type="tip" text="Available" />          | -                  |

docs/src/functions/financial/fv.md

@@ -3,8 +3,10 @@ layout: doc
 outline: deep
 lang: en-US
 ---
-
 # FV function
+::: warning
+**Note:** This draft page is under construction 🚧
+:::
 ## Overview
 FV (<u>F</u>uture <u>V</u>alue) is a function of the Financial category that can be used to predict the future value of an investment or asset based on its present value.
 
@@ -13,34 +15,38 @@ FV can be used to calculate future value over a specified number of compounding
 If your interest rate varies between periods, use the [FVSCHEDULE](/functions/financial/fvschedule) function instead of FV.
 ## Usage
 ### Syntax
-**FV(rate, nper, pmt, pv, type)**
+**FV(<span title="Number" style="color:#1E88E5">rate</span>, <span title="Number" style="color:#1E88E5">nper</span>, <span title="Number" style="color:#1E88E5">pmt</span>, <span title="Number" style="color:#1E88E5">pv</span>=0, <span title="Boolean" style="color:#43A047">type</span>=FALSE) => <span title="Number" style="color:#1E88E5">fv</span>**
 ### Argument descriptions
-* *rate*. The fixed percentage interest rate or yield per period.
-* *nper*. The number of compounding periods to be taken into account. While this will often be an integer, non-integer values are accepted and processed.
-* *pmt*. The fixed amount paid or deposited each compounding period.
-* *pv* (optional). The present value or starting amount of the asset (default 0).
-* *type* (optional). A logical value indicating whether the payment due dates are at the end (0) of the compounding periods or at the beginning (any non-zero value). The default is 0 when omitted.
+* *rate* ([number](/features/value-types#numbers), required). The fixed percentage interest rate or yield per period.
+* *nper* ([number](/features/value-types#numbers), required). "nper" stands for <u>n</u>umber of <u>per</u>iods, in this case the number of compounding periods to be taken into account. While this will often be an integer, non-integer values are accepted and processed.
+* *pmt* ([number](/features/value-types#numbers), required). "pmt" stands for <u>p</u>ay<u>m</u>en<u>t</u>, in this case the fixed amount paid or deposited each compounding period. 
+* *pv* ([number](/features/value-types#numbers), [optional](/features/optional-arguments.md)). "pv" is the <u>p</u>resent <u>v</u>alue or starting amount of the asset (default 0).
+* *type* ([Boolean](/features/value-types#booleans), [optional](/features/optional-arguments.md)). A logical value indicating whether the payment due dates are at the end (FALSE or 0) of the compounding periods or at the beginning (TRUE or any non-zero value). The default is FALSE when omitted.
 ### Additional guidance
 * Make sure that the *rate* argument specifies the interest rate or yield applicable to the compounding period, based on the value chosen for *nper*.
-* The *pmt* and *pv* arguments should be expressed in the same currency unit. The value returned is expressed in the same currency unit.
+* The *pmt* and *pv* arguments should be expressed in the same currency unit.
 * To ensure a worthwhile result, one of the *pmt* and *pv* arguments should be non-zero.
 * The setting of the *type* argument only affects the calculation for non-zero values of the *pmt* argument.
-<!--@include: ../markdown-snippets/error-type-details.md-->
-
+### Returned value
+FV returns a [number](/features/value-types#numbers) representing the future value expressed in the same [currency unit](/features/units) that was used for the *pmt* and *pv* arguments.
+### Error conditions
+* In common with many other IronCalc functions, FV propagates errors that are found in any of its arguments.
+* If too few or too many arguments are supplied, FV returns the [`#ERROR!`](/features/error-types.md#error) error.
+* If the value of any of the *rate*, *nper*, *pmt* or *pv* arguments is not (or cannot be converted to) a [number](/features/value-types#numbers), then FV returns the [`#VALUE!`](/features/error-types.md#value) error.
+* If the value of the *type* argument is not (or cannot be converted to) a [Boolean](/features/value-types#booleans), then FV again returns the [`#VALUE!`](/features/error-types.md#value) error.
+* For some combinations of valid argument values, FV may return a [`#NUM!`](/features/error-types.md#num) error or a [`#DIV/0!`](/features/error-types.md#div-0) error.
+<!--@include: ../markdown-snippets/error-type-details.txt-->
 ## Details
-* If *rate* = 0, FV is given by the equation:
-$$
-FV = -pv - (pmt \times nper)
-$$
+* If $\text{type} \neq 0$, $\text{fv}$ is given by the equation:
+$$ \text{fv} = -\text{pv} \times (1 + \text{rate})^\text{nper} - \dfrac{\text{pmt}\times\big({(1+\text{rate})^\text{nper}-1}\big) \times(1+\text{rate})}{\text{rate}}$$
 
-* If *rate* <> 0 and *type* = 0, FV is given by the equation:
-$$ FV = -pv \times (1 + rate)^{nper} - \dfrac{pmt\times\big({(1+rate)^{nper}-1}\big)}{rate}
-$$
-* If *rate* <> 0 and *type* <> 0, FV is given by the equation:
-$$ FV = -pv \times (1 + rate)^{nper} - \dfrac{pmt\times\big({(1+rate)^{nper}-1}\big) \times(1+rate)}{rate}
-$$
+* If $\text{type} = 0$, $\text{fv}$ is given by the equation:
+$$ \text{fv} = -\text{pv} \times (1 + \text{rate})^{\text{nper}} - \dfrac{\text{pmt}\times\big({(1+\text{rate})^\text{nper}-1}\big)}{\text{rate}}$$
+
+* For any $\text{type}$, in the special case of $\text{rate} = 0$, $\text{fv}$ is given by the equation:
+$$ \text{fv} = -\text{pv} - (\text{pmt} \times \text{nper}) $$
 ## Examples
-[See this example in IronCalc](https://app.ironcalc.com/?example=fv).
+[See some examples in IronCalc](https://app.ironcalc.com/?example=fv).
 
 ## Links
 * For more information about the concept of "future value" in finance, visit Wikipedia's [Future value](https://en.wikipedia.org/wiki/Future_value) page.

docs/src/functions/financial/pv.md

@@ -3,9 +3,57 @@ layout: doc
 outline: deep
 lang: en-US
 ---
-
-# PV
-
+# PV function
 ::: warning
-🚧 This function is implemented but currently lacks detailed documentation. For guidance, you may refer to the equivalent functionality in [Microsoft Excel documentation](https://support.microsoft.com/en-us/office/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb).
-:::
+**Note:** This draft page is under construction 🚧
+:::
+## Overview
+PV (<u>P</u>resent <u>V</u>alue) is a function of the Financial category that can be used to calculate the present value of a series of future cash flows.
+
+PV can be used to calculate present value over a specified number of compounding periods. A fixed interest rate or yield is assumed over all periods, and a fixed payment or deposit can be applied at the start or end of every period.
+## Usage
+### Syntax
+**PV(<span title="Number" style="color:#1E88E5">rate</span>, <span title="Number" style="color:#1E88E5">nper</span>, <span title="Number" style="color:#1E88E5">pmt</span>, <span title="Number" style="color:#1E88E5">fv</span>=0, <span title="Boolean" style="color:#43A047">type</span>=FALSE) => <span title="Number" style="color:#1E88E5">pv</span>**
+### Argument descriptions
+* *rate* ([number](/features/value-types#numbers), required). The fixed percentage interest rate or yield per period.
+* *nper* ([number](/features/value-types#numbers), required). "nper" stands for <u>n</u>umber of <u>per</u>iods, in this case the number of compounding periods to be taken into account. While this will often be an integer, non-integer values are accepted and processed.
+* *pmt* ([number](/features/value-types#numbers), required). "pmt" stands for <u>p</u>ay<u>m</u>en<u>t</u>, in this case the fixed amount paid or deposited each compounding period. 
+* *fv* ([number](/features/value-types#numbers), [optional](/features/optional-arguments.md)). "fv" is the <u>f</u>uture <u>v</u>alue at the end of the final compounding period (default 0).
+* *type* ([Boolean](/features/value-types#booleans), [optional](/features/optional-arguments.md)). A logical value indicating whether the payment due dates are at the end (FALSE or 0) of the compounding periods or at the beginning (TRUE or any non-zero value). The default is FALSE when omitted.
+### Additional guidance
+* Make sure that the *rate* argument specifies the interest rate or yield applicable to the compounding period, based on the value chosen for *nper*.
+* The *pmt* and *fv* arguments should be expressed in the same currency unit.
+* To ensure a worthwhile result, one of the *pmt* and *fv* arguments should be non-zero.
+* The setting of the *type* argument only affects the calculation for non-zero values of the *pmt* argument.
+### Returned value
+PV returns a [number](/features/value-types#numbers) representing the present value expressed in the same [currency unit](/features/units) that was used for the *pmt* and *fv* arguments.
+### Error conditions
+* In common with many other IronCalc functions, PV propagates errors that are found in any of its arguments.
+* If too few or too many arguments are supplied, PV returns the [`#ERROR!`](/features/error-types.md#error) error.
+* If the value of any of the *rate*, *nper*, *pmt* or *fv* arguments is not (or cannot be converted to) a [number](/features/value-types#numbers), then PV returns the [`#VALUE!`](/features/error-types.md#value) error.
+* If the value of the *type* argument is not (or cannot be converted to) a [Boolean](/features/value-types#booleans), then PV again returns the [`#VALUE!`](/features/error-types.md#value) error.
+* For some combinations of valid argument values, PV may return a [`#NUM!`](/features/error-types.md#num) error or a [`#DIV/0!`](/features/error-types.md#div-0) error.
+In paticular, PV always returns a [`#DIV/0!`](/features/error-types.md#div-0) error if the value of the *rate* argument is set to -1.
+
+<!--@include: ../markdown-snippets/error-type-details.txt-->
+## Details
+* If $\text{type} \neq 0$, $\text{pv}$ is given by the equation:
+$$ \text{pv} = - \Biggl(\dfrac{(\text{fv} \times \text{rate}) + \bigl(\text{pmt} \times (1+\text{rate})\times \bigl({(1+\text{rate})^{\text{nper}}-1\bigr)\bigr)}}{\text{rate} \times (1+\text{rate})^{\text{nper}}}\Biggl)
+$$
+
+* If $\text{type} = 0$, $\text{pv}$ is given by the equation:
+$$ \text{pv} = - \Biggl(\dfrac{(\text{fv} \times \text{rate}) + \bigl(\text{pmt}\times \bigl({(1+\text{rate})^{\text{nper}}-1\bigr)\bigr)}}{\text{rate} \times (1+\text{rate})^\text{{nper}}}\Biggl)
+$$
+
+* For any $\text{type}$, in the special case of $\text{rate} = 0$, $\text{pv}$ is given by the equation:
+$$
+\text{pv} = -\text{fv} - (\text{pmt} \times \text{nper})
+$$
+## Examples
+[See some examples in IronCalc](https://app.ironcalc.com/?example=pv).
+
+## Links
+* For more information about the concept of "present value" in finance, visit Wikipedia's [Present value](https://en.wikipedia.org/wiki/present_value) page.
+* See also IronCalc's [FV](/functions/financial/fv), [NPER](/functions/financial/nper), [PMT](/functions/financial/pmt)  and [RATE](/functions/financial/rate) functions.
+* Visit Microsoft Excel's [PV function](https://support.microsoft.com/en-gb/office/pv-function-23879d31-0e02-4321-be01-da16e8168cbd) page.
+* Both [Google Sheets](https://support.google.com/docs/answer/3093243) and [LibreOffice Calc](https://wiki.documentfoundation.org/Documentation/Calc_Functions/PV) provide versions of the PV function.

docs/src/functions/markdown-snippets/error-type-details.md

@@ -1 +0,0 @@
-* For information about the different types of errors that you may encounter when using IronCalc functions, visit our [Error Types](/features/error-types) page.

docs/src/functions/markdown-snippets/error-type-details.txt

@@ -0,0 +1 @@
+* For more information about the different types of errors that you may encounter when using IronCalc functions, visit our [Error Types](/features/error-types) page.

docs/src/functions/math-and-trigonometry.md

@@ -29,7 +29,7 @@ You can track the progress in this [GitHub issue](https://github.com/ironcalc/Ir
 | CEILING.PRECISE | <Badge type="info" text="Not implemented yet" /> | –             |
 | COMBIN          | <Badge type="info" text="Not implemented yet" /> | –             |
 | COMBINA         | <Badge type="info" text="Not implemented yet" /> | –             |
-| COS             | <Badge type="tip" text="Available" />          | –             |
+| COS             | <Badge type="tip" text="Available" />          | [COS](math_and_trigonometry/cos) |
 | COSH            | <Badge type="tip" text="Available" />          | –             |
 | COT             | <Badge type="info" text="Not implemented yet" /> | –             |
 | COTH            | <Badge type="info" text="Not implemented yet" /> | –             |
@@ -77,7 +77,7 @@ You can track the progress in this [GitHub issue](https://github.com/ironcalc/Ir
 | SERIESSUM       | <Badge type="info" text="Not implemented yet" /> | –             |
 | SEQUENCE        | <Badge type="info" text="Not implemented yet" /> | –             |
 | SIGN            | <Badge type="info" text="Not implemented yet" /> | –             |
-| SIN             | <Badge type="tip" text="Available" />          | –             |
+| SIN             | <Badge type="tip" text="Available" />          | [SIN](math_and_trigonometry/sin) |
 | SINH            | <Badge type="tip" text="Available" />          | –             |
 | SQRT            | <Badge type="tip" text="Available" />          | –             |
 | SQRTPI          | <Badge type="info" text="Not implemented yet" /> | –             |
@@ -90,6 +90,6 @@ You can track the progress in this [GitHub issue](https://github.com/ironcalc/Ir
 | SUMX2MY2        | <Badge type="info" text="Not implemented yet" /> | –             |
 | SUMX2PY2        | <Badge type="info" text="Not implemented yet" /> | –             |
 | SUMXMY2         | <Badge type="info" text="Not implemented yet" /> | –             |
-| TAN             | <Badge type="tip" text="Available" />          | –             |
+| TAN             | <Badge type="tip" text="Available" />          | [TAN](math_and_trigonometry/tan) |
 | TANH            | <Badge type="tip" text="Available" />          | –             |
 | TRUNC           | <Badge type="info" text="Not implemented yet" /> | –             |

docs/src/functions/math_and_trigonometry/cos.md

@@ -3,9 +3,41 @@ layout: doc
 outline: deep
 lang: en-US
 ---
-
-# COS
-
+# COS function
 ::: warning
-🚧 This function is implemented but currently lacks detailed documentation. For guidance, you may refer to the equivalent functionality in [Microsoft Excel documentation](https://support.microsoft.com/en-us/office/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb).
-:::
+**Note:** This draft page is under construction 🚧
+:::
+## Overview
+COS is a function of the Math and Trigonometry category that calculates the trigonometric cosine of an angle, returning a value in the range [-1, +1].
+## Usage
+### Syntax
+**COS(<span title="Number" style="color:#1E88E5">angle</span>) => <span title="Number" style="color:#1E88E5">cos</span>**
+### Argument descriptions
+* *angle* ([number](/features/value-types#numbers), required). The angle whose cosine is to be calculated, expressed in radians. To convert between degrees and radians, use the relation below. Alternatively, use the [DEGREES](/functions/math_and_trigonometry/degrees) or [RADIANS](/functions/math_and_trigonometry/radians) functions.
+$$
+1~\:~\text{degree} = \dfrac{\pi}{180} = 0.01745329252~\text{radians}
+$$
+
+### Additional guidance
+None.
+### Returned value
+COS returns a unitless [number](/features/value-types#numbers) that is the trigonometric cosine of the specified angle.
+### Error conditions
+* In common with many other IronCalc functions, COS propagates errors that are found in its argument.
+* If no argument, or more than one argument, is supplied, then COS returns the [`#ERROR!`](/features/error-types.md#error) error.
+* If the value of the *angle* argument is not (or cannot be converted to) a [number](/features/value-types#numbers), then COS returns the [`#VALUE!`](/features/error-types.md#value) error.
+* For some argument values, COS may return a [`#DIV/0!`](/features/error-types.md#div-0) error.
+<!--@include: ../markdown-snippets/error-type-details.txt-->
+## Details
+* The COS function utilizes the *cos()* method provided by the [Rust Standard Library](https://doc.rust-lang.org/std/).
+* The figure below illustrates the output of the COS function for angles $x$ in the range -2$\pi$ to +2$\pi$ radians.
+<center><img src="/functions/images/cosine-curve.png" width="350" alt="Graph showing cos(x) for x between -2π and +2π radians."></center>
+
+## Examples
+[See some examples in IronCalc](https://app.ironcalc.com/?example=cos).
+
+## Links
+* For more information about trigonometric cosine, visit Wikipedia's [Sine and cosine](https://en.wikipedia.org/wiki/Sine_and_cosine) page.
+* See also IronCalc's [ACOS](/functions/math_and_trigonometry/acos), [SIN](/functions/math_and_trigonometry/sin) and [TAN](/functions/math_and_trigonometry/tan) functions.
+* Visit Microsoft Excel's [COS function](https://support.microsoft.com/en-gb/office/cos-function-0fb808a5-95d6-4553-8148-22aebdce5f05) page.
+* Both [Google Sheets](https://support.google.com/docs/answer/3093476) and [LibreOffice Calc](https://wiki.documentfoundation.org/Documentation/Calc_Functions/COS) provide versions of the COS function.

docs/src/functions/math_and_trigonometry/sin.md

@@ -3,9 +3,41 @@ layout: doc
 outline: deep
 lang: en-US
 ---
-
-# SIN
-
+# SIN function
 ::: warning
-🚧 This function is implemented but currently lacks detailed documentation. For guidance, you may refer to the equivalent functionality in [Microsoft Excel documentation](https://support.microsoft.com/en-us/office/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb).
-:::
+**Note:** This draft page is under construction 🚧
+:::
+## Overview
+SIN is a function of the Math and Trigonometry category that calculates the trigonometric sine of an angle, returning a value in the range [-1, +1].
+## Usage
+### Syntax
+**SIN(<span title="Number" style="color:#1E88E5">angle</span>) => <span title="Number" style="color:#1E88E5">sin</span>**
+### Argument descriptions
+* *angle* ([number](/features/value-types#numbers), required). The angle whose sine is to be calculated, expressed in radians. To convert between degrees and radians, use the relation below. Alternatively, use the [DEGREES](/functions/math_and_trigonometry/degrees) or [RADIANS](/functions/math_and_trigonometry/radians) functions.
+$$
+1~\:~\text{degree} = \dfrac{\pi}{180} = 0.01745329252~\text{radians}
+$$
+
+### Additional guidance
+None.
+### Returned value
+SIN returns a unitless [number](/features/value-types#numbers) that is the trigonometric sine of the specified angle.
+### Error conditions
+* In common with many other IronCalc functions, SIN propagates errors that are found in its argument.
+* If no argument, or more than one argument, is supplied, then SIN returns the [`#ERROR!`](/features/error-types.md#error) error.
+* If the value of the *angle* argument is not (or cannot be converted to) a [number](/features/value-types#numbers), then SIN returns the [`#VALUE!`](/features/error-types.md#value) error.
+* For some argument values, SIN may return a [`#DIV/0!`](/features/error-types.md#div-0) error.
+<!--@include: ../markdown-snippets/error-type-details.txt-->
+## Details
+* The SIN function utilizes the *sin()* method provided by the [Rust Standard Library](https://doc.rust-lang.org/std/).
+* The figure below illustrates the output of the SIN function for angles $x$ in the range -2$\pi$ to +2$\pi$ radians.
+<center><img src="/functions/images/sine-curve.png" width="350" alt="Graph showing sin(x) for x between -2π and +2π."></center>
+
+## Examples
+[See some examples in IronCalc](https://app.ironcalc.com/?example=sin).
+
+## Links
+* For more information about trigonometric sine, visit Wikipedia's [Sine and cosine](https://en.wikipedia.org/wiki/Sine_and_cosine) page.
+* See also IronCalc's [ASIN](/functions/math_and_trigonometry/asin), [COS](/functions/math_and_trigonometry/cos) and [TAN](/functions/math_and_trigonometry/tan) functions.
+* Visit Microsoft Excel's [SIN function](https://support.microsoft.com/en-gb/office/sin-function-cf0e3432-8b9e-483c-bc55-a76651c95602) page.
+* Both [Google Sheets](https://support.google.com/docs/answer/3093447) and [LibreOffice Calc](https://wiki.documentfoundation.org/Documentation/Calc_Functions/SIN) provide versions of the SIN function.

docs/src/functions/math_and_trigonometry/tan.md

@@ -3,9 +3,42 @@ layout: doc
 outline: deep
 lang: en-US
 ---
-
-# TAN
-
+# TAN function
 ::: warning
-🚧 This function is implemented but currently lacks detailed documentation. For guidance, you may refer to the equivalent functionality in [Microsoft Excel documentation](https://support.microsoft.com/en-us/office/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb).
-:::
+**Note:** This draft page is under construction 🚧
+:::
+## Overview
+TAN is a function of the Math and Trigonometry category that calculates the trigonometric tangent of an angle, returning a value in the range (-$\infty$, +$\infty$).
+## Usage
+### Syntax
+**TAN(<span title="Number" style="color:#1E88E5">angle</span>) => <span title="Number" style="color:#1E88E5">tan</span>**
+### Argument descriptions
+* *angle* ([number](/features/value-types#numbers), required). The angle whose tangent is to be calculated, expressed in radians. To convert between degrees and radians, use the relation below. Alternatively, use the [DEGREES](/functions/math_and_trigonometry/degrees) or [RADIANS](/functions/math_and_trigonometry/radians) functions.
+$$
+1~\:~\text{degree} = \dfrac{\pi}{180} = 0.01745329252~\text{radians}
+$$
+
+### Additional guidance
+None.
+### Returned value
+TAN returns a unitless [number](/features/value-types#numbers) that is the trigonometric tangent of the specified angle.
+### Error conditions
+* In common with many other IronCalc functions, TAN propagates errors that are found in its argument.
+* If no argument, or more than one argument, is supplied, then TAN returns the [`#ERROR!`](/features/error-types.md#error) error.
+* If the value of the *angle* argument is not (or cannot be converted to) a [number](/features/value-types#numbers), then TAN returns the [`#VALUE!`](/features/error-types.md#value) error.
+* For some argument values, TAN may return a [`#DIV/0!`](/features/error-types.md#div-0) error.
+<!--@include: ../markdown-snippets/error-type-details.txt-->
+## Details
+* The TAN function utilizes the *tan()* method provided by the [Rust Standard Library](https://doc.rust-lang.org/std/).
+* The figure below illustrates the output of the TAN function for angles $x$ in the range -2$π$ to +2$π$.
+<center><img src="/functions/images/tangent-curve.png" width="350" alt="Graph showing tan(x) for x between -2π and +2π."></center>
+
+* Theoretically, $\text{tan}(x)$ is undefined for any critical $x$ that satisfies $x = \frac{\pi}{2} + k\pi$ (where $k$ is any integer). However, an exact representation of the mathmatical constant $\pi$ requires infinite precision, which cannot be achieved with the floating-point representation available. Hence, TAN will return very large or very small values close to critical $x$ values.
+## Examples
+[See some examples in IronCalc](https://app.ironcalc.com/?example=tan).
+
+## Links
+* For more information about trigonometric tangent, visit Wikipedia's [Trigonometric functions](https://en.wikipedia.org/wiki/Trigonometric_functions) page.
+* See also IronCalc's [ATAN](/functions/math_and_trigonometry/atan), [COS](/functions/math_and_trigonometry/cos) and [SIN](/functions/math_and_trigonometry/sin) functions.
+* Visit Microsoft Excel's [TAN function](https://support.microsoft.com/en-gb/office/tan-function-08851a40-179f-4052-b789-d7f699447401) page.
+* Both [Google Sheets](https://support.google.com/docs/answer/3093586) and [LibreOffice Calc](https://wiki.documentfoundation.org/Documentation/Calc_Functions/TAN) provide versions of the TAN function.

docs/src/other-spreadsheets/index.md

@@ -0,0 +1,30 @@
+---
+layout: doc
+outline: deep
+lang: en-US
+---
+
+::: warning
+**Note:** This draft page is under construction 🚧
+:::
+
+# Other spreadsheet engines
+
+There are numerous spreadsheet engines out there
+
+## Excel, the Friendly Giant
+
+## Google Sheets
+
+## LibreOffice
+
+## Gnumeric
+
+
+# Links
+
+For a list of spreadsheet software you can consult [wikipedia](https://en.wikipedia.org/wiki/List_of_spreadsheet_software).
+
+
+
+

docs/src/web-application/name-manager.md

@@ -0,0 +1,12 @@
+---
+layout: doc
+outline: deep
+lang: en-US
+---
+
+# Name Manager
+
+::: warning
+**Note:** This draft page is under construction 🚧
+:::
+

webapp/src/AppComponents/DeleteWorkbookDialog.tsx

@@ -1,21 +1,24 @@
 import styled from "@emotion/styled";
 import { Trash2 } from "lucide-react";
-import { forwardRef, useEffect } from "react";
-import { theme } from "../theme";
+import { useEffect, useRef } from "react";
+
+interface DeleteWorkbookDialogProperties {
+  onClose: () => void;
+  onConfirm: () => void;
+  workbookName: string;
+}
+
+function DeleteWorkbookDialog(properties: DeleteWorkbookDialogProperties) {
+  const deleteButtonRef = useRef<HTMLButtonElement>(null);
 
-export const DeleteWorkbookDialog = forwardRef<
-  HTMLDivElement,
-  {
-    onClose: () => void;
-    onConfirm: () => void;
-    workbookName: string;
-  }
->((properties, ref) => {
   useEffect(() => {
     const root = document.getElementById("root");
     if (root) {
       root.style.filter = "blur(2px)";
     }
+    if (deleteButtonRef.current) {
+      deleteButtonRef.current.focus();
+    }
     return () => {
       const root = document.getElementById("root");
       if (root) {
@@ -26,8 +29,12 @@ export const DeleteWorkbookDialog = forwardRef<
 
   return (
     <DialogWrapper
-      ref={ref}
       tabIndex={-1}
+      onKeyDown={(event) => {
+        if (event.code === "Escape") {
+          properties.onClose();
+        }
+      }}
       role="dialog"
       aria-labelledby="delete-dialog-title"
       aria-describedby="delete-dialog-description"
@@ -47,6 +54,7 @@ export const DeleteWorkbookDialog = forwardRef<
               properties.onConfirm();
               properties.onClose();
             }}
+            ref={deleteButtonRef}
           >
             Yes, delete workbook
           </DeleteButton>
@@ -55,10 +63,25 @@ export const DeleteWorkbookDialog = forwardRef<
       </ContentWrapper>
     </DialogWrapper>
   );
-});
+}
 
 DeleteWorkbookDialog.displayName = "DeleteWorkbookDialog";
 
+// some colors taken from the IronCalc palette
+const COMMON_WHITE = "#FFF";
+const COMMON_BLACK = "#272525";
+
+const ERROR_MAIN = "#EB5757";
+const ERROR_DARK = "#CB4C4C";
+
+const GREY_200 = "#EEEEEE";
+const GREY_300 = "#E0E0E0";
+const GREY_700 = "#616161";
+const GREY_900 = "#333333";
+
+const PRIMARY_MAIN = "#F2994A";
+const PRIMARY_DARK = "#D68742";
+
 const DialogWrapper = styled.div`
   position: fixed;
   top: 50%;
@@ -70,7 +93,7 @@ const DialogWrapper = styled.div`
   gap: 16px;
   padding: 12px;
   border-radius: 8px;
-  box-shadow: 0px 1px 3px 0px ${theme.palette.common.black}1A;
+  box-shadow: 0px 1px 3px 0px ${COMMON_BLACK}1A;
   width: 280px;
   max-width: calc(100% - 40px);
   z-index: 50;
@@ -84,9 +107,9 @@ const IconWrapper = styled.div`
   width: 36px;
   height: 36px;
   border-radius: 4px;
-  background-color: ${theme.palette.error.main}1A;
+  background-color: ${ERROR_MAIN}1A;
   margin: 12px auto 0 auto;
-  color: ${theme.palette.error.main};
+  color: ${ERROR_MAIN};
   svg {
     width: 16px;
     height: 16px;
@@ -106,13 +129,13 @@ const Title = styled.h2`
   margin: 0;
   font-weight: 600;
   font-size: inherit;
-  color: ${theme.palette.grey["900"]};
+  color: ${GREY_900};
 `;
 
 const Body = styled.p`
   margin: 0;
   text-align: center;
-  color: ${theme.palette.grey["900"]};
+  color: ${GREY_900};
   font-size: 12px;
 `;
 
@@ -126,8 +149,8 @@ const ButtonGroup = styled.div`
 
 const Button = styled.button`
   cursor: pointer;
-  color: ${theme.palette.common.white};
-  background-color: ${theme.palette.primary.main};
+  color: ${COMMON_WHITE};
+  background-color: ${PRIMARY_MAIN};
   padding: 0px 10px;
   height: 36px;
   border-radius: 4px;
@@ -139,22 +162,24 @@ const Button = styled.button`
   text-overflow: ellipsis;
   transition: background-color 150ms;
   &:hover {
-    background-color: ${theme.palette.primary.dark};
+    background-color: ${PRIMARY_DARK};
   }
 `;
 
 const DeleteButton = styled(Button)`
-  background-color: ${theme.palette.error.main};
-  color: ${theme.palette.common.white};
+  background-color: ${ERROR_MAIN};
+  color: ${COMMON_WHITE};
   &:hover {
-    background-color: ${theme.palette.error.dark};
+    background-color: ${ERROR_DARK};
   }
 `;
 
 const CancelButton = styled(Button)`
-  background-color: ${theme.palette.grey["200"]};
-  color: ${theme.palette.grey["700"]};
+  background-color: ${GREY_200};
+  color: ${GREY_700};
   &:hover {
-    background-color: ${theme.palette.grey["300"]};
+    background-color: ${GREY_300};
   }
 `;
+
+export default DeleteWorkbookDialog;

webapp/src/AppComponents/FileMenu.tsx

@@ -2,8 +2,8 @@ import styled from "@emotion/styled";
 import { Menu, MenuItem, Modal } from "@mui/material";
 import { Check, FileDown, FileUp, Plus, Trash2 } from "lucide-react";
 import { useRef, useState } from "react";
-import { DeleteWorkbookDialog } from "./DeleteWorkbookDialog";
-import { UploadFileDialog } from "./UploadFileDialog";
+import DeleteWorkbookDialog from "./DeleteWorkbookDialog";
+import UploadFileDialog from "./UploadFileDialog";
 import { getModelsMetadata, getSelectedUuid } from "./storage";
 
 export function FileMenu(props: {
@@ -105,10 +105,6 @@ export function FileMenu(props: {
       <Modal
         open={isImportMenuOpen}
         onClose={() => {
-          const root = document.getElementById("root");
-          if (root) {
-            root.style.filter = "";
-          }
           setImportMenuOpen(false);
         }}
         aria-labelledby="modal-modal-title"
@@ -117,10 +113,6 @@ export function FileMenu(props: {
         <>
           <UploadFileDialog
             onClose={() => {
-              const root = document.getElementById("root");
-              if (root) {
-                root.style.filter = "";
-              }
               setImportMenuOpen(false);
             }}
             onModelUpload={props.onModelUpload}
@@ -133,11 +125,13 @@ export function FileMenu(props: {
         aria-labelledby="delete-dialog-title"
         aria-describedby="delete-dialog-description"
       >
-        <DeleteWorkbookDialog
-          onClose={() => setDeleteDialogOpen(false)}
-          onConfirm={props.onDelete}
-          workbookName={selectedUuid ? models[selectedUuid] : ""}
-        />
+        <>
+          <DeleteWorkbookDialog
+            onClose={() => setDeleteDialogOpen(false)}
+            onConfirm={props.onDelete}
+            workbookName={selectedUuid ? models[selectedUuid] : ""}
+          />
+        </>
       </Modal>
     </>
   );

webapp/src/AppComponents/UploadFileDialog.tsx

@@ -1,17 +1,34 @@
 import styled from "@emotion/styled";
-import { BookOpen, FileUp } from "lucide-react";
-import { type DragEvent, useRef, useState } from "react";
+import { BookOpen, FileUp, X } from "lucide-react";
+import { type DragEvent, useEffect, useRef, useState } from "react";
 
-export function UploadFileDialog(properties: {
+function UploadFileDialog(properties: {
   onClose: () => void;
   onModelUpload: (blob: ArrayBuffer, fileName: string) => Promise<void>;
 }) {
   const [hover, setHover] = useState(false);
   const [message, setMessage] = useState("");
   const fileInputRef = useRef<HTMLInputElement>(null);
+  const crossRef = useRef<HTMLDivElement>(null);
 
   const { onModelUpload } = properties;
 
+  useEffect(() => {
+    const root = document.getElementById("root");
+    if (root) {
+      root.style.filter = "blur(2px)";
+    }
+    if (crossRef.current) {
+      crossRef.current.focus();
+    }
+    return () => {
+      const root = document.getElementById("root");
+      if (root) {
+        root.style.filter = "none";
+      }
+    };
+  }, []);
+
   const handleClose = () => {
     properties.onClose();
   };
@@ -79,12 +96,16 @@ export function UploadFileDialog(properties: {
     reader.readAsArrayBuffer(file);
   };
 
-  const root = document.getElementById("root");
-  if (root) {
-    root.style.filter = "blur(4px)";
-  }
   return (
-    <UploadDialog>
+    <UploadDialog
+      tabIndex={-1}
+      role="dialog"
+      onKeyDown={(event) => {
+        if (event.code === "Escape") {
+          handleClose();
+        }
+      }}
+    >
       <UploadTitle>
         <span style={{ flexGrow: 2, marginLeft: 12 }}>
           Import an .xlsx file
@@ -92,29 +113,11 @@ export function UploadFileDialog(properties: {
         <Cross
           style={{ marginRight: 12 }}
           onClick={handleClose}
-          onKeyDown={() => {}}
+          title="Close Dialog"
+          ref={crossRef}
+          tabIndex={0}
         >
-          <svg
-            width="16"
-            height="16"
-            viewBox="0 0 16 16"
-            fill="none"
-            xmlns="http://www.w3.org/2000/svg"
-          >
-            <title>Close</title>
-            <path
-              d="M12 4.5L4 12.5"
-              stroke="#333333"
-              strokeLinecap="round"
-              strokeLinejoin="round"
-            />
-            <path
-              d="M4 4.5L12 12.5"
-              stroke="#333333"
-              strokeLinecap="round"
-              strokeLinejoin="round"
-            />
-          </svg>
+          <X />
         </Cross>
       </UploadTitle>
       {message === "" ? (
@@ -215,6 +218,11 @@ const Cross = styled("div")`
   cursor: pointer;
   align-items: center;
   justify-content: center;
+  svg {
+    width: 16px;
+    height: 16px;
+    stroke-width: 1.5;
+  }
 `;
 
 const DocLink = styled("span")`
@@ -300,3 +308,5 @@ const DropZone = styled("div")`
   gap: 16px;
   transition: 0.2s ease-in-out;
 `;
+
+export default UploadFileDialog;

webapp/src/components/NameManagerDialog/NameManagerDialog.tsx

@@ -0,0 +1,244 @@
+import type { DefinedName, WorksheetProperties } from "@ironcalc/wasm";
+import {
+  Box,
+  Button,
+  Dialog,
+  DialogActions,
+  DialogContent,
+  DialogTitle,
+  IconButton,
+  Stack,
+  styled,
+} from "@mui/material";
+import { t } from "i18next";
+import { BookOpen, Plus, X } from "lucide-react";
+import { useEffect, useState } from "react";
+import NamedRangeActive from "./NamedRangeActive";
+import NamedRangeInactive from "./NamedRangeInactive";
+
+export interface NameManagerProperties {
+  newDefinedName: (
+    name: string,
+    scope: number | undefined,
+    formula: string,
+  ) => void;
+  updateDefinedName: (
+    name: string,
+    scope: number | undefined,
+    newName: string,
+    newScope: number | undefined,
+    newFormula: string,
+  ) => void;
+  deleteDefinedName: (name: string, scope: number | undefined) => void;
+  selectedArea: () => string;
+  worksheets: WorksheetProperties[];
+  definedNameList: DefinedName[];
+}
+
+interface NameManagerDialogProperties {
+  open: boolean;
+  onClose: () => void;
+  model: NameManagerProperties;
+}
+
+function NameManagerDialog(properties: NameManagerDialogProperties) {
+  const { open, model, onClose } = properties;
+  const {
+    newDefinedName,
+    updateDefinedName,
+    deleteDefinedName,
+    selectedArea,
+    worksheets,
+    definedNameList,
+  } = model;
+  // If editingNameIndex is -1, then we are adding a new name
+  // If editingNameIndex is -2, then we are not editing any name
+  // If editingNameIndex is a positive number, then we are editing that index
+  const [editingNameIndex, setEditingNameIndex] = useState(-2);
+
+  useEffect(() => {
+    if (open) {
+      setEditingNameIndex(-2);
+    }
+  }, [open]);
+
+  return (
+    <StyledDialog open={open} onClose={onClose} maxWidth={false} scroll="paper">
+      <StyledDialogTitle>
+        {t("name_manager_dialog.title")}
+        <IconButton onClick={onClose}>
+          <X size={16} />
+        </IconButton>
+      </StyledDialogTitle>
+      <StyledDialogContent dividers>
+        <StyledRangesHeader>
+          <StyledBox>{t("name_manager_dialog.name")}</StyledBox>
+          <StyledBox>{t("name_manager_dialog.range")}</StyledBox>
+          <StyledBox>{t("name_manager_dialog.scope")}</StyledBox>
+        </StyledRangesHeader>
+        <NameListWrapper>
+          {definedNameList.map((definedName, index) => {
+            const scopeName = definedName.scope
+              ? worksheets[definedName.scope].name
+              : "[global]";
+            if (index === editingNameIndex) {
+              return (
+                <NamedRangeActive
+                  worksheets={worksheets}
+                  name={definedName.name}
+                  scope={scopeName}
+                  formula={definedName.formula}
+                  key={definedName.name + definedName.scope}
+                  onSave={(
+                    newName,
+                    newScope,
+                    newFormula,
+                  ): string | undefined => {
+                    const scope_index = worksheets.findIndex(
+                      (s) => s.name === newScope,
+                    );
+                    const scope = scope_index > 0 ? scope_index : undefined;
+                    try {
+                      updateDefinedName(
+                        definedName.name,
+                        definedName.scope,
+                        newName,
+                        scope,
+                        newFormula,
+                      );
+                      setEditingNameIndex(-2);
+                    } catch (e) {
+                      return `${e}`;
+                    }
+                  }}
+                  onCancel={() => setEditingNameIndex(-2)}
+                />
+              );
+            }
+            return (
+              <NamedRangeInactive
+                name={definedName.name}
+                scope={scopeName}
+                formula={definedName.formula}
+                key={definedName.name + definedName.scope}
+                showOptions={editingNameIndex === -2}
+                onEdit={() => setEditingNameIndex(index)}
+                onDelete={() => {
+                  deleteDefinedName(definedName.name, definedName.scope);
+                }}
+              />
+            );
+          })}
+        </NameListWrapper>
+        {editingNameIndex === -1 && (
+          <NamedRangeActive
+            worksheets={worksheets}
+            name={""}
+            formula={selectedArea()}
+            scope={"[global]"}
+            onSave={(name, scope, formula): string | undefined => {
+              const scope_index = worksheets.findIndex((s) => s.name === scope);
+              const scope_value = scope_index > 0 ? scope_index : undefined;
+              try {
+                newDefinedName(name, scope_value, formula);
+                setEditingNameIndex(-2);
+              } catch (e) {
+                return `${e}`;
+              }
+            }}
+            onCancel={() => setEditingNameIndex(-2)}
+          />
+        )}
+      </StyledDialogContent>
+      <StyledDialogActions>
+        <Box display="flex" alignItems="center" gap={"8px"}>
+          <BookOpen color="grey" size={16} />
+          <UploadFooterLink
+            href="https://docs.ironcalc.com/web-application/name-manager.html"
+            target="_blank"
+            rel="noopener noreferrer"
+          >
+            {t("name_manager_dialog.help")}
+          </UploadFooterLink>
+        </Box>
+        <Button
+          onClick={() => setEditingNameIndex(-1)}
+          variant="contained"
+          disableElevation
+          sx={{ textTransform: "none" }}
+          startIcon={<Plus size={16} />}
+          disabled={editingNameIndex > -2}
+        >
+          {t("name_manager_dialog.new")}
+        </Button>
+      </StyledDialogActions>
+    </StyledDialog>
+  );
+}
+
+const StyledDialog = styled(Dialog)(() => ({
+  "& .MuiPaper-root": {
+    height: "380px",
+    minHeight: "200px",
+    minWidth: "620px",
+  },
+}));
+
+const StyledDialogTitle = styled(DialogTitle)`
+  padding: 12px 20px;
+  height: 20px;
+  font-size: 14px;
+  font-weight: 600;
+  display: flex;
+  align-items: center;
+  justify-content: space-between;
+`;
+
+const NameListWrapper = styled(Stack)`
+  overflow-y: auto;
+  gap: 12px;
+`;
+
+const StyledBox = styled(Box)`
+  width: 161.67px;
+`;
+
+const StyledDialogContent = styled(DialogContent)`
+  display: flex;
+  flex-direction: column;
+  gap: 12px;
+  padding: 20px 12px 20px 20px;
+`;
+
+const StyledRangesHeader = styled(Stack)(({ theme }) => ({
+  flexDirection: "row",
+  padding: "0 8px",
+  gap: "12px",
+  fontFamily: theme.typography.fontFamily,
+  fontSize: "12px",
+  fontWeight: "700",
+  color: theme.palette.info.main,
+}));
+
+const StyledDialogActions = styled(DialogActions)`
+  padding: 12px 20px;
+  height: 40px;
+  display: flex;
+  align-items: center;
+  justify-content: space-between;
+  font-size: 12px;
+  color: #757575;
+`;
+
+const UploadFooterLink = styled("a")`
+  font-size: 12px;
+  font-weight: 400;
+  font-family: "Inter";
+  color: #757575;
+  text-decoration: none;
+  &:hover {
+    text-decoration: underline;
+  }
+`;
+
+export default NameManagerDialog;

webapp/src/components/NameManagerDialog/NamedRangeActive.tsx

@@ -0,0 +1,154 @@
+import type { WorksheetProperties } from "@ironcalc/wasm";
+import {
+  Box,
+  Divider,
+  IconButton,
+  MenuItem,
+  TextField,
+  styled,
+} from "@mui/material";
+import { t } from "i18next";
+import { Check, X } from "lucide-react";
+import { useState } from "react";
+import { theme } from "../../theme";
+
+interface NamedRangeProperties {
+  worksheets: WorksheetProperties[];
+  name: string;
+  scope: string;
+  formula: string;
+  onSave: (name: string, scope: string, formula: string) => string | undefined;
+  onCancel: () => void;
+}
+
+function NamedRangeActive(properties: NamedRangeProperties) {
+  const { worksheets, onSave, onCancel } = properties;
+  const [name, setName] = useState(properties.name);
+  const [scope, setScope] = useState(properties.scope);
+  const [formula, setFormula] = useState(properties.formula);
+
+  const [formulaError, setFormulaError] = useState(false);
+
+  return (
+    <>
+      <StyledBox>
+        <StyledTextField
+          id="name"
+          variant="outlined"
+          size="small"
+          margin="none"
+          fullWidth
+          error={formulaError}
+          value={name}
+          onChange={(event) => setName(event.target.value)}
+          onKeyDown={(event) => {
+            event.stopPropagation();
+          }}
+          onClick={(event) => event.stopPropagation()}
+        />
+        <StyledTextField
+          id="scope"
+          variant="outlined"
+          select
+          size="small"
+          margin="none"
+          fullWidth
+          error={formulaError}
+          value={scope}
+          onChange={(event) => {
+            setScope(event.target.value);
+          }}
+        >
+          <MenuItem value={"[global]"}>
+            <MenuSpan>{t("name_manager_dialog.workbook")}</MenuSpan>
+            <MenuSpanGrey>{` ${t("name_manager_dialog.global")}`}</MenuSpanGrey>
+          </MenuItem>
+          {worksheets.map((option) => (
+            <MenuItem key={option.name} value={option.name}>
+              <MenuSpan>{option.name}</MenuSpan>
+            </MenuItem>
+          ))}
+        </StyledTextField>
+        <StyledTextField
+          id="formula"
+          variant="outlined"
+          size="small"
+          margin="none"
+          fullWidth
+          error={formulaError}
+          value={formula}
+          onChange={(event) => setFormula(event.target.value)}
+          onKeyDown={(event) => {
+            event.stopPropagation();
+          }}
+          onClick={(event) => event.stopPropagation()}
+        />
+        <IconsWrapper>
+          <IconButton
+            onClick={() => {
+              const error = onSave(name, scope, formula);
+              if (error) {
+                setFormulaError(true);
+              }
+            }}
+          >
+            <StyledCheck size={12} />
+          </IconButton>
+          <StyledIconButton onClick={onCancel}>
+            <X size={12} />
+          </StyledIconButton>
+        </IconsWrapper>
+      </StyledBox>
+      <Divider />
+    </>
+  );
+}
+
+const MenuSpan = styled("span")`
+  font-size: 12px;
+  font-family: "Inter";
+`;
+
+const MenuSpanGrey = styled("span")`
+  white-space: pre;
+  font-size: 12px;
+  font-family: "Inter";
+  color: ${theme.palette.grey[400]};
+`;
+
+const StyledBox = styled(Box)`
+  display: flex;
+  gap: 12px;
+  width: 577px;
+`;
+
+const StyledTextField = styled(TextField)(() => ({
+  "& .MuiInputBase-root": {
+    height: "28px",
+    width: "161.67px",
+    margin: 0,
+    fontFamily: "Inter",
+    fontSize: "12px",
+  },
+  "& .MuiInputBase-input": {
+    padding: "8px",
+  },
+}));
+
+const StyledIconButton = styled(IconButton)(({ theme }) => ({
+  color: theme.palette.error.main,
+  "&.Mui-disabled": {
+    opacity: 0.6,
+    color: theme.palette.error.light,
+  },
+}));
+
+const StyledCheck = styled(Check)(({ theme }) => ({
+  color: theme.palette.success.main,
+}));
+
+const IconsWrapper = styled(Box)({
+  display: "flex",
+});
+
+export default NamedRangeActive;

webapp/src/components/NameManagerDialog/NamedRangeInactive.tsx

@@ -0,0 +1,76 @@
+import { Box, Divider, IconButton, styled } from "@mui/material";
+import { t } from "i18next";
+import { PencilLine, Trash2 } from "lucide-react";
+
+interface NamedRangeInactiveProperties {
+  name: string;
+  scope: string;
+  formula: string;
+  onDelete: () => void;
+  onEdit: () => void;
+  showOptions: boolean;
+}
+
+function NamedRangeInactive(properties: NamedRangeInactiveProperties) {
+  const { name, scope, formula, onDelete, onEdit, showOptions } = properties;
+
+  const scopeName =
+    scope === "[global]"
+      ? `${t("name_manager_dialog.workbook")} ${t(
+          "name_manager_dialog.global",
+        )}`
+      : scope;
+
+  return (
+    <>
+      <WrappedLine>
+        <StyledDiv>{name}</StyledDiv>
+        <StyledDiv>{scopeName}</StyledDiv>
+        <StyledDiv>{formula}</StyledDiv>
+        <IconsWrapper>
+          <StyledIconButtonBlack onClick={onEdit} disabled={!showOptions}>
+            <PencilLine size={12} />
+          </StyledIconButtonBlack>
+          <StyledIconButtonRed onClick={onDelete} disabled={!showOptions}>
+            <Trash2 size={12} />
+          </StyledIconButtonRed>
+        </IconsWrapper>
+      </WrappedLine>
+      <Divider />
+    </>
+  );
+}
+
+const StyledIconButtonBlack = styled(IconButton)(({ theme }) => ({
+  color: theme.palette.common.black,
+}));
+
+const StyledIconButtonRed = styled(IconButton)(({ theme }) => ({
+  color: theme.palette.error.main,
+  "&.Mui-disabled": {
+    opacity: 0.6,
+    color: theme.palette.error.light,
+  },
+}));
+
+const WrappedLine = styled(Box)({
+  display: "flex",
+  height: "28px",
+  alignItems: "center",
+  gap: "12px",
+});
+
+const StyledDiv = styled("div")(({ theme }) => ({
+  fontFamily: theme.typography.fontFamily,
+  fontSize: "12px",
+  fontWeight: "400",
+  color: theme.palette.common.black,
+  width: "153.67px",
+  paddingLeft: "8px",
+}));
+
+const IconsWrapper = styled(Box)({
+  display: "flex",
+});
+
+export default NamedRangeInactive;

webapp/src/components/NameManagerDialog/index.ts

@@ -0,0 +1 @@
+export { default } from "./NameManagerDialog";

webapp/src/components/SheetTabBar/SheetListMenu.tsx

@@ -59,7 +59,10 @@ const SheetListMenu = (properties: SheetListMenuProps) => {
           )}
           {hasColors && <ItemColor style={{ backgroundColor: tab.color }} />}
           <ItemName
-            style={{ fontWeight: index === selectedIndex ? "bold" : "normal" }}
+            style={{
+              fontWeight: index === selectedIndex ? "bold" : "normal",
+              color: tab.state === "visible" ? "#333" : "#888",
+            }}
           >
             {tab.name}
           </ItemName>

webapp/src/components/SheetTabBar/SheetRenameDialog.tsx

@@ -1,4 +1,5 @@
 import { Dialog, TextField, styled } from "@mui/material";
+import { Check, X } from "lucide-react";
 import { useState } from "react";
 import { useTranslation } from "react-i18next";
 import { theme } from "../../theme";
@@ -20,28 +21,13 @@ const SheetRenameDialog = (properties: SheetRenameDialogProps) => {
     <Dialog open={properties.open} onClose={properties.onClose}>
       <StyledDialogTitle>
         {t("sheet_rename.title")}
-        <Cross onClick={handleClose} onKeyDown={() => {}}>
-          <svg
-            width="16"
-            height="16"
-            viewBox="0 0 16 16"
-            fill="none"
-            xmlns="http://www.w3.org/2000/svg"
-          >
-            <title>Close</title>
-            <path
-              d="M12 4.5L4 12.5"
-              stroke="#333333"
-              strokeLinecap="round"
-              strokeLinejoin="round"
-            />
-            <path
-              d="M4 4.5L12 12.5"
-              stroke="#333333"
-              strokeLinecap="round"
-              strokeLinejoin="round"
-            />
-          </svg>
+        <Cross
+          onClick={handleClose}
+          title={t("sheet_rename.close")}
+          tabIndex={-1}
+          onKeyDown={() => {}}
+        >
+          <X />
         </Cross>
       </StyledDialogTitle>
       <StyledDialogContent>
@@ -73,6 +59,9 @@ const SheetRenameDialog = (properties: SheetRenameDialogProps) => {
             properties.onNameChanged(name);
           }}
         >
+          <Check
+            style={{ width: "16px", height: "16px", marginRight: "8px" }}
+          />
           {t("sheet_rename.rename")}
         </StyledButton>
       </DialogFooter>
@@ -94,7 +83,7 @@ const StyledDialogTitle = styled("div")`
 
 const Cross = styled("div")`
   &:hover {
-    background-color: ${theme.palette.grey["100"]};
+    background-color: ${theme.palette.grey["50"]};
   }
   display: flex;
   border-radius: 4px;
@@ -103,6 +92,11 @@ const Cross = styled("div")`
   cursor: pointer;
   align-items: center;
   justify-content: center;
+  svg {
+    width: 16px;
+    height: 16px;
+    stroke-width: 1.5;
+  }
 `;
 
 const StyledDialogContent = styled("div")`

webapp/src/components/SheetTabBar/SheetTab.tsx

@@ -15,8 +15,9 @@ interface SheetTabProps {
   onSelected: () => void;
   onColorChanged: (hex: string) => void;
   onRenamed: (name: string) => void;
-  canDelete: () => boolean;
+  canDelete: boolean;
   onDeleted: () => void;
+  onHideSheet: () => void;
   workbookState: WorkbookState;
 }
 
@@ -94,15 +95,23 @@ function SheetTab(props: SheetTabProps) {
           Change Color
         </StyledMenuItem>
         <StyledMenuItem
-          disabled={!props.canDelete()}
+          disabled={!props.canDelete}
           onClick={() => {
             props.onDeleted();
             handleClose();
           }}
         >
-          {" "}
           Delete
         </StyledMenuItem>
+        <StyledMenuItem
+          disabled={!props.canDelete}
+          onClick={() => {
+            props.onHideSheet();
+            handleClose();
+          }}
+        >
+          Hide sheet
+        </StyledMenuItem>
       </StyledMenu>
       <SheetRenameDialog
         open={renameDialogOpen}

webapp/src/components/SheetTabBar/SheetTabBar.tsx

@@ -19,6 +19,7 @@ export interface SheetTabBarProps {
   onSheetColorChanged: (hex: string) => void;
   onSheetRenamed: (name: string) => void;
   onSheetDeleted: () => void;
+  onHideSheet: () => void;
 }
 
 function SheetTabBar(props: SheetTabBarProps) {
@@ -33,6 +34,18 @@ function SheetTabBar(props: SheetTabBarProps) {
     setAnchorEl(null);
   };
 
+  const nonHidenSheets = sheets
+    .map((s, index) => {
+      return {
+        state: s.state,
+        index,
+        name: s.name,
+        color: s.color,
+        sheetId: s.sheetId,
+      };
+    })
+    .filter((s) => s.state === "visible");
+
   return (
     <Container>
       <LeftButtonsContainer>
@@ -54,25 +67,24 @@ function SheetTabBar(props: SheetTabBarProps) {
       <VerticalDivider />
       <Sheets>
         <SheetInner>
-          {sheets.map((tab, index) => (
+          {nonHidenSheets.map((tab) => (
             <SheetTab
               key={tab.sheetId}
               name={tab.name}
               color={tab.color}
-              selected={index === selectedIndex}
-              onSelected={() => onSheetSelected(index)}
+              selected={tab.index === selectedIndex}
+              onSelected={() => onSheetSelected(tab.index)}
               onColorChanged={(hex: string): void => {
                 props.onSheetColorChanged(hex);
               }}
               onRenamed={(name: string): void => {
                 props.onSheetRenamed(name);
               }}
-              canDelete={(): boolean => {
-                return sheets.length > 1;
-              }}
+              canDelete={nonHidenSheets.length > 1}
               onDeleted={(): void => {
                 props.onSheetDeleted();
               }}
+              onHideSheet={props.onHideSheet}
               workbookState={workbookState}
             />
           ))}

webapp/src/components/SheetTabBar/types.ts

@@ -2,4 +2,5 @@ export interface SheetOptions {
   name: string;
   color: string;
   sheetId: number;
+  state: string;
 }

webapp/src/components/borderPicker.tsx

@@ -62,7 +62,7 @@ const BorderPicker = (properties: BorderPickerProps) => {
   // biome-ignore lint/correctness/useExhaustiveDependencies: We reset the styles, every time we open (or close) the widget
   useEffect(() => {
     setBorderSelected(null);
-    setBorderColor(theme.palette.common.white);
+    setBorderColor(theme.palette.common.black);
     setBorderStyle(BorderStyle.Thin);
   }, [properties.open]);
 

webapp/src/components/formatPicker.tsx

@@ -1,6 +1,6 @@
 import styled from "@emotion/styled";
 import { Dialog, TextField } from "@mui/material";
-import { Check } from "lucide-react";
+import { Check, X } from "lucide-react";
 import { useState } from "react";
 import { useTranslation } from "react-i18next";
 import { theme } from "../theme";
@@ -36,28 +36,8 @@ const FormatPicker = (properties: FormatPickerProps) => {
     >
       <StyledDialogTitle>
         {t("num_fmt.title")}
-        <Cross onClick={handleClose} onKeyDown={() => {}}>
-          <svg
-            width="16"
-            height="16"
-            viewBox="0 0 16 16"
-            fill="none"
-            xmlns="http://www.w3.org/2000/svg"
-          >
-            <title>Close</title>
-            <path
-              d="M12 4.5L4 12.5"
-              stroke="#333333"
-              strokeLinecap="round"
-              strokeLinejoin="round"
-            />
-            <path
-              d="M4 4.5L12 12.5"
-              stroke="#333333"
-              strokeLinecap="round"
-              strokeLinejoin="round"
-            />
-          </svg>
+        <Cross onClick={handleClose} title={t("num_fmt.close")}>
+          <X />
         </Cross>
       </StyledDialogTitle>
 
@@ -101,7 +81,7 @@ const StyledDialogTitle = styled("div")`
 
 const Cross = styled("div")`
   &:hover {
-    background-color: ${theme.palette.grey["100"]};
+    background-color: ${theme.palette.grey["50"]};
   }
   display: flex;
   border-radius: 4px;
@@ -110,6 +90,11 @@ const Cross = styled("div")`
   cursor: pointer;
   align-items: center;
   justify-content: center;
+  svg {
+    width: 16px;
+    height: 16px;
+    stroke-width: 1.5;
+  }
 `;
 
 const StyledDialogContent = styled("div")`

webapp/src/components/tests/util.test.ts

@@ -1,6 +1,8 @@
+import { readFile } from "node:fs/promises";
+import { type SelectedView, initSync } from "@ironcalc/wasm";
 import { expect, test } from "vitest";
 import { decreaseDecimalPlaces, increaseDecimalPlaces } from "../formatUtil";
-import { isNavigationKey } from "../util";
+import { getFullRangeToString, isNavigationKey } from "../util";
 
 test("checks arrow left is a navigation key", () => {
   expect(isNavigationKey("ArrowLeft")).toBe(true);
@@ -24,3 +26,22 @@ test("decrease decimals", () => {
     'dddd"," mmmm dd"," yyyy',
   );
 });
+
+test("format range to get the full formula", async () => {
+  const buffer = await readFile("node_modules/@ironcalc/wasm/wasm_bg.wasm");
+  initSync(buffer);
+
+  const selectedView: SelectedView = {
+    sheet: 0,
+    row: 1,
+    column: 8,
+    range: [1, 8, 1, 8],
+    top_row: 1,
+    left_column: 8,
+  };
+  const worksheetNames = ["Sheet1", "Notes"];
+
+  expect(getFullRangeToString(selectedView, worksheetNames)).toBe(
+    "Sheet1!$H$1",
+  );
+});

webapp/src/components/toolbar.tsx

@@ -22,6 +22,7 @@ import {
   Percent,
   Redo2,
   Strikethrough,
+  Tags,
   Type,
   Underline,
   Undo2,
@@ -34,6 +35,8 @@ import {
   DecimalPlacesIncreaseIcon,
 } from "../icons";
 import { theme } from "../theme";
+import NameManagerDialog from "./NameManagerDialog";
+import type { NameManagerProperties } from "./NameManagerDialog/NameManagerDialog";
 import BorderPicker from "./borderPicker";
 import ColorPicker from "./colorPicker";
 import { TOOLBAR_HEIGHT } from "./constants";
@@ -72,12 +75,14 @@ type ToolbarProperties = {
   numFmt: string;
   showGridLines: boolean;
   onToggleShowGridLines: (show: boolean) => void;
+  nameManagerProperties: NameManagerProperties;
 };
 
 function Toolbar(properties: ToolbarProperties) {
   const [fontColorPickerOpen, setFontColorPickerOpen] = useState(false);
   const [fillColorPickerOpen, setFillColorPickerOpen] = useState(false);
   const [borderPickerOpen, setBorderPickerOpen] = useState(false);
+  const [nameManagerDialogOpen, setNameManagerDialogOpen] = useState(false);
 
   const fontColorButton = useRef(null);
   const fillColorButton = useRef(null);
@@ -340,6 +345,18 @@ function Toolbar(properties: ToolbarProperties) {
       >
         {properties.showGridLines ? <Grid2x2Check /> : <Grid2x2X />}
       </StyledButton>
+      <Divider />
+      <StyledButton
+        type="button"
+        $pressed={false}
+        onClick={() => {
+          setNameManagerDialogOpen(true);
+        }}
+        disabled={!canEdit}
+        title={t("toolbar.name_manager")}
+      >
+        <Tags />
+      </StyledButton>
 
       <ColorPicker
         color={properties.fontColor}
@@ -375,6 +392,13 @@ function Toolbar(properties: ToolbarProperties) {
         anchorEl={borderButton}
         open={borderPickerOpen}
       />
+      <NameManagerDialog
+        open={nameManagerDialogOpen}
+        onClose={() => {
+          setNameManagerDialogOpen(false);
+        }}
+        model={properties.nameManagerProperties}
+      />
     </ToolbarContainer>
   );
 }

webapp/src/components/util.ts

@@ -1,6 +1,6 @@
 import type { Area, Cell } from "./types";
 
-import { columnNameFromNumber } from "@ironcalc/wasm";
+import { type SelectedView, columnNameFromNumber } from "@ironcalc/wasm";
 
 /**
  *  Returns true if the keypress should start editing
@@ -61,3 +61,20 @@ export function rangeToStr(
     columnStart,
   )}${rowStart}:${columnNameFromNumber(columnEnd)}${rowEnd}`;
 }
+
+// Returns the full range of the selected view as a string in absolute form
+// e.g. 'Sheet1!$A$1:$B$2' or 'Sheet1!$A$1'
+export function getFullRangeToString(
+  selectedView: SelectedView,
+  worksheetNames: string[],
+): string {
+  const [rowStart, columnStart, rowEnd, columnEnd] = selectedView.range;
+  const sheetName = `${worksheetNames[selectedView.sheet]}`;
+
+  if (rowStart === rowEnd && columnStart === columnEnd) {
+    return `${sheetName}!$${columnNameFromNumber(columnStart)}$${rowStart}`;
+  }
+  return `${sheetName}!$${columnNameFromNumber(
+    columnStart,
+  )}$${rowStart}:$${columnNameFromNumber(columnEnd)}$${rowEnd}`;
+}

webapp/src/components/workbook.tsx

@@ -5,7 +5,9 @@ import type {
   WorksheetProperties,
 } from "@ironcalc/wasm";
 import { styled } from "@mui/material/styles";
+import { PaintRoller } from "lucide-react";
 import { useCallback, useEffect, useRef, useState } from "react";
+import ReactDOMServer from "react-dom/server";
 import SheetTabBar from "./SheetTabBar/SheetTabBar";
 import {
   COLUMN_WIDTH_SCALE,
@@ -19,7 +21,11 @@ import {
 import FormulaBar from "./formulabar";
 import Toolbar from "./toolbar";
 import useKeyboardNavigation from "./useKeyboardNavigation";
-import { type NavigationKey, getCellAddress } from "./util";
+import {
+  type NavigationKey,
+  getCellAddress,
+  getFullRangeToString,
+} from "./util";
 import type { WorkbookState } from "./workbookState";
 import Worksheet from "./worksheet";
 
@@ -30,11 +36,13 @@ const Workbook = (props: { model: Model; workbookState: WorkbookState }) => {
   // Calling `setRedrawId((id) => id + 1);` forces a redraw
   // This is needed because `model` or `workbookState` can change without React being aware of it
   const setRedrawId = useState(0)[1];
-  const info = model
-    .getWorksheetsProperties()
-    .map(({ name, color, sheet_id }: WorksheetProperties) => {
-      return { name, color: color ? color : "#FFF", sheetId: sheet_id };
-    });
+
+  const worksheets = model.getWorksheetsProperties();
+  const info = worksheets.map(
+    ({ name, color, sheet_id, state }: WorksheetProperties) => {
+      return { name, color: color ? color : "#FFF", sheetId: sheet_id, state };
+    },
+  );
   const focusWorkbook = useCallback(() => {
     if (rootRef.current) {
       rootRef.current.focus();
@@ -136,7 +144,15 @@ const Workbook = (props: { model: Model; workbookState: WorkbookState }) => {
     const el = rootRef.current?.getElementsByClassName("sheet-container")[0];
     if (el) {
       (el as HTMLElement).style.cursor =
-        `url('data:image/svg+xml;utf8,<svg data-v-56bd7dfc="" xmlns="http://www.w3.org/2000/svg" width="24" height="24" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round" class="lucide lucide-paintbrush-vertical"><path d="M10 2v2"></path><path d="M14 2v4"></path><path d="M17 2a1 1 0 0 1 1 1v9H6V3a1 1 0 0 1 1-1z"></path><path d="M6 12a1 1 0 0 0-1 1v1a2 2 0 0 0 2 2h2a1 1 0 0 1 1 1v2.9a2 2 0 1 0 4 0V17a1 1 0 0 1 1-1h2a2 2 0 0 0 2-2v-1a1 1 0 0 0-1-1"></path></svg>'), auto`;
+        `url('data:image/svg+xml;utf8,${encodeURIComponent(
+          ReactDOMServer.renderToString(
+            <PaintRoller
+              width={24}
+              height={24}
+              style={{ transform: "rotate(-8deg)" }}
+            />,
+          ),
+        )}'), auto`;
     }
   };
 
@@ -559,6 +575,38 @@ const Workbook = (props: { model: Model; workbookState: WorkbookState }) => {
           model.setShowGridLines(sheet, show);
           setRedrawId((id) => id + 1);
         }}
+        nameManagerProperties={{
+          newDefinedName: (
+            name: string,
+            scope: number | undefined,
+            formula: string,
+          ) => {
+            model.newDefinedName(name, scope, formula);
+            setRedrawId((id) => id + 1);
+          },
+          updateDefinedName: (
+            name: string,
+            scope: number | undefined,
+            newName: string,
+            newScope: number | undefined,
+            newFormula: string,
+          ) => {
+            model.updateDefinedName(name, scope, newName, newScope, newFormula);
+            setRedrawId((id) => id + 1);
+          },
+          deleteDefinedName: (name: string, scope: number | undefined) => {
+            model.deleteDefinedName(name, scope);
+            setRedrawId((id) => id + 1);
+          },
+          selectedArea: () => {
+            const worksheetNames = worksheets.map((s) => s.name);
+            const selectedView = model.getSelectedView();
+
+            return getFullRangeToString(selectedView, worksheetNames);
+          },
+          worksheets,
+          definedNameList: model.getDefinedNameList(),
+        }}
       />
       <FormulaBar
         cellAddress={cellAddress()}
@@ -586,6 +634,9 @@ const Workbook = (props: { model: Model; workbookState: WorkbookState }) => {
         selectedIndex={model.getSelectedSheet()}
         workbookState={workbookState}
         onSheetSelected={(sheet: number): void => {
+          if (info[sheet].state !== "visible") {
+            model.unhideSheet(sheet);
+          }
           model.setSelectedSheet(sheet);
           setRedrawId((value) => value + 1);
         }}
@@ -616,6 +667,11 @@ const Workbook = (props: { model: Model; workbookState: WorkbookState }) => {
           model.deleteSheet(selectedSheet);
           setRedrawId((value) => value + 1);
         }}
+        onHideSheet={(): void => {
+          const selectedSheet = model.getSelectedSheet();
+          model.hideSheet(selectedSheet);
+          setRedrawId((value) => value + 1);
+        }}
       />
     </Container>
   );

webapp/src/locale/en_us.json

@@ -18,6 +18,7 @@
     "decimal_places_increase": "Increase decimal places",
     "decimal_places_decrease": "Decrease decimal places",
     "show_hide_grid_lines": "Show/hide grid lines",
+    "name_manager": "Name manager",
     "vertical_align_bottom": "Align bottom",
     "vertical_align_middle": " Align middle",
     "vertical_align_top": "Align top",
@@ -58,12 +59,14 @@
   "num_fmt": {
     "title": "Custom number format",
     "label": "Number format",
+    "close": "Close dialog",
     "save": "Save"
   },
   "sheet_rename": {
     "rename": "Save",
     "label": "New name",
-    "title": "Rename Sheet"
+    "title": "Rename Sheet",
+    "close": "Close dialog"
   },
   "formula_input": {
     "update": "Update",
@@ -73,5 +76,15 @@
   "navigation": {
     "add_sheet": "Add sheet",
     "sheet_list": "Sheet list"
+  },
+  "name_manager_dialog": {
+    "title": "Named Ranges",
+    "name": "Name",
+    "range": "Scope",
+    "scope": "Range",
+    "help": "Learn more about Named Ranges",
+    "new": "Add new",
+    "workbook": "Workbook",
+    "global": "(Global)"
   }
 }

xlsx/src/export/worksheets.rs

@@ -192,9 +192,10 @@ pub(crate) fn get_worksheet_xml(
                         &parsed_formulas[*f as usize],
                     );
                     let style = get_cell_style_attribute(*s);
+                    let escaped_v = escape_xml(v);
 
                     row_data_str.push(format!(
-                        "<c r=\"{cell_name}\" t=\"str\"{style}><f>{formula}</f><v>{v}</v></c>"
+                        "<c r=\"{cell_name}\" t=\"str\"{style}><f>{formula}</f><v>{escaped_v}</v></c>"
                     ));
                 }
                 Cell::CellFormulaError {

xlsx/src/import/worksheets.rs

@@ -41,6 +41,30 @@ pub(crate) struct Relationship {
     pub(crate) rel_type: String,
 }
 
+impl WorkbookXML {
+    fn get_defined_names_with_scope(&self) -> Vec<(String, Option<u32>)> {
+        let sheet_id_index: Vec<u32> = self.worksheets.iter().map(|s| s.sheet_id).collect();
+
+        let defined_names = self
+            .defined_names
+            .iter()
+            .map(|dn| {
+                let index = dn
+                    .sheet_id
+                    .and_then(|sheet_id| {
+                        // returns an Option<usize>
+                        sheet_id_index.iter().position(|&x| x == sheet_id)
+                    })
+                    // convert Option<usize> to Option<u32>
+                    .map(|pos| pos as u32);
+
+                (dn.name.clone(), index)
+            })
+            .collect::<Vec<_>>();
+        defined_names
+    }
+}
+
 fn get_column_from_ref(s: &str) -> String {
     let cs = s.chars();
     let mut column = Vec::<char>::new();
@@ -280,11 +304,12 @@ fn from_a1_to_rc(
     worksheets: &[String],
     context: String,
     tables: HashMap<String, Table>,
+    defined_names: Vec<(String, Option<u32>)>,
 ) -> Result<String, XlsxError> {
-    let mut parser = Parser::new(worksheets.to_owned(), tables);
+    let mut parser = Parser::new(worksheets.to_owned(), defined_names, tables);
     let cell_reference =
         parse_reference(&context).map_err(|error| XlsxError::Xml(error.to_string()))?;
-    let t = parser.parse(&formula, &Some(cell_reference));
+    let t = parser.parse(&formula, &cell_reference);
     Ok(to_rc_format(&t))
 }
 
@@ -681,6 +706,7 @@ pub(super) fn load_sheet<R: Read + std::io::Seek>(
     worksheets: &[String],
     tables: &HashMap<String, Table>,
     shared_strings: &mut Vec<String>,
+    defined_names: Vec<(String, Option<u32>)>,
 ) -> Result<(Worksheet, bool), XlsxError> {
     let sheet_name = &settings.name;
     let sheet_id = settings.id;
@@ -855,8 +881,13 @@ pub(super) fn load_sheet<R: Read + std::io::Seek>(
                                 // It's the mother cell. We do not use the ref attribute in IronCalc
                                 let formula = fs[0].text().unwrap_or("").to_string();
                                 let context = format!("{}!{}", sheet_name, cell_ref);
-                                let formula =
-                                    from_a1_to_rc(formula, worksheets, context, tables.clone())?;
+                                let formula = from_a1_to_rc(
+                                    formula,
+                                    worksheets,
+                                    context,
+                                    tables.clone(),
+                                    defined_names.clone(),
+                                )?;
                                 match index_map.get(&si) {
                                     Some(index) => {
                                         // The index for that formula already exists meaning we bumped into a daughter cell first
@@ -910,7 +941,13 @@ pub(super) fn load_sheet<R: Read + std::io::Seek>(
                         // Its a cell with a simple formula
                         let formula = fs[0].text().unwrap_or("").to_string();
                         let context = format!("{}!{}", sheet_name, cell_ref);
-                        let formula = from_a1_to_rc(formula, worksheets, context, tables.clone())?;
+                        let formula = from_a1_to_rc(
+                            formula,
+                            worksheets,
+                            context,
+                            tables.clone(),
+                            defined_names.clone(),
+                        )?;
 
                         match get_formula_index(&formula, &shared_formulas) {
                             Some(index) => formula_index = index,
@@ -1023,6 +1060,9 @@ pub(super) fn load_sheets<R: Read + std::io::Seek>(
     let mut sheets = Vec::new();
     let mut selected_sheet = 0;
     let mut sheet_index = 0;
+
+    let defined_names = workbook.get_defined_names_with_scope();
+
     for sheet in &workbook.worksheets {
         let sheet_name = &sheet.name;
         let rel_id = &sheet.id;
@@ -1044,8 +1084,15 @@ pub(super) fn load_sheets<R: Read + std::io::Seek>(
                     .ok_or_else(|| XlsxError::Xml("Corrupt XML structure".to_string()))?
                     .to_vec(),
             };
-            let (s, is_selected) =
-                load_sheet(archive, &path, settings, worksheets, tables, shared_strings)?;
+            let (s, is_selected) = load_sheet(
+                archive,
+                &path,
+                settings,
+                worksheets,
+                tables,
+                shared_strings,
+                defined_names.clone(),
+            )?;
             if is_selected {
                 selected_sheet = sheet_index;
             }

xlsx/tests/test.rs

@@ -465,11 +465,13 @@ fn test_documentation_xlsx() {
         .unwrap();
     entries.sort();
     // We can't test volatiles
-    let skip = ["DATE.xlsx", "DAY.xlsx", "MONTH.xlsx", "YEAR.xlsx"];
-    let skip = skip.map(|s| format!("tests/docs/{s}"));
+    let mut skip = vec!["DATE.xlsx", "DAY.xlsx", "MONTH.xlsx", "YEAR.xlsx"];
+    // Numerically unstable
+    skip.push("TAN.xlsx");
+    let skip: Vec<String> = skip.iter().map(|s| format!("tests/docs/{s}")).collect();
     println!("{:?}", skip);
     // dumb counter to make sure we are actually testing the files
-    assert_eq!(entries.len(), 7);
+    assert!(entries.len() > 7);
     let temp_folder = env::temp_dir();
     let path = format!("{}", Uuid::new_v4());
     let dir = temp_folder.join(path);