Can a macro create another macro?

Asked

Viewed 1,121 times

2

I have a spreadsheet of analysts, but when you change the analyst or enter a new one I need to copy everything the same as the other analysts just by changing his name. I wanted to know if there is a possibility for me to create a macro that creates a macro with the name of the new analyst typed 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 job of another analyst. But I want it done by the user. I want when he clicks on a "Add Analyst" button to open an inputbox that only takes the name of the analyst, and alone he 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 for the name inserted via inputbox.

I hope you can understand.

excel vba macro

  • Hello. Please provide more details as the question is hardly anyone will be able to help you. Today you do this manually? Do you simply copy the file and change its name? If not, where do you store the analyst name?

  • @Alexandre, put an image of what the spreadsheet is like and how it should look, maybe we don’t even need Macro to do what you want, help us to understand your difficulty right and we will be more assertive in the answer.

  • Apparently, by the description of your problem, you are using an inappropriate approach to solve it. You’re building a module for each analyst, while you should have a module to serve all analysts. Finally, you should have Functions (Function) and Sub (Procedures) that meet any analyst. It’s like any program, you write the routines that will operate on the data and not a routine for each data.

1 answer

3


I’m not a VBA expert, but I think I was able to solve your problem using the available code 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:

inserir a descrição da imagem aqui

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

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

Private Sub CommandButton1_Click()
    Call Adicionar_Analista
End Sub

An Inputbox will be opened 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 precautions:

  • The quotes that go to sub-dynamics cannot be explicit, you can put """" or, as I did, use a variable to facilitate reading.
  • All lines have to end with & vbCrLf _ to skip the line and concetenar the string

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

  • Thank you very much friend, that was exactly it.

Browser other questions tagged excel vba macro

You are not signed in. Login or sign up in order to post.