Help/Advice Required

[JAC]

I have managed to devolve a substantial portion of VBA code from an
Excel workbook that behaves as an application into a DLL.

However, I have encountered a problem when I have other unrelated
workbooks open. Normally, when another workbook is opened when Excel
is running, the most recently opened workbook joins those already open
in the Workbooks collection.

For my special workbooks, I have an instance of Excel running in the
DLL, created using CreateObject. The calling workbook, which uses the
DLL, communicates with the instance of Excel successfully. However,
the object in the DLL has no knowledge whatsoever of any workbooks
opened outside itself.

Does anyone know how I could do this? Many thanks

 

[JAC]

I have managed to devolve a substantial portion of VBA code from an
Excel workbook that behaves as an application into a DLL.

However, I have encountered a problem when I have other unrelated
workbooks open. Normally, when another workbook is opened when Excel
is running, the most recently opened workbook joins those already open
in the Workbooks collection.

For my special workbooks, I have an instance of Excel running in the
DLL, created using CreateObject. The calling workbook, which uses the
DLL, communicates with the instance of Excel successfully. However,
the object in the DLL has no knowledge whatsoever of any workbooks
opened outside itself.

Does anyone know how I could do this? Many thanks

Do I need to use VBIDE? This would seem to be indicated by looking at
the VBProjects with open workbooks.

Can anyone point me to a How-To? Thank you.

 

[Chip Pearson]

If I were designing the application, I would not create a separate
instance of Excel. It is unnecessary, consumes resources, and makes
the logic more difficult and harder to maintain. If you require that
the functionality provided by the DLL be restricted to only one or a
few workbooks, out of any number of unrelated workbook that might be
open, you have a few alternatives. Your DLL could implement its own
Open function that the user would use to open your "special"
workbooks. The DLL would keep track of those workbooks in a Collection
or in an array. Then, when the user clicks a button or menu item or
whatever user interface you provide, your DLL could test whether the
ActiveWorkbook is in the array of "special" workbooks, and if so,
carry out the operations. If the ActiveWorkbook isn't in the array of
special workbooks, the DLL procedure would simply exit, doing nothing.
Another approach would be to create a hidden defined name called, for
example, "Special", and when the user initiates an action in your DLL
via the UI, the DLL code would look for the "Special" defined name in
the ActiveWorkbook and, if found, carry out the operation. If the name
is not found, the DLL proc simply exits.

You could implement Application level events in your DLL to get
triggers when a workbook is opened and when a workbook is closed. If
there is an identifying feature of a special workbook, such as a
specific defined name, the DLL would do whatever it needs to do for
the newly opened workbook. If such identifying information isn't
found, the DLL would do nothing. An addition to this would be to use
the App_WorkbookActivate and App_WorkbookDeactivate application events
to enable your UI features only when one of your special workbooks is
active. When another, not-special, workbook is activated, your DLL
code would disable its UI elements.

If you need to track which "special" workbooks are open, even when
other unrelated workbooks might be open, forget about using Excel's
Workbooks collection object. Insted, implement your own Collection
that tracks only special workbooks.

Creating a separate instance of Excel is wasteful and unnecessarily
complicated. It may make sense to you when you code it, but unless it
is very well documented, another developer will find it hard to figure
out what is going on when maintenance is required later.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[JAC]

If I were designing the application, I would not create a separate
instance of Excel. It is unnecessary, consumes resources, and makes
the logic more difficult and harder to maintain. If you require that
the functionality provided by the DLL be restricted to only one or a
few workbooks, out of any number of unrelated workbook that might be
open, you have a few alternatives. Your DLL could implement its own
Open function that the user would use to open your "special"
workbooks. The DLL would keep track of those workbooks in a Collection
or in an array. Then, when the user clicks a button or menu item or
whatever user interface you provide, your DLL could test whether the
ActiveWorkbook is in the array of "special" workbooks, and if so,
carry out the operations. If the ActiveWorkbook isn't in the array of
special workbooks, the DLL procedure would simply exit, doing nothing.
Another approach would be to create a hidden defined name called, for
example, "Special", and when the user initiates an action in your DLL
via the UI, the DLL code would look for the "Special" defined name in
the ActiveWorkbook and, if found, carry out the operation. If the name
is not found, the DLL proc simply exits.  

You could implement Application level events in your DLL to get
triggers when a workbook is opened and when a workbook is closed. If
there is an identifying feature of a special workbook, such as a
specific defined name, the DLL would do whatever it needs to do for
the newly opened workbook. If such identifying information isn't
found, the DLL would do nothing. An addition to this would be to use
the App_WorkbookActivate and App_WorkbookDeactivate application events
to enable your UI features only when one of your special workbooks is
active. When another, not-special, workbook is activated, your DLL
code would disable its UI elements.

If you need to track which "special" workbooks are open, even when
other unrelated workbooks might be open, forget about using Excel's
Workbooks collection object. Insted, implement your own Collection
that tracks only special workbooks.

Creating a separate instance of Excel is wasteful and unnecessarily
complicated. It may make sense to you when you code it, but unless it
is very well documented, another developer will find it hard to figure
out what is going on when maintenance is required later.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
    Excel Product Group
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)

Thanks, Chip. Most valuable insights as ever.

I was thinking along the lines of making my workbooks special as an
alternative, perhaps with a Custom Document Property.

You given me food for thought. I was unhappy about making the solution
over-complicated, and now I have some fruitful lines of attack.

Much appreciated.