PDA

View Full Version : Dynamic VBA Generation



nameuser321
03-15-2012, 11:56 AM
Is there a way to have a macro in one workbook dynamically generate VBA code in another workbook?

Kenneth Hobs
03-15-2012, 01:21 PM
Yes. See vbacomponents in the VBE or: http://www.cpearson.com/Excel/vbe.aspx

nameuser321
03-15-2012, 01:25 PM
Thanks Mr. Hobs for your quick reply!

nameuser321
03-15-2012, 01:36 PM
I'm a little worried about this warning "CAUTION: Many VBA-based computer viruses propagate themselves by creating and/or modifying VBA code. Therefore, many virus scanners may automatically and without warning or confirmation delete modules that reference the VBProject object, causing a permanent and irretrievable loss of code. Consult the documentation for your anti-virus software for details. ". How do you avoid accidently writing code that will be deleted and where in your antivirus documentation would this be found?

Kenneth Hobs
03-15-2012, 02:27 PM
I guess it would be documented in your AV software. Contact the vendor if you need to know. If you backup your file, I don't see an issue.

I would not recommend vbacomponent methods just like SendKey() methods.

An add-in method is usually a better solution for some.

nameuser321
03-21-2012, 09:31 AM
Great. Thanks again Kenneth.

Aussiebear
03-22-2012, 03:31 AM
Just out of curiosity..... why do you need to dynamically create code in another workbook? This is one of the things that antivirus software are designed to find and delete.

nameuser321
03-22-2012, 05:34 AM
I want one "template creating" workbook to generate other "template" workbooks that can do specific calculations without having to repeat code in those template workbooks. I know there's probably other way to do this, but it was the first thing that came to mind, when I was trying to solve this problem.... Plus I thought it would be an interesting question for the forum.