Search a Word with VBA

[workmad3]

try

If Instr(Trim(.Lines(lngCounterB, 1)) ,target) Then
.Lines(lngCounterB, 1) = Replace(.Lines(lngCounterB, 1), target, "Washington")

 

[knowledge76]

unfortunately this is also not working.

 

[workmad3]

hmm
ok, try
If Instr(Trim(.Lines(lngCounterB, 1)) ,target) > 0 Then
Dim tempStr = Replace(.Lines(lngCounterB, 1), target, "Washington")
.ReplaceLine lngCounterB, tempStr

sorry for the amount of tries... im doing lookups on how 2 use these functions (silly memory) :/

 

[knowledge76]

I am doing the changes to following Code.Can you try on your computer, perhaps I am making a mistake which I am not seeing.

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

 

[workmad3]

Public Function checkarray(targets() As String, replaces() As String)
Dim i As Integer
Dim size As Integer
Dim results(UBound(targets))


size = UBound(targets)
If (UBound(targets) <> UBound(replaces)) Then
MsgBox "Arrays not equal in size"
Exit Function
End If

For i = 0 To size
results(i) = CheckSpacesInModules(targets(i), replaces(i))
Next i

checkarray = results
End Function


Public Function CheckSpacesInModules(target As String, replace 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 InStr(Trim(.Lines(lngCounterB, 1)), target) > 0 Then
                    Dim tempStr As String
                    tempStr = replace(.Lines(lngCounterB, 1), target, replace)
                    .ReplaceLine lngCounterB, tempStr
                    zahl = zahl + 1
                End If
            Next lngCounterB
        End With
        
        CheckSpacesInModules = zahl

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

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

This will do a find and replace, finding the string target and replacing it with replace

the other function will take an array of strings and will do a series of find and replaces with each of the corresponding replace strings

this then returns an array of results for all the operations

 

[workmad3]

ok... to get it to do what it was doing, you use
CheckSpacesInModules("New York", "Washington")

 

[knowledge76]

When I type in what you have written then I get error expected:=. Sorry I am beginner in VBA. Sorry for all my silly questions.
I run the Code with CheckSpacesInModules "New york","Washington" Then I get an error on the line
"tempStr = replace(.Lines(lngCounterB, 1), targets, replace)"
and it say Compile error Array expected.When I comment the line and then run it then I get an error "Cannot perform requested operation".

 

[workmad3]

that line should be:

tempStr = replace(.Lines(lngCounterB, 1), target, replace)

and try running this code to get it working

dim result as integer
result = CheckSpacesInModule("New York", "Washington")

and its ok to have questions and they arent silly. Everyone has to start somewhere(admittedly, i've been using VBA for about 2 weeks now, but have had about 2 years of experience of java and C, so have a good grounding for learning a language quickly)

 

[knowledge76]

Sorry But on my side it is not working. I am getting the same error.Is it working on your side?

 

[workmad3]

ah ha, problem found, and the problem was: im an idiot(well, really the problem was that id called a variable the same name as a function i was trying to use, but well)

change the first line to
Public Function CheckSpacesInModules(target As String, replace2 As String)

and then change the line

tempStr = replace(.Lines(lngCounterB, 1), target, replace)
to
tempStr = replace(.Lines(lngCounterB, 1), target, replace2)

that then works again on my computer. hope it works for you finally

 

[Mile-O]

Two threads merged. I don't know why you had to start a second one.

 

[knowledge76]

What references have you set.Tell me these and send me please the full code which is working because it is not working here.

 

[workmad3]

the code i have running is this

Option Compare Database

Public Sub test()
Dim result As Integer
result = CheckSpacesInModules("Washington", "Washington")


End Sub

Public Function checkarray(targets() As String, replaces() As String)
Dim i As Integer
Dim size As Integer
Dim results(UBound(targets))


size = UBound(targets)
If (UBound(targets) <> UBound(replaces)) Then
MsgBox "Arrays not equal in size"
Exit Function
End If

For i = 0 To size
results(i) = CheckSpacesInModules(targets(i), replaces(i))
Next i

checkarray = results
End Function


Public Function CheckSpacesInModules(target As String, replace2 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 InStr(Trim(.Lines(lngCounterB, 1)), target) > 0 Then
                    Dim tempStr As String
                    tempStr = replace(.Lines(lngCounterB, 1), target, replace2)
                    .ReplaceLine lngCounterB, tempStr
                    zahl = zahl + 1
                End If
            Next lngCounterB
        End With
        
        CheckSpacesInModules = zahl

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

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

references i have set are
Visual Basic For Applications
Microsoft Access 10.0 Object Library
MS DAO 3.6 Object Library
MS ActiveX Data Object (Multi Dimensional) 2.8 Object Libraryt
MS ActiveX Data Object 2.8 Object Library
MS ActiveX Data Object Recordset 2.8 Object Library
OLE Automation
MS Visual Basic for Applications Exstensibility 5.3

All of these were set up for me in the basic Access Install, except for the ActiveX data object libraries

I really dont know why its not working for you now. It all works fine for me :/

 

[knowledge76]

Thanks workmad it is working now and I am able to replace all the words.

 

[workmad3]

heh good, got there eventually.

just as a quick qeustion though. what is this code for? i mean, i can see it changes words in modules, but to what purpose?

 

[knowledge76]

It is first of all searches a word and then replaces it with another word. and if you just wanna search the word and dont wanna replace it then just leave the leave the second option blank.

 

[Mile-O]

I think workmad3 was asking why you would want to do this. Especially since it's much easier to use the Find & Replace method as found in the menu.

 

[Rich]

Yes but why would you want to replace words used in a Module?

 

[workmad3]

umm.. you want the replace term to be optional?

have you got that working yourself yet?

if not, heres an updated version

Public Function CheckSpacesInModules(target As String, Optional replace2 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 InStr(Trim(.Lines(lngCounterB, 1)), target) > 0 Then
                    If Not (replace2 = "") Then
                        Dim tempStr As String
                        tempStr = replace(.Lines(lngCounterB, 1), target, replace2)
                        .ReplaceLine lngCounterB, tempStr
                    End If
                    zahl = zahl + 1
                End If
            Next lngCounterB
        End With
        
        CheckSpacesInModules = zahl
        
        MsgBox target & " kam im Modul " & Modules.Item(modModule).Name & " " & zahl & " mal vor."

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

 

[knowledge76]

Thanks again.