Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't understand why I'm getting an argument not optional
I am using this program to replace text in other excel vba files. I don't
understand why it is highlighting the ReplacecodeInModule and saying , "Compile Error: Argument not optional". It worked before, but what happend now? I have the code below. Sub FileSearchforMacros2() Dim i As Integer Dim wkbkOne As Workbook With Application.FileSearch .LookIn = "C:\Documents and Settings\BSmith\Desktop\workfiles\changecode" .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 ReplacecodeInModule2 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, 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") myRStr = Array("xf4_", "ExcludePoint", "IncludePoint") 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 -------------------------------------------------------------------------------------------- Sub ReplacecodeInModule2(RepWk2 As Workbook, myFStr2 As Variant, myRStr2 As Variant) Dim myCode2 As String Dim myMod2 As VBComponent myFStr2 = Array("XLFitExcludePoint", "XLFitIncludePoint", "YRange") myRStr2 = Array("xf4_ExcludePoint", "xf4_IncludePoint", "RangeY") For Each myMod2 In RepWk2.VBProject.VBComponents With myMod2.CodeModule If .CountOfLines 0 Then myCode2 = .Lines(1, .CountOfLines) If InStr(1, myCode2, myFStr(i)) 0 Then myCode2 = Replace(myCode2, myFStr2(i), myRStr2(i)) .DeleteLines 1, .CountOfLines .InsertLines .CountOfLines + 1, myCode2 End If End If End With Next myMod2 End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't understand why I'm getting an argument not optional
Untested, but try this
] Sub FileSearchforMacros2() Dim i As Integer Dim wkbkOne As Workbook With Application.FileSearch .LookIn = "C:\Documents and Settings\BSmith\Desktop\workfiles\changecode" .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 ReplacecodeInModule2 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") myRStr = Array("xf4_", "ExcludePoint", "IncludePoint") 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 ---------------------------------------------------------------------------- ---------------- Sub ReplacecodeInModule2(RepWk2 As Workbook) Dim myCode2 As String Dim myFStr As Variant Dim myRStr As Variant Dim myMod2 As VBComponent myFStr2 = Array("XLFitExcludePoint", "XLFitIncludePoint", "YRange") myRStr2 = Array("xf4_ExcludePoint", "xf4_IncludePoint", "RangeY") For Each myMod2 In RepWk2.VBProject.VBComponents With myMod2.CodeModule If .CountOfLines 0 Then myCode2 = .Lines(1, .CountOfLines) If InStr(1, myCode2, myFStr(i)) 0 Then myCode2 = Replace(myCode2, myFStr2(i), myRStr2(i)) .DeleteLines 1, .CountOfLines .InsertLines .CountOfLines + 1, myCode2 End If End If End With Next myMod2 End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Brett Smith" wrote in message ... I am using this program to replace text in other excel vba files. I don't understand why it is highlighting the ReplacecodeInModule and saying , "Compile Error: Argument not optional". It worked before, but what happend now? I have the code below. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't understand why I'm getting an argument not optional
Re-examine:
ReplacecodeInModule wkbkOne ReplacecodeInModule2 wkbkOne -- Gary's Student "Brett Smith" wrote: I am using this program to replace text in other excel vba files. I don't understand why it is highlighting the ReplacecodeInModule and saying , "Compile Error: Argument not optional". It worked before, but what happend now? I have the code below. Sub FileSearchforMacros2() Dim i As Integer Dim wkbkOne As Workbook With Application.FileSearch .LookIn = "C:\Documents and Settings\BSmith\Desktop\workfiles\changecode" .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 ReplacecodeInModule2 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, 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") myRStr = Array("xf4_", "ExcludePoint", "IncludePoint") 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 -------------------------------------------------------------------------------------------- Sub ReplacecodeInModule2(RepWk2 As Workbook, myFStr2 As Variant, myRStr2 As Variant) Dim myCode2 As String Dim myMod2 As VBComponent myFStr2 = Array("XLFitExcludePoint", "XLFitIncludePoint", "YRange") myRStr2 = Array("xf4_ExcludePoint", "xf4_IncludePoint", "RangeY") For Each myMod2 In RepWk2.VBProject.VBComponents With myMod2.CodeModule If .CountOfLines 0 Then myCode2 = .Lines(1, .CountOfLines) If InStr(1, myCode2, myFStr(i)) 0 Then myCode2 = Replace(myCode2, myFStr2(i), myRStr2(i)) .DeleteLines 1, .CountOfLines .InsertLines .CountOfLines + 1, myCode2 End If End If End With Next myMod2 End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compile Error Argument Not optional | Excel Discussion (Misc queries) | |||
Compile Error: Argument not optional | Excel Programming | |||
error message: compile error, argument not optional | Excel Programming | |||
optional argument in a function | Excel Programming | |||
Complie Error- Argument not optional | Excel Programming |