6
How to program one macro to delete another? Would it be something like, when, I need to call? Ex.:
Sub callmacro()
Call macro1
End sub
And better yet, if I want to program it to delete itself?
Ex.:
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
7
It’s possible to do what you want, but for it to work the user (yes, himself! ) needs to change its Excel configuration to indicate that it trusts macros that access the object model of the project VBA (this option is not checked by default because it avoids macro viruses).
How to give this access depends on the Excel version. In Office 2010 (which is what I have here), the user should:
1 - Select the "Options" item from the "File" menu".
2 - Select the "Reliability Center" option and then click the "Reliability Center Settings" button..."
3 - Select the option "Macro Settings" and then mark the option "Rely on access to the VBA project object model"
Once this has been set up, the following code works:
Sub RemoveEuMesmo()
On Error Resume Next
Set oModulo = 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 just for example. I assume that it is in the module name "Module 1", and that it is the only sub in this module, and so I remove all lines of 1 to oModulo.CodeModule.CountOfLines. If you want to remove a specific snippet, you will have to know beforehand which lines are to be removed. Maybe you 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 the VBA has this level of complexity.
Ah, in the example, the lines are not only removed, I leave a comment in place (I thought it was cool the illustration that you can also create new code directly in VBA).
Attention: 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 (maybe to the point of never being executed), requires only one
if. The code can remain there, without never run again. Do you understand? The removal approach may seem a good one balcony (something "Clever"), but it requires special settings that can get you into trouble. First of all, it can open up gaps for malicious codes actually run in your Excel client/user. Second, it will clearly generate for you many difficulties in maintaining the environment, since the configuration not enabled the code will simply not work without generate any error message! In addition, certain users may have a poor perception of the quality of your product, if you ask for them to enable something that is, say, dangerous.
Lead Dough. If I had more points I would give you a reward. I thank you!
Thank you. I’m glad you liked it. I just added an important observation at the end. I insist that this is not the best way. But, here it is with you. Good luck! :)
Leave it to me ;)
3
I thought that way too:
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
Works too.
True, well remembered. The important difference is that in this example you literally remove the module (with everything inside). In the solution of my answer the module remains there and you have exact control over the lines of code to be removed. : ) If you want, you can edit the answer to include this difference in it (then I delete the comment).
Browser other questions tagged excel vba
You are not signed in. Login or sign up in order to post.
The question is interesting. But here comes the inevitable doubt: there is some reason you want to remove the macro?
– Luiz Vieira
Absolutely. The first question I think is common. If I have a spreadsheet with extensive commands and very dynamic. The second may seem unusual, but it’s a possibility.
– h1k3r
My point is: why would you need to delete an existing macro (whether it is done by itself or by another macro)? I don’t know if it’s possible (I imagine it is, without being necessarily trivial), but I don’t see much point in it. A spreadsheet with extensive commands was - in theory - planned like this. No one would spend time building a "command" that was not necessary. So I wondered if your problem really isn’t another (and sometimes has different solution).
– Luiz Vieira
Luiz understands very well your position. But my question is direct. If I were to debate the applications that would be running away from the scope of this community( what has happened, I’ve been beaten and negative, nor want to create more questions with extensive).
– h1k3r
But just to illustrate, very simple. A macro runs automatically when opening the spreadsheet, after a while, storing data or generating more information, I would like the Workbook open routine to change. At least it would have to modify it or in case, delete and insert another.
– h1k3r
I understand your fear. Still, there are numerous other ways for you to resolve the "[...] part after a while, storing data or generating more information, I would like the Workbook open routine to change." without having to remove anything. But really, your question is quite straightforward. I was just trying to help by predicting that maybe your choice of approach is not the best one. I’ll delete the comments then, okay? Good luck. :)
– Luiz Vieira
No need to erase. It is actually enlightening. I am not refusing criticism.
– h1k3r
Okay, no problem at all. :)
– Luiz Vieira
One problem I have been facing here is that sometimes I find many look at the question and probably interpret it as that of a beginner, and seem to ignore. I don’t even get answers, let alone a good question like yours. I’m simply negative, either because they don’t have the answer, because I think it’s too easy, or because they don’t really know and think it would be unnecessary.
– h1k3r
Believe me: nay happens only with you. :)
– Luiz Vieira