Passing in Strings of >255 Characters

Jul 28, 2011 at 10:45 AM

I'm having trouble passing a string of >255 characters into an Add-In function and then casting it to const char* inside the routine. Is this possible?

Here's the header for my routine:

static AddInX xai_bind_text(	
	FunctionX(XLL_LPOPERX, _T("?sqlite3_xll_bind_text"), _T("SQLite3.bind_text"))
	.Arg(XLL_LONGX, _T("Handle"), _T("Statement Handle"))
	.Arg(XLL_SHORTX, _T("Integer"), _T("Column Number"))
	.Arg(XLL_CSTRINGX, _T("String"), _T("Value to bind"))
	.Category(_T("SQLite3"))
	.FunctionHelp(_T("Description of what function does."))
	.Documentation(_T("Optional documentation for function."))
	);
LPOPERX WINAPI	
	sqlite3_xll_bind_text(sqlite3_stmt* pStmt, int paramIndex, const char *zValue)
{	

I'd like:

  1. zValue to be something other than XLL_CSTRINGX which will accept >255 characters, and
  2. zValue to be cast to const char* inside the routine.

Any clues?

Incidentally, I'm calling this from VBA using the following:

SQLite3BindText = Application.Run("SQLite3.bind_text", stmtHandle, OneBasedParamIndex, Value)

If 'Value' - a VBA String - is longer than 255 characters, the Application.Run fails with a type mismatch, which is a pretty elegant way of it falling over, certainly much better than an unterminated string finding its way into the C++...

Thanks again,

Dr. Matt Wenham.

Coordinator
Jul 28, 2011 at 12:27 PM

You need to #define EXCEL12 before including xll.h to use wide character strings. Otherwise you are limited to 255 characters.

Another approach is to pass in an array of strings and use to_string to concatenate them together inside your C++ code.

"Elegant way of falling over." If you're riding a tricycle the fall is not very far. Ditch the VBA and just call it like a regular Excel function. If you declare an argument as XLL_CSTRINGX you will be guaranteed to get a null terminated string passed to your C code. If someone tries to slip you something else, Excel will take care of that for you and display a #VALUE! to the user.

Jul 28, 2011 at 1:03 PM

I'm hacking writing an XLL wrapper for SQLite to allow integration into our VBA applications.

It may be possible to do SQLite open / initiate / query / close using a worksheet with all the function calls in the correct order, but what I'm actually aiming for is to be able to use SQLite from VBA via an XLL rather than DLLs.