Entering data: Drop-down lists

GS-Calc (ver. 17 and later) can be configured to use functions written by users in C/C++ as plain DLL libraries. Such functions can be even used as a replacement for the default built-in functions. They can accept and return all the argument types and additionally they can also "return" images displayed directly in worksheets or messages displayed after each update. What's important, using these functions GS-Calc retains the complete multicore support, the same speed and memory requirements (e.g. no extra arrays are allocated when passing ranges as arguments). You can add virtually any number of such functions.

Libraries are added by the Settings > Imported Function Libraries command as shown on the screenshot below. The internal function names MUST match the original C/C++ names. The displayed names are what is entered in worksheet cells and the only requirement is that they must be unique.

*

The added functions must be declared as

typedef double(__cdecl *DLL_EXT_FUNCTION)(GSCalcArg *arg);

where GSCalcArg is a structure enabling you to pass up to 15 arguments: numbers, strings and ranges/arrays.

struct ArrayItem
{
	uint8_t type;		// DLL_ARGT_EMPTY | DLL_ARGT_DOUBLE | DLL_ARGT_TEXT
	uint8_t err_code;	// ERROR1_DIV_BY_ZERO...ERROR1_SYNTAX_ERROR
	uint16_t col;		// [0, 4095]
	uint32_t row;		// [0, 12582911]
	double num;		// numeric cell
	char text[MAXINPUT + 1];// text cell; max. 1024+NULL char. utf-8 string 
};

typedef int(__cdecl *READ_ARRAY)(void *env, int array_index, ArrayItem *val);
typedef int(__cdecl *WRITE_ARRAY)(void *env, int array_index, ArrayItem *val);

typedef void* (__cdecl *MALLOC)(size_t s);
typedef void(__cdecl *MFREE)(void *_Memory);

struct GSCalcArg
{
	uint8_t types[16];		// input: DLL_ARGT_NUMBER | DLL_ARGT_TEXT | DLL_ARGT_ARRAY | DLL_ARGT_EMPTY
					// output: as above + DLL_ARGT_IMAGE | DLL_ARGT_MESSAGE
	uint8_t errors[16];		// ERROR1_DIV_BY_ZERO...ERROR1_SYNTAX_ERROR
	double numbers[16];
	char *strings[16];		// input (UTF-8) 0...14 strings are preallocated by GS-Calc and must not be overwritten;
					// strings[15] is used for output strings and already points to a MAXINPUT+1 char. temp. buffer
	struct Dims
	{
		uint32_t cx;
		uint32_t cy;
	} array_dims[16];		// dimensions of the subsequent and grouped together range/array arguments passed from GS-Calc
	struct
	{
		BYTE *data;		// DIB data if you return types[15] = DLL_ARGT_IMAGE; must be (de-)allocated with memory_alloc/memory_free
		size_t size;		// DIB data size
		Dims dims;		// optional resizing when displaying the image (returned either as a DIB or a file path)
		MALLOC memory_alloc;
		MFREE memory_free;
	} image;
	READ_ARRAY read_array;		// returns ArrayItem.type
	WRITE_ARRAY write_array;	// returns ArrayItem.type or -1 if the out-of-memory condition occurs 
	void *env;			// internal data that must be passed back in read_array/write_array calls
};

Indices 0...14 in types[] and errors[] in GSCalcArg represent all subsequent argument types and errors from left to right passed from GS-Calc.

Indices 0...14 in numbers[], strings[] and array_dims[] point to argument values. The original parameters are already divided into these 3 groups (numbers, strings and ranges/arrays) when your functions is called. For example, if some worksheet cell execute your function

my_function(1.0, 2, "abc", a5:b15,)

the parameters passed to the DLL in the GSCalcArg structure will be as follows:

types[0] = DLL_ARGT_DOUBLE, types[1] = DLL_ARGT_DOUBLE, types[2] = DLL_ARGT_TEXT, types[3] = DLL_ARGT_ARRAY, types[4] = DLL_ARGT_EMPTY
numbers[0] = 1, numbers[1] = 2, strings[0] = "abc", array_dims[0] = {2, 10}

You can access numbers and strings directly. Array elements can be read (and write on output) using the read_array() (and write_array()) functions as shown below in the filter function example. The first argument is the "env" internal pointer from the GSCalcArg structure, the 2nd is the array index (0-14) as passed in array_dims[] and the last is a pointer to the ArrayItem structure with the "col" and "row" of the requested element.

Note: for very large array, for best possible performance while retrieving data from data from arrays or writing data to arrays, subsequent read_array() (and write_array()) calls MUST access the array elements in the left-to-right and top-to-bottom order.

On output, the index 15 (DLL_ARGC_RET) is used to specify the returned type (in types[15]), possible error code (in errors[15]) and value (in numbers[15], strings[15] or array_dims[15]). The values types[15] and errors[15] MUST always be set correctly on return.
Note: the strings[15] already contain a valid buffer (up to 1024 characters + NULL) allocated by GS-Base and if a given function returns a text value, it must be copied to that buffer as a NULL terminated string.

Examples:


// messageIf(condition, text)
//
// arguments:
//			condition, message text
// returns:
//			if condition != 0, 'messageIf' displays 'text' as a message box after each update/recalculation
//			otherwise the message text is displayed in a cell as normal text

