Recent changes to wiki

Home modified by Eric Reynolds

2014-07-17T09:41:34.421000Z

--- v11
+++ v12
@@ -1 +1 @@
-For up-to-date documentation please visit the ExcelPython GitHub site - https://github.com/ericremoreynolds/excelpython/wiki
+For up-to-date documentation please visit the ExcelPython GitHub site - https://github.com/ericremoreynolds/excelpython

Home modified by Eric Reynolds

2014-06-16T07:42:29.699000Z

--- v10
+++ v11
@@ -1 +1 @@
-For up-to-date documentation please visit the ExcelPython GitHub site - https://github.com/ericremoreynolds/excelpython
+For up-to-date documentation please visit the ExcelPython GitHub site - https://github.com/ericremoreynolds/excelpython/wiki

Home modified by Eric Reynolds

2014-06-09T08:09:11.472000Z

--- v9
+++ v10
@@ -1,17 +1 @@
-Getting started
-===
-
-
-Installation
----
-
-ExcelPython is now packaged up in an installer which registers the relevant type libraries.
-
-
-Tutorials
----
-
-* [A very simple usage example]
-* [A more practical use of ExcelPython]
-* [Putting it all together]
-* [Lists and SAFEARRAYs]
+For up-to-date documentation please visit the ExcelPython GitHub site - https://github.com/ericremoreynolds/excelpython

Home modified by Eric Reynolds

2014-01-15T10:17:18.337000Z

--- v8
+++ v9
@@ -8,109 +8,10 @@
 ExcelPython is now packaged up in an installer which registers the relevant type libraries.

-A very simple usage example
+Tutorials
 ---

