<< Click to Display Table of Contents >> Navigation:  Gekcel (Excel add-in) > More: Gekko_Get/Put

The two subroutines Gekko_Get() and Gekko_Put() are not complicated at all, and if you have special requirements regarding how to fetch or store data, you may rewrite them to suit your particular needs.

 

The basic idea of both subroutines is that an internal two-dimensional VBA table (think of it as a matrix or array) is used as an interface from Gekko to Excel (Gekko_Get()) and from Excel to Gekko (Gekko_Put()). Below are these two subroutines, as they look like in Gekcel 3.1.14.

 

Gekko_Get (transfer from Gekko to Excel)

 

 

All of this except the line containing "Gekcel.COMLibrary" is normal VBA code. It first defines cells as a type that can receive a 2-d table of data from Gekko, which takes place in line 2. The right-hand side of line 2 technically returns an object[,] from Gekko, that is, an array of two dimensions, where the elements can be of any type. When Gekko is run from inside Excel, this array is produced by either the PRT or SHEET statement. Line 3 handles any missings (NaN) in the array, and the next two lines figures out how many rows and cols the array has. Next, a rectangular range of this size is selected (starting at the A1 cell in the current worksheet), this range is cleared, and finally the cells are "pasted" into the range.

 

Gekko_Put (transfer from Excel to Gekko)

 

 

All of this except the line containing "Gekcel.COMLibrary" is normal VBA code. The first two lines try to figure out the data dimensions of the current worksheet (using xlCellTypeLastCell to do this). In line 3, a rectangular range of this size is selected (this more or less corresponds to putting the cursor at A1 and hitting [Ctrl+A] to select the current data area). The VBA object x1 contains the data and is a object[,] type, that is, an array of two dimensions, where the elements can be of any type. In the last line of the subroutine, this array is is transferred to Gekko. When Gekko is run from inside Excel, the array is consumed by calling the IMPORT<xlsx> method. It should be noted that in this transferal, integers, floating values and strings are accepted, but Excel dates may pose problems (if so, use strings or integers as dates, for instance 2023q3 or 2024).

 

All in all, these subroutines are centered around a 2-dimensional table-like data representation (object[,]) of the data that is being fetched or stored, either like this:

 

 

or like this:

 

 

If you use this data representation as interface when talking to Gekko, you may use VBA to handle how and where the data areas are found, retrieved, filled, etc. VBA does that part of the job, not Gekko or Gekcel.