How does one macro create another macro?

I have a spreadsheet of analysts, however, when the analyst changes or some new one comes in I need to copy everything the same from the other analysts just by changing his name. I wanted to know if there is a possibility that I create a macro that creates a macro with the name of the new analyst typed by the user.

Edition 

Sub Victor()

Application.ScreenUpdating = False
Call limpaFiltro
Call resetEmail(Sheets("Email_Victor"))
Call BuscaBasePenhoras("Victor ", "Pendente", Sheets("Email_Victor"))
Call BuscaPendencias("Victor ", Sheets("Email_Victor"))
Call ExibePendenciasDaAgenda(Sheets("Email_Victor"))
Call ExibePendenciaAgendaNoEmail(Sheets("Email_Victor"))
Call acoesPro("Victor ", "Pendente", "ACAO PRO", Sheets("Email_Victor"))
Call ExibeTextoAcaoPro(Sheets("Email_Victor"))
Call ExibeAcoesProNoEmail(Sheets("Email_Victor"))
Call PendenciasNoEmail(Sheets("Email_Victor"))
Call EmAnalise("Victor ", "Em Análise", Sheets("Email_Victor"))
Call REDLINE("Victor ", "xAtualizado", Sheets("Email_Victor"))
Call ClearClipboard

End Sub

I want to do the function of another analyst. But I want it to be done by the user. I want it when he clicks a "Add" button Analyst " open an inputBox that takes only the name of the analyst, and alone it creates a new module with the name of the analyst, copies this code inside the module and changes the fields where it has the name victor to the name entered via inputBox.

I hope you understand.

Author: Math, 2015-06-29
Source

1 answers

I'm not a VBA expert, but I think I managed to solve your problem using the code available here .

Before testing, two settings need to be made:

  1. in the VBA project, add the reference Microsoft Visual Basic For Applications Extensibility 5.3:

insert the description of the image here

  1. in the worksheet, go to options - > reliability Center - > reliability Center Settings - > Macro settings - > Select Trust Project Object Model access do VBA

Once this is done, add the following code to a module: 

Option Explicit

Sub Adicionar_Analista()

    Dim moduleName As String
    moduleName = addModule
    WriteToModule moduleName, CStr("")

End Sub

Private Function addModule() As String
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule

    Set VBProj = ThisWorkbook.VBProject
    Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule)
    Set CodeMod = VBComp.CodeModule

    Dim macro As String
    Dim nome As String

    nome = InputBox("Nome do Analista")

    Dim vbDQ As String
    vbDQ = """"

    macro = "Sub " & nome & "()" & vbCrLf _
        & vbTab & "Application.ScreenUpdating = False" & vbCrLf _
        & vbTab & "Call limpaFiltro" & vbCrLf _
        & vbTab & "Call resetEmail(Sheets(" & vbDQ & "Email_" & nome & vbDQ & "))" & vbCrLf _
        & vbTab & "Call BuscaBasePenhoras(" & vbDQ & nome & " " & vbDQ & ", " & vbDQ & "Pendente" & vbDQ & ", Sheets(" & vbDQ & "Email_" & nome & vbDQ & "))" & vbCrLf _
        & vbTab & "Call BuscaPendencias(" & vbDQ & nome & " " & vbDQ & ", Sheets(" & vbDQ & "Email_" & nome & vbDQ & "))" & vbCrLf _
        & vbTab & "Call ExibePendenciasDaAgenda(Sheets(" & vbDQ & "Email_" & nome & vbDQ & "))" & vbCrLf _
        & vbTab & "Call ExibePendenciaAgendaNoEmail(Sheets(" & vbDQ & "Email_" & nome & vbDQ & "))" & vbCrLf _
        & vbTab & "Call acoesPro(" & vbDQ & nome & " " & vbDQ & ", " & vbDQ & "Pendente" & vbDQ & ", " & vbDQ & "ACAO PRO" & vbDQ & ", Sheets(" & vbDQ & "Email_" & nome & vbDQ & "))" & vbCrLf _
        & vbTab & "Call ExibeTextoAcaoPro(Sheets(" & vbDQ & "Email_" & nome & vbDQ & "))" & vbCrLf _
        & vbTab & "Call ExibeAcoesProNoEmail(Sheets(" & vbDQ & "Email_" & nome & vbDQ & "))" & vbCrLf _
        & vbTab & "Call PendenciasNoEmail(Sheets(" & vbDQ & "Email_" & nome & vbDQ & "))" & vbCrLf _
        & vbTab & "Call EmAnalise(" & vbDQ & nome & " " & vbDQ & ", " & vbDQ & "Em Análise" & vbDQ & ", Sheets(" & vbDQ & "Email_" & nome & vbDQ & "))" & vbCrLf _
        & vbTab & "Call REDLINE(" & vbDQ & nome & " " & vbDQ & ", " & vbDQ & "xAtualizado" & vbDQ & ", Sheets(" & vbDQ & "Email_" & nome & vbDQ & "))" & vbCrLf _
        & vbTab & "Call ClearClipboard" & vbCrLf _
    & "End Sub"

    CodeMod.AddFromString macro

    addModule = VBComp.Name
End Function

Private Sub WriteToModule(moduleName As String, arrayName As String)
    With ActiveWorkbook.VBProject.VBComponents(moduleName).CodeModule
        .InsertLines .CountOfLines + 2, ""
    End With
End Sub

To rotate, just call the Sub. For example, with a button: 

Private Sub CommandButton1_Click()
    Call Adicionar_Analista
End Sub

An InputBox will open asking for the analyst's name.

Since the code that will be written in the Sub is inside a string, it is important to take some care:

  • the quotes that go to the dynamic sub can not be explicit, you can put """" or, as I did, use a variable to facilitate the read.
  • all lines have to end with & vbCrLf _ to skip the line and design the string

Each analyst will be added in a new module (it should be possible to join everything in one by changing the code).

 3
Author: Molx, 2015-06-29 18:19:28