+ Reply to Thread
Results 1 to 9 of 9

Replacing VBA code strings by using VBA code?

  1. #1
    Registered User
    Join Date
    06-27-2005
    Posts
    3

    Cool Replacing VBA code strings by using VBA code?

    Does anyone know a fast way to replace a VBA string IN a VBA module by using VBA code? So I'm not talking about replacing strings in worksheets...

    Suppose that I have workbook A with the following sub in a codemodule:
    Sub Example()
    MsgBox "Just an example.", vbOKOnly
    End Sub


    So now I want to use VBA code in workbook B to change the string "Example" in the codemulde of A to "ShowMessage", so that the new sub in A will be:
    Sub ShowMessage()
    MsgBox "Just an example.", vbOKOnly
    End Sub


    Macro security settings aside, I know you need a reference to the MS VBA Extensibilty, but my problem is the following. The object "codemodule" has no method "replace".
    So these are valid codelines:
    Workbooks("A.xls").VBProject.VBComponents(1).CodeModule.Find
    Workbooks("A.xls").VBProject.VBComponents(1).CodeModule.ReplaceLine

    but:
    Workbooks("A.xls").VBProject.VBComponents(1).CodeModule.Replace
    doesn't exist...

    All suggestions are welcome. Thanks in advance.
    shoba

  2. #2
    Chip Pearson
    Guest

    Re: Replacing VBA code strings by using VBA code?

    Try something like the following:

    Dim SL As Long, EL As Long, SC As Long, EC As Long
    Dim S As String
    Dim Found As Boolean
    With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
    SL = 1
    SC = 1
    EL = 99999
    EC = 999
    Found = .Find("find this", SL, SC, EL, EC, True, False,
    False)
    If Found = True Then
    S = .Lines(SL, 1)
    S = Replace(S, "find this", "replace with")
    .ReplaceLine SL, S
    End If
    End With


    --
    Cordially,
    Chip Pearson
    Microsoft MVP - Excel
    Pearson Software Consulting, LLC
    www.cpearson.com




    "shoba" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > Does anyone know a fast way to replace a VBA string IN a VBA
    > module by
    > using VBA code? So I'm not talking about replacing strings in
    > worksheets...
    >
    > Suppose that I have workbook A with the following sub in a
    > codemodule:
    > -Sub Example()
    > MsgBox "Just an example.", vbOKOnly
    > End Sub-
    >
    > So now I want to use VBA code in workbook B to change the
    > string
    > "Example" in the codemulde of A to "ShowMessage", so that the
    > new sub
    > in A will be:
    > -Sub ShowMessage()
    > MsgBox "Just an example.", vbOKOnly
    > End Sub-
    >
    > Macro security settings aside, I know you need a reference to
    > the MS
    > VBA Extensibilty, but my problem is the following. The object
    > "codemodule" has no method "replace".
    > So these are valid codelines:
    > -Workbooks("A.xls").VBProject.VBComponents(1).CodeModule.Find
    > Workbooks("A.xls").VBProject.VBComponents(1).CodeModule.ReplaceLine-
    > but:
    > -Workbooks("A.xls").VBProject.VBComponents(1).CodeModule.Replace-
    > doesn't exist...
    >
    > All suggestions are welcome. Thanks in advance.
    > shoba
    >
    >
    > --
    > shoba
    > ------------------------------------------------------------------------
    > shoba's Profile:
    > http://www.excelforum.com/member.php...o&userid=24676
    > View this thread:
    > http://www.excelforum.com/showthread...hreadid=382617
    >




  3. #3
    Registered User
    Join Date
    02-27-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Replacing VBA code strings by using VBA code?

    @Chip Pearson
    Is there a reason to use

    EL = 99999
    EC = 999

    instead of

    EL = -1
    EC = -1

    To default to the last line & column

    The only think I can think of is backwards compatability or something only I can't find anything.....

    Also please be aware BOTH SL & EL are set to the line number of the found line
    SL is documented as such only EL undocumented so if you are looping set EL to -1 each time (& if you are not replacing the text such as a debug.print for sanity would be like I was then Use SL = SL +1 [misses second hit in a line but ok for my sanity check])

    eg
    Here is a sub which will replace every occurence of the text

    Please Login or Register  to view this content.
    PS
    Comment out the TextFile_InsertLineAtEnd lines as this was my logging



    @Chip Pearson
    "Thanks for all your help over the years"
    Last edited by sirplus; 02-10-2012 at 02:01 AM. Reason: missed the code tags

  4. #4
    Registered User
    Join Date
    12-06-2013
    Location
    Swe
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Replacing VBA code strings by using VBA code?

    Do I need to Open # and Close # tried a version of this and it f__ed up my references for other macros

  5. #5
    Registered User
    Join Date
    12-07-2018
    Location
    Ukrain
    MS-Off Ver
    office 2016
    Posts
    1

    Re: Replacing VBA code strings by using VBA code?

    What does it mean false and true in the find-string? Please, decrypt this.

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Replacing VBA code strings by using VBA code?

    Quote Originally Posted by Ok07 View Post
    What does it mean false and true in the find-string? Please, decrypt this.
    The .Find method has several arguments that define how a text search is performed.
    This explains it well...
    The Find Method: The ultimate search
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Replacing VBA code strings by using VBA code?

    Hello Ok07

    Here are the argument names and types for the VBIDE Find Function:
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  8. #8
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    861

    Cool Re: Replacing VBA code strings by using VBA code?

    Duude!! you've got reply from Chip Pearson!

    Guys sorry for the off topic but... Chip Pearson!!
    .. and don't forget to have fun!
    Bogdan.

    mark SOLVED and Add Reputation if my answer pleases you

  9. #9
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Replacing VBA code strings by using VBA code?

    Quote Originally Posted by bulina2k View Post
    Duude!! you've got reply from Chip Pearson!

    Guys sorry for the off topic but... Chip Pearson!!
    I think the excitement wore off some 13 years ago.

    ...and I still have the same version of Excel .

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1