Calling Python from an Excel UDF

Just to start of this blog, I'll post some interesting code I found, which lets you run python within VBA. I got this from here. I'll comment on it afterward.

Global sc As New MSScriptControl.ScriptControl

Public Function os_getcwd()
  sc.Language = "python"
  sc.ExecuteStatement ("import os")
  os_getcwd = sc.Eval("os.getcwd()")
End Function

With this you can set your Excel formula to =os_getcwd()

For me it returns "C:\Documents and Settings\Administrator\My Documents",
which I needed to know at the time so I didn't have to screw around with
the ever annoying pythonpath.

Read it all. We can do other things in it. For example, I would suggest the following:

sc.ExecuteStatement ("x = 7 + 6")
os_getcwd = sc.Eval("x")

and we would get 13. This is just a proof of concept. Practical examples which make use of this would be useful may be forthcoming if I am interested enough. In particular, the memory this retains plus the built in set and list formers may lead to some very powerful built-in functions. Clever!

One such example: How do I center text in a string, by padding with spaces before and after, such that the entire length of the string is 100. I could take the string length, figure out have many spaces to append before, how many after, and so on. Or else I could use this UDF:

Public Function CenterText(text, width)
text = Chr(34) & text & Chr(34) ' add quotes around "text"
sc.ExecuteStatement ("import string")
sc.ExecuteStatement ("x = string.center(" & text & ", " & width & ")")
CenterText = sc.Eval("x")
End Function

See how I first build the command using string concatenation, and then run it.
I don't actually need to store in a temporary in the meantime. I could write:

Public Function CenterText(text, width)
text = Chr(34) & text & Chr(34) ' add quotes around "text"
sc.ExecuteStatement ("import string")
CenterText = sc.Eval("string.center(" & text & ", " & width & ")")
End Function

And of course, the "import string" should come much earlier, on the load of the workbook, so that we don't need to import every single time.

Is there anything you would like to accomplish in Excel, but don't know how? Ask away!