Run VBA from calc cell?

[vib]

I am working an an extension for calc and I am wondering if it is possible to run VBA driectly from a Calc or automatically add VBA to a Calc document.

If I can figure it out I would like to have a calc function that popus up a dialog box, in the dialog the user could input. The the addon would be responsible for running the vba and returning the result.

I don’t think vba can execute a string of code like Python’s eval() function. However, I am wondering if a module can be injected into the document using the API or if a module can be executed that does not live directly in the user or shared folders.

I am fairly certain that it is not possible to run a VBA module outsited the shared or user folders but if it could it would be a simple matter of writing the VBA to the tmp folder and then load/run.

Any thoughts?

[sokol92]

Please clarify what you mean by VBA?
VBA is a programming language used in MS Office applications.
The syntaxes of VBA and LO Basic are almost the same, the object models of MS Office and LibreOffice applications are different.

[vib]

For this context VBA means any macro code that LibreOffice can run in the Visual Basic for Applications format.

[sokol92]

Example: see the RunFromLib function in the famous book by A. Pitonyak OOME_4_1.odt

[vib]

Thanks, This looks promising. I will study this further.

[Lupp]

@vib

For this context VBA means any macro code that LibreOffice can run in the Visual Basic for Applications format.

Once more you refuse to be precise.
Is your code createdf for Excel-VBA?

If so:

• Does it run correctly if started in a different way (called from a LibO-Basic UDF e.g. or directly in the Basic IDE of LibO)?
• Does it require to have set Option VBAsupport 1?

If otherwise:

• Why do you use terms like VBA or “VBA format” (which isn’t a well introduced term)?
• What do you mean by “Run … from a cell”?

UDF expressions are “run from a cell” independent of the supported language the code was created with.
Macros declared as Sub in any Basic can’t be called as evaluators of argument lists.
Subroutine code can be called “from a cell” using the vnd.sun.star.script: construct in different ways:

• with the help of the HYPERLINK() function.
• activating a portion of the cell’s textual content inserted as a TextField and having assigned the respective link as its URL.

[vib]

@Lupp

Once more you refuse to be precise.

If I am not precise I apologize. It is certainly because I refuse to do so. If I am not precise then most likely I just need a better understanding.

I Work mostly in Python so the Basic (VBA) termonolgy is not always clear for me.

I am just exploring the idea of running code (Python and Basic) from a calc custom formula(s). I am trying to figure out if it is even possible. Think of running python in MS Excel using the =PY() formula. I am attempting to bring something similar to LibreOffice Calc.

I am still in the early testing stages, I have solved a number of problems for running python code in this manor. While I was working on this I thought, would this be possible for basic as well.
That is what prompted my post in this thread.

So, the details you have mentioned I am actually not certain of just yet. They may be important, I just have not gotten that far with it yet. Although I welcome much feedback.

[Wanderer]

@vib

It is certainly because I refuse to do so.

Think again…

@vib

so the Basic (VBA) termonolgy is not always clear for me.

The problem is: You are precise, but you use the wrong term, because VBA is the specific version by MS, wich is not available in LibreOffice, even if there is an internal switch to make modules interpreted in more compatible manner.
.
To translate roughly in the python world: You asked for specific things like using conda or IronPython/ Python2 when you only needed to ask vor Python.

[vib]

Thank you, Got it!

[vib]

I got the initial code working. I can create basic module, library, and a method in python and run the basic code. Should be in the next version of OOO Development Tools, 0.43.0.

Example actually pops up a message box in Calc.

from __future__ import annotations
import logging
import uno

from ooodev.calc import CalcDoc
from ooodev.loader import Lo
from ooodev.loader.inst.options import Options
from ooodev.utils.string.str_list import StrList
from ooodev.adapter.container.name_container_comp import NameContainerComp
from ooodev.macro.script.macro_script import MacroScript


def main():
    loader = Lo.load_office(connector=Lo.ConnectPipe(), opt=Options(log_level=logging.DEBUG))
    doc = CalcDoc.create_doc(loader=loader, visible=True)
    try:
        inst = doc.basic_libraries
        mod_name = "MyModule"
        lib_name = "MyLib"
        clean = True
        added_lib = False

        if not inst.has_by_name(lib_name):
            added_lib = True
            inst.create_library(lib_name)

        inst.load_library(lib_name)

        lib = NameContainerComp(inst.get_by_name(lib_name))  # type: ignore
        if lib.has_by_name(mod_name):
            lib.remove_by_name(mod_name)

        code = StrList(sep="\n")
        code.append("Option Explicit")
        code.append("Sub Main")
        with code.indented():
            code.append('MsgBox "Hello World"')
        code.append("End Sub")
        lib.insert_by_name(mod_name, code.to_string())

        MacroScript.call(
            name="Main",
            library=lib_name,
            module=mod_name,
            location="document",
        )
        print("Macro Executed")
        if clean:
            lib.remove_by_name(mod_name)
            if added_lib:
                inst.remove_library(lib_name)

        print("Done")
    finally:
        doc.close()
        Lo.close_office()

if __name__ == "__main__":
    main()