-First of all add a reference to the ExcelPython type library. To do this, open Excel, go to the VBA window (Alt-F11) and select Tools > References. Scroll down and select ExcelPythonXX where XX is 26 or 27 depending on which version of Python you have installed on your machine (note only Python 2.6 and 2.7 are supported currently). Click OK to get back to VBA.
-
-You can try out ExcelPython from the VBA Immediate Window (Ctrl + G if it's not already visible in the VBA window).
-
-Type the following
-
-    ?PyStr(PyEval("1+2"))
-
-and press return. You should see 
-
-    ?PyStr(PyEval("1+2"))
-    3
-
-You can try evaluating any Python expression, and you'll get the result
-printed in the Immediate Window.
-
-    ?PyStr(PyEval("[1,2,3]+[4,5,6]"))
-    [1, 2, 3, 4, 5, 6]
-
-(As a side note: why is the PyStr function needed? If you try just calling
-
-    ?PyEval("1+2")
-
-you'll get a type mismatch error. This is because the PyEval function
-returns a handle to the Python object itself, and VBA doesn't know how
-to print it to the Immediate Window. PyStr takes a Python object and
-calls the Python str function on it and returns a VBA string, which can
-be printed)
-
-The PyEval function takes as an optional second argument a locals dictionary to be used when evaluating the expression. To build the dictionary, you can use the function PyDict which takes alternating keys and values as arguments.
-
-    ?PyStr(PyEval("x+y", PyDict("x", 3, "y", 4)))
-    7
-    ?PyStr(PyEval("x+y", PyDict("x", "abc", "y", "def")))
-    abcdef
-
-
-A more practical use of ExcelPython
----
-
-The above example gives a light introduction to manipulating a few simple objects through VBA. In practice though, what's needed is a way to get a load of inputs from Excel, pass them to a method defined in a Python script somewhere, get the outputs back from Python and use them VBA or place them in the spreadsheet as required.
-
-The key to doing this are the methods PyModule and PyCall.
-
-PyModule returns a pointer to a Python module, much like the import statement. If no additional arguments are specified, the embedded Python interpreter will look for the requested module in the default search path.
-
-    ?PyStr(PyModule("datetime"))
-    
-
-If you want to call functions from a script which you have placed in a non-standard location, you can tell ExcelPython to add additional search directories before trying to load the module, as follows:
-
-    ?PyStr(PyModule("MyScript", AddPath:="D:\\Scripts"))
-    
-
-Once you have access to the module you want, you can use PyCall to invoke functions contained in the module (note that PyCall actually calls any method of any object, not just module objects). This is done by explicitly passing the ordered and keyword arguments. For example calling
-
-    ?PyStr(PyCall(PyModule("datetime"), "date", Args:=PyTuple(2013, 8, 9)))
-    2013-08-09
-
-is equivalent to calling `datetime.date(2013, 8, 9)`, whereas
-
-    ?PyStr(PyCall(PyModule("datetime"), _
-        "timedelta", Args:=PyTuple(3), KwArgs:=PyDict("milliseconds", 500)))
-    3 days, 0:00:00.500000
-
-is like calling `datetime.timedelta(3, milliseconds=500)`.
-
-Putting it all together
----
-
-Let's suppose we have the following script MyScript.py saved in the same folder as the workbook.
-
-    :::python
-    def MyFunction(x, y):
-      return {
-        "sum": x + y,
-        "sorted": sorted([x, y])
-      }
-
-The following VBA code can be used to call this function, taking the input parameters from a worksheet and pasting the outputs back to that sheet.
-
-    Function PyPath()
-        PyPath = ThisWorkbook.Path
-    End Function
-    
-    Sub CallPythonCode()
-        Set res = PyCall( _
-            PyModule("MyScript", AddPath:=PyPath), "MyFunction", _
-            KwArgs:=PyDict( _
-                "x", Sheet1.Range("A1").Value2, _
-                "y", Sheet1.Range("A2").Value2))
-        Sheet1.Range("A3").Value2 = PyVar(PyGetItem(res, "sum"))
-        Sheet1.Range("A4:B4").Value2 = PyVar(PyGetItem(res, "sorted"))
-    End Sub
-
-The function `PyPath` is a utility function which returns our additional module search path. It's set up so that to access Python scripts we just need to place them in the same folder as the workbook. It's worth defining this once in your workbook's VBA project for use wherever necessary.
-
-The calls to `PyModule` and `PyCall` have been explained above. Once the result dictionary is obtained from the function, its elements can be accessed using `PyGetItem`.
-
-Finally, we use `PyVar` to convert the Python objects into variants so that they can be pasted into worksheet ranges.
-
-
-    
+* [A very simple usage example]
+* [A more practical use of ExcelPython]
+* [Putting it all together]
+* [Lists and SAFEARRAYs]

Putting it all together modified by Eric Reynolds

2014-01-15T09:51:28.303000Z

Putting it all together

Let's suppose we have the following script MyScript.py saved in the same folder as the workbook.

def MyFunction(x, y):
  return {
    "sum": x + y,
    "sorted": sorted([x, y])
  }

The following VBA code can be used to call this function, taking the input parameters from a worksheet and pasting the outputs back to that sheet.

Function PyPath()
    PyPath = ThisWorkbook.Path
End Function

Sub CallPythonCode()
    Set res = PyCall( _
        PyModule("MyScript", AddPath:=PyPath), "MyFunction", _
        KwArgs:=PyDict( _
            "x", Sheet1.Range("A1").Value2, _
            "y", Sheet1.Range("A2").Value2))
    Sheet1.Range("A3").Value2 = PyVar(PyGetItem(res, "sum"))
    Sheet1.Range("A4:B4").Value2 = PyVar(PyGetItem(res, "sorted"))
End Sub

The function PyPath is a utility function which returns our additional module search path. It's set up so that to access Python scripts we just need to place them in the same folder as the workbook. It's worth defining this once in your workbook's VBA project for use wherever necessary.

The calls to PyModule and PyCall have been explained above. Once the result dictionary is obtained from the function, its elements can be accessed using PyGetItem.

Finally, we use PyVar to convert the Python objects into variants so that they can be pasted into worksheet ranges.

A more practical use of ExcelPython modified by Eric Reynolds

2014-01-15T09:50:54.158000Z

A more practical use of ExcelPython

The above example gives a light introduction to manipulating a few simple objects through VBA. In practice though, what's needed is a way to get a load of inputs from Excel, pass them to a method defined in a Python script somewhere, get the outputs back from Python and use them VBA or place them in the spreadsheet as required.

The key to doing this are the methods PyModule and PyCall.

PyModule returns a pointer to a Python module, much like the import statement. If no additional arguments are specified, the embedded Python interpreter will look for the requested module in the default search path.

?PyStr(PyModule("datetime"))
<module 'datetime' (built-in)\>

If you want to call functions from a script which you have placed in a non-standard location, you can tell ExcelPython to add additional search directories before trying to load the module, as follows:

?PyStr(PyModule("MyScript", AddPath:="D:\\Scripts"))
<module 'MyScript' from 'D:\\Scripts\\MyScript.py'\>

Once you have access to the module you want, you can use PyCall to invoke functions contained in the module (note that PyCall actually calls any method of any object, not just module objects). This is done by explicitly passing the ordered and keyword arguments. For example calling

?PyStr(PyCall(PyModule("datetime"), "date", Args:=PyTuple(2013, 8, 9)))
2013-08-09

is equivalent to calling datetime.date(2013, 8, 9), whereas

?PyStr(PyCall(PyModule("datetime"), _
    "timedelta", Args:=PyTuple(3), KwArgs:=PyDict("milliseconds", 500)))
3 days, 0:00:00.500000

is like calling datetime.timedelta(3, milliseconds=500).

A very simple usage example modified by Eric Reynolds

2014-01-15T09:49:51.499000Z

--- v2
+++ v3
@@ -0,0 +1,38 @@
+A very simple usage example
+---
+
+First of all add a reference to the ExcelPython type library. To do this, open Excel, go to the VBA window (Alt-F11) and select Tools > References. Scroll down and select ExcelPythonXX where XX is 26 or 27 depending on which version of Python you have installed on your machine (note only Python 2.6 and 2.7 are supported currently). Click OK to get back to VBA.
+
+You can try out ExcelPython from the VBA Immediate Window (Ctrl + G if it's not already visible in the VBA window).
+
+Type the following
+
+    ?PyStr(PyEval("1+2"))
+
+and press return. You should see 
+
+    ?PyStr(PyEval("1+2"))
+    3
+
+You can try evaluating any Python expression, and you'll get the result
+printed in the Immediate Window.
+
+    ?PyStr(PyEval("[1,2,3]+[4,5,6]"))
+    [1, 2, 3, 4, 5, 6]
+
+(As a side note: why is the PyStr function needed? If you try just calling
+
+    ?PyEval("1+2")
+
+you'll get a type mismatch error. This is because the PyEval function
+returns a handle to the Python object itself, and VBA doesn't know how
+to print it to the Immediate Window. PyStr takes a Python object and
+calls the Python str function on it and returns a VBA string, which can
+be printed)
+
+The PyEval function takes as an optional second argument a locals dictionary to be used when evaluating the expression. To build the dictionary, you can use the function PyDict which takes alternating keys and values as arguments.
+
+    ?PyStr(PyEval("x+y", PyDict("x", 3, "y", 4)))
+    7
+    ?PyStr(PyEval("x+y", PyDict("x", "abc", "y", "def")))
+    abcdef

ffgdgf modified by Eric Reynolds

2014-01-15T09:48:46.173000Z

A very simple usage example modified by Eric Reynolds

2014-01-15T09:48:31.019000Z

Home modified by Eric Reynolds

2013-08-09T14:16:13.134000Z

--- v7
+++ v8
@@ -106,9 +106,7 @@
         Sheet1.Range("A4:B4").Value2 = PyVar(PyGetItem(res, "sorted"))
     End Sub

-Let's go through this step-by-step.
-
-The function `PyPath` is a utility function which returns our additional module search path. It's set up so that to access Python scripts we just need to place them in the same folder as the workbook.
+The function `PyPath` is a utility function which returns our additional module search path. It's set up so that to access Python scripts we just need to place them in the same folder as the workbook. It's worth defining this once in your workbook's VBA project for use wherever necessary.

 The calls to `PyModule` and `PyCall` have been explained above. Once the result dictionary is obtained from the function, its elements can be accessed using `PyGetItem`.