Results 1 to 15 of 15
  1. #1
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert ExcelVBA to VB (Excel2k)

    You want to create a Com addin using .Net.

    Take a look here to get started.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  2. #2
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert ExcelVBA to VB (Excel2k)

    Thanks for the link but that's not what I had in mind. I want to re-write from scratch my Excel app in VB, I don't want to use Excel at all. What's the best approach to take for this? Or are you suggesting that I 'link' to Excel (via a COM object) to generate the output (my charts and tables)? Since some of the output data tables are quite large, it might be easier for the user to view them in Excel than me builidng a big form that pops up with the data. I dunno..... I'm trying to understand my options and the good/bad of each choice.

    I'll go ahead and install VB on my PC and see what I can do as far as a UI goes.

    Thnx anyway,
    Deb

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert ExcelVBA to VB (Excel2k)

    Hi there,
    I'd like your wise advice on something. I have finished work on a complex Excel financial modeling tool (9MB now) that's about 5000 lines of VBA and 40+ worksheets (mostly hidden from the user used for look ups or other calcs). This tool has a special menu that offers other features too (such as wizards to guide a user to a specific solution, loading/saving business cases, templates, etc.). I also have an HTML help file I wrote tied to the F1 key.

    What would it take to convert this to a pure VB program? <img src=/S/time.gif border=0 alt=time width=23 height=39> The model doesn't use any fancy add-ins, its output are a set of quarterly financials represented in tables and charts. The UI has lots of drop down lists for user selections and some navigation buttons to go from sheet to sheet (based on user-selections).

    The person who had the original concept for this financial tool is now designing a new version from scratch and so far it's 15MB (and that's w/o any of my VBA code). He's thinking of alternate ways to do the same thing so that's why I'm asking you all.

    I'm an experience programmer (10 yrs C/C++, 3 yrs VBA) but never did VB. I do have the MSDN Universal subsciption so could install VB (or use VB.NET??). Any advice on how long it would take to convert this model or to get up to speed on VB? Is there anything I'd lose if I went to a pure VB application as opposed to using Excel? It seems to me that Excel is just giving me an easy UI and nothing else. <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15>

    One advantange is that my VB app would be more secure than Excel and people who don't like macros would be more willing to use it. Another is that I don't have to waste tons of hours testing in xl97, xl200, xl2002 like I do now to find all those irritating inconsistencies that always pop up with the same code under different versions. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    Any thoughts? <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

    Thnx, Deb

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert ExcelVBA to VB (Excel2k)

    Seems to me you would not want to reinvent the spreadsheet.

    But if you want the app to be completely standalone and not
    dependent on Office apps, you'll have to find VB objects to
    do the job. The grid control might be of help.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert ExcelVBA to VB (Excel2k)

    Very true, re-inventing is never a good idea.... so you'd recommend doing the UI and pop-up forms that I have in Excel in VB then do the chart and data table output in Excel? The user would then have my VB app open and another window with Excel open, right? The Excel workbook would only be accessible via my VB program, it wouldn't contain any VBA.

    I'll do some more searching on-line for communicating with Excel via VB and try a test program.

    Thnx, Deb

  6. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert ExcelVBA to VB (Excel2k)

    Yes, that's it, but I'd go straight to .Net for this. VB, whether we like it or not is dying...
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  7. #7
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert ExcelVBA to VB (Excel2k)

    On second thought <img src=/S/cloud9.gif border=0 alt=cloud9 width=25 height=23>... it doesn't seem to make much sense to write the UI (forms and logic for my non-Excel specific features) in VB only to have the output be in Excel when Excel is so easily broken with the many password breakers out there.

    This is the issue that the person who asked me to research the VB angle was more concerned about. <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20> The fact that it's easier to steal/break into the Excel file than an standalone executable.

    Anyway, let me install VB.NET and play with it... Now where did I put my MSDN CDs... (most of which I end up tossing out since I don't need those programs). <img src=/S/groan.gif border=0 alt=groan width=16 height=15>

    Thnx, Deb

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Convert ExcelVBA to VB (Excel2k)

    I think there is an Excel-ish ActiveX control (e.g., the one that appears in the browser if you "browse in same window" for an office document). This might be a way to use as much Excel as possible while retaining total control from VB/.Net. But I don't know if it's redistributable. If it is, it would ease the "different versions" problem, too.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert ExcelVBA to VB (Excel2k)

    AFAIK COM add-ins are more secure. But I don't know anything about them.
    WHat is it that needs this protection? the code or the underlying data?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert ExcelVBA to VB (Excel2k)

    Yes, more protection for both code and the worksheets themselves (make it harder to get into the hands of competitors). With so many Excel password breakers out there it's too easy to crack it even though I use long non-Dicitionary passwords and I have a digital certificate.

    It's also a perception issue that many people don't want to run anything with macros in it. <img src=/S/spook.gif border=0 alt=spook width=15 height=15> The financial model doesn't use any fancy Excel features, no Pivot tables or add-ins, just the most simple math functions for quarterly data. The charts are on a separate worksheet. There are quite a few hidden worksheets that contain look-ups for drop down boxes and intermediate data. The user selects from a big set of options (configuration items) and populates basic financial data about their business in some tables.

    We're just trying to determine if there is a 'better' way to do this next version of this model (which is being re-written from scratch). It'd also be great to get away from constantly struggling with maintaining the code so it works in three versions of Excel <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>.

    Thnx, Deb <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  11. #11
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert ExcelVBA to VB (Excel2k)

    You'd need to get a 3rd party spreadsheet control.

  12. #12
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert ExcelVBA to VB (Excel2k)

    Code in VB .NET is not at all secure.

    THere are two main ways to secure Excel code.

    1. If you are using the Office 2000 or Office XP Developer editions, you can creare a COM Add-In.
    2. Split your code into Excel dependent and non-Excel dependent code. Compile the non-Excel dependent code into an ActiveX DLL, and the rest of the code into a password protected Excel Add-in (XLA file). Of course, XLS file can be cracked, but you could put enough into the DLL to make cracking the XLA a not so attractive adventure.

  13. #13
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert ExcelVBA to VB (Excel2k)

    Excel 2000 and EXcel 2002 do come with Speadsheet controls that may be used via VB, but ONLY if the system has Office 2000 or Office XP installed. IN which case, might as well just use Excel.

  14. #14
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert ExcelVBA to VB (Excel2k)

    If you are concerned with all 3 versions of Excel (97, 2000 and 2002), then you cannot use COM Add-In because Excel 97 does not support COM Add-Ins.

  15. #15
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert ExcelVBA to VB (Excel2k)

    So VB.NET isn't secure? You mean it's just as easy to crack an .exe made with .NET as it is an .xls file? That's really bad!! <img src=/S/barf.gif border=0 alt=barf width=64 height=23> Why is that? You'd think people would be furious over that problem. I could write it in C++ if that's any better. I just need to do the output in a spreadsheet-like format.

    How much more secure is your alternate solution (to use a COM add-in and split my code between a .DLL and a .XLA file)? I can forgo the xl97 back-ward compatibility to use the COM add-in (it was a nice-to-have sort of thing since the current version supports it). Yes I have the Developer editions (from my MSDN Universal subscription).

    It sure is hard to find out this information from MS (but I guess I should expect that). Is this discussed anywhere in any of the .NET books out there?

    Deb <img src=/S/confused.gif border=0 alt=confused width=15 height=20> and <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •