Search a Word with VBA

K

knowledge76

Registered User.
Local time
Today, 22:18
Joined
Jan 20, 2005
Messages
165
Hello Friends,
I want to write a code in VBA that should search some specific words in the modules and there should be also an option to replace that words with another words. Can someone help me with the Code?
 
Last edited:
Can I create a macro which can list number of words searched by the Code and in which Module they are present.
 
Sorry I am not very good in VBA so how can I modify the code given below to search for a word say "Record" in all the modules in my database.
Code: 
Sub CheckSpacesInModules()

    On Error GoTo Err_CheckSpacesInModules

    Dim lngCounterA As Long, lngCounterB As Long
    Dim modModule As Module

    For lngCounterA = 0 To Modules.Count - 1
        Set modModule = Modules.Item(lngCounterA)
        With modModule
            For lngCounterB = 1 To .CountOfLines
                If Trim(.Lines(lngCounterB, 1)) = "" Then
                    .ReplaceLine lngCounterB, ""
                End If
            Next lngCounterB
        End With
    Next lngCounterA
    
Exit_CheckSpacesInModules:
    Exit Sub
    
Err_CheckSpacesInModules:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_CheckSpacesInModules
    
End Sub
 
Last edited:
Change the if in the middle to:
Code: 
If Trim(.Lines(lngCounterB, 1)) Like "Replace" Then
'(.Lines(lngCounterB, 1)) contains 'Replace' some where in it
End If
You could then use the Replace() function to replace the words. If A97 then you will need to find a VBA equivalent of the replace function.
 
Last edited:
cable said:
Change the if in the middle to:
Code: 
If Trim(.Lines(lngCounterB, 1)) Like "Replace" Then
'(.Lines(lngCounterB, 1)) contains 'Replace' some where in it
End If
You could then use the Replace() function to replace the words. If A97 then you will need to find a VBA equivalent of the replace function.
Click to expand...

I would disagree with cable with the use of the Like operator since you should only use it in conjunction with a wildcard operator - these being * or ?.

Code: 
If Trim(.Lines(lngCounterB, 1)) = "Replace" Then
'(.Lines(lngCounterB, 1)) contains 'Replace' some where in it
End If
 
Last edited:
True I wouldn't use Like in that exact case either, but from the OP's original msg he's going want to end up looking for words mid line, so you would need Like "*word*" or similar.
 
Thanks friends the code is working and now If i have to search ten different words what would I do to search these words one after another.I want that the macro should run this function by clicking on it.But when i set the runcode property of macro to CheckSpacesInModules(), an error comes and says the function you have entered is nor found. How should I handle it?
 
Last edited:
Can anyone tell me how to get the name of Module that is affected?
 
knowledge76 said:
Can anyone tell me how to get the name of Module that is affected?
Click to expand...

Knowledge, search this forum for ProcOfLine ... you'll find that without the line number, it is will not be that easy of a task.
 
Now I am getting the name of just first Module with the given Code, bu the word is also present in another module.How can I get the name of the next module/s?
Code: 
Option Compare Database

Sub CheckSpacesInModules()

    On Error GoTo Err_CheckSpacesInModules

    Dim lngCounterA As Long, lngCounterB As Long
    Dim modModule As Module

    For lngCounterA = 0 To Modules.Count - 1
        Set modModule = Modules.Item(lngCounterA)
        With modModule
            For lngCounterB = 1 To .CountOfLines
                If Trim(.Lines(lngCounterB, 1)) = "arnum12" Then
                    .ReplaceLine lngCounterB, "arnum"
                    
                End If
            Next lngCounterB
        End With
        MsgBox Application.VBE.ActiveCodePane.CodeModule, vbInformation
        
    Next lngCounterA

Exit_CheckSpacesInModules:
    Exit Sub

Err_CheckSpacesInModules:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_CheckSpacesInModules

End Sub
 
With the code given down all the Modules in the database will be shown.In the last post the code was doing the replacement of words.How can I combine the code to show only the modules in which the word is being replcaed?
Code: 
Public Function ListAllProcedures()

    On Error Resume Next
    Dim obj As Object
    Dim i As Integer
    Dim j As Long
    Dim RetVar As Variant
    
    'For Each obj In CurrentProject.allforms
    '   DoCmd.OpenForm obj.Name, acDesign
    '   If Forms(obj.Name).HasModule = True Then
    '       AllProcs ("Form_" & obj.Name)
    '   End If
    'Next
    
    'For Each obj In CurrentProject.AllReports
    '   DoCmd.OpenReport obj.Name, acDesign
    '   If Reports(obj.Name).HasModule = True Then
    '       AllProcs ("Report_" & obj.Name)
    '   End If
    'Next
    
    For i = 0 To CodeDb.Containers("Modules").Documents.Count - 1
        RetVar = AllProcs(CodeDb.Containers("Modules").Documents(i).Name)
    Next i

End Function


