Gekcel (Excel add-in) > Gekcel basics

The next section (Gekcel guided tour) provides a step by step guided tour of Gekcel and how to use it, whereas the current section tries to explain some of the basic ideas behind the add-in. (The impatient may skip the current section and jump directly to the guided tour).

Gekcel comes in form of a macro-enabled Gekcel.xlsm file, together with Gekcel.xll , Gekcel.dll, and some other .exe and .dll files. The Gekcel.xlsm file is primary in the sense that it contains Excel VBA functions to interact with Gekko.

These interfacing Excel functions are written in VBA (Visual Basic), the programming language of Excel. This does not imply that you are expected to code complicated stuff in VBA, since basic use of Gekcel only entails the use of the simplest kinds of VBA calls like Gekko "read demo.gbk;" and similar. So don't worry: Gekcel is meant to make it more simple for Excel users to use Gekko, not to force anyone to learn how to code VBA.

As mentioned, the next section (guided tour) takes you through an example step by step, and after that guided tour you will probably realize that the VBA part of Gekcel mostly works like a kind of Gekko program file (.gcm), storing the Gekko statements in sequences. And if you really dislike VBA, you may alternatively use the so-called Excel Immediate Window, completely avoiding the use of the Excel VBA editor (there is a section on that, too).

To return to the functionality of Gekcel, in its essence, the Gekcel add-in is capable of starting up a silent Gekko session in the background (without opening up the Gekko graphical user interface), and issue Gekko commands from within Excel (typically via VBA). In addition, data can be transferred from Gekko to Excel worksheet cells and vice versa. There are the following fundamental Gekcel VBA functions available:

•Gekko(). Runs a Gekko statement from within Excel.

•Gekko_Get(). Transfers data from Gekko into an Excel worksheet tab.

•Gekko_Put(). Transfers data from an Excel worksheet tab to Gekko.

The Gekko_Get() and Gekko_Put() subroutines are explained in more details here. In Gekcel.xlsm, the three VBA subroutines can be seen in the VBA Editor in Excel. More on how to open and use the Excel VBA Editor in the next sections, but the functionality of the three subroutines is as follows:

•The Gekko() function is straightforward. For instance, you may use Gekko("read demo.gbk;") in VBA to issue the Gekko statement read demo.gbk;, opening up that particular Gekko databank.

•The Gekko_Get() function uses the following logic. When a Gekko statement is called from within Excel/Gekcel, instead of producing an .xlsx file or printing out results in the Gekko main window, some of the Gekko statements instead produce an internal two-dimensional table containing the results. This table can subsequently be loaded into the active Excel worksheet by means of the VBA Gekko_Get() function. At the moment, two Gekko statements can be used in this way: SHEET and PRT. So the idea is to call for instance SHEET with Gekko("sheet x1, x2;"), which produces the before-mentioned two-dimensional internal table. After this, the internal table can be transferred to the active worksheet in Excel with Gekko_Get(). More details here.

•The Gekko_Put() function does the reverse, that is, it takes all cells from the currently active worksheet tab and transfers them to the internal two-dimensional table. After this, the Gekko IMPORT statement can be used to transfer data from the internal table to Gekko (to its first-position databank). So the procedure is to first call the Gekko_Put() function, and then use Gekko("import <xlsx> gekcel;") to transfer the data to Gekko. The use of gekcel as filename is arbitrary just to indicate where the data comes from: when Gekcel is used, all import<xlsx> statements will take data from the internal table, instead of looking for an xlsx file. (It is planned to make it possible to use just Gekko("import <xlsx>;") in Gekcel, skipping the superfluous filename). More details here.

So to reiterate: when "remote controlling" Gekko from Excel/Gekcel, three of the Gekko statements are rewired, namely SHEET, PRT and IMPORT<xlsx>. The first two statements produce an internal two-dimensional table that can be fetched into Excel via the Gekko_Get() VBA function. To do the reverse, Gekko_Put() transfers the current worksheet cells to the same kind of internal table, which IMPORT<xlsx> can subsequently read into the Gekko first-position databank.

When getting data from Gekko, note that both SHEET and PRT have a lot of functionality besides "just" printing out variables. For instance, you may use mathematical expressions, and there are a lot of operators available, like <p> for percentage growth, <m> for databank comparisons, etc. Therefore, you can often avoid the effort of transforming the data via point and click (and dragging formulas) in Excel, and instead make Gekko do the heavy lifting. Besides mathematical expressions and operators, SHEET and PRT can also use wildcards and lists to search for or "compose" variable names.

It should be noted that when used from Excel/Gekcel, the Gekko SHEET and PRT statements produce the same contents, except for a difference regarding presentation:

•SHEET produces a data table with periods in each row, and variables in each column

•PRT produces a data table with variables in each row, and periods in each column. Besides, percentage growth is also shown per default.

There are a lot of options available to control this formatting if needed (cf. option sheet ... ; and option print ... ;).