1

I was looking at the answer given here How do I call an Excel macro from Python using xlwings?. I implemented that solution, but testing about the possibilities of calling a VBA I was wondering if would be possible to call a VBA function that uses RunPython from my python script.

To illustrate this I have three files in the folder

  • -- myproject.xlsm
  • -- myproject.py
  • -- Hello_World.py

VBA in myproject.xlsm

Sub SampleCall()
    RunPython ("import myproject; myproject.xl_main()")
End Sub

Sub Hello()
    RunPython ("import Hello_World; Hello_World.xl_test()")
End Sub

Sub Message()
    MsgBox ("ok")
End Sub

myproject.py

from xlwings import Workbook, Range, Application
import os
import sys


def xl_main():
    # Create a WorkBook Object
    wb = Workbook('myproject.xlsm')

    Range('B1').value = 17

    #Call a VBA Function
    Application(wb).xl_app.Run("Message")
    Application(wb).xl_app.Run("Hello")



if __name__ == "__main__":
    if not hasattr(sys, 'frozen'):
        # The next two lines are here to run the example from Python
        # Ignore them when called in the frozen/standalone version
        #TODO: Change the name of excel file
        path = os.path.abspath(os.path.join(os.path.dirname(__file__), 'myproject.xlsm'))
        Workbook.set_mock_caller(path)
    xl_main()

Hello_World.py

import xlwings as xw
from xlwings import Workbook, Range

import os


def xl_test():
    # Create a WorkBook Object
    wb = xw.Workbook.caller()

    Range('A1').value = "Hello World"


if __name__ == '__main__':
    # To run from Python, not needed when called from Excel.
    # Expects the Excel file next to this source file, adjust accordingly.
    path = os.path.abspath(os.path.join(os.path.dirname(__file__), 'myproject.xlsm'))
    xw.Workbook.set_mock_caller(path)
    xl_test()

The problem:

The problem is that at the time I run the Hello_World.py script It works well, but when I executed the SampleCall() Sub in VBA it crash and gives a blank error.

Community
  • 1
  • 1
jvaldiviezo
  • 101
  • 9
  • Why would you ever want to do that? why don't you call the python code from python itself? – Felix Zumstein Feb 16 '16 at 14:06
  • Hi Felix, It's because I'm using xlwings to insert rows and columns and put formats. Also I think in some cases would be faster to record a macro and just append a Python Script to solve some numeric manipulations. What I want is to have a main function in python that calls for formats and columns inserts, In short, I would have a main function that calls VBA Macro and depending the manipulation a Python Script is called inside the VBA Macro. Also, Is there a way to pass arguments to a VBA function from python? Thanks!! – jvaldiviezo Feb 24 '16 at 15:15

1 Answers1

0

you can do something like the following

xlwings - python code

wb.xl_workbook.Application.Run("someFunction", param1)

vba fucntion

Public Sub someFunction(param1)
    ...
End Sub
Shawn K
  • 779
  • 5
  • 13