How to run VBA function using QAxObject?

Pauly

In excel, there are VBA functions such as Asc, Abs, which is not appliated with any object.
https://docs.microsoft.com/en-us/office/vba/language/reference/functions-visual-basic-for-applications

How can I run those functions using QAxObject. Tried dynamicCall but it doesn't work... Thanks.

hskoglund

Hi, you can use Evaluate to call Excel macros and built-in functions, like this:

auto excel = new QAxObject("Excel.Application");
qDebug() << excel->dynamicCall("Evaluate(\"Char(72) & Char(101) & Char(108) & Char(108) & Char(111)\")");

So one way to call VBA functions in Excel is via your own macro/UDF

Pauly

Thanks for the hint. It works.

So how can I make "Char(x)" to work, where x is a numeric variable. I tried

excel->dynamicCall("Evaluate(\"Char(int)\")",x);
excel->dynamicCall("Evaluate(Char(int))",x);

but none seems to work.

JonB

@pauly
I can't tell you what exactly to do, but:

It won't be Char(int), if int is a type (line in C++). It needs to be Char(variable_name), more like your Char(x).
To make that work, you would need to pass your variable's name & value across to VBA for it to create such a variable to evaluate. Usually that is down via functions with parameters. I don't know how you would have to send something to VBA to be set as a variable accessible from Evaluate, you'd have to look that up. But...
...For this particular case I suggest you'd want to do it easier. Do the work to create the string to Evaluate back it Qt before you send it to Excel. Replace the need to pass a variable to Excel by producing the desired literal string instead. That is what @hskoglund's example does. So something like:

int myVar = 41 * 3;
excel->dynamicCall("Evaluate(\"Char(" + QString::number(myVar) + ")\")");
// or more like
excel->dynamicCall(QString("Evaluate(\"Char(%1)\"").arg(myVar));

(My code may be a little dodgy with C++ and "+", I don't use it, but you should get the idea. The point is that by the time it's sent your evaluation string already reads Evaluate("Char(123)"), there is no variable to pass.)

Pauly

Thanks, this works for x as variable in qt. What if the x is a variable in vba? I have some case where I need to get something like below to work with QAxObject. So in VBA:

x=1
TypeName(x)

JonB

@pauly
Then the whole thing is some VBA question and has nothing to do with Qt?

Pauly

I'm trying to do this with QAxObject... calling a vba function that takes argument of a vba variable. Thanks.

hskoglund

Hi, first, about the Evaluate call, it's all string based (no ints) like this:

qDebug() << excel->dynamicCall("Evaluate(QString)","Char(65)");

then, on how to reach vba variables:
problem is that the Evaluate function gives you only access to the formula bar, i.e. Excel just adds the '=' in front of the argument you supply to Evaluate. There's no way to enter code/text into the ALT+F11 VBA world using QAxObject :-(

But, as I mentioned above, you can create a "bridge" by creating a user function in your Excel file. Example (alt+F11, insert module in Sheet1):

Public Function GetTypename(x)
GetTypename = TypeName(x)
End Function

to use this from QAxObject you need to save the function and worksheet as a xlsm file (to enable macros) and then load it, say like this:

auto excel     = new QAxObject("Excel.Application");
auto workbooks = excel->querySubObject("Workbooks");
auto workbook  = workbooks->querySubObject("Open(QString)","c:\\temp\\test.xlsm");
auto sheets    = workbook->querySubObject("Worksheets");
auto sheet     = sheets->querySubObject("Item(int)", 1);    // use first worksheet
qDebug() << excel->dynamicCall("Evaluate(QString)","GetTypename(1)");
qDebug() << excel->dynamicCall("Evaluate(QString)","GetTypename(\"ABC\")");

Pauly

Thanks a lot, setting upu a function and run it is brilliant idea, and this works around the problem.

BTW I use "run" instead of "evaluate" with gives a little bit more flexibility...

qDebug() << excel->dynamicCall("Run(QString,QVariant)","GetTypename",<something>);

hskoglund

@pauly said in How to run VBA function using QAxObject?:

BTW I use "run" instead of "evaluate" with gives a little bit more flexibility...

Nice! I remember testing "Run" but I couldn't get it to work, only "Evaluate", must be getting old :-)