Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to VB6 DLL, interface split to support Excel 2000-2003, 2007
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to VB6 DLL, interface split to support Excel 2000-2003, 2007
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to VB6 DLL, interface split to support Excel 2000-2003, 2007
Forgot about this -
I have a working VBA add-in for Excel that needs to support Excel versions 2000-2007 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 "Peter T" <peter_t@discussions wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to VB6 DLL, interface split to support Excel 2000-2003, 2007
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 didn't say if you intend to make a dll to be called by an xla wrapper, or a ComAddin. 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. You mentioned "userform", VB6 does not use Office type Userforms but something that looks superficially similar but is in fact very different. Thanks for the info - I have been learning about this from the book - Ch 20 (A Hello World ActiveX DLL) - Remember to qualify all Excel object declarations, eg Dim ws As Excel.Worksheet, rng As Excel.Range Sub foo(wb as Excel.Workbook) 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to VB6 DLL, interface split to support Excel 2000-2003, 2007
"Roger That" wrote in message
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 You didn't say if you intend to make a dll to be called by an xla wrapper, or a ComAddin. 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to VB6 DLL, interface split to support Excel 2000-2003, 2007
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Applying 2003 User interface to Excell 2007 | Excel Discussion (Misc queries) | |||
Split Data 1 Column to 3 columns, Excel 2000 & 2003 | Excel Programming | |||
Excel 2007 to Excel 2003 (Split data to sheets) | Excel Discussion (Misc queries) | |||
Does not support automation or does not support expected interface | Excel Programming | |||
excel vba interface with outlook (office 2000) | Excel Programming |