Public Function AllProcs(strModuleName As String)
   
   Dim mdl As Module
   Dim lngCount As Long, lngCountDecl As Long, lngI As Long
   Dim strProcName As String, astrProcNames() As String
   Dim intI As Integer
   Dim lngR As Long
   
   Dim intBlankLineCount As Integer

   ' Open specified Module object.
   DoCmd.OpenModule strModuleName
   ' Return reference to Module object.
   Set mdl = Modules(strModuleName)
   ' Count lines in module.
   lngCount = mdl.CountOfLines
   ' Count lines in Declaration section in module.
   lngCountDecl = mdl.CountOfDeclarationLines
   ' Determine name of first procedure.
   strProcName = mdl.ProcOfLine(lngCountDecl + 1, lngR)
   ' Initialize counter variable.
   intI = 0
   ' Redimension array.
   ReDim Preserve astrProcNames(intI)
   ' Store name of first procedure in array.
   astrProcNames(intI) = strProcName
   ' Determine procedure name for each line after declarations.
   For lngI = lngCountDecl + 1 To lngCount
       ' Compare procedure name with ProcOfLine property value.
       If strProcName <> mdl.ProcOfLine(lngI, lngR) Then
           ' Increment counter.
           intI = intI + 1
           strProcName = mdl.ProcOfLine(lngI, lngR)
           ReDim Preserve astrProcNames(intI)
           ' Assign unique procedure names to array.
           astrProcNames(intI) = strProcName
       End If
   Next lngI
   
   For intI = 0 To UBound(astrProcNames)
       Debug.Print strModuleName & " - " & astrProcNames(intI)
   Next intI
   
End Function
 
Searching words in a Module

I have the following code to search for a word and to show how many times it came in a specific Module.How can I modify the Code to search for two words a a same time in a module and to show how many time the both words came in the Modules?
Code: 
Sub CheckSpacesInModules()
On Error GoTo Err_CheckSpacesInModules
    Dim lngCounterA As Long, lngCounterB As Long
    Dim modModule As Module
    Dim zahl       
    For lngCounterA = 0 To Modules.count - 1
        Set modModule = Modules.Item(lngCounterA)
        zahl = 0
        With modModule
            For lngCounterB = 1 To .CountOfLines
                If Trim(.Lines(lngCounterB, 1)) = "New york" Then
                    '.ReplaceLine lngCounterB, "Washington"
                    zahl = zahl + 1
                End If
            Next lngCounterB
        End With

        MsgBox "New York kam im Modul " & modModule & " " & zahl & " mal vor."

    Next lngCounterA
    
Exit_CheckSpacesInModules:
    Exit Sub
Err_CheckSpacesInModules:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_CheckSpacesInModules
End Sub
 
umm... by running the code twice? have a second function that sits on top of this one that takes two words and just calls your code twice, once for each word.
 
workmad3 said:
umm... by running the code twice? have a second function that sits on top of this one that takes two words and just calls your code twice, once for each word.
Click to expand...

I was trying it but it is not happening, can you help me with the Code. Could you send me the sample how I should do it.
 
hmm... ok

first off, you really need to make the first function a bit more general purpose.

Code: 
Public Sub CheckSpacesInModules(target As String)
On Error GoTo Err_CheckSpacesInModules
    Dim lngCounterA As Long, lngCounterB As Long
    Dim modModule As Module
    Dim zahl       
    For lngCounterA = 0 To Modules.count - 1
        Set modModule = Modules.Item(lngCounterA)
        zahl = 0
        With modModule
            For lngCounterB = 1 To .CountOfLines
                If Trim(.Lines(lngCounterB, 1)) = target Then
                    '.ReplaceLine lngCounterB, "Washington"
                    zahl = zahl + 1
                End If
            Next lngCounterB
        End With

        MsgBox target & " kam im Modul " & modModule & " " & zahl & " mal vor."

    Next lngCounterA
    
Exit_CheckSpacesInModules:
    Exit Sub
Err_CheckSpacesInModules:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_CheckSpacesInModules
End Sub

you can then make a second function like this

Code: 
Public sub chkMod2(target 1 as String, target 2 as String)
CheckSpacesInModules(target1)
CheckSpacesInModules(target2)
End Sub

Hope this helps a bit
 
Well I have discovered another Problem in the Code it searches for the word only, if it is standing alone in a line, what should I do to search this word when other words are also sharing one line.
 
good point.

try

If Trim(.Lines(lngCounterB, 1)) = "*" & target & "*" Then

as the line for checkng the line


also,
what is this line meant to be doing?
'.ReplaceLine lngCounterB, "Washington"
 
workmad3 said:
good point.

try

If Trim(.Lines(lngCounterB, 1)) = "*" & target & "*" Then

as the line for checkng the line


also,
what is this line meant to be doing?
'.ReplaceLine lngCounterB, "Washington"
Click to expand...
´This line is replacing the searched word with Washington. I tried your suggestion If Trim(.Lines(lngCounterB, 1)) = "*" & target & "*" Then
but it is not working.
 
You must log in or register to reply here.

Similar threads

H
Extract a variable number of parts of text from a string
2
Replies
26
Views
824
AHeyne
A
C
Format text between two words
Replies
8
Views
580
CJ_London
C
C
Solved Searching two words in one paragraph
Replies
4
Views
381
Capitala
C
K
Solved How to reinitialize a form with a fresh data-entry view?
2
Replies
32
Views
998
Minty
Minty
H
Problem with Search Boxes
2
Replies
24
Views
1,350
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom