CODEWITHSUNDEEP
c#excelvba
fortm
fortm

Reputation: 4198

Excel addon for VBA editing

Need to replace patterns matching regular expression inside pre-existing VBA Excel macros preferably using Excel Forms. How can I create such an Excel addon to appear in ribbon after one click installation.

Being new to this, any pointers will be helpful.

Upvotes: 0

Views: 110

Answers (1)

abillon
abillon

Reputation: 629

You can access the VBA code via VBA with the object VBComponent.

You will have the change some security options too to access programatically to you VBA Project (in French the option is called "Accès approuvé au modèle d'objet du projet VBA")

Sub ModifyVBACode()
    Dim sCodeSource As String

    'Get the source code in a string'

    With ThisWorkbook.VBProject.VBComponents("CodeName").CodeModule
        sCodeSource = .Lines(1, .CountOfLines)
    End With

    'modify your code'

    With ThisWorkbook.VBProject.VBComponents("CodeName").CodeModule
        'Delete the old source code'
        .DeleteLines 1, .CountOfLines

        'write the new one'
        .AddFromString sCodeSource
    End With
End Sub

Hope this helps

Upvotes: 1

Related Questions