Changing Code Modules, PLEASE HELP ASAP!! PART 2

[Brett Smith]

I posted a question before and it was answered for the most part. I need to
replace 6 strings all at once, but my code only replaces 2 of the 6
variables. How can I change all 6? Here is the code below. Any help would
be greatly appreciated. Thanks!!

Sub FileSearchforMacros2()

Dim i As Integer
Dim wkbkOne As Workbook

With Application.FileSearch
.LookIn = "\\rnumsriggap06\Abase401\Restempl\652"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
'MsgBox "There were " & .FoundFiles.Count & _
' "file(s) found."
For i = 1 To .FoundFiles.Count
'MsgBox .FoundFiles(i)
Set wkbkOne = Application.Workbooks.Open( _
.FoundFiles(i), , , , Password:=("INGRAM"))
ReplacecodeInModule wkbkOne
remove_xlfit3_ref wkbkOne
wkbkOne.Save
wkbkOne.Close
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub



------------------------------------------------------------------------------------------
Sub ReplacecodeInModule(RepWk As Workbook)
Dim myCode As String
Dim myFStr As Variant
Dim myRStr As Variant
Dim myMod As VBComponent

myFStr = Array("XLfit3_", "ExcludePoints", "IncludePoints",
"XLFitExcludePoint", "XLFitIncludePoint", "YRange")
myRStr = Array("xf4_", "ExcludePoint", "IncludePoint",
"xf4_ExcludePoint", "xf4_IncludePoint", "RangeY")

For Each myMod In RepWk.VBProject.VBComponents
With myMod.CodeModule

If .CountOfLines > 0 Then
myCode = .Lines(1, .CountOfLines)
If InStr(1, myCode, myFStr(i)) > 0 Then
'MsgBox myCode
myCode = Replace(myCode, myFStr(i), myRStr(i))
'MsgBox myCode

.DeleteLines 1, .CountOfLines
.InsertLines .CountOfLines + 1, myCode
End If
End If
End With
Next myMod
End Sub

 

[Brett Smith]

Oops, please take a look at this code as a replacement of ReplacecodeInModule
Sub below.


Sub ReplacecodeInModule(RepWk As Workbook, myFStr As Variant, myRStr As
Variant)
Dim myCode As String
'Dim myFStr As Variant
'Dim myRStr As Variant
Dim myMod As VBComponent

myFStr = Array("XLfit3_", "ExcludePoints", "IncludePoints",
"XLFitExcludePoint", "XLFitIncludePoint", "YRange")
myRStr = Array("xf4_", "ExcludePoint", "IncludePoint",
"xf4_ExcludePoint", "xf4_IncludePoint", "RangeY")

For Each myMod In RepWk.VBProject.VBComponents
With myMod.CodeModule

If .CountOfLines > 0 Then
myCode = .Lines(1, .CountOfLines)
If InStr(1, myCode, myFStr(i)) > 0 Then
'MsgBox myCode
myCode = Replace(myCode, myFStr(i), myRStr(i))
'MsgBox myCode

.DeleteLines 1, .CountOfLines
.InsertLines .CountOfLines + 1, myCode
End If
End If
End With
Next myMod
End Sub

 

[Dave Peterson]

I'm surprised that it did two.

This is untested, but it did compile.

Option Explicit
Sub ReplacecodeInModule(RepWk As Workbook)
Dim myCode As String
Dim myFStr As Variant
Dim myRStr As Variant
Dim myMod As VBComponent
Dim iCtr As Long

myFStr = Array("XLfit3_", "ExcludePoints", "IncludePoints", _
"XLFitExcludePoint", "XLFitIncludePoint", "YRange")
myRStr = Array("xf4_", "ExcludePoint", "IncludePoint", _
"xf4_ExcludePoint", "xf4_IncludePoint", "RangeY")


For Each myMod In RepWk.VBProject.VBComponents
With myMod.CodeModule
For iCtr = LBound(myFStr) To UBound(myFStr)
If .CountOfLines > 0 Then
myCode = .Lines(1, .CountOfLines)
If InStr(1, myCode, myFStr(iCtr)) > 0 Then
'MsgBox myCode
myCode = Replace(myCode, myFStr(iCtr), myRStr(iCtr))
'MsgBox myCode
.DeleteLines 1, .CountOfLines
.InsertLines .CountOfLines + 1, myCode
End If
End If
Next iCtr
End With
Next myMod
End Sub

And just to remember, your instr() is case sensitive as written and so is your
replace().

That might be important.

 

[Brett Smith]

I still wasn't able to get it to replace all of the text. Is there a reason
why it is not replacing the last 3 or 4 variable strings in the array?

 

[Dave Peterson]

Did the elements of the myFstr match the code--including upper/lower case?

 

[Edward Ulle]

Brett,

I must be missing something. Where is variable i set to a value.

If InStr(1, myCode, myFStr(i)) > 0 Then

Only the first comparison is made, are you sure the second comparison is
made?



*** Sent via Developersdex http://www.developersdex.com ***

 

[Edward Ulle]

Are you using Option Explicit? Always a good thing to do.


*** Sent via Developersdex http://www.developersdex.com ***

 

[Brett Smith]

Edward,
I never set variable i to any value. I just declared it.

 

[Brett Smith]

Hi Dave,
Not sure what you mean by your question. Are you asking if the program
caught the case specific variables in the code?

 

[Brett Smith]

Do you think Option Explicit could be the reason why my program is not
working as planned? I was thinking about that actually.

 

[Tim Williams]

If you just declare i and never set it to anything then its value will
always be 0

Tim

 

[Tim Williams]

I think Dave is asking whether the values in your array were correct
(including the case). In otherwords, are you sure the code is checking for
the correct strings in the opened workbook.

Tim

Hi Dave,
Not sure what you mean by your question. Are you asking if the program
caught the case specific variables in the code?

 

[Tim Williams]

No good reason not to use it. Will catch all undeclared variables and typos
in variable names.

Tim

 

[Edward Ulle]

Brett,

I think your problem is with i. You say you declare it but where? Not
in ReplacecodeInModule. If it is declared some where else as a public
variable then the value of i could be anything. The i in the main
module is local to that module not ReplacecodeInModule.

If it is not a public variable then i would be 0 in ReplacecodeInModule
and the comparison would only be performed for the first value in
myFStr(i=0).

Option Explicit requires that all variable be declared. If i had not
been declared outside of ReplacecodeInModule the compiler would have
complained.



*** Sent via Developersdex http://www.developersdex.com ***

 

[Dave Peterson]

Just to add to Tim's reply:

In your code, there will be a difference between

Brett
BRETT
brETT
bretT
brett

Case matters the way you wrote the code.

Hi Dave,
Not sure what you mean by your question. Are you asking if the program
caught the case specific variables in the code?

 

[Edward Ulle]

Brett,

It would seem to me you need to loop through myFStr to compare with each
value in ReplacecodeInModule the line

If InStr(1, myCode, myFStr(i)) > 0 Then

You say you declare but don't set i. I don't see that in your code.
Unless there is an i declared as a global variable i will be 0 and only
the first value of myFStr will compare. With Option Explicit unless i
is global the compiler would complain about i in ReplacecodeInModule.




*** Sent via Developersdex http://www.developersdex.com ***

 

[Brett Smith]

Tim, Edward;

Then I see where my problem lies. I missed one of the basic fundamentals of
declaring a variable. You must always declare it with a value, and I did
not. So, I will declare i as = 0, and then maybe the program will work. I
will also throw in Option Explicit.

 

[Tim Williams]

Brett,

You're using i as a loop counter when cycling through your arrays of search
strings. Your loop takes care of assigning the values.

I can help you out tomorrow if it's still not working. Drop me a line at
work.

Tim.