Hi guys,

I was dabbling with the following great piece of code, courtesy of
Chip Pearson:

Sub DeleteSheets(SheetsToKeep As Variant, _
Optional WhichWorkbook As Workbook)
Dim WB As Workbook
Dim Arr() As String
Dim N As Long
Dim V As Variant

If WhichWorkbook Is Nothing Then
Set WB = ThisWorkbook
Else
Set WB = WhichWorkbook
End If
..
..
..

And I came across the following problem: since I put all my general
subroutines in the PERSONAL.xls workbook (so that they are easily
accessed from any other project), ThisWorkbook would be PERSONAL.xls,
no matter which was the workbook from which I called DeleteSheets. I
solved this by changing

Set WB = ThisWorkbook

into

Set WB = ActiveWorkbook

I think this is the only way, if I don't want to include the Sub
DeleteSheets into each workbook which uses it. Am I right? And more
importantly, how do you manage your own Excel VBA library? Do you
store all your modules inside PERSONAL.xls or is there a better way?
Thanks,

Best Regards

deltaquattro

I like sticking everything into a workbook then saving as an Add-in.

No worries about getting "this workbook contains macros" because Add-ins are
inherently trusted.

No need to qualify the macro with a workbook name like
Personal.xls!macroname.

You can edit your existing macros to refer to ActiveWorkbook


Gord Dibben MS Excel MVP

On Fri, 5 Mar 2010 08:42:03 -0800 (PST), deltaquattro
<(E-Mail Removed)> wrote:

>Hi guys,
>
>I was dabbling with the following great piece of code, courtesy of
>Chip Pearson:
>
>Sub DeleteSheets(SheetsToKeep As Variant, _
> Optional WhichWorkbook As Workbook)
>Dim WB As Workbook
>Dim Arr() As String
>Dim N As Long
>Dim V As Variant
>
>If WhichWorkbook Is Nothing Then
> Set WB = ThisWorkbook
>Else
> Set WB = WhichWorkbook
>End If
>.
>.
>.
>
>And I came across the following problem: since I put all my general
>subroutines in the PERSONAL.xls workbook (so that they are easily
>accessed from any other project), ThisWorkbook would be PERSONAL.xls,
>no matter which was the workbook from which I called DeleteSheets. I
>solved this by changing
>
> Set WB = ThisWorkbook
>
>into
>
> Set WB = ActiveWorkbook
>
>I think this is the only way, if I don't want to include the Sub
>DeleteSheets into each workbook which uses it. Am I right? And more
>importantly, how do you manage your own Excel VBA library? Do you
>store all your modules inside PERSONAL.xls or is there a better way?
>Thanks,
>
>Best Regards
>
>deltaquattro

Hi Gord,

that's some fine suggestion: thank you! I'll do it right away. I've
seen that saving as an Add-in is easy, it's just the last voice of the
Save As menu (in my Excel 2000). I will also edit the macros to refer
to ActiveWorkbook. Thanks again,

Best Regards

Sergio

On 5 Mar, 22:42, Gord Dibben <gorddibbATshawDOTca> wrote:
> I like sticking everything into a workbook then saving as an Add-in.
>
> No worries about getting "this workbook contains macros" because Add-ins are
> inherently trusted.
>
> No need to qualify the macro with a workbook name like
> Personal.xls!macroname.
>
> You can edit your existing macros to refer to ActiveWorkbook
>
> Gord Dibben *MS Excel MVP
>
> On Fri, 5 Mar 2010 08:42:03 -0800 (PST), deltaquattro
>
> <deltaquat...@gmail.com> wrote:
> >Hi guys,
>
> >I was dabbling with the following great piece of code, courtesy of
> >Chip Pearson:
>
> >Sub DeleteSheets(SheetsToKeep As Variant, _
> > * *Optional WhichWorkbook As Workbook)
> >Dim WB As Workbook
> >Dim Arr() As String
> >Dim N As Long
> >Dim V As Variant
>
> >If WhichWorkbook Is Nothing Then
> > * *Set WB = ThisWorkbook
> >Else
> > * *Set WB = WhichWorkbook
> >End If
> >.
> >.
> >.
>
> >And I came across the following problem: since I put all my general
> >subroutines in the PERSONAL.xls workbook (so that they are easily
> >accessed from any other project), ThisWorkbook would be PERSONAL.xls,
> >no matter which was the workbook from which I called DeleteSheets. I
> >solved this by changing
>
> > * *Set WB = ThisWorkbook
>
> >into
>
> > * *Set WB = ActiveWorkbook
>
> >I think this is the only way, if I don't want to include the Sub
> >DeleteSheets into each workbook which uses it. Am I right? And more
> >importantly, how do you manage your own Excel VBA library? Do you
> >store all your modules inside PERSONAL.xls or is there a better way?
> >Thanks,
>
> >Best Regards
>
> >deltaquattro