MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

writing vba code to generate vba code

  Asked By: Ayden    Date: Jan 09    Category: MS Office    Views: 3511

Is it possible to write code in vba that would automatically generate additional
code and create dynamic values.

Essentially I have vba code which uses ado to insert data into a newly created
worksheet. However, i want to be able to insert code that retains the connection
information so that the user can re-establish a connection another time. The
complication is that there is possibly more than one connection required
depending on the values output from the database. Therefore on one sheet 2 , 3 ,
4 etc recordsets may have been required to generate the sheet output. My code
would need to insert vba into the sheet as it was created and also be able to
identify the cells which refer to each recordset connection.



6 Answers Found

Answer #1    Answered By: Blas Fischer     Answered On: Jan 09

Yes, this is possible, but I don't recall how as I've never needed it. I
am quite certain that this question has been asked before within the
newsgroup, and if you do a search you should find the answer. Either
that or if someone else can provide. Other wise a quick search on the
internet via Google should also find what you are looking for.

Answer #2    Answered By: Tara Ryan     Answered On: Jan 09

With all respect and without meaning to sound rude I have checked and could not
find an answer so I came to list. Any help would be greatly appreciated.

Answer #3    Answered By: Sam Anderson     Answered On: Jan 09

Yes, you can do this. You can access the code  on pretty much a line-by-line

You may find my TechTrax article helpful:


Have fun – it’s pretty trippy, to be able to change your code on the fly!

Answer #4    Answered By: Mehreen Malik     Answered On: Jan 09
Answer #5    Answered By: Daya Sharma     Answered On: Jan 09

Thanks for this. It has given me some ideas on how to progress. Nevertheless, if
there are any further contributions i would be interested to hear them.

Answer #6    Answered By: Viren Rajput     Answered On: Jan 09

I've done a lot of this and there are some shortcomings!

Reporting lines of code  is not a problem but I'm sure that the VBA
compiler/interpreter gets it's panties twisted if you try to alter the code
you are actually running. This seems to occur if you alter code in the same
module so I think that "it" probably keeps a pointer to the line of code
it's working on so that it can come back to it after it's been on it's
travels. If you insert  code above this then there are all sortsa problems!
If you insert code below then you maaaay upset the pointers to a procedure
being run at a later stage.

I've written code to sort a set of Dim statements in a procedure and that
worked pretty well soooo my thinking is that if you keep the same number of
bytes between two places then you are ok.

Best practice I've found is to alter the code from another project even.

The calls to get lines of code are pretty straight forward and reasonably
documented. You can read a block of code or go down line by line.

If you need more I may be able to find some of that - very old... sorry -
code for you, but it's a great excersize to figure it out for yourself. If
you do want the code it won't be quick 'coz I have to look back quite a
way... Again sorry.

Once I tried to write  something that would write code to run itself and then
run it!! I don't remember why now... But it didn't work. If I restarted the
application running the written procedure worked fine though.

Didn't find what you were looking for? Find more on writing vba code to generate vba code Or get search suggestion and latest updates.