Calling an Excel user defined function from Python always returns None

Question

I'm trying to get the return value from an Excel function that i called from a python. I always get a None value.

Here is the excel function :

Public Function GetString()
    GetString = "aa"
End Function

And Here is the call :

xlApp.Run('ThisWorkbook.GetString')

Returned :None

I have no problem getting in the specified function since I tried to modify a cell value from it and it worked.

Am I missing anything ?

score 0 · Accepted Answer · edited May 23 '17 at 11:51

0

It is not clear how you assigned your variable xlApp. Assuming code like

import win32com.client
xl = win32com.client.Dispatch("Excel.Application")
xlApp = xlApp.Application

consider:

You have to assign the result to a variable, as in

myStr = xlApp.Run('ThisWorkbook.GetString')
You seem to have placed the user defined function in ThisWorkbook, in the Excel file. Unless this is intentional, open the Excel file, insert a Module in the Visual Basic Editor, and move there your Function. Modify your call from Python accordingly.
You may want to define the function as

Public Function GetString() as String

edited May 23 '17 at 11:51

Community

answered Jan 12 '15 at 10:31

I forgot the return type but I don't get why it wouldn't work from the workbook but on a module. – paul c Jan 12 '15 at 10:42
I was not sure if that would make a difference. At the least, it is a matter of good coding practice (which would not explain your second question). I have seen a couple of strange things when moving code this way, so I though it might have been an additional factor in you problem. – sancho.s ReinstateMonicaCellio Jan 12 '15 at 10:48

1 Answers1