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 ?

Community · Accepted Answer · 2017-05-23 11:51:12Z

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

See How to call Excel VBA functions and subs using Python win32com?

edited May 23, 2017 at 11:51

CommunityBot

11 silver badge

answered Jan 12, 2015 at 10:31

sancho.s ReinstateMonicaCellio

15.8k25 gold badges110 silver badges214 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

paul c Over a year ago

I forgot the return type but I don't get why it wouldn't work from the workbook but on a module.

sancho.s ReinstateMonicaCellio Over a year ago

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.

Collectives™ on Stack Overflow

Calling an Excel user defined function from Python always returns None

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related