Forum Discussion
Solved
Using new 'spill range' formulas in Excel VBA
I thought it would be useful to use the 'filter' function from Excel in an Excel VBA script. I had a table where I needed the results from one column, dependent on the value of the second column, me...
- I think you need to get rid of the square brackets in the string, the Evaluate function effectively replaces them
I couldn't get this very Pythonesque syntax to work, type mismatch error
Dim v As Variant
With Me.Controls(dropName)
.Clear
For Each v In [Filter(causes["ListValue"],causes["ListName"]=listName)]
.AddItem v
Next v
End WithI'm using this instead, which works but looks amateur as heck!
Dim LO As ListObject
Set LO = Sheets("Pick Lists").ListObjects("Causes")
With Me.Controls(dropName)
.Clear
For r = 1 To LO.ListRows.Count
If LO.Range.Cells(r + 1, 1).Value = listName Then
.AddItem LO.Range.Cells(r + 1, 2)
End If
Next r
End WithGet rid of the quotes, simply write it as if it were a regular Excel formula
- PS how would you achieve a result if the table and/or field names were dynamic?
Private Sub testfilter() Dim v As Variant, str As String str = "Shut Down Reason" For Each v In [filter(causes[listValue],Causes[ListName]=str)] Debug.Print v Next v End SubDoesn't seem to work if you have to use a variable as the condition.
- so how is that even working?
- The square brackets are shorthand for "Evaluate". So you could write the same formula as a string variable and use Evaluate on the string variable:
str = "[filter(causes[listValue],Causes[ListName]=str)]"
For Each v In EValuate(str)- That is achingly cool. Never knew that was a thing. Thank you.