locked
for loop-store inputbox values in its own variables RRS feed

  • Question

  • Hello vba fellows

    I am working on a word macro that should do the following:

    1.prompt the user 3 times for a word storing each word in its own variable

    2.if the 1st, 2nd, 3rsd word was found highlight it in any color

    3. word matching should be case Insensitive - word is the same as WORD

     

    I'm stuck at the beginning where each word should be stored in its own variable, I have the following code that is not working out as intended

    basically I am trying to concatenate the variable "word" with the current i counter variable number so that each word gets stored in its own variable but for a extraneous reason I keep on getting a compilation error  :

     

    Sub Highlight()

    Dim word1, word2, word3 As String

    For i = 1 To 3
    Word & i = InputBox("enter 3 non-spaced words", "gimme 3 words")
    Next i


    End Sub

    Wednesday, September 7, 2011 3:34 PM

Answers

  • Hi there. Yes, as you are finding, you can't dynamically construct the name of a variable. What you want to use instead is an Array - a list of invidual value all contained in one named variable. You get the individual elements by calling the variable name plus the index of the element in parenthesis. This should be familiar from the way you get a particular item out of many built-in collections in VBA.  For example, to get the first document that is currently open you would use Documents(1), not "Documents & 1".

    Check this out for an introduction to Arrays in VBA: Using Arrays

    An even more convenient option is to use the special VBA Collection variable type. Collections make it easy to grow or shrink the size of the collection at will, and to add or get elements with or without using the index numbers. One important difference: Remember that arrays by default always start with index 0, while Collections start with index 1.

    So here's how you would get three words from your user and store them in a Collection:

    Sub GetWords()
        'Declare the collection
        Dim words As New Collection
        
        'Get some words
        For i = 1 To 3
            words.Add InputBox("Enter word #" & i)
        Next
    
        'Show the words - using for each
        For Each wordItem In words
            Debug.Print wordItem
        Next
        
        'Show the words - using the index
        For i = 1 To 3
            Debug.Print words(i)
        Next
    
    End Sub
    


    To find words, you use the Find object. It allows you to specificy many different options for what to look for, then you use the Execute method to actually look for the next match. If a match is found, the Found property will be true. See the examples in Finding and Replacing Text or Formatting.

    To actually apply the highlighting, you use the Range.HighlightColorIndex property.

    Putting it together, here is a function that highlights all the matches for a word in a document:

    Sub HighlightMatches(ByVal inDocument As Document, _
                         ByVal wordToFind As String, _
                         ByVal highlightColor As WdColorIndex)
        
        inDocument.Activate
        
        'Go to the top of the document
        Selection.GoTo What:=wdGoToLine, Count:=1
    
        'Set the search parameters
        With Selection.Find
            .ClearFormatting
            .Text = wordToFind
            .MatchCase = False
            .Forward = True
        End With
        
        'Loop through all the matches
        Do
            Selection.Find.Execute
            If Selection.Find.Found Then
                'Set the highlight color
                Selection.Range.HighlightColorIndex = highlightColor
            End If
        Loop While Selection.Find.Found
    
    End Sub
    

    Here's how you might use it:

    Sub TestHighlight()
        HighlightMatches ThisDocument, "word", wdYellow
    End Sub
    

    The results:

    Finally, to get words, find them, and highlight them:

    Sub FindAndHighlightWords()
        Dim words As New Collection
        
        For i = 1 To 3
            words.Add InputBox("Enter word #" & i)
        Next
    
        For Each wordItem In words
            Debug.Print "Finding and highlighting '" & wordItem & "'"
            HighlightMatches ThisDocument, wordItem, wdYellow
        Next
    End Sub
    

     


    jmh
    • Proposed as answer by Jacob Wagner Wednesday, September 7, 2011 5:30 PM
    • Marked as answer by flashy_lord Wednesday, September 7, 2011 5:42 PM
    Wednesday, September 7, 2011 4:36 PM

All replies

  • Hi there. Yes, as you are finding, you can't dynamically construct the name of a variable. What you want to use instead is an Array - a list of invidual value all contained in one named variable. You get the individual elements by calling the variable name plus the index of the element in parenthesis. This should be familiar from the way you get a particular item out of many built-in collections in VBA.  For example, to get the first document that is currently open you would use Documents(1), not "Documents & 1".

    Check this out for an introduction to Arrays in VBA: Using Arrays

    An even more convenient option is to use the special VBA Collection variable type. Collections make it easy to grow or shrink the size of the collection at will, and to add or get elements with or without using the index numbers. One important difference: Remember that arrays by default always start with index 0, while Collections start with index 1.

    So here's how you would get three words from your user and store them in a Collection:

    Sub GetWords()
        'Declare the collection
        Dim words As New Collection
        
        'Get some words
        For i = 1 To 3
            words.Add InputBox("Enter word #" & i)
        Next
    
        'Show the words - using for each
        For Each wordItem In words
            Debug.Print wordItem
        Next
        
        'Show the words - using the index
        For i = 1 To 3
            Debug.Print words(i)
        Next
    
    End Sub
    


    To find words, you use the Find object. It allows you to specificy many different options for what to look for, then you use the Execute method to actually look for the next match. If a match is found, the Found property will be true. See the examples in Finding and Replacing Text or Formatting.

    To actually apply the highlighting, you use the Range.HighlightColorIndex property.

    Putting it together, here is a function that highlights all the matches for a word in a document:

    Sub HighlightMatches(ByVal inDocument As Document, _
                         ByVal wordToFind As String, _
                         ByVal highlightColor As WdColorIndex)
        
        inDocument.Activate
        
        'Go to the top of the document
        Selection.GoTo What:=wdGoToLine, Count:=1
    
        'Set the search parameters
        With Selection.Find
            .ClearFormatting
            .Text = wordToFind
            .MatchCase = False
            .Forward = True
        End With
        
        'Loop through all the matches
        Do
            Selection.Find.Execute
            If Selection.Find.Found Then
                'Set the highlight color
                Selection.Range.HighlightColorIndex = highlightColor
            End If
        Loop While Selection.Find.Found
    
    End Sub
    

    Here's how you might use it:

    Sub TestHighlight()
        HighlightMatches ThisDocument, "word", wdYellow
    End Sub
    

    The results:

    Finally, to get words, find them, and highlight them:

    Sub FindAndHighlightWords()
        Dim words As New Collection
        
        For i = 1 To 3
            words.Add InputBox("Enter word #" & i)
        Next
    
        For Each wordItem In words
            Debug.Print "Finding and highlighting '" & wordItem & "'"
            HighlightMatches ThisDocument, wordItem, wdYellow
        Next
    End Sub
    

     


    jmh
    • Proposed as answer by Jacob Wagner Wednesday, September 7, 2011 5:30 PM
    • Marked as answer by flashy_lord Wednesday, September 7, 2011 5:42 PM
    Wednesday, September 7, 2011 4:36 PM
  •  

    thanks buddy for such helpful reply, now I have to validate each entered word as the user hits ok to enter the next word.

    the validation criteria is as following:

    1: if the user leaves the inputbox empty or types a bunch of spaces, give him a message box saying "word can't be blank or be all spaces"

    2. each word must be a single word, if a word is composed by two or more words ex: "john smith" display a message saying" words can't contain multiple strings" and return execution to the current inputbox

     

    for this I have put up a do...loop while loop but is still clumpsy

     

    the part where I have no total idea how to do is where I have to check for spaces between words. Im assuming I have to use the space() function but then again Im not quite sure

     

    thanks for your time

     

    here is the code snippet

     

    Sub Highlight()
    '
    ' Highlight Macro
    '
    Dim myarray(3) As String
    
    
    
    For i = 1 To 3
    Do
    myarray(i) = InputBox("enter 3 non-spaced word" & " word# " & i, "gimme words")
    If myarray(i) = "" Then
    MsgBox "words can't be all spaces "
    End If
    
    Loop While myarray(1) <> ""
    Next i
    
    
    
    End Sub
    



    • Edited by flashy_lord Wednesday, September 7, 2011 5:56 PM
    • Proposed as answer by Joshua Honig Wednesday, September 7, 2011 6:56 PM
    • Unproposed as answer by Joshua Honig Wednesday, September 7, 2011 6:56 PM
    Wednesday, September 7, 2011 5:53 PM
  • You're on the right track. The easiest ways to check for one contiguous word would be to use the Trim and InStr functions. Use Trim() to remove leading and trailing spaces from a string, and InStr to look for spaces inside a string. It might be clearer, too, to use a temporary variable for getting and testing the input.

    Sub FindAndHighlightWords()
        Dim words(2) As String, tmpString As String, isValid As Boolean, errMsg As String
        
        For i = 1 To 3
            isValid = False
            Do Until isValid
                tmpString = Trim(InputBox("Enter word #" & i))
                If Len(tmpString) = 0 Then
                    errMsg = "Blank words not accepted"
                ElseIf InStr(tmpString, " ") > 0 Then
                    errMsg = "You must enter a single word without spaces"
                Else
                    isValid = True
                End If
                If Not isValid Then MsgBox errMsg
            Loop
            words(i - 1) = tmpString
        Next
    
        For Each wordItem In words
            Debug.Print "Finding and highlighting '" & wordItem & "'"
            HighlightMatches ThisDocument, wordItem, wdYellow
        Next
    End Sub
    

     


    jmh
    Wednesday, September 7, 2011 6:56 PM
  • Dear Jmh

    I found and implemented a simpler way to get the code work partially in terms of asking for 3 words and giving a error message if the words were empty, spaces or multiple strings I also managed to use the selection.find object method to find words I only have one little inconvenience before I get my project to be finished at the end I have to display a message box saying :

    "word1 had 10 matches"

    "word2 had 0 matches"

    word3 had 2 matches"

     

    basically the messagebox has to display the total count of matches for each of the three words

     

    thank you so much for your time & help I would have been lost with out your help, I hope I am not imposing you in any way

     

    best regards.

     

    this is my code snippet:

     

    Option Base 1
    Sub Highlight()
    ' Highlight Macro
    Dim word1, word2, word3 As String
    
    wrd1:
    word1 = Trim(InputBox("enter a non-spaced word #1", "gimme words"))
    If word1 = "" Then
        MsgBox ("word cannot be all spaces")
        GoTo wrd1
    ElseIf InStr(word1, " ") >= 1 Then
        MsgBox ("words cannot contain multiple strings")
        GoTo wrd1
    End If
    
    wrd2:
    word2 = Trim(InputBox("enter a non-spaced word #2", "gimme words"))
    If word2 = "" Then
        MsgBox ("word cannot be all spaces")
        GoTo wrd2
    ElseIf InStr(word2, " ") >= 1 Then
        MsgBox ("words cannot contain multiple strings")
        GoTo wrd2
    End If
    
    wrd3:
    word3 = Trim(InputBox("enter a non-spaced word #3", "gimme words"))
    If word3 = "" Then
        MsgBox ("word cannot be all spaces")
        GoTo wrd3
    ElseIf InStr(word3, " ") >= 1 Then
        MsgBox ("words cannot contain multiple strings")
        GoTo wrd3
    End If
    
    'go to the top of the document
    Selection.GoTo what:=wdGoToLine, Count:=1
    
    'set the search parameters for word1
    With Selection.Find
        .ClearFormatting
        .Text = word1
        .MatchCase = False
        .Forward = True
    End With
    
    Do
    Selection.Find.Execute
    If Selection.Find.Found Then
        Selection.Range.HighlightColorIndex = wdYellow
    End If
    Loop While Selection.Find.Found
    
    ' -----------------------------------------------------------------------------------------------------------
    'go to the top of the document
    Selection.GoTo what:=wdGoToLine, Count:=1
    
    'set the search parameters for word2
    With Selection.Find
        .ClearFormatting
        .Text = word2
        .MatchCase = False
        .Forward = True
    End With
    
    Do
    Selection.Find.Execute
    If Selection.Find.Found Then
        Selection.Range.HighlightColorIndex = wdTeal
    End If
    Loop While Selection.Find.Found
    '--------------------------------------------------------------------------------------------------------------
    'go to the top of the document
    Selection.GoTo what:=wdGoToLine, Count:=1
    
    'set the search parameters for word3
    With Selection.Find
        .ClearFormatting
        .Text = word3
        .MatchCase = False
        .Forward = True
    End With
    
    Do
    Selection.Find.Execute
    If Selection.Find.Found Then
        Selection.Range.HighlightColorIndex = wdPink
    End If
    Loop While Selection.Find.Found
    
    
    End Sub
    
    
    


    • Edited by flashy_lord Thursday, September 8, 2011 6:15 PM
    Thursday, September 8, 2011 4:33 PM
  •  I hope I am not imposing you in any way

    :D Not at all. I'm posting of my own volition.

    I would still strongly encourage you to use a loop structure for getting and using the words. That way you don't have to write and maintain three copies of the same code. If the only thing changing between the three words is the highlight color, you can still handle that in the loop.

    For counting the words, just make a variable and increment it by one every time you go through the Find.Execute loop...I've adjusted the code below to reflect your style more closely, but still keeping things in one big loop, using arrays for the words and highlight colors. Note you can make an array out of anything--including enumerations like WdColorIndex:

    Sub Highlight()
        Dim words(2) As String
        Dim colors(2) As WdColorIndex
    
        Dim tempWord As String
        Dim wordCount As Integer
        
        'Set your preferred highlight colors
        colors(0) = wdYellow
        colors(1) = wdTeal
        colors(2) = wdPink
    
        'First get all three words
        For i = 0 To 2
    GetWord:
            tempWord = Trim(InputBox("Enter a non-spaced word #" & (i + 1), "Gimme Words"))
            If tempWord = "" Then
                MsgBox "word cannot be all spaces"
                GoTo GetWord
            ElseIf InStr(tempWord, " ") >= 1 Then
                MsgBox "words cannot contain multiple strings"
                GoTo GetWord
            End If
            words(i) = tempWord
        Next
    
        'Now find and highlight all three words
        For i = 0 To 2
            'Go to the top of the document
            Selection.GoTo What:=wdGoToLine, Count:=1
        
            'Set the search parameters
            With Selection.Find
                .ClearFormatting
                .Text = words(i)
                .MatchCase = False
                .Forward = True
            End With
            
            'Reset the word counter
            wordCount = 0
            
            Do
                Selection.Find.Execute
                If Selection.Find.Found Then
                    Selection.Range.HighlightColorIndex = colors(i)
                    wordCount = wordCount + 1
                End If
            Loop While Selection.Find.Found
                
            MsgBox "Found " & wordCount & " instances of the word '" & words(i) & "'", vbOKOnly, "Found Words"
                
        Next
    
    End Sub
    

    The colorful result:


    jmh
    Thursday, September 8, 2011 6:46 PM