Active Workbook Add Modules - From Array List

[dj44]

folks,

good sunday


My array is not working,

I set it up to add some modules to my workbook

it looks ok




Sub Array_Modules()


Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Set VBProj = ActiveWorkbook.VBProject
Dim i As Long


Dim arrWords(4) As String

arrWords(1) = "hello"
arrWords(2) = "Test1"
arrWords(3) = "Test2"
arrWords(4) = "Test3"





For i = 0 To UBound(arrWords)


Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule)

VBComp.Name = arrWords(i)

Next i
Loop

End Sub



normally i do one module at a time i wanted a quick array to help me

it added 1 module then failed

any advice thank you

[Bob Phillips]

This works for me


Sub Array_Modules()

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Set VBProj = ActiveWorkbook.VBProject
Dim i As Long

Dim arrWords As Variant

arrWords = Array("hello", "Test1", "Test2", "Test3")

For i = LBound(arrWords) To UBound(arrWords)

Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule)
VBComp.Name = arrWords(i)
Next i
End Sub

[dj44]

Hello xld,

it works

my array failed a couple of dozen times, so i had to lay it out even simpler becuase the vcomp had some problem with it - i dont know

so long as i can add a bunch of modules im happy :)

I hate right clicking add module when i have 10 of them to add

Good sunday

[SamT]

The next step is a repository where your most used Modules have been Exported, then programmatically Importing them.

Or maybe a single workbook, then programmatically copying them.

[dj44]

I learnt a lazy man trick
Drag the text or bas files into the workbook, but sometimes thats a problem if i label the modules wrong :doh: so i have to recheck them all

[SamT]

I have several "generic" modules in My Personal, and they drag quite well in Project Explorer.

[Paul_Hossler]

I keep a gold copy of my reusable modules in a hidden Subs.xlsm in XLSTART so that they're always available to drag into a project workbook

If I have to make bug fixes or enhancements to the module in the project workbook, it's easy to drag the other way

So Subs.xlsm might have my 'mod_strings_18' in it with useful String functions, and I can drag that to a project WB. If I find I had to change or enhance 'mod_strings_18' I just rename it as _19 and drag a new gold copy back to Subs.xlsm (and probable remove the _18 version)

Later when I return to a previous project, I might have to update an older version (e.g.) 'mod_strings_11' that was the latest at that time to the new latest 'mod_strings_19'

Might not work for everyone (or anyone) but it works for me