How do I iterate through a list with an unknown length?

[christopher white]

In the examples you give a way to set the cell values from a list:
Sheet1.Range("A4:B4").Value2 = PyVar(PyGetItem(res, "sorted"))

This assumes you know the length of the list and also populates horizontally, not vertically. I am trying to iterate through a list and then setting the cell values. I would like to have A1 be the first value, A2 the second, and so on.

1. Set res = PyCall(PyModule("myfunctions", AddPath:="C:\blah"), "stuff")
   2: Dim i As Integer

3: ll = PyVar(PyGetItem(res, "length")) 'seems weird to calc length in python

  For i = 1 To ll:

4: Sheet1.Range("A" & i).Value2 = PyVar(PyGetItem(res, "values"))
Next

2 Problems:

  1. This just fills column A with "3" (the first item in the list). 
  2. Also, Rather than pass the length of the list from python to VB I'd like to get the length from VB:
     ll = PyObj(PyGetItem(res, "values")).GetLength()

     Throws "Object Required" error.

I am a complete newb to VB so pardon my ignorance. I haven't been able to find a solution that works via googling.

[Eric Reynolds]

Hi Christopher.

At present there aren't any functions to facilitate iterator use though you have given me a good idea so I'll probably add it soon.

1.
The reason you're getting 3 in every column is that you're not actually extracting the i-th element from the list, you should be doing:

Sheet1.Range("A" & i).Value2 = PyVar(PyGetItem(PyGetItem(res, "values"), i))

2.
It's not clear to me why you're expecting

PyObj(PyGetItem(res, "values")).GetLength()

to work. However you can do

ll = PyLen(PyGetItem(res, "values"))

[christopher white]

It works. Thanks! I didn't know about PyLen. Is that documented anywhere? Even a quick list of everything available would be nice: PyObj, PyVar, PyLen, etc.

[Eric Reynolds]

Hi
I have just uploaded version 1.0.2 which has two new functions for iterating over sequences. You can now do the following:

Set L = PyList(1, 2, 3)
Set L_iter = PyIter(L)
While PyNext(L_iter, e)
    MsgBox PyStr(e)
Wend

Set d = PyDict("a", 1, "b", 2)
Set d_iter = PyIter(PyCall(d, "iteritems"))
While PyNext(d_iter, kv)
    MsgBox PyStr(PyGetItem(kv, 0)) + " = " + PyStr(PyGetItem(kv, 1))
Wend

There is no separate documentation for ExcelPython because you COM type libraries are pretty explorable anyway.

From the VBA window go to View > Object Browser and select the ExcelPython27 library. You can see all the functions with short descriptions of what they do. Most of it is pretty self-explanatory if you know Python anyway.

[atrulz2]

Hi Eric,

How can I iterate over a dictionary of unknown keys,

for example, in this code

PyVar(PyGetItem(PyGetItem(res, "name"), i))

I am assuming "name" is a key in the python dictionary. What if i don't know the key, in my case its the order_ids, which I want to iterate over and save them to excel and then iterate over the respective values associated with those order_ids.

Hope it all makes sense.

Thanks for your help !

[Eric Reynolds]

Hi,

I gave the example in my post just above yours (2014-03-19). It's the second half of the code. That VBA code is equivalent to the following python code:

d = { 'a': 1, 'b': 2 }
for k, v in d.iteritems():
    print k, "=", v

which is how you'd solve your problem in Python.

[atrulz2]

brilliant Eric, works just fine.

Just one more question. How do I translate the following python code in excel.

d =  {'n':['22','42','12'],'c':['a','b','c']}   
for k, v in d.iteritems():
    print k    
    for e in v:    
        print e

Thanks, :)

[Eric Reynolds]

Set d = PyDict("n", PyList("22", "42", "12"), "c", PyList("a", "b", "c"))
Set d_iter = PyIter(PyCall(d, "iteritems"))
While PyNext(d_iter, kv)
    Set k = PyGetItem(kv, 0)
    Set v = PyGetItem(kv, 1)
    MsgBox PyStr(k)
    Set v_iter = PyIter(v)
    While PyNext(v_iter, e)
        MsgBox PyStr(e)
    Wend
Wend

[atrulz2]

wonderful cheers !