ExcelBanter - Is it possible to copy vba macros into a workbook using a vba macroto do the copying

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Is it possible to copy vba macros into a workbook using a vba macroto do the copying (https://www.excelbanter.com/excel-programming/451380-possible-copy-vba-macros-into-workbook-using-vba-macroto-do-copying.html)

Is it possible to copy vba macros into a workbook using a vba macroto do the copying

I have a series of workbooks one for each year handling budgetary data that prepares tax information for the year in question. If I modify a macro or add a new macro, I have to manually make the modification in each of the workbooks (12 in all). Each workbook has links back to the preceding workbook so that cumulative data from year 1 can be presented in the current year.

I would like to make the VBA changes in the base year's workbook and then migrate that changed macro to all the other 11 workbooks using VBA code. Is this possible ?.

I am already using PERSONAL.XLSM to hold a library of commonly used macros.. However, I think it is poor design to place macros common to one particular series of workbooks in PERSONAL.XLSM.

Looking forward to being enlightened. Thank you.

Is it possible to copy vba macros into a workbook using a vba macro to do the copying

You could put the macros in an XLAM (addin) that also loads a custom
menu. This makes the current code always contained in a single
(distributable) file, and obviates the need for your period files to
contain any code at all. Just redistribute any new version of your
addin as needed!

Normally, I'd 'tag' the project workbooks so the addin knows which
files to make its menus available to. I use a template for the fiscal
period file so a new one can be started in new fiscal periods.

So then...

Addin:
- contains all code/forms/features/functionality
- creates/destroys its own custom menus at startup/shutdown
- only enables menus when one of its project files is active

Template:
- reusable project file used by the addin for dedicated tasks
- new template created by the addin for each new fiscal period

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Is it possible to copy vba macros into a workbook using a vbamacro to do the copying

On Sunday, April 10, 2016 at 1:02:14 PM UTC-4, Desmond Walsh wrote:
I have a series of workbooks one for each year handling budgetary data that prepares tax information for the year in question. If I modify a macro or add a new macro, I have to manually make the modification in each of the workbooks (12 in all). Each workbook has links back to the preceding workbook so that cumulative data from year 1 can be presented in the current year.

I would like to make the VBA changes in the base year's workbook and then migrate that changed macro to all the other 11 workbooks using VBA code. Is this possible ?.

I am already using PERSONAL.XLSM to hold a library of commonly used macros. However, I think it is poor design to place macros common to one particular series of workbooks in PERSONAL.XLSM.

Looking forward to being enlightened. Thank you.

Actually, I found a VBA macro CopyModule on Chip Pearson's webite www.cpearson.com that does exactly what I want. The following test code shows how to use this macro.

Sub test_CopyModule()
'
' A test procedure to test the Vba macro CopyModule
' NOTE 1: CopyModule is from Chip Pearson's site www.cpearson.com
' (check topic VBA EDitor, Automating The VBA EDitor and its objects)
' NOTE 2: The module may be any VBComponent such as ThisWorkbook, ModuleN,
' SheetN or any form in Forms
' Updated: 10-Apr-2016
'---------------------------------------------------------------------------
Dim result As Boolean
Dim FromVBProject As VBIDE.VBProject
Dim ToVBProject As VBIDE.VBProject
Dim from_wb As Workbook
Dim to_wb As Workbook

Set from_wb = Workbooks("Tax_Section216_2005.xlsm")
Set to_wb = Workbooks("test_copy_macro.xlsm")

Set FromVBProject = from_wb.VBProject
Set ToVBProject = to_wb.VBProject

result = CopyModule("Module1", FromVBProject, ToVBProject, True)
End Sub

Is it possible to copy vba macros into a workbook using a vba macro to do the copying

This will copy the entire module, resulting in duplicate procedure
names if you don't remove the existing module first. Not to mention
having to do maintenance on every workbook, wouldn't it be easier (and
more productive/efficient) to maintain 1 addin?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Is it possible to copy vba macros into a workbook using a vbamacro to do the copying

Is it possible to copy vba macros into a workbook using a vba macro to do the copying

On Monday, April 11, 2016 at 9:38:40 AM UTC-4, GS wrote:
This will copy the entire module, resulting in duplicate procedure
names if you don't remove the existing module first. Not to mention
having to do maintenance on every workbook, wouldn't it be easier
(and more productive/efficient) to maintain 1 addin?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Yes, your point is very valid. I will look into the addin technique
that you suggested and report if I have any difficulty. Thank you for
taking the time to reply

Desmond

If you do a lot of this sort of work, addins would be of great benefit.
For reference material you could check out the following books...

http://www.appspro.com/Books/Books.htm

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion