VBA to VB6 DLL, interface split to support Excel 2000-2003, 2007

[Roger That]

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/communitie...2c6ee77837e1&lang=en&cr=US&sloc=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

 

[Peter T]

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

 

[Peter T]

Forgot about this -

I have yet to look at 2007, not sure yet if one dll for all versions or one
for 2007 and one for earlier versions. However when you add the ref to Excel
(VB6 / Project / References) make sure you set to the lowest version, eg
"path\excel9.olb" to cater for Excel 2000 (trust you have that installed on
your machine).

Regards,
Peter T

 

[Roger That]

Peter,

Thanks for the replies. I developed the XLA add-in using Excel 2000 and
it works on Excel 2000-2007 but I need to take advantage of the Ribbon in
Excel 2007 which is the second reason for porting this to a DLL (first being
code security).

I guess I don't know what the difference is between using an XLA wrapper
vs a COM add-in. I was just hoping to have all of the code ported from the
original XLA into a DLL with the exception of user preferences stored on the
XLA worksheets and the code for generating/configuring the menubar/commandbar
items and wanted to know if that was possible before attempting it. I guess
I am most concerned about how to handle the global variable that takes care
of the connection to the web service - initially that would be the most
important part.

Thanks for the info - I have been learning about this from the book - Ch
20 (A Hello World ActiveX DLL)

Thanks for that information as well - that will probably be a bit of a
learning curve for me. And thanks for all your help!

Sincerely,
Roger

 

[Peter T]

Peter,

Thanks for the replies. I developed the XLA add-in using Excel 2000 and
it works on Excel 2000-2007 but I need to take advantage of the Ribbon in
Excel 2007 which is the second reason for porting this to a DLL (first being
code security).

You should be able to use the Ribbon without having to port to a DLL

I guess I don't know what the difference is between using an XLA wrapper
vs a COM add-in.

You NEED to know the difference, all explained in the book. You might
consider developing the dll as a normal dll with an xla/s wrapper as step
through and debugging is a bit easier. Then add the COM interface and
convert to a ComAddin. The compiled dll can be both a ComAddin and a normal
dll if it has the appropriate classes. I know you are only catering for
XL2000+ but if you (other readers) needed to cater for XL97 it can't be a
ComAddin in that version

I was just hoping to have all of the code ported from the
original XLA into a DLL

Sounds like you will want the dll to be a ComAddin.

with the exception of user preferences stored on the
XLA worksheets and the code for generating/configuring the menubar/commandbar
items and wanted to know if that was possible before attempting it.

I think I know what you have but for your ComAddin menus best forget about
storing your commandbar details in cells and port all to the VB dll.

Other user prefernces can be stored either in the registry or an ini file.

I guess
I am most concerned about how to handle the global variable that takes care
of the connection to the web service - initially that would be the most
important part.

I don't know anything about that but I suspect very doable in the dll.

Regards,
Peter T

 

[Roger That]

Peter,

You should be able to use the Ribbon without having to port to a DLL

I am not sure about using the Ribbon without writing an XLAM/XML interface
for it (http://msdn.microsoft.com/en-us/library/bb410116.aspx).

You NEED to know the difference, all explained in the book. You might
consider developing the dll as a normal dll with an xla/s wrapper as step
through and debugging is a bit easier.

That's probably a good place to start (for me anyway). I guess some
examples of when to use which one would help.

I think I know what you have but for your ComAddin menus best forget about
storing your commandbar details in cells and port all to the VB dll.

All the commandbar/menubar information is in code, not on the XLA worksheet.

Other user preferences can be stored either in the registry or an ini file.

I am trying to avoid both of these as some of the information stored is
encrypted/hashed login information which I would prefer to keep hidden.

Thanks again,
Roger