Components are mostly used in workbook apps to automate the workbook. A workbook app can define any number of components, each controlling its own (arbitrarily defined) part of the workbook.
Components have the following characteristics:
- They can accept dependencies via dependency injection.
- They are data bound to a data context (usually a
- Public properties of a component can be data-bound to cells in Excel via the
[Bind]attribute as well as to tables via the
- The methods of the component react to events coming in from Excel (e.g. click of a button). This is configured using the
For example, the following component reads a
searchText parameter from a cell in Excel. Each time this cell's value changes, the component updates a table called
People (by putting a star next to each name that contains the searchText), and writes a message into a cell named
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44
Here is the resulting behavior:
The important thing to note is that no part of the code interacts with Excel directly. The component only accesses its own properties, and the binding infrastructure takes care of communicating with Excel.
Bindings allow you to read and write values from Excel without having to access Excel objects directly or subscribe to their events to listen for changes.
Bindings and the data context
It's important to note that components aren't bound to Excel directly; they don't actually know anything about Excel. Instead, they are bound to a data context. For workbook applications, the default data context happens to be a
WorkbookDataContext which exposes data and events from the workbook. For more information about the data context, customizing it and replacing it, click here.
Binding to cells
Component properties can be bound to cells in Excel by using the
By default, bindings are bi-directional. When the user changes the value of a cell, any property that's bound to the cell also gets updated to the new value.
On the other hand, when a component changes the value of one of its properties,
any cells that are bound to that property also get updated to the new value. For the binding infrastructure to detect the change, however, the component must raise the
PropertyChanged event by calling the
OnPropertyChanged method. This is typically done in the setter of the property, for example:
1 2 3 4 5 6 7 8 9 10 11 12
Having to call
OnPropertyChangedin the setter unfortunately means we can't use auto-implemented properties, but we can make typing this boiler-plate code easier in two ways:
- by using the
propnsnippet which scaffolds a property with the
OnPropertyChangedcall in the setter
- by using the "convert to full property" code fixer (Ctrl+.) on the auto-implemented property, in which case we only need to add the
Also, if changes only need to flow from Excel into the component, and not in both directions, having an auto-implemented property is sufficient.
Binding to tables
A component's property can also bind to an Excel table by applying the
Once we define the property, we can use it to read and write data in the table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
It's important to note that any changes made to the table need to be explicitly saved by calling
SaveChanges(). In C# scripts,
SaveChanges() is called automatically after each run, but in components this call needs to be explicit.
Strongly-typed table access
At this point, you might be wondering where the
PeopleTable class in our example came from. The answer is: it was generated automatically. Each time the structure of the tables in the workbook is changed, the project dynamically generates a dll with types that offer strongly typed access to the tables. This dll is then added to the
lib folder of the project.
The data-binding mechanism then uses the types in this dll to wrap the Excel tables and set the data bound properties. For more information about the data context and strongly typed access to tables, see the data context section.
Components can react to events coming in from the workbook by defining
public void methods marked with the
1 2 3 4 5
EventHandlerattributes can be applied to a method to allow it to handle multiple events.
Currently, workbooks can emit the following events:
- ActiveX button was clicked
- Range value has changed
- An event was sent from VBA
WorkbookDataContextemits the events, not the workbook.
Handling button click events
When a method needs to handle the click of an ActiveX button, the following syntax should be used for the event name:
For example, to handle the click of an ActiveX button named
MyButton located on a sheet named
Sheet1, the method should be decorated as follows:
1 2 3 4 5
ActiveX buttons have several issues (occasionally require double-clicking, visual artifacts when using multiple screens), so it is advisable to use regular Excel buttons and VBA instead (as described below).
Handling range value changes
When a method needs to be called every time a range changes, the name of the range should be used as the event name:
1 2 3 4 5
Sending and handling events from VBA
Events can be sent from VBA code into the workbook app. One particular reason this might be useful is that it allows using regular buttons to send events, instead of ActiveX buttons which have known issues in Excel.
To send an event from VBA, use the
QueryStorm.Runtime.API class, like so:
1 2 3
Instances of the
QueryStorm.Runtime.API class are lightweight objects that forward messages to the QueryStorm Runtime. They carry no state and do not need to be cached.
Handling the event is simple:
1 2 3 4 5