How to program one macro to exclude another? It would be something like, like when, do I need to call? Ex.:
Sub callmacro()
Call macro1
End sub
And, even better, if I want to program it to self-delete?
Eg:
Sub macro1()
Dim i as...
For i = 0 to...
...
Next
'e aqui, depois de uma serie de comandos, a macro se auto excluiria.'
End sub
You can do what you want, but for this to work the user (yes, himself!) needs to change his Excel configuration to indicate that he relies on macros that access the project VBA objects (this option is not checked by default because it avoids macro viruses).
How to give this access depends on the version of Excel. In Office 2010 (which is what I have here), the user should:
1 - Select the "Options" item in the "File" menu.
2-Selectthe"Reliability Center" option and then click the "Reliability Center Settings ..." button
3-Selectthe"Macro Settings" option and then select "Trust access to the VBA project object model"
Oncethishasbeenconfigured,thefollowingcodeworks:
SubRemoveEuMesmo()OnErrorResumeNextSetoModulo=ActiveWorkbook.VBProject.VBComponents("Módulo1")
If Not oModulo Is Nothing Then
oModulo.CodeModule.DeleteLines 1, oModulo.CodeModule.CountOfLines
lin = "' Código removido automaticamente em " + Format(Now, "dd/mm/yyyy hh:MM:ss")
oModulo.CodeModule.AddFromString lin
End If
End Sub
This code is for example only. I assume it is in the module named "Module1", and that is the only sub in that module, so I remove all lines from 1 to oModulo.CodeModule.CountOfLines . If you want to remove a specific snippet, you will have to know beforehand which lines to remove. Maybe I have methods to search (via a kind of reflection) for a sub with the given name, but I did not look for details and I find it unlikely that VBA has this level of complexity.
In the example, the lines are not only removed, I leave a comment in place (I thought it was cool to illustrate that you can also create new code directly in VBA).
Warning: As I mention in comments, this approach is not ideal To do what I understand you need. To have a code that adapts to the volume of data (perhaps to the point of never being executed), requires only
if. The code can remain there, without never run again. You see? The removal approach may seem like a good ("clever "), but it requires special configurations that can bring you problems. First, it can malicious code actually runs in your client / user. Secondly, it will clearly generate for you many difficulties in maintaining the environment, because if the configuration is not enabled the code simply will not work without generate any error message! In addition, certain users can have a poor perception of the quality of your product, if you ask for them to enable something that is, say, dangerous.
I also found this way:
Sub DeleteModule()
Dim VBProj As VBIDE.VBproject
Dim VBComp As VBIDE.VBComponent
Set VBProj = activeworkbook.VBProject
Set VBComp = VBProj.VBComponent("Module1")
VBProj.VBComponents.Remove VBComp
End sub
It works too.