ThisExcel and ThisWorkbook are static classes which provide a convenient API for getting and setting Excel range values as well as subscribing to Excel "cell changed" events.

The methods in ThisExcel take a workbook as a paramater so can be used with any open workbook.

ThisWorkbook works with Excel ActiveWorkbook if any.

Note

You can get a reference to Excel ActiveWorkbook by calling FCell.ManagedXll.StateManager.getActiveWorkbook().

FCell.ManagedXll.StateManager.getCurrentApplication() will return current Excel Application object.

Note

This API uses Excel COM and should not be used in UDFs.

The API will accept Excel ranges in 3 different ways:

  • "A1" style, e.g. range in sheet "Sheet1" with A1 reference "A1:B3"

  • R1C1 style, e.g. range in sheet "Sheet2" with range first row = 1, first column = 1, last row = 3, last column = 3 (same as "A1:C3")

  • Excel name, e.g. "interestRate" as defined in Excel

To get values from Excel ranges you use the overloaded TryGetValue<'T>. This method returns Option<'T> and uses the same type conversion system to convert from Excel data into 'T as FCell UDFs.

If no converter is found or data cannot be converted then None is returned.

You can optionally specify conversion assembly with custom type converters.

Here are all available overloads of TryGetValue:

  • ThisExcel.TryGetValue<'T>(wbk : Workbook, sheetName : string, rangeA1Ref : string) : 'T option

  • ThisExcel.TryGetValue<'T>(wbk : Workbook, sheetName : string, rangeA1Ref : string, converterAssembly : Assembly) : 'T option

  • ThisExcel.TryGetValue<'T>(wbk : Workbook, sheetName : string, firstRow : int, firstCol : int, lastRow : int, lastCol : int) : 'T option

  • ThisExcel.TryGetValue<'T>(wbk : Workbook, sheetName : string, firstRow : int, firstCol : int, lastRow : int, lastCol : int, converterAssembly : Assembly) : 'T option

  • ThisExcel.TryGetValue<'T>(wbk : Workbook, xlName : string) : 'T option

  • ThisExcel.TryGetValue<'T>(wbk : Workbook, xlName : string, converterAssembly : Assembly) : 'T option

  • ThisWorkbook.TryGetValue<'T>(sheetName : string, rangeA1Ref : string) : 'T option

  • ThisWorkbook.TryGetValue<'T>(sheetName : string, rangeA1Ref : string, converterAssembly : Assembly) : 'T option

  • ThisWorkbook.TryGetValue<'T>(sheetName : string, firstRow : int, firstCol : int, lastRow : int, lastCol : int) : 'T option

  • ThisWorkbook.TryGetValue<'T>(sheetName : string, firstRow : int, firstCol : int, lastRow : int, lastCol : int, converterAssembly : Assembly) : 'T option

  • ThisWorkbook.TryGetValue<'T>(xlName : string) : 'T option

  • ThisWorkbook.TryGetValue<'T>(xlName : string, converterAssembly : Assembly) : 'T option

Note

TryGetValue should always be called on Excel main UI thread.

You can get Excel UI synchronization context with FCell.ManagedXll.StateManager.getUIContext()

To set values in Excel ranges you use the overloaded SetValue<'T> method. It uses the same type conversion system to convert from 'T into Excel data as FCell UDFs.

If no converter is found or data cannot be converted then error is returned to Excel.

You can optionally specify conversion assembly with custom type converters.

Here are all available overloads of SetValue:

  • ThisExcel.SetValue<'T>(wbk : Workbook, x : 'T, sheetName : string, rangeA1Ref : string)

  • ThisExcel.SetValue<'T>(wbk : Workbook, x : 'T, sheetName : string, rangeA1Ref : string, converterAssembly : Assembly)

  • ThisExcel.SetValue<'T>(wbk : Workbook, x : 'T, sheetName : string, firstRow : int, firstCol : int, lastRow : int, lastCol : int)

  • ThisExcel.SetValue<'T>(wbk : Workbook, x : 'T, sheetName : string, firstRow : int, firstCol : int, lastRow : int, lastCol : int, converterAssembly : Assembly)

  • ThisExcel.SetValue<'T>(wbk : Workbook, x : 'T, xlName : string)

  • ThisExcel.SetValue<'T>(wbk : Workbook, x : 'T, xlName : string, converterAssembly : Assembly)

  • ThisWorkbook.SetValue<'T>(x : 'T, sheetName : string, rangeA1Ref : string)

  • ThisWorkbook.SetValue<'T>(x : 'T, sheetName : string, rangeA1Ref : string, converterAssembly : Assembly)

  • ThisWorkbook.SetValue<'T>(x : 'T, sheetName : string, firstRow : int, firstCol : int, lastRow : int, lastCol : int)

  • ThisWorkbook.SetValue<'T>(x : 'T, sheetName : string, firstRow : int, firstCol : int, lastRow : int, lastCol : int, converterAssembly : Assembly)

  • ThisWorkbook.SetValue<'T>(x : 'T, xlName : string)

  • ThisWorkbook.SetValue<'T>(x : 'T, xlName : string, converterAssembly : Assembly)

Note

SetValue is thread safe and can be called from non UI thread.

ThisExcel and ThisWorkbook can also subscribe to Excel cell changed event. This is used by FCell Calculation Engine, see details in "Calculation Engine" chapter.