SheetKraft vs VBA

Long term view

VBA is an old technology that stopped evolving 15 years ago. In fact some of the most notable recent developments in Microsoft Office such as Office 365 and Office Store indicate a shift away from VBA. This means that users of VBA cannot look forward to any improvements in the core platform and will need to consider migrating away from VBA over time. SheetKraft technology has been built and tested over the last 3 years and is being actively developed and improved based on an increasing number of real life use-cases. Users of SheetKraft can look forward to more capabilities, better usability of existing capabilities, and new tools for diagnostics being built at a rapid pace over the next few years. Level of abstraction

SheetKraft functions can be thought of as a comprehensive library of generic, reusable VBA functions. The functions are configurable via a user interface, and are represented via formulas directly in the cells of the spreadsheet. Using simple range references, these formulas can be used to compose complex chains of calculations and import and export operations. In addition, the SheetKraft calculation engine can be thought of as a master-macro that knows how to determine the dependencies between all the individual formulas and execute them in the correct sequence. In programming terms, SheetKraft is a declarative language in contrast to VBA which is an imperative language. In other words, SheetKraft functions are about "what"; VBA macros are about "how". For example, a SheetKraft formula to filter rows will merely specify the input range and the criterion. A VBA macro will involve a for loop. This declarative nature enables SheetKraft to provide user interfaces and visualizations (flow chart) of the logic. SheetKraft thus operates at a much higher level of abstraction, enabling users to think and work in terms of the business problem, and freeing them from worrying about syntax, variables, arrays, etc. Low level programming errors (such as incorrect loops) are entirely eliminated with SheetKraft. As SheetKraft continues to improve, the performance and usability of existing setups also improves with no further effort from end users. For example, a performance optimization in the "Filter" functionality in SheetKraft will apply to every use of Filter. In contrast, every VBA macro with a loop for filtering data will need to be rewritten with the same optimization to get the benefit of the optimization.

Integration with Excel and robustness to changes

Despite being an addin, SheetKraft formulas integrate much more directly with the spreadsheet than VBA macros. Since the formulas are in the spreadsheet itself, changes to the spreadsheet structure such as inserting or deleting rows or columns, cutting and pasting cells are handled correctly by Excel. With VBA macros, any change to structure poses a significant risk that some macro will get invalidated. A lot of experience, competence, and discipline is required to write macros that are robust to changes. This essentially "comes for free" with SheetKraft. Also, Excel features like "Trace Precedents", "Trace Dependents" work well with SheetKraft formulas but provide no help with references to cells from VBA code.

User Interface

While Excel's macro recorder can help create a first version of a macro, no further help is available to edit it in future. SheetKraft formulas can be reopened in a user interface to make changes or even to just understand the logic.

Execution Environment

VBA macros require Excel to run. SheetKraft's calculation engine is independent of Excel and runs both within Excel and on a server. This frees the calculation logic from artificial data size restrictions imposed by Excel. The SheetKraft website allows users to run the logic from a browser interface on their desktop or a mobile device with no dependency on Excel whatsoever. The web user interface is auto-generated by specifying the input cells / ranges of Excel workbooks. The web interface makes it much more convenient to run the logic compared to changing values of cells directly. Additionally, a record of the inputs and outputs is automatically maintained in a database providing a robust trail of user activity.