• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is it possible to use "list comprehension" in VBA?

shahin

shahin

Active Member
First off, sorry to everyone coming across this post as it may not be a core vba question. I was trying to figure out for the last few days whether there is any option in vba to create "LIST COMPREHENSION". But, I could not find any hint from anywhere. I tried something like below but that should never work as it is not the right way to do so. Can anyone tell me if I'm wasting my time to create something which is not possible in vba?

Tried something like below to get started:

Code: 
Sub Create_List()
    Dim I As Variant, ILst As Variant

    ILst = [I for I in [{1,2,3,4,5,6,7}]]
    Debug.Print ILst
End Sub
 

As your code has no sense on VBA side,
as Excel & VBA have list features and EZ text functions,
better is to crystal clear explain your thought !
​
 
@sir Hui, thanks for shading some light on this. I'll surely get back to you when I'm done going through the links.
 
I think you can use Collections or Dictionaries to replicate the performance you are seeking
 
List Comprehension is python specific way to define and create list. So the answer is no, you can't do it in VBA.

Though you can certainly create list/array using VBA matching those generated via python, method through which these are generated will differ from python.

Edit: For clarity.
 
Thanks sir chihiro, for the clarity. I needed to know this very much. You just saved me a lot of time.
 
I read some documentation here:
http://www.pythonforbeginners.com/basics/list-comprehensions-in-python

In certain contexts, you may achieve similar behavior using Filter like below:
Code: 
Public Sub ListComprehension()
Dim SourceArray As Variant, ComprList As Variant
SourceArray = Array("1a", "2b", "3a", "4b", "5a", "6b", "7a", "8b", "9a", "10b")
ComprList = Filter(SourceArray, "a", True, vbTextCompare)
End Sub

ComprList will give results of matched strings where "a" could be found. It unfortunately doesn't allow a conditional test other than presence of string.
 

Maybe it's possible, as I'm always expecting an explanation of the need !
Maybe it's already in this forum ! Just with a crystal clear sample …​
 
@shrivallabha
Actually list comprehension is complete substitute of filter function and other function sets (Lambda, map, filter, reduce).

Ex: list comprehension for Pythagorean triples:
Code: 
[(x,y,z) for x in range(1,30) for y in range(x,30) for z in range(y,30) if x**2 + y**2 == z**2]

I'd imagine it may be possible to write custom class module to imitate this. But I see no benefit other than satisfying academic curiosity. ;)
 
I'm pretty sure you could use some collections in similar way, not as tidy as Python, but still get the same results
 
I think using power query is another option. It might be quicker and natively supports some of the array processing you get in R or Python.

For example, here is a query to generate all Pythagorean triplets between 2 numbers denoted by parameters or queries named from and to.

Code: 
let
    Source = List.Numbers(from,to),
    Custom1 = Table.FromColumns({{Source}, {Source}, {Source}},{"x","y","z"}),
    #"Expanded x" = Table.ExpandListColumn(Custom1, "x"),
    #"Expanded y" = Table.ExpandListColumn(#"Expanded x", "y"),
    #"Expanded z" = Table.ExpandListColumn(#"Expanded y", "z"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded z",{{"x", Int64.Type}, {"y", Int64.Type}, {"z", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([z]*[z] = [x]*[x] + [y]*[y]))
in
    #"Filtered Rows"

Keep in mind that this approach is not generic. So you will have to write appropriate queries + filter logic for each scenario.
 
Just as sample, Pythagorean triples in VBA.
Code: 
Sub Test()
Dim x As Long, y As Long, z As Long, s As Long, l As Long
Dim xDic As Object
Dim res
s = 1: l = 30
Set xDic = CreateObject("Scripting.Dictionary")

For x = s To l - 1
    For y = s To l - 1
        For z = s To l - 1
            If y > x And z > y Then
                If (x * x + y * y) = z * z Then
                    xDic(x) = y & "," & z
                End If
            End If
        Next
    Next
Next

i = 1
ReDim res(1 To xDic.Count, 1 To 3)
For Each Key In xDic.Keys
    res(i, 1) = Key
    res(i, 2) = Split(xDic(Key), ",")(0)
    res(i, 3) = Split(xDic(Key), ",")(1)
    i = i + 1
Next
Range("A1").Resize(UBound(res), 3) = res
End Sub
 
Found error in logic, in above. Should not have used dictionary and should have used collection.

Amended code:
Code: 
Sub Test()
Dim x As Long, y As Long, z As Long, s As Long, l As Long, j as Long
Dim coll As New Collection
Dim res, a
s = 1: l = 40

For x = s To l - 1
    For y = s To l - 1
        For z = s To l - 1
            If y > x And z > y Then
                If (x * x + y * y) = z * z Then
                    coll.Add x & "," & y & "," & z
                End If
            End If
        Next
    Next
Next

ReDim res(1 To coll.Count, 1 To 3)
For i = 1 To coll.Count
    a = Split(coll(i), ",")
    For j = 0 To 2
        res(i, j + 1) = a(j)
    Next
Next
Range("A1").Resize(UBound(res), 3) = res
End Sub
 
You must log in or register to reply here.
Back
Top