Excel as a user interface With the introduction of VisiCalc
, the first spreadsheet software, blossomed in 1979 the Apple II from mere home and hobby computers to business computers. The simple representation of data and their dependencies in a modifiable table brought success! To date, the spreadsheet is a basic need of many professional computer users. A spread sheet can be sent around by email and attach it to his presentation or specification, especially you can change data in order to investigate the interdependence of values. Data can be displayed graphically in charts and a simple format [1] passed via clipboard into other applications. The subjective experience for the user is that he has everything under control. That feels good, and demands satisfaction. The "domain experts" that developers in the role of customers, users and testers have to do swear to Excel. Of course, Excel makes
abuse, "If you have a hammer, everything looks like a nail." Those who know Excel and understood is the temptation to use it for everything
that gets under your fingers. The financial account as an Excel sheet might still be - but for the general ledger of the company to use but rather something else. The project design with colored cells for the calendar weeks looks strangely unprofessional. We also have people use that Excel as a database replacement, where they met earlier often to the limit of 65,536 rows per sheet (there is a limit still exists, but it now amounts to 1,048,576 rows - that there is room for many new crazy abuse ideas ).
farsighted had
Charles Simonyi
, Then developers at Microsoft, the potential of the spreadsheet recognized as a universal user interface, when he took up the ideas of VisiCalc and MultiPlan Microsoft and then put it into Excel. Today, he tried with his own company, Intentional Software, his vision of
form will allow. Here, too, spreadsheet once again plays a big role in that it can be used as a rule editor to metaprogramming. Anyone who has read my blog about software
Stone Age
, knows where the journey leads. Which is of Simonyi's team developed and the first company already productively employed
Intentional Workbench
a milestone on the way to better, more robust, more compact and read software.
But these perspectives would be worth its own blog. Today I want to write about the use of Excel as a user interface in my project
astro
patterns.
astro patterns
is a library of time-critical functions, the search for astrological constellations or patterns. It will also run millions of planetary calculations. In order not to give away time on unnecessary layers such as. NET, I have decided to implement with C + +, where I used the GNU gcc compiler
to generate the library. In the implementation of key players from
astro patterns
I could greatly benefit from the object orientation - to the versatile applicability of the public functions of the library's sake but are defined as C functions. In essence, I am therefore platform-independent, even if I compile the software itself on a single Windows computer and test. This creates a library astropatterns.dll
. The library in turn uses only the Ephemeridensoftware Swiss Ephemeris, which is used as a shared library at run time. This is the only dependency. With the API astro
patterns can be defined constellations and perform calculations, the results returned in the form of arrays or structures be. astro patterns
itself has neither a user interface or any I / O. to disallow a user interface that was my choice to Excel. The possibility of different flyers were distributed to define settings and rules, was exactly what I needed. The first worksheet acts as a work area in which calls the functions of astro patterns
and the results - the dates on which occurred the sought constellations - are displayed as a list: preface
I must ensure that the programming language Visual Basic (in whatever their variety, either VBscript or VBA) for me the ugliest language ever heard (it is perhaps only exceeded in ugliness by the programming language
Brainfuck
, but which is only a gimmick of computer science students). Nevertheless, I bit for this project in the bullet and written a few lines of VBA, to link the DLL into the Excel spreadsheet.
Some things it's worth it for similar projects noted:
Excel does not know their own directories.
the beginning this is a bit confusing: If I have a DLL with the Excel document you want to call them, have in a folder that Excel does not find this DLL. If dynamic libraries be sought in addition to those listed in the PATH environment variable
directories not in the current directory? Yes, this is so. But when you start Excel, tückischerweise is the "current directory" on the "My Documents" Reverse shot, which is provided in the Windows-user concept for storing your own files - no matter where you find the currently open Excel document. There is only, at the time
Workbook_Open the current directory by calling the Windows API function again to set the value that an unprejudiced person would expect the current directory:
Private Sub Workbook_Open ()
'Set the current directory to the location 'where the Excel sheet is stored
' This Enable the VBA code to find 'the astropatterns.dll and swedll32.dll there
SetCurrentDirectory (ThisWorkbook.path) End Sub The function is SetCurrentDirectory
from kernel32.dll
declared it as follows:
'SetCurrentDirectory
' Needed since Excel uses "My Documents"
'as current directory by default
' It should use the path of the workbook
Public Declare Function SetCurrentDirectory _
Lib "kernel32" _ Alias
"SetCurrentDirectoryA" (_ ByVal lpPathName
_ As String) As Long stdcall requires VBA.
gcc generates so-called "decorated" functions with this calling convention. The "decoration" is in an attached
@ , followed by the number of bytes consumed the whole list of parameters on the stack. If you make it known the VBA functions, you have to use these functions decorated. So that the call is no problem, the function must be declared in C as follows
extern "C" __declspec (dllexport) void __stdcall
ap_set_mundane_positions (
t_double_pair const * data, const size_t size
); and can then VBA with the following declaration be published: Public Declare Function
ap_set_mundane_positions
_ Lib "astropatterns.dll"
_ Alias "ap_set_mundane_positions @ 8 (_
ByRef data As Double, _ ByVal size As Long _ ) As Long
this I do not writer's cramp get, I have defined for the definition of the public interface in C, a macro
APIFUNC :
# define APIFUNC (x) extern "C" __declspec (dllexport) x __stdcall
The argument of the macro function takes the type of the return value contrary. So I write the above function in reality more compact: APIFUNC (void) ap_set_mundane_positions (
t_double_pair const * data, const size_t size );
Only
written code modules can be reused.
Beware of the code blocks, the PLA to offer "Microsoft Excel Objects". What is implemented there is no cross-use. "Microsoft Excel Objects" should only event handler and direct hits are stored to the table data. The actual logic should be implemented as a "module" as it is not available across otherwise.
passing null-terminated strings.
It is possible for an ordinary C string, more precisely a null-terminated array of character, by reference from the interface to receive and reuse in VBA. Suppose the function
ap_get_log ()
give back the reference to a C string. Then we can copy the string with the function
lstrcpy
of the Windows kernel in a VB string, after we have established with the
Space $ () 'Determine the length of a \\ 0 terminated string
Public Declare Function lstrlen _ Lib "kernel32" _ Alias
"lstrlenA"
_ (ByVal lpString As Long) As Long
'Copy a string
Public Declare Function lstrcpy
_ Lib "kernel32" _ Alias
"lstrcpyA" _ (ByVal lpString1 As String, _ ByVal
lpString2 As Long) As Long Public Function cstar_to_string (lpString As Long) As String = cstar_to_string lstrlen Space $ ( (ByVal lpString)) lstrcpy ByVal cstar_to_string, ByVal lpString
End Function
In the reverse direction - from VBA to C - it's simple: You give a
String
outright to a parameter in the declaration of DLL function as ByVal ... String
is declared. On the other hand it is at the desired char *
. [2] transfer of data structures and arrays flat data structures can be passed in both directions when they are declared in the same way. Example: The type
t_horo that contains the three for the calculation of a horoscope necessary figures, typedef struct {
double jd_et; double lon, double lat
;}
t_horo;
I note how similar in VBA follows: Public Type
t_horo
jd_et As Double lon As Double
lat As Double End Type
difficult is the situation with arrays: An array of VBA is unfortunately not file is passed as one would expect - as a simple sequence of individual records. A VBA array to pass with custom line type is possible, however, require a most inappropriate expenses: You'd have this, the C change / C + + program and teach him the PLA convention for such arrays (see - KB 194 609 in MSDN). This effort can save you, either by the array of sets are in a loop (for smaller arrays) or in an easy walk to be analyzed intermediate format (eg csv
) and then passed as a string. Leave A sheet with self-tests!
This is almost an obvious remark: Code should, wherever possible and appropriate, be covered by automated tests. A test framework in Excel you have written down quickly. It is advisable to establish a separate worksheet for self-testing and then line by line to deposit the name of the test functions and the expected outcome. With a button "do test" then, all of these test functions are executed. It does no harm this sheet to be delivered. In maintenance, it will prove quite safe. In the implementation I used the dynamic function call with the function
CallByName ()
, as I have in my
Excel interface for the Swiss Ephemeris
had done.
All in all, I am with the combination of Excel for the user interface with C + + for the actual program logic yet met with is not an insoluble problem. Some I would like to see easier in the implementation, and a more beautiful language than just VBA would also be pleasant. But it is everything, and for the implementation of a project like astro patterns, the main burden on the library functions themselves is, I'm happy with my decision to Excel as a user interface.
[1] The data exchange format of spreadsheet programs to clipboard usually is a single version of CSV to TAB as the delimiter.
[2] has NET to proceed here somewhat differently. It uses the function declaration, a reference to the class
System.Text.StringBuilder (instead of String
) and then gets on the design of an external function in VBA program a reusable string by using the method
toString ()
this reference.
what he does with the concrete 
