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 :-)