Do you have a macro to create another macro?

Question

Do you have a macro to create another macro?

Navigation

#1 by (3 votes)

2

I have a spreadsheet of analysts, however, when the analyst changes or some new one comes in I need to copy everything just like the other analysts just changing the name of it. I would like to know if there is a possibility of creating a macro that creates a macro with the name of the new analyst entered by the user.

Editing

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 role of another analyst. But I want it to be done by the user. I want it when it clicks an "Add Analyst" button open an inputBox that takes only the analyst's name, and alone it creates a new module with the analyst's name, copies that code inside the module and changes the fields where it has the name victor for the name entered via the inputBox.

I hope you understand.

macro excel vba

asked by anonymous 29.06.2015 / 15:19

1 answer

how to make a query that the user can generate? How to display source codes on screens as help sites? [closed]

score 3 · Accepted Answer

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

Before testing, two settings need to be made:

In the VBA project, add the reference Microsoft Visual Basic For Applications Extensibility 5.3 :

In the worksheet, go to Options - > Reliability Center - > Trust Center Settings - > Macro Settings - > select Trust Access to the VBA project object model

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 run, just call Sub. For example, with a button:

Private Sub CommandButton1_Click()
    Call Adicionar_Analista
End Sub

An InputBox will be called for the name of the analyst.

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

The quotes that go into the sub dynamics can not be explicit, you can put """" or, as I did, use a variable to make reading easier.
All lines must end with & vbCrLf _ to skip the line and conceten the string

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