Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #108736 > unrolled thread

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

Back to article view | Back to microsoft.public.excel.programming

Contents

  Is it possible to copy vba macros into a workbook using a vba macro to do the copying Desmond Walsh <deasmhuinbreathnach@gmail.com> - 2016-04-10 10:02 -0700
    Re: Is it possible to copy vba macros into a workbook using a vba macro to do the copying GS <gs@v.invalid> - 2016-04-10 14:37 -0400
    Re: Is it possible to copy vba macros into a workbook using a vba macro to do the copying Desmond Walsh <deasmhuinbreathnach@gmail.com> - 2016-04-10 15:42 -0700
      Re: Is it possible to copy vba macros into a workbook using a vba macro to do the copying GS <gs@v.invalid> - 2016-04-11 09:38 -0400
        Re: Is it possible to copy vba macros into a workbook using a vba macro to do the copying Desmond Walsh <deasmhuinbreathnach@gmail.com> - 2016-04-11 13:01 -0700
          Re: Is it possible to copy vba macros into a workbook using a vba macro to do the copying GS <gs@v.invalid> - 2016-04-11 18:26 -0400
    Re: Is it possible to copy vba macros into a workbook using a vba macroto do the copying profilmuoitam18 <profilmuoitam18@gmail.com> - 2016-04-11 10:23 +0100

#108736 — Is it possible to copy vba macros into a workbook using a vba macro to 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.

[toc] | [next] | [standalone]

#108737

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

[toc] | [prev] | [next] | [standalone]

#108738

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

[toc] | [prev] | [next] | [standalone]

#108740

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

[toc] | [prev] | [next] | [standalone]

#108742

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 

[toc] | [prev] | [next] | [standalone]

#108743

> 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

[toc] | [prev] | [next] | [standalone]

#108739 — Re: Is it possible to copy vba macros into a workbook using a vba macroto do the copying

Mời bạn đọc bài viết về bất
động sản

'Choáng ngợp những phòng khách sạn đắt
đỏ nhất thế giới '
(http://tinyurl.com/zba7vpn)
'Cơ ngơi sang trọng của bà chủ công ty
người mẫu Venus ' (http://tinyurl.com/jgy5rdc)
'Soi ngôi nhà ngốn núi tiền của ca sĩ Trang Nhung
' (http://tinyurl.com/z3k8hj8)




-- 
profilmuoitam18

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web