• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to manipulate VBA code and class modules using VBA?

BobBridges

Active Member
I've seen hints here and there that VBA can manipulate VBA modules, but I've never really gotten a handle on how it works, not in detail. Can someone explain it, or point me to a page that does a decent job?

What I'm after is to be able to write a program that looks at two .xlsm workbooks, and copies all the modules from one to the other, replacing all. That is, delete all the code modules in A and copy all the modules from B to A.

If it helps to know, the purpose is this: I have users who are constantly modifying data in a worksheet with many worksheets. I have written some programs to help them, so the workbook not only has dozens of worksheets but also a few dozen modules and class modules. As things stand now, when they want me to enhance the programs they have to stop work on the data and send the workbook to me; when the updates are ready I send the workbook back and they can resume modifying the data. It'd be more convenient if I could work on program code in my own copy of the workbook at the same time they're updating the contents of the worksheets in a copy at their end; from time to time they could send me their workbook, I'd replace their program code with my updates and send it back right away, thus delaying them only a few hours.
 

Debaser

Well-Known Member
I would suggest you should be looking at splitting the code off into a separate workbook/add-in if at all possible. It would make code updates a lot simpler.
 

BobBridges

Active Member
Debaser, I may have misunderstood you but I think you simply said what I said. What I want is to be able to keep my code in a separate workbook, and write a VBA program that will copy over all the modules on demand. What I'm looking for is (I suppose) a class with properties and methods that will allow a program to do the replace/copy.

Marc, I'm looking for "VBComponents" in the Excel object model, by itself or under Application, and I don't see it. Where would I look?
 

Debaser

Well-Known Member
No, what I suggested was that your data workbook should not have any code in it at all. The code should be separated from the data precisely so that you can easily supply a new version of the code without interfering with existing data.
If you insist on writing code that manipulates code, you will want to look at referencing the VBA Extensibility library (not absolutely necessary, but will make your life easier) and you will need to ensure that anyone who needs to run your code has allowed programmatic access to the VBA project in their trust center settings. I'd also recommend reading Chip's page here: http://www.cpearson.com/Excel/VBE.aspx
 

BobBridges

Active Member
Oh, so the scheme you're suggesting is that my customer have two workbooks, one containing code and the other containing data. The programs would open the data workbook (if it's not already open) and generate the reports, update the data, create a new worksheet, whatever is necessary. I see the value of that for dealing with the problem I described above, but isn't it more work for the user when he wants to run one of the programs? Instead of just pushing a button on one of the worksheets in the data workbook, he has to open the program workbook and run it there. Or is there a way to have both? Could a button in the data workbook invoke a program in the program workbook?
 

Debaser

Well-Known Member
Ideally, your code would be in an add-in that the user simply installs once so that it's always available.
 

BobBridges

Active Member
Interesting. I read a little about creating add-ins yesterday, while pursuing the VBE option, and from what you say that would be ideal. But I don't think I'm ready yet to attempt it. Also, it talked about Java; can an add-in be done in VBA? I'm guessing not.
 

Debaser

Well-Known Member
Yes, it can. An Excel add-in is basically a workbook with a few special properties set. All you actually need to do to create one from your workbook is to save it as an add-in. You will however require some sort of menu system for the user to run the add-in's routines - eg a custom ribbon.
 

BobBridges

Active Member
This sounds really interesting! (I'm always up for a new gimmick I can learn how to do.) I'm thinking that for right now this client has more urgent needs that I should supply first, but one day in the hypothetical future it might be a really convenient improvement. I just hope I'll remember where to look when the time comes to learn it :).
 
Top