Hi Roger,
Q. "is anything that can't/shouldn't be moved from an XLA to a DLL"
You didn't say if you intend to make a dll to be called by an xla wrapper,
or a ComAddin. If the latter the only thing not available directly in the
dll is the hidden sheets of the XLA. However there are all sorts of
alternative places to store data that might otherwise exist on hidden
sheets. If you need hidden sheets for other purposes your dll can create and
save an xla/s (I do just that for one particular objective).
You mentioned "userform", VB6 does not use Office type Userforms but
something that looks superficially similar but is in fact very different. Do
not try porting VBA userform code into a VB6 Form. What you can do though,
if you already have a complex VBA userform, is simply drag the *.frm into
the vb6 project; it will import as a "designer" and remain as a userform and
(surprisingly) it should work.
If you have a VB6 app and the book you mentioned you have everything you
need!
Some quick tips
- If using forms/userforms make sure they are set as child windows of the
excel app (see example in the book)
- Remember to qualify all Excel object declarations, eg
Dim ws As Excel.Worksheet, rng As Excel.Range
Sub foo(wb as Excel.Workbook)
- All Excel object references need to be fully qualified back to the Excel
reference, implicit type references that you may have got into the habit of
using in VBA will fail.
VBA: Range("A1") ' implicit
VB6: xlApp.Activesheet.range("A1") ' explicit
- Qualify Excel/VBA functions to the reference to Excel, eg
Set rng = xlApp.Union(r1,r2)
Regards,
Peter T
"Roger That" <Roger
wrote in message
...
Hello,
I have a working VBA add-in for Excel that needs to support Excel
versions
2000-2007. Since Excel 2007 makes use of a different menu system I will
need
to create two different interfaces (one for Excel 2000-2003 and one for
2007). I would like to try to re-use as much code as possible and would
like
to pack it into a VB6 DLL (for code security as well) and install that
plus
the desired interface XLA/XLAM(?) based on which version of Excel the user
has.
I was wondering if there is anything that can't/shouldn't be moved from
an
XLA to a DLL. Here is a list of some things the add-in currently
performs:
- connects to a web service to get/send data
(strings/datasets/collections/etc)
- utilizes MSXML4 and Regular Expressions references
- displays user forms & performs various operations based on selections
- checks for the presence of 3rd-party add-ins
- populates cells with 3rd-party add-in automation calls
- makes use of the EventClassModule class and changes menu options
according to different events (greys out options when no worksheet open,
etc)
- makes use of global CONSTS & some global variables
- writes data to log files
- stores user configurations in the XLA worksheets
I'm guessing at least that last list item will be reproduced in both XLA
and XLAM versions, but how much of the rest can be done in a VB6 DLL? I
have
done some programming in VB6 but not in this manner. I have found some
help
from this post:
http://www.microsoft.com/communities...=en-us&m=1&p=1
as well as browsing through "Professional Excel Development: The
Definitive
Guide to Developing Applications Using Microsoft Excel and VBA" by Bullen,
Bovery, & Green
Thanks,
Roger