Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Combining XLA files into 1 XLA

  Asked By: Seth    Date: Sep 29    Category: MS Office    Views: 778
  

I am a financial systems
administrator for a Dutch multinational in Office Products.

Because of my job, I have created a lot of VBA routines, to build
applications, to make personalized toobard for people and so on.

I have some difficulties combining all different macro's and VB
routines. And therefor I am looking for a "tool" or an easy way to
copy all sources into 1 file, which is easy to do maintenance on.

Does anyone has such a tool, to copy all routines into 1 big
macro "file"?

I am also looking for a tool which can edit XLA files. I now have in
my toolbar a lot of different Excel add-ins and I would like to
combine them into one custom build menu.

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Chad Bradley     Answered On: Sep 29

Not sure where you're finding trouble, but the following thoughts ...

(1) Put your code into appropriate modules in the existing worksheets, and name
those modules.

(2) Open your target worksheet as well, then simply drag the modules into it
from within the VB editor. It will copy, rather than move, and the unique
module names will allow all the code to co-exist.

(3) You will need to avoid naming conflicts for subroutines and functions. I
prefer "dot" notation for VBA, where I use both the module name and the
subroutine/function name for all except "common subroutines". I use meaningful
and unique names for the modules, and much more generic names for the
subroutines / functions. E.g. DatabaseAccess.Initialise would start up a
database access module - not a non-descript module name and a subroutine name of
DatabaseAccessInitialise. This is closer to the modularising offered by other
languages.

Not sure what you mean by a tool  to edit  VBA files. Why not just edit them from
within Excel?

 
Answer #2    Answered By: Laurel Collins     Answered On: Sep 29

Here's a page with some tips on programming within VBE:

http://www.cpearson.com/excel/vbe.htm

For example, if I run the code below in a newly opened workbook, it
will list the names of all the standard modules for all of the open
workbooks (including add-ins!). If there are no other open workbooks,
you'll just get the add-ins you have active. I think all you'd need
to do is change append of the module name to the "Msg" string to be an
export of the module from "oComponent" and an import into
"ThisWorkBook...". There are examples of that on the web page I cited
above.

===============================================================

Sub ListStandardModules()
' Needs "Microsoft VBS Extensibility" reference
Dim oComponent As VBComponent
For Each oProject In Application.VBE.VBProjects
If oProject.Name <> ThisWorkbook.VBProject.Name Then
If oProject.Protection = False Then
Msg = Msg & "-------->" & oProject.Name & Chr(13)
For Each oComponent In oProject.VBComponents
If oComponent.Type = vbext_ct_StdModule Then
Msg = Msg & oComponent.Name & Chr(13)
End If
Next oComponent
End If
End If
Next oProject
MsgBox Msg
End Sub

 
Didn't find what you were looking for? Find more on Combining XLA files into 1 XLA Or get search suggestion and latest updates.




Tagged: