Differences between VBA development for MS Excel compared to JavaScript for P7-Office

The first chapter of the free course Development Basics for P7 is published. Additional materials can be found right here

Features of development in MS Office (Excel)

The basic automation tool in the Microsoft Office suite has been the VBA (VisualBasic for Application) scripting language for a long time (since 1996 to the present). Since 2007, VBA has not been licensed for other developers. According to the Wikipedia article on VBA, this language is a simplified subset of the VisualBasic language.

Let's look at some of the features of VBA, which will later form the basis for automation tools in P7:

• Although the VBA language is not fully object-oriented, the developers have implemented an “object model” of both the application itself and the document structure of this application into its structure. The user gets the ability to influence the state of the program and the active document right out of the box using scripts.

• Another huge advantage of MS Office is the ability to record manual user actions (both actions with the program interface itself and actions with the document) in VBA program code, which can then be launched to repeat the sequence of actions. This is the original idea of ​​macros.

• The ability to visually construct a graphical interface between the user and the software product, for which VBA received the word Visual in its name. This allows you to build complex environments for the user to work with a developed logic of work, which can include processes related to other software products within the office package.

• VBA initially uses deep integration with Microsoft system automation software technologies such as ActiveX, COM, and OLE. Since these technologies underlie the VBA object model itself, Microsoft Office, and those software products where VBA is integrated as an automation language, the implementation of these technologies is hidden from the end user. This simplifies working with them and expands the capabilities of user macro forms, allowing the implementation of ready-made third-party solutions in easy ways compared to many other programming languages.

• VBA has a built-in ability to use system or application libraries (dll), thereby enabling the user to integrate the functions embedded in these libraries into their macros, expanding their capabilities through ready-made APIs, both the Windows operating system and any other APIs registered in the operating system.

• To build complex macros or edit recorded ones, the application's integrated development environment (IDE) is used. The IDE is outdated, but still provides all the necessary basic capabilities for development and debugging.

In addition to VBA, later versions added automation tools (including integrated ones) in other programming languages ​​(C++, C#), such as VSTA (Visual Studio Tools for Applications). Support for building macros in Python languages ​​was added. In addition, a form of additional functionality of office packages appeared, called an extension (add-in) in JavaScript. The functionality and principles of building extensions are beyond the scope of this course.

A feature of using VBA in Microsoft Office is that macros cannot be used separately, outside of documents. To be used, a macro must be saved as part of a document, and to be used again, it must be opened in the editor.

If additional functionality independent of documents is required, an add-in should be created and installed using Office tools.

Features of development in P7-Office JavaScript

The P7 office suite has two types of built-in automation of user actions: macros and plugins.

Macros are automation scripts integrated into a document and acting only within the framework of this document. Macros in P7, unlike MS Office, cannot record user actions in a script with subsequent playback of such actions. For macros, the built-in editor “macro” plugin is used, which, although limited in functionality compared to full-fledged IDEs, does not require a separate external editor. It has a simple debugger integrated into it, allowing you to set breakpoints and view the values ​​of some variables when stopping, which is useful when developing macros.

Plugins are document-independent sets of files packed as a zip archive (with the .plugin extension) and installed into applications. Plugins can have a separate user interface built using HTML, CSS, and JavaScript. To create them, you can use any program code editor that supports JavaScript, such as Microsoft Visual Studio Code. Debugging of plugins can only be done using the application launch key “–ascdesktop-support-debug-info”. This allows you to call DevTools, which is a debugging environment for Chromium, on the basis of which the P7 editor interface is built, via the editor's context menu after launching the plugin.

The process of creating and debugging macros and plugins will be discussed later in the relevant classes. To create your own automation tools in the P7-Office editor, you need basic knowledge of the JavaScript scripting language, the HTML web markup language, and the CSS formatting language. JavaScript will be covered in a minimal amount during the introductory course. You will have to learn HTML and CSS on your own.

The API for working with the application and documents is provided in a limited volume, not all the possibilities available to the user when working with the editor and documents are available for automation in plugins and macros. Due to the peculiarity of the architecture of the editor itself, the possibilities for interaction between the plugin and the operating system are very limited. For example, it is impossible to save data to an arbitrary file on the hard drive using standard methods or organize interaction between the plugin and external programs without using quasi-proxy servers. All such limitations will be discussed in the relevant classes.

The main differences between MS Office development and P7-Office

The main differences in the development of automation tools in MS Office from R7-Office are as follows:

As can be seen from this table, due to limitations of both the architecture of P7 applications and the current implementation of its API, transferring macros for a number of tasks created in MS Office to P7 is not possible without involving external server extensions. This may lead to partial use of all accumulated automation experience in VBA.

The differences between programming languages ​​(VBA and JavaScript) will require specialists who are well versed in the specifics of programming macros in VBA in MS Office and JavaScript in P7-Office when switching between office packages. However, for most tasks such a process is possible, although with the above-mentioned reservations and limitations. Examples of such transitions will be considered in subsequent lessons.