Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Mar 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DisAssembler (97 sr2)

    Is there any utility out there (shareware or commercial) that would "disassemble" a workbook into pure VB/VBA source code? By this I mean it creates a single [most likely huge] plain text file that could be "re-compiled" into a spreadsheet complete with data, formatting, forms, and macros.

    Such a utility would be very handy for finding what is causing sluggish performance and for building formatting templates in VBA.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts

    Re: DisAssembler (97 sr2)

    If you want to find out what is causing sluggish performance have a look at FastExcel from Decision Models.
    The have some performance tips on their website (do a search to locate - don't have it handy).
    Another product I have heard about is ExcelWriter which creates Excel format files and which might do some of the stuff you are interested in.

    zeddy

  3. #3
    Lounger
    Join Date
    Mar 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DisAssembler (97 sr2)

    No, these don't really do the trick.

    As an example of what I would like to find is a program that can "translate" a userform from a graphical representation to a code representation showing all the properties and code for the form and for each control.

    This code should be capable of being run as a procedure in a different workbook and thus creating the identical userform "from scratch" in that book.

    Maybe the right term would be "de-compiler" ??

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

    Re: DisAssembler (97 sr2)

    There is a utility that can translate dialog sheets to userforms. I also know a utility that can convert a dialog to code, but I haven't seen one yet that can convert a userform into Code.
    Maybe this gives you some hints:

    http://www.j-walk.com/ss/excel/tips/tip76.htm

    Question: Why do that? What is wrong with having the form as it is, a form?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts

    Re: DisAssembler (97 sr2)

    Why don't you just export the Form and then import the Form into another workbook through VBA?

    zeddy

  6. #6
    Lounger
    Join Date
    Mar 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DisAssembler (97 sr2)

    It was that tip that got me started thinking on this issue so we are definitely on the same track.

    Why I am looking for this is so that I can extract existing forms, make tweaks via VBA for whatever needs, and then rebuild them. Below are four examples of useful tweaks...

    1. Automatically resequence the TabIndex properties based on placement and other user defined criteria. Or manually using just a list of code showing all the TabIndex properties create a subroutine to alter the TabIndex as needed.

    2. Squeeze or expand the spacing between all controls by a uniform amount by adding an offset amount to every control's location properties. This can be an automated process using some For Each sequences but I want the code as a diagnostic to test the process.

    3. Export/Import does not allow you to rename anything on the form or even the form name itself. Having the code would allow you to create the controls with meaningful names and not have to go in later. Having the code would also allow you to create multiple copies of the form with different names if you wanted / needed to do that.

    4. You have a complex form with lots of OptionButtons and suddenly realize that some of these should be Checkbox controls. Instead of doing a bunch of cut & paste and re-edit of those controls I would simply change their control type in the code and recompile the form.

    The fact that I am still learning about working with UserForms is giving me a "newbie's" perspective on what direct code tools could be useful for UserForm generation ... a VBA tradition judging by the fondness we all have for recording macros to use as code snippets. Some of these ideas I suggested above may be able to be done easily in ways I have yet to learn but until then I see a lot of value in being able to de-compile, edit, & re-compile UserForms.

    PS: The same could be said for extracting the formatting of a worksheet to build modifiable code for formatting new worksheets.

Posting Permissions

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