MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Can you add modules in VBA to an Excel workbook

  Asked By: Rabiah    Date: Jan 29    Category: MS Office    Views: 4132

I have an app in VB6 that has several forms and several
.bas modules. This app "lives" in 2 versions -- one is
a test version that is constantly being updated
with improvements. The other is a production version,
part of which does not occur in the test version,
and the rest of it which is a copy of the test version.
So, when an upgrade to the test version is deemed ready,
I click a button in the production version and the appropriate
files are copied from the test version subdirectory. Piece
of cake.

Is such an approach available in Excel? I know the code
to accomplish it would be the same as in VB6, but can a
workbook consist of multiple ".bas modules" ??



11 Answers Found

Answer #1    Answered By: Mena Schmidt     Answered On: Jan 29

Sure, workbooks can contain multiple  code modules, you can import the
.bas files...

Answer #2    Answered By: Jessie Banks     Answered On: Jan 29

I must confess to being a little confused, however. I just
pulled up an .xls I created that has some VBA code that I
wrote. I can view the code in the VBA editor. However,
it all seems to be part  of the same .xls.

In VB6, there is something called the "project" window. It
lists all of the .frm files and all of the .bas files. Further,
on the menu bar, there is a Project button  that facilitates
adding a form, module, etc. When I look in my directory tree,
I see all of the files associated with this project -- with
.frm and .bas extensions.

Where does any/all of this occur in Excel?

Answer #3    Answered By: Sam Evans     Answered On: Jan 29

Try this, as I do this all of the time.
Bring up a brand new Excel Workbook.

Select ( Tools ) from command bar line.
Select the ( Macro ) item and then in adjacent panel, select the listed ( Macros
) item.
This will display a (Macro Name) panel, because there are no existing
(Modules/Macros) in the workbook.
Enter in any MacroName ( sample ) and select ( Create ), so you can enter into
the VB Editor.
This will bring up the VBE for VBA Programming&Coding section of the workbook.

As a result, you will see a new module created as ( Module1 ), which you can
highlight(right-click) and ( Remove ) it.
After doing so, you can goto the ( File ) command bar button  and select the (
Import ) item.
This will display the Import Panel, where you would speccify the
directory/folder source for the ( Modules ) & ( UserForms ).
You have to do a single file Import for each module and for each userform,
unless somebody knows how to do all in one single step.

Answer #4    Answered By: Gregg Bennett     Answered On: Jan 29

Thanks. I'm familiar with the first part. But the 2nd part
threw me.. wait a minute, scratch that..

I was looking for File|Import on the spreadsheet, and couldn't
find it. My bad. In the VBE, I see File|Import File and
File|Remove Module 1. Great.

However, it seems, as you surmise, that this set of steps must
be done manually. The nice thing about VB6 is that, since
projects are built from individual .frm and .bas files, they
can be copied  under program control.

Thanks again. I'll play around with this.

Answer #5    Answered By: Madiha Malik     Answered On: Jan 29

you can import modules  in VBA under program control.
You have to use a set of objects in the VBA Extensibility Library. I
wrote an article about it in TechTrax:

See, in VBA, whether Word, Excel, Outlook, Access, etc. all the modules
are stored in the one document file (unlike VB6), such as a .doc, .xls,
.otm (Outlook), .mdb (Access). In VBA, if you see a .bas or .frm file on
your hard drive, it's because you exported it, or were about to import
it. But the system doesn't actually use these files -- you can use them
as backups of your modules or as a means of transferring them to other
documents, but VBA can't work with them directly -- it only works with
what's in the document file itself.

Answer #6    Answered By: Ned Storm     Answered On: Jan 29

I'll check out the link, too...............

Answer #7    Answered By: Catherine Campbell     Answered On: Jan 29

You can see that in excel  as well, although the modules  are stored with
each excel workbook, unless you create a personal workbook  which will
act like an add-in and allow you to run your code against any loaded

Inside the VBE - select View, Project Window & I also like the
Properties Window to be displayed.

See www.personal-computer-tutor.com/personalxls.htm for help on
creating a Personal Macro Library for yourself. (I store my
Personal.xls file on the network to get backed up, and store a shortcut
to it in my XLStart folder so it opens and is available for use.)

Answer #8    Answered By: Janet Phillips     Answered On: Jan 29

Oh yeah, how 'bout that. Again, I was doing View on the
spreadsheet "side", not the VBA Editor "side". Very nice.
And I'll also check out your link.

Answer #9    Answered By: Jacob Bouchard     Answered On: Jan 29

You can add  modules in VBA according to


I picked this up at another site on the web and tried it, but my virus scanner
didn't like it.

So be careful it removed all my VBA code from my workbook. I had a backup havn't
got to try it again. Let me know if you try it and get it to work

Answer #10    Answered By: Theresa Watson     Answered On: Jan 29

Yes, the ( Import ) is only from within the VBE(VB Editor)via the File command
bar button
And each module & userform has to be imported separately/manually.

Another reason why I do the above, of exporting&importing.
Sometimes an Excel workbook  may become corrupt and it can't be
Then, I goto my backup Excel workbook and re-Import all of the modules&userforms
in it.
I just wished there was a way to( Import ) all the modules&userforms in 1 single

Answer #11    Answered By: Andrew Levensky     Answered On: Jan 29

The bas modules  are stored internally, so you'd need to use VBA itself to
manipulate the modules programmatically. This could be done, but would take
quite a bit of work to set up. (You can import the bas modules yourself by
simple drag and drop from one sheet to the other in the VBA environment.)

An alternative is to put the software into a .xla file. You can then link to
the production or test  one as required. In particular, you can replace the
production one by overwriting it, and people will automatically start using the
new one.

Didn't find what you were looking for? Find more on Can you add modules in VBA to an Excel workbook Or get search suggestion and latest updates.