Array - Split Function

[dfpp]

Hello,

I'm trying to build a function that splits the nodes of a treeview in order to make it searchable by using the boolean terms AND, OR, and NOT.

The search is intended to be done by using just one textbox

So, here is what I have until now:

1. The event which loads the treeview and perform the search.

    [COLOR=#0066ff]Dim[/COLOR] booCurrMatch [COLOR=#0000ff]As[/COLOR] [COLOR=#6600ff]Boolean[/COLOR]
    [COLOR=#0066ff]Dim[/COLOR] booMatched [COLOR=#0000ff]As[/COLOR] [COLOR=#6600ff]Boolean[/COLOR]
    [COLOR=#0066ff]Dim[/COLOR] lngLoop [COLOR=#0000ff]As[/COLOR] [COLOR=#6600ff]Long[/COLOR]
    [COLOR=#0066ff]Dim[/COLOR] strOperator [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]String[/COLOR]
    [COLOR=#0066ff]Dim[/COLOR] strWord [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]String[/COLOR]
    [COLOR=#0066ff]Dim[/COLOR] varWords [COLOR=#0000ff]As[/COLOR] [COLOR=#6600ff]Variant[/COLOR]

    varWords = GetWords(Me.Text2.Text)
  
    [COLOR=#0066ff]If[/COLOR] [COLOR=#0000ff]Len[/COLOR](Me.Text2.Text & [COLOR=#888800]""[/COLOR]) > 0 [COLOR=#0000ff]Then[/COLOR]
        [COLOR=#0000ff]For[/COLOR] [COLOR=#0066ff]Each[/COLOR] nodNode In tv.Nodes
            booMatched = (InStr(1, nodNode.Text, varWords(1), vbTextCompare) > 0)
            [COLOR=#0066ff]If[/COLOR] UBound(varWords) > 0 [COLOR=#0000ff]Then[/COLOR]
                [COLOR=#0000ff]For[/COLOR] lngLoop = 1 [COLOR=#0000ff]To[/COLOR] UBound(varWords)
                    strOperator = Left(varWords(lngLoop), InStr(varWords(lngLoop), [COLOR=#888800]" "[/COLOR]) - 1)
                    strWord = [COLOR=#0000ff]Mid[/COLOR](varWords(lngLoop), InStr(varWords(lngLoop), [COLOR=#888800]" "[/COLOR]) + 1)
                        booCurrMatch = (InStr(1, nodNode.Text, strWord, vbTextCompare) > 0)
                        [COLOR=#0066ff]Select[/COLOR] [COLOR=#0066ff]Case[/COLOR] strOperator
                            [COLOR=#0066ff]Case[/COLOR] [COLOR=#888800]"And"[/COLOR]
                                booMatched = booMatched [COLOR=#bb00ff]And[/COLOR] booCurrMatch
                            [COLOR=#0066ff]Case[/COLOR] [COLOR=#888800]"Or"[/COLOR]
                                booMatched = booMatched [COLOR=#bb00ff]Or[/COLOR] booCurrMatch
                            [COLOR=#0066ff]Case[/COLOR] [COLOR=#888800]"Not"[/COLOR]
                                booMatched = booMatched [COLOR=#bb00ff]And[/COLOR] ([COLOR=#bb00ff]Not[/COLOR] booCurrMatch)
                        [COLOR=#0066ff]End[/COLOR] [COLOR=#0066ff]Select[/COLOR]
                 [COLOR=#0000ff]Next[/COLOR] lngLoop
            [COLOR=#0066ff]End[/COLOR] [COLOR=#0066ff]If
[/COLOR]

2. The code of the Function

[COLOR=#0066ff]Function[/COLOR] GetWords(Expression [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]String[/COLOR]) [COLOR=#0000ff]As[/COLOR] [COLOR=#6600ff]Variant[/COLOR]()
    [COLOR=#0066ff]Dim[/COLOR] vItem() [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]String[/COLOR]
    [COLOR=#0066ff]Dim[/COLOR] l [COLOR=#0000ff]As[/COLOR] [COLOR=#6600ff]Long[/COLOR]
    
    vItem = Split(Expression, [COLOR=#888800]" "[/COLOR])

    [COLOR=#0000ff]For[/COLOR] l = LBound(vItem) [COLOR=#0000ff]To[/COLOR] UBound(vItem)
    
    Debug.Print [COLOR=#888800]"vItem 0="[/COLOR] & vItem(0), l
    Debug.Print [COLOR=#888800]"vItem 1="[/COLOR] & vItem(1), l
    Debug.Print [COLOR=#888800]"vItem 2="[/COLOR] & vItem(2), l
    [COLOR=#0000ff]Next[/COLOR]

[COLOR=#0066ff]End[/COLOR] [COLOR=#0066ff]Function
[/COLOR]

3. The results in the immediate window

vItem 0=Sistema              0 
vItem 1=AND                  0 
vItem 2=Nervioso             0 
vItem 0=Sistema              1 
vItem 1=AND                  1 
vItem 2=Nervioso             1 
vItem 0=Sistema              2 
vItem 1=AND                  2 
vItem 2=Nervioso             2

As you may see, my big problem is building the array. varWords returns an Error 9 "Subscript out of range". I tried several ways to solve this error, unsuccesfuly.

I really appreciate any help or guidance.

Regards,

Diego

 

[wazz]

Function GetWords(Expression As String) As Variant()
    Dim vItem() As String
    Dim i As Integer
    
    vItem = Split(Expression, " ")
    For i = 0 To UBound(vItem)
        Debug.Print "vItem " & i & " = " & vItem(i)
    Next
End Function

 

[wazz]

and welcome to awf.

 

[dfpp]

Thank you very much wazz. This is much better, 'cause I get this in the immediate window

vItem 0 = Sistema
vItem 1 = AND
vItem 2 = Nervioso

However, I still get the same error on the same line

booMatched = (InStr(1, nodNode.Text, varWords(1), vbTextCompare) > 0)

The error points to varWords.

Thank you very much,

Diego

PS: Thank you for being here

 

[boblarson]

Well, I don't see Wazz's code using varWords at all. Did you use Wazz's code or did you modify yours using Wazz's code? Post again what you are currently using after you modified it.

 

[wazz]

i didn't really look at the rest of it.
the main thing is, you have sent anything back from the function.
but it seems to me you don't need the extra function at all.
instead of
varWords = GetWords(Me.Text2.Text)
just make it
varWords = Split(Me.Text2.Text)

- then you should dim i, as in the sample i gave above
- change varWords(1) to varWords(i)
- and move this
booMatched = (InStr(1, nodNode.Text, varWords(i), vbTextCompare) > 0)
inside one of the loops

If UBound(varWords) > 0 Then
booMatched = (InStr(1, nodNode.Text, varWords(1), vbTextCompare) > 0)
For lngLoop = 0 To UBound(varWords)

- split creates a zero-based, one-dimensional array.
- etc.
probably will need some more tweaking. hth.

 

[dfpp]

Thank you very much Wazz,

I'll try it right now.

I'll keep you posted.

Thanks again,

Diego

 

[dfpp]

Ok. Here we go. I followed your advice and finally I could move from that line

Now I got an Error 5 "Invalid procedure call or argument" on this line,

strOperator = Left(varWords(lngLoop), InStr(varWords(lngLoop), " ") - 1)

This is the code modified from your suggestions

    [COLOR=#0066ff]If[/COLOR] [COLOR=#0000ff]Len[/COLOR](Me.Text2.Text & [COLOR=#888800]""[/COLOR]) > 0 [COLOR=#0000ff]Then[/COLOR]
        [COLOR=#0000ff]For[/COLOR] [COLOR=#0066ff]Each[/COLOR] nodNode In tv.Nodes
        [COLOR=#0066ff]If[/COLOR] UBound(varWords) > 0 [COLOR=#0000ff]Then[/COLOR]
            booMatched = (InStr(1, nodNode.Text, varWords(i), vbTextCompare) > 0)
                [COLOR=#0000ff]For[/COLOR] lngLoop = 0 [COLOR=#0000ff]To[/COLOR] UBound(varWords)
                    strOperator = Left(varWords(lngLoop), InStr(varWords(lngLoop), [COLOR=#888800]" "[/COLOR]) - 1)
                    strWord = [COLOR=#0000ff]Mid[/COLOR](varWords(lngLoop), InStr(varWords(lngLoop), [COLOR=#888800]" "[/COLOR]) + 1)
                        booCurrMatch = (InStr(1, nodNode.Text, strWord, vbTextCompare) > 0)
                        [COLOR=#0066ff]Select[/COLOR] [COLOR=#0066ff]Case[/COLOR] strOperator
                            [COLOR=#0066ff]Case[/COLOR] [COLOR=#888800]"And"[/COLOR]
                                booMatched = booMatched [COLOR=#bb00ff]And[/COLOR] booCurrMatch
                            [COLOR=#0066ff]Case[/COLOR] [COLOR=#888800]"Or"[/COLOR]
                                booMatched = booMatched [COLOR=#bb00ff]Or[/COLOR] booCurrMatch
                            [COLOR=#0066ff]Case[/COLOR] [COLOR=#888800]"Not"[/COLOR]
                                booMatched = booMatched [COLOR=#bb00ff]And[/COLOR] ([COLOR=#bb00ff]Not[/COLOR] booCurrMatch)
                        [COLOR=#0066ff]End[/COLOR] [COLOR=#0066ff]Select[/COLOR]
                 [COLOR=#0000ff]Next[/COLOR] lngLoop
            [COLOR=#0066ff]End[/COLOR] [COLOR=#0066ff]If[/COLOR]

Best Regards,

Diego

 

[dfpp]

I'm sorry Bob, I just saw your message.

It seems that Wazz code cannot be used in my event, because it won't handle spaces.

This is my complete code (without the error handling) of the event that builds the treeview and search throughout its nodes.text by using a textbox:

[COLOR=#0000ff]Private[/COLOR] [COLOR=#0066ff]Sub[/COLOR] Text2_BeforeUpdate(Cancel [COLOR=#0000ff]As[/COLOR] [COLOR=#6600ff]Integer[/COLOR])

    [COLOR=#0066ff]Dim[/COLOR] rst [COLOR=#0000ff]As[/COLOR] DAO.Recordset
    [COLOR=#0066ff]Dim[/COLOR] tv [COLOR=#0000ff]As[/COLOR] TreeView
    [COLOR=#0066ff]Dim[/COLOR] nodNode [COLOR=#0000ff]As[/COLOR] Node
    [COLOR=#0066ff]Dim[/COLOR] nodFirstFind [COLOR=#0000ff]As[/COLOR] Node
    [COLOR=#0066ff]Dim[/COLOR] booCurrMatch [COLOR=#0000ff]As[/COLOR] [COLOR=#6600ff]Boolean[/COLOR]
    [COLOR=#0066ff]Dim[/COLOR] booMatched [COLOR=#0000ff]As[/COLOR] [COLOR=#6600ff]Boolean[/COLOR]
    [COLOR=#0066ff]Dim[/COLOR] lngLoop [COLOR=#0000ff]As[/COLOR] Long
    [COLOR=#0066ff]Dim[/COLOR] strOperator [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]String[/COLOR]
    [COLOR=#0066ff]Dim[/COLOR] strWord [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]String[/COLOR]
    [COLOR=#0066ff]Dim[/COLOR] varWords [COLOR=#0000ff]As[/COLOR] [COLOR=#6600ff]Variant[/COLOR]    

    [COLOR=#0000ff]Set[/COLOR] tv = Me.CUPStree.Object

Me.CUPStree.Nodes.Clear

  SetupTreeview
  CreateCapitulosCUPSNodes
  CreateGruposCUPSNodes
  CreateSubgruposCUPSNodes
  CreateCategoriasCUPSNodes
  CreateSubcategoriasCUPSNodes

  varWords = GetWords(Me.Text2.Text)  

  [COLOR=#0066ff]If[/COLOR] [COLOR=#0000ff]Len[/COLOR](Me.Text2.Text & [COLOR=#888800]""[/COLOR]) > 0 [COLOR=#0000ff]Then[/COLOR]
      [COLOR=#0000ff]For[/COLOR] [COLOR=#0066ff]Each[/COLOR] nodNode In tv.Nodes
          booMatched = (InStr(1, nodNode.Text, varWords(0), vbTextCompare) > 0)
          [COLOR=#0066ff]If[/COLOR] UBound(varWords) > 0 [COLOR=#0000ff]Then[/COLOR]
              [COLOR=#0000ff]For[/COLOR] lngLoop = 1 [COLOR=#0000ff]To[/COLOR] UBound(varWords)
                  strOperator = Left(varWords(lngLoop), InStr(varWords(lngLoop), [COLOR=#888800]" "[/COLOR]) - 1)
                  strWord = [COLOR=#0000ff]Mid[/COLOR](varWords(lngLoop), InStr(varWords(lngLoop), [COLOR=#888800]" "[/COLOR]) + 1)
                      booCurrMatch = (InStr(1, nodNode.Text, strWord, vbTextCompare) > 0)
                      [COLOR=#0066ff]Select[/COLOR] [COLOR=#0066ff]Case[/COLOR] strOperator
                          [COLOR=#0066ff]Case[/COLOR] [COLOR=#888800]"And"[/COLOR]
                              booMatched = booMatched [COLOR=#bb00ff]And[/COLOR] booCurrMatch
                          [COLOR=#0066ff]Case[/COLOR] [COLOR=#888800]"Or"[/COLOR]
                              booMatched = booMatched [COLOR=#bb00ff]Or[/COLOR] booCurrMatch
                          [COLOR=#0066ff]Case[/COLOR] [COLOR=#888800]"Not"[/COLOR]
                              booMatched = booMatched [COLOR=#bb00ff]And[/COLOR] ([COLOR=#bb00ff]Not[/COLOR] booCurrMatch)
                      [COLOR=#0066ff]End[/COLOR] [COLOR=#0066ff]Select[/COLOR]
               [COLOR=#0000ff]Next[/COLOR] lngLoop
          [COLOR=#0066ff]End[/COLOR] [COLOR=#0066ff]If[/COLOR]
          [COLOR=#0066ff]If[/COLOR] booMatched [COLOR=#0000ff]Then[/COLOR]
              nodNode.Expanded = [COLOR=#0000ff]True[/COLOR]
              nodNode.ForeColor = vbBlue
              nodNode.EnsureVisible
              [COLOR=#0066ff]If[/COLOR] nodFirstFind [COLOR=#0000ff]Is[/COLOR] [COLOR=#0000ff]Nothing[/COLOR] [COLOR=#0000ff]Then[/COLOR]
                  [COLOR=#0000ff]Set[/COLOR] nodFirstFind = nodNode
              [COLOR=#0066ff]End[/COLOR] [COLOR=#0066ff]If[/COLOR]
          [COLOR=#0000ff]Else[/COLOR]
              nodNode.Expanded = [COLOR=#0000ff]False[/COLOR]
              nodNode.ForeColor = vbBlack
          [COLOR=#0066ff]End[/COLOR] [COLOR=#0066ff]If[/COLOR]
      [COLOR=#0000ff]Next[/COLOR] nodNode
  [COLOR=#0066ff]End[/COLOR] [COLOR=#0066ff]If[/COLOR]
  [COLOR=#0066ff]If[/COLOR] [COLOR=#bb00ff]Not[/COLOR] nodFirstFind [COLOR=#0000ff]Is[/COLOR] [COLOR=#0000ff]Nothing[/COLOR] [COLOR=#0000ff]Then[/COLOR]
      nodFirstFind.Selected = [COLOR=#0000ff]True[/COLOR]
      nodFirstFind.EnsureVisible
  [COLOR=#0000ff]Else[/COLOR]
      [COLOR=#0066ff]If[/COLOR] [COLOR=#0000ff]Len[/COLOR](Me.Text2.Text & [COLOR=#888800]""[/COLOR]) > 0 [COLOR=#0000ff]Then[/COLOR]
          MsgBox [COLOR=#888800]"No se encuentra el procedimiento"[/COLOR], vbInformation
      [COLOR=#0066ff]End[/COLOR] [COLOR=#0066ff]If[/COLOR]
  [COLOR=#0066ff]End[/COLOR] [COLOR=#0066ff]If[/COLOR]
[COLOR=#0066ff]End[/COLOR] [COLOR=#0066ff]Sub
[/COLOR]

... and this is the code of my function

[COLOR=#0066ff]Function[/COLOR] GetWords(Expression [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]String[/COLOR]) [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]String[/COLOR]
    [COLOR=#0066ff]Dim[/COLOR] vItem() [COLOR=#0000ff]As[/COLOR] [COLOR=#6600ff]Variant[/COLOR]
    vItem = Split(Expression, [COLOR=#888800]" "[/COLOR])
    [COLOR=#0066ff]If[/COLOR] UBound(vItem) > 0 [COLOR=#0000ff]Then[/COLOR]
        GetWords = vItem(UBound(vItem))
    [COLOR=#0066ff]End[/COLOR] [COLOR=#0066ff]If[/COLOR]
[COLOR=#0066ff]End[/COLOR] [COLOR=#0066ff]Function
[/COLOR]

This latter code just returns a normal variable, not an array.

So, what do I have to do in order to get an array from this function?

Many thanks in advance.

Diego