Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. How to run VBA function using QAxObject?
Forum Updated to NodeBB v4.3 + New Features

How to run VBA function using QAxObject?

Scheduled Pinned Locked Moved Solved General and Desktop
10 Posts 3 Posters 1.8k Views
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • P Offline
    P Offline
    Pauly
    wrote on last edited by
    #1

    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.

    1 Reply Last reply
    0
    • hskoglundH Offline
      hskoglundH Offline
      hskoglund
      wrote on last edited by
      #2

      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

      1 Reply Last reply
      3
      • P Offline
        P Offline
        Pauly
        wrote on last edited by
        #3

        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.

        JonBJ 1 Reply Last reply
        0
        • P 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.

          JonBJ Offline
          JonBJ Offline
          JonB
          wrote on last edited by JonB
          #4

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

          1 Reply Last reply
          0
          • P Offline
            P Offline
            Pauly
            wrote on last edited by Pauly
            #5

            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)
            
            JonBJ 1 Reply Last reply
            0
            • P 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)
              
              JonBJ Offline
              JonBJ Offline
              JonB
              wrote on last edited by
              #6

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

              1 Reply Last reply
              0
              • P Offline
                P Offline
                Pauly
                wrote on last edited by
                #7

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

                1 Reply Last reply
                0
                • hskoglundH Offline
                  hskoglundH Offline
                  hskoglund
                  wrote on last edited by
                  #8

                  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\")");
                  
                  1 Reply Last reply
                  2
                  • P Offline
                    P Offline
                    Pauly
                    wrote on last edited by
                    #9

                    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>);
                    
                    hskoglundH 1 Reply Last reply
                    0
                    • P 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>);
                      
                      hskoglundH Offline
                      hskoglundH Offline
                      hskoglund
                      wrote on last edited by
                      #10

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

                      1 Reply Last reply
                      0

                      • Login

                      • Login or register to search.
                      • First post
                        Last post
                      0
                      • Categories
                      • Recent
                      • Tags
                      • Popular
                      • Users
                      • Groups
                      • Search
                      • Get Qt Extensions
                      • Unsolved