How to run VBA function using QAxObject?
-
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-applicationsHow can I run those functions using QAxObject. Tried dynamicCall but it doesn't work... Thanks.
-
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
I can't tell you what exactly to do, but:-
It won't be
Char(int)
, ifint
is a type (line in C++). It needs to beChar(variable_name)
, more like yourChar(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.) -
-
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\")");
-
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>);
-