Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

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-applications

    How 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



  • 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.



  • @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.)



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


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



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



  • 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>);


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


Log in to reply