Macro Updated by Macro ?

[ayazgreat]

Hi All

How should I update a macro by a macro ? meaning to say that codes are replaced by new codes in module.

Suppose I have workbook named Ayaz.xls containing module named "TDP" which is protectd by VBA Password(Lock Project). I want codes to be updated/replaced by New codes.

For example I have following codes

Private Sub CopySheet1()
   
    With Sheet1.UsedRange
        Sheet2.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
End Sub

want change to

 [/COLOR]
[COLOR=black]Private Sub CopySheet1()
   
    With Sheet1.UsedRange
        [COLOR=red]Sheet3.[/COLOR]Range[COLOR=red]("[/COLOR][COLOR=red]C2").[/COLOR]Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
End Sub[/COLOR]
[COLOR=black]

Thanks in advance

 

[ZVI]

Hi Ayazgreat,
Instead of code replacing you can pass parameters of destination sheet object and its start address as string to the Sub CopySheet1():

<font face=Courier New>
Private Sub CopySheet1(Sh As Worksheet, Addr As String)
  With Sheet1.UsedRange
    Sh.Range(Addr).Resize(.Rows.Count, .Columns.Count).Value = .Value
  End With
End Sub

Sub Test1()
  CopySheet1 Sheet2, "A1"
End Sub

Sub Test2()
  CopySheet1 Sheet3, "C1"
End Sub</FONT>

Regards,
Vladimir

 

[ayazgreat]

ZVI this is not a solution I want.

 

[ZVI]

Ok, then:

1. Set the reference to Microsoft Visual Basic for Application Extensibility 5.3 by the aid of menu: VBE-Tools-References
2. Define and set variable file in Sub UnprotectProjectandImportVBAmodule() of your post #8 code
3. Run your code from Excel sheet window, not from VBE window!

And take into account that SendKeys statement is danger because could cause to unexpected actions being running from the diferent active windows

 

[ZVI]

To give your code the independence from requirement of p.3 (refer to my previous post) add this code line Application.VBE.MainWindow.Visible = False before the code line with SendKeys of your code

 

[ZVI]

Please read about settings requirements in Introduction of John’s suggested link to the great Chip Pearson resource and set trust access to the VBA project object model.
By the way, it is enough information in this link for your task, just try to modify its code for your purposes.

Hope that this solution on replacement of the code line is what you asked for:

<font face=Courier New>
' ZVI: 2009-04-04 http://www.mrexcel.com/forum/showthread.php?p=1895091
' Replacing line of code in vbaproject of Workbooks(Wb)
Function CodeLineReplace(Wb As String, ModuleName As String, ProcName As String, OldCodeLine As String, NewCodeLine As String) As Boolean
  Dim i As Long, s As String, StartLine As Long, EndLine As Long
  With Workbooks(Wb).VBProject.VBComponents(ModuleName).CodeModule
    StartLine = .ProcStartLine(ProcName, vbext_pk_Proc)
    EndLine = .ProcCountLines(ProcName, vbext_pk_Proc) - StartLine + 1
    For i = StartLine To EndLine
      s = .Lines(i, 1)
      If Trim(s) = Trim(OldCodeLine) Then
        .ReplaceLine i, Space(Len(s) - Len(Trim(s))) & Trim(NewCodeLine)
        CodeLineReplace = True
        Exit Function
      End If
    Next
  End With
End Function

' Testing
Sub Test_CodeLineReplace()
  Dim ok As Boolean
  ' Set parameters to be passed to CodeLineReplace()
  Const Wb = "Target.xls"
  Const ModuleName = "TDP"
  Const ProcName = "CopySheet1"
  Const OldLine = "Sheet2.Range(""A1"").Resize(.Rows.Count, .Columns.Count).Value = .Value"
  Const NewLine = "Sheet3.Range(""C2"").Resize(.Rows.Count, .Columns.Count).Value = .Value"
  ' Replace the code
  ok = CodeLineReplace(Wb, ModuleName, ProcName, OldLine, NewLine)
  ' Check if successful
  If ok Then MsgBox "Replaced" Else MsgBox "Not found"
End Sub</FONT>

Regards,
Vladimir

 

[ayazgreat]

Thank you very much ZVI, I try it.

 

[ZVI]

Correction of previous code: EndLine = StartLine + .ProcCountLines(ProcName, 0) – 1

Here is the modified version with possibility of each matched part of code replacement (default) or the full line matching & replacement (LineMatch=True):