__declspec(dllexport) double __cdecl messageIf(GSCalcArg *arg)
{
	uint8_t errorCode = 0;

	if (arg->types[0] != DLL_ARGT_DOUBLE && arg->types[0] != DLL_ARGT_EMPTY || arg->types[1] != DLL_ARGT_TEXT)
		errorCode = ERROR1_INVALID_VALUE;
	if (arg->types[2] != DLL_ARGT_NONE)	// more than 2 arguments detected
		errorCode = ERROR1_SYNTAX_ERROR;

	if (!errorCode && arg->errors[0])
		errorCode = arg->errors[0];
	if (!errorCode && arg->errors[1])
		errorCode = arg->errors[1];

	if (!errorCode)
	{
		::strncpy(arg->strings[DLL_ARGC_RET], arg->strings[0], 1024);
		arg->strings[DLL_ARGC_RET][1024] = 0; 
		return arg->types[DLL_ARGC_RET] = (arg->numbers[0] ? DLL_ARGT_MESSAGE : DLL_ARGT_TEXT);
	}
	else
	{
		arg->errors[DLL_ARGC_RET] = errorCode;
		return arg->types[DLL_ARGC_RET] = DLL_ARGT_EMPTY;
	}

	return 0;
}


// filter(range, column, number)
//
// arguments:
//			range - a range/array
//			column - column index (from 0 to the number of columns in 'range' - 1)
//			number - numeric value to perform equality check
// returns:
//			an array consisting of rows of 'range' containing 'number' in the specified column

__declspec(dllexport) double __cdecl filter(GSCalcArg *arg)
{
	uint8_t errorCode = 0;

	if (arg->types[0] != DLL_ARGT_ARRAY || arg->types[1] != DLL_ARGT_DOUBLE || arg->types[2] != DLL_ARGT_DOUBLE)
		errorCode = ERROR1_INVALID_VALUE;
	if (arg->types[3] != DLL_ARGT_NONE)
		errorCode = ERROR1_SYNTAX_ERROR;

	if (!errorCode && arg->numbers[0] >= arg->array_dims[0].cx)
		errorCode = ERROR1_INVALID_VALUE;
	if (!errorCode && arg->errors[0])
		errorCode = arg->errors[0];
	if (!errorCode && arg->errors[1])
		errorCode = arg->errors[1];
	if (!errorCode && arg->errors[2])
		errorCode = arg->errors[2];

	ArrayItem x = { 0 };
	int outRow = 0;

	for (uint32_t inRow = 0; inRow < arg->array_dims[0].cy && !errorCode; ++inRow)
	{
		x.row = inRow;
		x.col = static_cast<uint16_t>(arg->numbers[0]);
		if (arg->read_array(arg->env, 0, &x) == -1)
		{
			errorCode = x.err_code;
			break;
		}

		bool match = false;
		if (x.type == DLL_ARGT_DOUBLE)
			match = (x.num == arg->numbers[1] || x.err_code && x.err_code == arg->errors[2]);
		else if (x.type == DLL_ARGT_EMPTY)
			match = (arg->numbers[1] == 0);
		else
			errorCode = ERROR1_INVALID_VALUE;

		if (match)
		{
			for (x.col = 0; x.col < arg->array_dims[0].cx && !errorCode; ++x.col)
			{
				x.row = inRow;
				arg->read_array(arg->env, 0, &x);
				x.row = outRow;

				// for best performance when writing to very large arrays, try to write in the left-to-right and top-to-bottom order

				if (arg->write_array(arg->env, DLL_ARGC_RET, &x) == -1) // -1 means the out-of-memory condition or an invalid row/column
					errorCode = x.err_code;
			}
			++outRow;
		}
	}

	arg->errors[DLL_ARGC_RET] = (!outRow ? ERROR1_NULL_VALUE : 0);
	return arg->types[DLL_ARGC_RET] = DLL_ARGT_ARRAY;

	return 0;
}



// image(path, cx, cy)
//
// arguments:
//			path - a bmp, jpg, png, gif file path
//			cx/cy - display image size (optional)
// returns:
//			loads an image (bmp, jpg, png, gif) from a disk and displays it, optionally resizing it
//			to the cx/cy dimensions (in screen pixels)

__declspec(dllexport) double __cdecl image(GSCalcArg *arg)
{
	uint8_t errorCode = 0;

	if (arg->types[0] != DLL_ARGT_TEXT ||
		arg->types[1] != DLL_ARGT_DOUBLE && arg->types[1] != DLL_ARGT_EMPTY ||
		arg->types[2] != DLL_ARGT_DOUBLE && arg->types[2] != DLL_ARGT_EMPTY)
		errorCode = ERROR1_INVALID_VALUE;
	if (arg->types[3] != DLL_ARGT_NONE)
		errorCode = ERROR1_SYNTAX_ERROR;

	if (!errorCode && arg->errors[0])
		errorCode = arg->errors[0];

	::strcpy_s(arg->strings[DLL_ARGC_RET], MAXINPUT, arg->strings[0]);
	arg->image.dims.cx = static_cast<uint32_t>(arg->numbers[0]);
	arg->image.dims.cy = static_cast<uint32_t>(arg->numbers[1]);

	// arg->image.data must remain NULL

	arg->errors[DLL_ARGC_RET] = errorCode;
	return arg->types[DLL_ARGC_RET] = DLL_ARGT_IMAGE;
}

Complete gsclib.h header file that must be included in each DLL project

A sample complete project with the messageIf, filter, image and fractal functions:

gsclib.h
dllmain.cpp
gsclib.cpp