The best way to build your own VBA library
 

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

The best way to build your own VBA library
 

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
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

The best way to build your own VBA library
 

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

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