<font face=Courier New>
' ZVI: 2009-04-04 http://www.mrexcel.com/forum/showthread.php?p=1895341#post1895341
' Replacing of each matched parts or full matched line of Workbooks(Wb)vbproject code
' Using Excel menu set On: Tools-Macros-Security-Trust access to the VBA project object model
' Reference to Microsoft Visual Basic For Applications Extensibility 5.3 is not required
Function CodeReplace(Wb$, ModuleName$, ProcName$, OldCode$, NewCode$, Optional LineMatch As Boolean) As Long
  Dim i&, sFind$, s$, StartLine&, EndLine&
  With Workbooks(Wb).VBProject.VBComponents(ModuleName).CodeModule
    StartLine = .ProcStartLine(ProcName, 0)
    EndLine = StartLine + .ProcCountLines(ProcName, 0) - 1
    sFind = UCase(Trim(OldCode))
    For i = StartLine To EndLine
      s = .Lines(i, 1)
      If LineMatch Then
        If UCase(Trim(s)) = sFind Then
          .ReplaceLine i, Space(Len(s) - Len(Trim(s))) & Trim(NewCode)
          CodeReplace = CodeReplace + 1
        End If
      ElseIf Mid$(Trim(s), 1, 1) <> "'" And InStr(1, s, OldCode, 1) > 0 Then
        .ReplaceLine i, Replace(Expression:=s, Find:=OldCode, Replace:=NewCode, Compare:=1)
        CodeReplace = CodeReplace + 1
      End If
    Next
  End With
End Function

' Testing of each matched part of code replacement
Sub Test1_CodeReplace()
  Dim i As Long
  ' Set parameters for CodeLineReplace()
  Const Wb = "Target.xls"
  Const ModuleName = "TDP"
  Const ProcName = "CopySheet1"
  Const OldLine = "Sheet2.Range(""A1"")"
  Const NewLine = "Sheet3.Range(""C2"")"
  ' Replace each part of matched code
  i = CodeReplace(Wb, ModuleName, ProcName, OldLine, NewLine)
  ' Check the amount of replacements
  If i > 0 Then MsgBox "Replaced " & i & " times" Else MsgBox "Not found"
End Sub

' Testing of full code line matching and replacing
Sub Test2_CodeReplace()
  Dim i As Long
  ' Set parameters for CodeLineReplace()
  Const Wb = "Target.xls"
  Const ModuleName = "TDP"
  Const ProcName = "CopySheet1"
  Const OldLine = "Sheet2.Range(""A1"").Resize(.Rows.Count, .Columns.Count).Value = .Value"
  Const NewLine = "Sheet3.Range(""C2"").Resize(.Rows.Count, .Columns.Count).Value = .Value"
  ' Replace each matched full line of code
  i = CodeReplace(Wb, ModuleName, ProcName, OldLine, NewLine, True)
  ' Check the amount of replacements
  If i > 0 Then MsgBox "Replaced " & i & " times" Else MsgBox "Not found"
End Sub</FONT>

 

[ayazgreat]

Thanks ZVI

One thing more an idea hit uopn me that Could it be possible a a.bas is externaly link with excel not in VBA project and if it is how should it be protected not to view because it is opened in note pad.

 

[stanleydgromjr]

ayazgreat,

Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
This page describes how to write code that modifies or reads other VBA code.
http://www.cpearson.com/Excel/vbe.aspx

Introduction
You can write code in VBA that reads or modifies other VBA projects, modules, or procedures. This is called extensibility because extends the editor -- you can used VBA code to create new VBA code. You can use these features to write custom procedures that create, change, or delete VBA modules and code procedures.

In order to use the code on this page in your projects, you must change two settings.

First, you need to set an reference to the VBA Extensibililty library. The library contains the definitions of the objects that make up the VBProject. In the VBA editor, go the the Tools menu and choose References. In that dialog, scroll down to and check the entry for Microsoft Visual Basic For Applications Extensibility 5.3. If you do not set this reference, you will receive a User-defined type not defined compiler error.

Next, you need to enable programmatic access to the VBA Project. In Excel 2003 and earlier, go the Tools menu (in Excel, not in the VBA editor), choose Macros and then the Security item. In that dialog, click on the Trusted Publishers tab and check the Trust access to the Visual Basic Project setting.

In Excel 2007, click the Developer item on the main Ribbon and then click the Macro Security item in the Code panel. In that dialog, choose Macro Settings and check the Trust access to the VBA project object model.

The VBA Project that you are going to change with these procedures must be unlocked.

There is no programmatic way to unlock a VBA project (other than using SendKeys). If the project is locked, you must manually unlock. Otherwise, the procedures will not work.
CAUTION: Many VBA-based computer viruses propagate themselves by creating and/or modifying VBA code. Therefore, many virus scanners may automatically and without warning or confirmation delete modules that reference the VBProject object, causing a permanent and irretrievable loss of code. Consult the documentation for your anti-virus software for details.

For information about using creating custom menu items in the Visual Basic Editor, see Menus In The VBA Editor.

You can download an example workbook containing the code on this page.


See:
1. Deleting A Procedure From A Module
2. Adding A Procedure To A Module


Have a great day,
Stan

 

[ayazgreat]

Thanks stanleydgromjr