+ Reply to Thread
Results 1 to 98 of 98

Create code to recreate ActiveSheet contents at run-time

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Cool Create code to recreate ActiveSheet contents at run-time

    This is going to be a) difficult to explain and b) even more difficult to solve (but not impossible ). Any takers? :D


    EXPLANATION OF WHAT I WANT TO ACHIEVE:
    INPUT: An active worksheet with cells containing values, formatting, borders etcetcera
    OUTPUT: A VBA module containing code to re-create an (almost-) exact copy of this sheet

    Another way to describe it - I am after an equivalent of de-compiling code. Except IDE would examine the sheet and work out what code is required to create an almost exact copy of the sheet.


    THOUGHTS ON HOW TO ACHIEVE/POTENTIAL DIFFICULTIES:
    I am aware that solving this will be extremely difficult. But I can't see it as being totally impossible.

    At worst case scenario, IDE could be used to loop through each cell in the used range and examine the cell contents, cell border, cell formatting etc. (Of course that would be the least efficient way to do it. It would probably easier to examine the used range for contiguous "blocks" of cells containing the same similar formatting or borders etc. and then loop through these "blocks". e.g. IDE would loop through the used range and pick up all the cell borders, then it would loop through again and pick up all the cell values and so on).

    I am also aware that the output code would be a total mess but, in most cases, it would probably much easier to tidy the output code rather than try to recreate the sheet from scratch with Macro Recorder.

    If it makes it easier to solve this, I don't require an exact copy. Number formatting can be ignored. So could Conditional Formatting etc. In fact, Cell values, Cell formulas and Cell borders are the only essential items that I can think of.


    OPTIONAL - WHY DO I NEED THIS:
    I have a large number of XLSMs. I wish to convert a lot of these to XLAMs. Converting the "input sheets" to User Forms is tricky but not impossible. The big headache I am finding is how to dynamically recreate the "output sheets" in an active workbook. If this thread was solved, it would give me the nearest equivalent of macro recorder output on creating the said sheet - then it's just a matter of me manually tidying this code.
    Last edited by arlu1201; 06-28-2013 at 01:57 AM. Reason: Thread title edited on OP request.
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,667

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Why not you just duplicate the worksheet and clear the contents alone?


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,145

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Or copy and paste it?

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,182
    Just copy the sheet.
    If posting code please use code tags, see here.

  5. #5
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    @ Sixth Sense, Kyle123 & Norie:
    But I don't want to copy the sheet. I want to dynamically recreate the sheet.


    @ Everyone else:
    Folks, please don't try to "second guess" me on what I am hoping to achieve from this thread.
    I do have some practical uses for this code
    (supposing anyone is talented enough to pull off the solution ).
    So please refrain from posting any suggestions that involve copying sheets - that is not what the thread is asking!
    Thank you
    Last edited by mc84excel; 05-29-2013 at 08:14 PM.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,182

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Copying the sheet is dynamically creating the sheet.

  7. #7
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by Norie View Post
    Copying the sheet is dynamically creating the sheet.
    ...

    I wont have the original sheets any more.
    I wont have the original workbooks any more.
    I need the code to recreate these sheets "on the fly". Not copied.

    To mark this thread as completed, I need IDE code that will produce an output that would be an extremely rough equivalent to what macro recorder would have produced after creating these sheets originally.

  8. #8
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    I got it: you want to create a Macro that records what was on the old sheet and save it as vbs (for example). then use that macro later on to create an identical version of the old one. Am I right?
    Last edited by adyteo; 05-29-2013 at 05:03 AM.

  9. #9
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,145

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    I really don't get why you don't just copy and paste. The whole question is seriously over-complicating and over-engineering a simple operation

  10. #10
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by adyteo View Post
    I got it: you want to create a Macro that records what was on the old sheet and save it as vbs (for example). then use that macro later on to create an identical version of the old one. Am I right?
    YES! At least someone understood what I want to achieve!

  11. #11
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,644

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by Kyle123 View Post
    I really don't get why you don't just copy and paste. The whole question is seriously over-complicating and over-engineering a simple operation
    +1 .
    Entia non sunt multiplicanda sine necessitate

  12. #12
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by Kyle123 View Post
    I really don't get why you don't just copy and paste. The whole question is seriously over-complicating and over-engineering a simple operation
    Please read the latter section of post #5. Thank you.

  13. #13
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by shg View Post
    +1 .
    Please read the latter section of post #5. Thank you.

  14. #14
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Angry Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Before posting to this thread please read the latter section of post #5.


    I accept that very few people will ever have a use for this solution. However I am one of those few.

    If my explanation of what I want to achieve was so obtuse that it took a while before anyone even understood what I am looking for (thanks adyteo), how do you think my explanation of what I want this code for would fare? I'm not even going to try. (I can visualize what I want to do with the code, I'm just not good at putting this into words).

    If you can't/don't want to provide code towards helping solve this thread - then please don't post in this thread at all. Address the thread question or not at all.
    (I'm sorry I have to be so blunt but I don't want any more copy/paste suggestions nor do I want any more 'you don't need/why do you need this solution' posts).

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,182

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    I actually understood what you meant from the get go.

    I didn't reply then because I couldn't think of an easy way to do it.

    Where and how are you going to store all the properties of each cell on the worksheet?

    Also, how are you going to extract that information in an efficient manner?

    One thought I did have was to do something with the XML files that make up a workbook but that would only apply to files that were OpenXML format.

  16. #16
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Thanks Norie. +1 for being helpful

    Where and how are you going to store all the properties of each cell on the worksheet?
    I've never learnt IDE but I understand that IDE could be used to create/edit VBA code. I was hoping that IDE could be used to create a new module in the workbook, scan the used range of the active sheet and, while scanning, update the new module to record the output/resulting code. Pardon my IDE ignorance - this is not possible?
    (Otherwise, I am happy with any output format really as long as I can copy & paste the code from that format into a new module in another workbook).

    And to make it simpler, I don't require ALL the cell properties/values/colours/cell borders/cell formatting/conditional formatting/number formatting/protection etcetcera. That would be a complete nightmare. The cell value/formula + cell borders is all that I really need from this threads code result (because I figure it would be faster for me to use Macro Recorder to record the code for the cell colours and for the cell formats and then combine this with the IDE code result).


    how are you going to extract that information in an efficient manner?
    I know - that will be a major headache. Looping through each cell in used range would be extremely inefficient - but may be required in order to solve the thread (I pondered this in post #1).

    Maybe thinking through the Macro Record process (but in reverse) may help - when we create a sheet, we don't manually select each cell and add a border, normally we would select a block of cells and add a border. If somehow IDE could identify a "block" of cells with the same border, IDE could record the code needed to create the borders for that block?

  17. #17
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,182

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Sorry I'm confused.

    You actually want to wrie code that will create code that will recreate a worksheet?

    It's would be hard enough to write the code to recreate the worksheet.

    As for gettin the IDE to recgonise 'blocks', I really can't see that happening.

    Also, what if there are no blocks?

    How complicated are the worksheets anyway?

  18. #18
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by Norie View Post
    You actually want to wrie code that will create code that will recreate a worksheet?
    That is correct.


    Quote Originally Posted by Norie View Post
    It's would be hard enough to write the code to recreate the worksheet.
    That is true. Which is why I am trying to save manual labour by having most of the code reproduced by IDE.

    Quote Originally Posted by Norie View Post
    As for gettin the IDE to recgonise 'blocks', I really can't see that happening.
    When I refer to "blocks", I mean a range of contigous cells sharing some of the same properties. e.g. say A1 to D8 has a border outline. I was hoping that IDE could detect that and provide the code for it. (As it would be more efficient then trying to work out by looping through each cell - which I add would produce code far worse than Macro Recorder )


    Quote Originally Posted by Norie View Post
    How complicated are the worksheets anyway?
    Too complicated to recreate manually with Macro Recorder on. Thus this thread. (But as long as IDE can give me the code to reproduce the formulas & borders in a new worksheet, I will use Macro Recorder to work out all the other formatting required and then just merge both procedures together)

  19. #19
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Suppose I have Macro Recorder on and I add a new sheet and fill it with values & borders etc. In effect the Macro Recorder then gives an output of the code needed to recreate that entire sheet again at will - in any workbook.

    What I am after is, to so speak, the decompiling equivalent of creating a sheet with Macro Recorder on.

    I want code that I can input a sheet and have the code output VBA code that could be used to create that sheet again. (Obviously the output code will need extensive editing - but then so does code from Macro Recorder).

  20. #20
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,667

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Still I cant able to understand your logic of creating a macro for reading cell formats, still just wondering why not you just keep a template workbook and use a code to open the file and copy a sheet from it?

  21. #21
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by :) Sixthsense :) View Post
    Still I cant able to understand your logic of creating a macro for reading cell formats, still just wondering why not you just keep a template workbook and use a code to open the file and copy a sheet from it?
    Deep sigh. Please see post #5 & #14.

  22. #22
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,976

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Just to add my 2c worth, and no, Im not going to suggest you copy/paste anything...anywhere lol.

    I have nothing material to add towards solving your question, I will simply add that to be totally closed to alternative solution/options, seems a little short-sighted to me

    I sincerely hope you find the answer you are looking for
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  23. #23
    Registered User
    Join Date
    12-18-2012
    Location
    Houston TX
    MS-Off Ver
    Excel 2007
    Posts
    19

    Lightbulb Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Naughty boy, you are trying to use VBA to write VBA. If Bill Gates had wanted us to do this, He would have given us LISP.

    Nonetheless, I think you want to do something like this.
    Please Login or Register  to view this content.
    This will put the function definition out into "replicant.bas"; which you can load as a module. The code need to be extended to handle empty cells, etc, and to do whatever you're doing with the borders. And I'm not sure I've got all those $%#$@&! double-quotes right. But I've run the code and looked at the output, and I think the approach is clear and sound.

  24. #24
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,644

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Nice work, Adam

    You have a few extra quotes, and the Text property of a cell is read-only. I replaced your Print statements for convenience.

    Please Login or Register  to view this content.
    More generally, though, if you're going to serialize the worksheet, why not just export as html (a text file is a text file) instead of reinventing the wheel?
    Last edited by shg; 05-30-2013 at 07:49 PM.

  25. #25
    Registered User
    Join Date
    12-18-2012
    Location
    Houston TX
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    thanks, shg

    You ask if you're going to serialize the worksheet, why not just export as html (a text file is a text file) instead of reinventing the wheel?

    He wants the page to be creatable as part of an add-in. You can include a function def in an add-in; I can't think of how you'd include an html file in an add in.

  26. #26
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by FDibbins View Post
    to be totally closed to alternative solution/options, seems a little short-sighted to me
    Thank you FDibbins. I hear what you say and would normally agree with the concept of the OP being open to alternative solutions/options.

    However, in a few exceptions, I specifically want the outcome in the thread title so I state that requirement in order to prevent forum users suggesting the same "alternative" over and over. However it doesn't seem to work.

  27. #27
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by AdamBecker View Post
    Naughty boy, you are trying to use VBA to write VBA.
    LOL. I thought that's what IDE was about - using VBA to output more VBA?!

    Your code btw is brilliant +1

  28. #28
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by shg View Post
    I replaced your Print statements for convenience.
    Firstly, +1 for helping out - especially on something you think is useless.

    Regretfully though, I prefer the code supplied by Adam (No offence intended - my Immediate Window doesn't hold all the output from your version of Adams code. I note your change on Text property - thanks)

  29. #29
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Thanks to Adam, the code is now started and slowly making progress. See below for current WIP.

    My biggest headache at the moment is those pesky """s.


    Please Login or Register  to view this content.

  30. #30
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Apostrophe hell solved. Also fix for line breaks in original sheet cells (removes them - I would like to keep them if possible but that's not a major goal).

    See code below. I'm open to any suggestions on improving this code.

    I would be most interested in any code that could output as much as possible as ranges rather then cells.

    Please Login or Register  to view this content.
    Last edited by mc84excel; 05-31-2013 at 03:28 AM. Reason: line break now solved

  31. #31
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,644

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by mc84excel View Post
    Firstly, +1 for helping out - especially on something you think is useless.
    It is beyond useless; it is the antithesis of data-driven design. I was complimenting Adam on the code, independent of purpose.

    Regretfully though, I prefer the code supplied by Adam (No offence intended - my Immediate Window doesn't hold all the output from your version of Adams code. ...)
    None taken; the post was not directed to you, and I wasn't suggesting it as a change. It was a convenience to not have to create a file while running Adam's code.
    Last edited by shg; 05-31-2013 at 10:04 AM.

  32. #32
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by mc84excel View Post
    I'm open to any suggestions on improving this code.
    but not on improving on it presumably ;-)

    I would be most interested in any code that could output as much as possible as ranges rather then cells.
    you might find that if you created your original sheet using styles it would make life easier (although that is not an obvious goal) but borders around ranges rather than individual cells will always complicate as there is no property equivalent of the borderaround method

    have fun :-)
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  33. #33
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by shg View Post
    It is beyond useless;
    But not useless to me. I need this solution.


    Quote Originally Posted by shg View Post
    it is the antithesis of data-driven design.
    How so?
    For arguments sake, imagine you created an EXE and lost the source, would you consider decompiling this to be the antithesis of data-driven design??


    Quote Originally Posted by shg View Post
    It was a convenience to not have to create a file while running Adam's code.
    Yes well this thread is not a theoretical exercise. The typical worksheets I am looking at using this code on generate far too much output for the Immediate Window to hold (so your idea is theoretically correct just that it is useless for practical purposes).

  34. #34
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Good to see you're back Joseph.

    Quote Originally Posted by JosephP View Post
    but not on improving on it presumably ;-)
    I don't follow. I welcome any suggestion that would help achieve the thread outcome (viz. use VBA to examine worksheet and then output the VBA code needed to create a copy of the sheet)


    Quote Originally Posted by JosephP View Post
    you might find that if you created your original sheet using styles it would make life easier
    I know but the sheets I want to run a solution on weren't created using styles.


    Quote Originally Posted by JosephP View Post
    borders around ranges rather than individual cells will always complicate as there is no property equivalent of the borderaround method
    I figured that bit out re the borders not working as a range.
    My reference to ranges (in post #30) - I intended to mean formulas (not borders). Think of Excels error checking option where it alerts you that a formula isn't the same as the one next to it. I think it is possible to create code to do the equivalent of this.
    Last edited by mc84excel; 06-02-2013 at 10:52 PM. Reason: clarify

  35. #35
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Still forging resolutely onward to where no excel coder has ever gone before

    I am now picking up column widths. But, more importantly, I have managed to come up with code that recognises 'blocks' (contiguous columns/rows with similar properties). (See the code that handles the column widths to see what I mean). It ain't pretty but it seems to be working. (I am open to & interested in any suggestions on improving the block detect/write)

    Next step is to implement the block detect/write code into 1. the existing code for cell formulas and 2. start creating code to pickup the row widths.

    Please Login or Register  to view this content.

  36. #36
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    briefly popping on to update this thread. An issue with Column Width vs ColumnWidths is now solved. Rows now solved. Indent of output has been improved. Code so far below:

    Please Login or Register  to view this content.

  37. #37
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,644

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Fascinating.

  38. #38
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,182

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Where's the part that writes the code to create the worksheet to the IDE?

  39. #39
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Unfortunately, if you have a bigger sheet (I tested it on 16 columns x 2763 rows) when running the module just created you get an error: Procedure too large.
    (Help from MS: "When compiled, the code for a procedure can't exceed 64K. This error has the following cause and solution:
    Code for this procedure exceeds 64K when compiled.
    Break this, and any other large procedures, into two or more smaller procedures."
    We need now somehow to automatically break the procedure in several consecutive procedures to avoid this error.
    Otherwise it is working very well.
    What we can try to do is to save the "data" in a CSV file and the "formatting" in the code so we will have two files: first format the worksheet and then import the data from CSV file.
    Last edited by adyteo; 06-11-2013 at 01:43 AM. Reason: To offer a suggestion for avoiding the error.

  40. #40
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,182

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    I tested it on a sheet with only 350ish rows and 26 columns (approx 8800 cells) with all the columns having the same width.

    That also generated code that caused the 'Procedure is too large' problem.
    Last edited by Norie; 06-11-2013 at 04:40 AM.

  41. #41
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by Norie View Post
    Where's the part that writes the code to create the worksheet to the IDE?
    That was what I originally had in mind. But I don't know how to do this.

    Personally I think that outputting to a bas module is a superior approach. (Because it would be pointless having the output module created in the same workbook. I would be importing the output .bas into the add-in I require it for - after some editing for cell formatting & borders of course).

  42. #42
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by adyteo View Post
    Unfortunately, if you have a bigger sheet (I tested it on 16 columns x 2763 rows) when running the module just created you get an error: Procedure too large.
    (Help from MS: "When compiled, the code for a procedure can't exceed 64K. This error has the following cause and solution:
    Code for this procedure exceeds 64K when compiled.
    Break this, and any other large procedures, into two or more smaller procedures."
    We need now somehow to automatically break the procedure in several consecutive procedures to avoid this error.
    Otherwise it is working very well.
    What we can try to do is to save the "data" in a CSV file and the "formatting" in the code so we will have two files: first format the worksheet and then import the data from CSV file.
    Thanks for pointing this out. I hadn't realised this was happening. In the real world example I was testing it on - the used range was ~140 rows by ~28 columns.

    I will have to think through this issue.

  43. #43
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by adyteo View Post
    Unfortunately, if you have a bigger sheet (I tested it on 16 columns x 2763 rows) when running the module just created you get an error: Procedure too large.
    I have an idea but I need some feedback on the sheets that failed.

    Was your sheet populated with random values (for testing this code)?
    If not, what were the formulas mainly consisting of (in general terms)?

    What I'm trying to get to here - is to see if any of the cell formulas were repeated down columns. If a large number of them were then I should try to implement the contiguous block code into the formula section. This would help reduce the amount of output code.

  44. #44
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by Norie View Post
    I tested it on a sheet with only 350ish rows and 26 columns (approx 8800 cells) with all the columns having the same width.

    That also generated code that caused the 'Procedure is too large' problem.
    I have an idea but I need some feedback on the sheets that failed.

    Was your sheet populated with random values (for testing this code)?
    If not, what were the formulas mainly consisting of (in general terms)?

    What I'm trying to get to here - is to see if any of the cell formulas were repeated down columns. If a large number of them were then I should try to implement the contiguous block code into the formula section. This would help reduce the amount of output code.

  45. #45
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    If the sheets by Norie and adyteo were using large quantities of non-contiguous data then I will need to look at another solution. My current idea is to output separate modules. So the first module output would be a "main" sub which will pass the sheet object as an argument to functions in the other modules. The other modules would be separate modules for the output of the column widths, row widths, cell formulas, cell formats and so on. So when creating an add-in, you would need to import multiple modules in order to create this sheet.

    (In the real world, I don't need to recreate sheets of such a size that they would this fix. However I should still try to solve it anyway).

  46. #46
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,182
    The sheet I tested on had no non-contiguous data.

  47. #47
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    So it was just the same formula copied down each column?

    If there is no confidential data on it, could you please upload a copy of the sheet sometime so I can run tests on it?

  48. #48
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    I am putting to one side my idea about breaking the code into separate modules - the sheet tested by Norie sounds like the code output would exceed a modules limits.

    So my current idea is to look into how Excel detects "Formulas inconsistent with other formulas in the region" (Error checking rules). I need to somehow replicate this behavior in VBA.

    If I can produce a function which does this - then, when looping through the cells, I can check each cell to see if it has a consistent formula with the previous cell - and if so, add this cell to the current "block" so that it will be output as a range of cells rather than one formula per individual cell. This would vastly reduce the amount of output code where a sheet has a large amount of contiguous formulas.

    (The looping to add to a range has already been solved - refer my last code uploaded - the column widths & row heights. The really tricky bit will be trying to come up with a function that does the equivalent of Excels consistent formula detection - I'm not sure where to begin solving this).
    Last edited by mc84excel; 06-18-2013 at 08:53 PM.

  49. #49
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,145

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    I'd still just copy the sheet....

  50. #50
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by Kyle123 View Post
    I'd still just copy the sheet....
    Oh? And the XLAM size would balloon in order to hold all the report worksheets that it would need. I don't know about you but I like to keep XLAMs as small as possible in order to reduce the overhead on the end user. So if I could reproduce the report sheets at runtime from the XLAM that would keep its file size down.

    (BTW I said previously in this thread that I wasn't interested in any more suggestions of copying sheets).

  51. #51
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,145

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    I was joking since you've already made your mind up

    To address your question, I wouldn't store them in the add-in, I'd store them in a folder and just import them as necessary. Having folders containing relevant application data is pretty much standard in any actual application you install. On starting your add-in, you'd simply loop through all the files in the folder and maintain a list of all the templates you've created so that the user knows what's there and available to create

  52. #52
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Which would work if you knew that all the users were on the same computer. (Or at least - if you could be sure they would place the templates in the required location).
    At that point, I decided it would be easier to insert code into the add-in to create the reports at run-time.

    (And the concept seems to be working OK. Just it can't handle very large sheets...so far)

  53. #53
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    My current idea is to look into R1C1 to see if that will aid the formula loops for detecting ranges.

    I'm also very interested in how Excel detects "Formulas inconsistent with other formulas in the region" (Error checking rules). I need to somehow replicate this behavior in VBA. I would be interested in any input/suggestions on writing a function for this.

  54. #54
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by Norie View Post
    I actually understood what you meant from the get go.

    I didn't reply then because I couldn't think of an easy way to do it.

    Where and how are you going to store all the properties of each cell on the worksheet?

    Also, how are you going to extract that information in an efficient manner?

    One thought I did have was to do something with the XML files that make up a workbook but that would only apply to files that were OpenXML format.
    I'm nowhere close to talented enough (at present ) to propose a solution anywhere near what mc84excel is asking for... but I'm thinking along the same lines... he has to store the information in some manner, some way. I just checked my Excel 2010 export formats (Save As) and it has an Excel Add-in format (*.xlam). Have never had a reason to use it, nor do I know how it compares to what mc84excel wants, but it seems like that is the route I would choose to at least get started...

    ETA: Sorry about the late post. I just read to the bottom of the first page without realizing there were 3 more
    Last edited by jhren; 06-23-2013 at 09:26 PM.

  55. #55
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    In the meantime, I have now altered the code to deal with 1D vertical ranges with the same formulas.

    To do:
    • Improve this new cell range code to do the same for 1D vertical ranges containing values
    • Somehow detect & record 2D ranges not just 1D (anyone have any suggestions/input?)
    • Somehow come up with a Function that mimics Excels non-contiguous range error check (anyone have any suggestions/input?)
    • Deal with merged cells
    • Improve the loops used in code (I think Do/While/Until would be more useful here)
    • Improve end of loop detection/avoidance
    • Improve the empty cell detection
    • Add loops for cell borders and/or formatting and/or cell styles

  56. #56
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,145

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    At that point, I decided it would be easier to insert code into the add-in to create the reports at run-time.
    that's one pretty big leap!!

    Which would work if you knew that all the users were on the same computer. (Or at least - if you could be sure they would place the templates in the required location)
    Just store them relative to the location of the add-in.... Or if you want to share them on a network folder, drop box etc...

    Anyway, I'll stop stirring, I promise to keep my neb out now

  57. #57
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by Kyle123 View Post
    that's one pretty big leap!!
    You think so? At the moment the code is ~ 90% there in terms of what I need it for.
    The reason why there is so much left to do is because I want it to be able to handle almost any worksheet not just the ones I need to use it for (so adyteo and Norie could run it and it would work)
    So I need to improve the range detection/print.

  58. #58
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Here's the latest version. I can't upload in one go as it exceeds character limit. So it is now broken down into two modules.

    Main Module:
    Please Login or Register  to view this content.

  59. #59
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    2nd module:
    Please Login or Register  to view this content.

  60. #60
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,182
    Why all the Private and module level declarations?

  61. #61
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Hi Norie,

    Private = Because I believe it is good practice to keep scopes as tight as possible. Any procedures or declarations that I don't need outside of the current module I always set to private.

    Module Level Declarations = Because the subs in the 2nd module are re-using the same variable names. So there is no harm in setting all of these to module level declaration (especially as each sub in the 2nd module is called individually from the 1st module - the variables would be flushed at the end of each sub in the 2nd module).

    P.S. How has the latest version of the code worked on your large Excel sheet? The output should be greatly reduced.

  62. #62
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,182

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    My Excel sheet wasn't large.

  63. #63
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by Norie View Post
    My Excel sheet wasn't large.
    Sorry my mistake. Anyway does the latest code (posts #58 & #59) still crash on this sheet due to size limitations? Or does the latest code now work for you?
    Last edited by mc84excel; 06-24-2013 at 06:31 PM.

  64. #64
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Talking Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Vastly improved loops in all replicant functions. (Also altered the variable names so they make more sense). Current progress uploaded.

    Next = work on a 3rd loop to detect the Cell ranges...
    Attached Files Attached Files

  65. #65
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Ha! Now where all the people who said this couldn't be done?!


    I have finally cracked the 3rd loop (to pick up the rightmost extent of the current range being checked).


    See attached workbook. If you click the button on the test worksheet, it should create a .bas module in your 'My Documents' which can then be imported into Visual Basic Editor and run to create the cell values/formulas in a new sheet.

    Next - to look at expanding the code to cover: cell colours, font styles, cell styles, cell formats and borders. (I expect no difficulty with any of these with the exception of borders).

    Also I need to deal with the problem of merged cells (which you shouldn't be using anyway )
    Attached Files Attached Files

  66. #66
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,182

    Re: Create code to recreate ActiveSheet contents at run-time

    Why isn't the button replicated?

  67. #67
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by mc84excel View Post
    Ha! Now where all the people who said this couldn't be done?!
    the word was shouldn't rather than couldn't

  68. #68
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Create code to recreate ActiveSheet contents at run-time

    Quote Originally Posted by Norie View Post
    Why isn't the button replicated?
    Because replicating buttons & images was never stated/intended to be an outcome. (BTW does the new code work on your 8800 cell worksheet? PLMK)

  69. #69
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by JosephP View Post
    the word was shouldn't rather than couldn't
    I was under the impression it was both?

    Oh well - at least one person (me) has a practical use for this code, it's not like its theoretical.
    Last edited by mc84excel; 07-02-2013 at 06:09 PM.

  70. #70
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by mc84excel View Post
    BTW ever noticed that some Forum users tend to react to theoretical exercises in VBA?)
    I can't speak for others but from my perspective it's not that they are theoretical but the fact that they imply design/coding practices with which I strongly disagree and which I feel will cause more problems in the long run: I don't believe in short term gain for long term pain and reckon I would be doing a disservice to assist in such endeavors

    clearly you will never appreciate this so I for one will try to stop wasting my time on it-though it is surprisingly difficult to simply sit and watch

  71. #71
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,145

    Re: Create code to recreate ActiveSheet contents at run-time

    copy and paste .....

  72. #72
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Create code to recreate ActiveSheet contents at run-time

    it's a new record for me-got 13 notifications of that last posting B-0

  73. #73
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by Kyle123 View Post
    I'd still just copy the sheet....
    Or make a template.
    Be fore warned, I regularly post drunk. So don't take offence (too much) to what I say.
    I am the real 'Napster'
    The Grid. A digital frontier. I tried to picture clusters of information as they moved through the computer. What did they look like? Ships? motorcycles? Were the circuits like freeways? I kept dreaming of a world I thought I'd never see. And then, one day...

    If you receive help please give thanks. Click the * in the bottom left hand corner.

    snb's VBA Help Files

  74. #74
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,145

    Re: Create code to recreate ActiveSheet contents at run-time

    @Dave, you don't want to open that can of worms I'm just baiting lol

  75. #75
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Create code to recreate ActiveSheet contents at run-time

    Well, if your baiting, aren't you glad I opened those can of worms? LOL

  76. #76
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by JosephP View Post
    I can't speak for others but from my perspective it's not that they are theoretical but the fact that they imply design/coding practices with which I strongly disagree and which I feel will cause more problems in the long run: I don't believe in short term gain for long term pain and reckon I would be doing a disservice to assist in such endeavors

    clearly you will never appreciate this so I for one will try to stop wasting my time on it-though it is surprisingly difficult to simply sit and watch
    Please accept my sincere apologies - I never intended my comment (re certain forum users & theoretical exercises) to be taken as including yourself as one of these.

    And although I may not always appear to - I do carefully consider your corrections and/or alternative solutions before jumping off the deep end.


    With this thread here - where am I creating long term pain?


    I want to produce certain report worksheets from a XLAM. I have considered (& dismissed) the following alternatives:
    1. Copy & paste (While it is possible to have worksheets inside a XLAM and copy them to another workbook, I need to keep the XLAM as small as possible to reduce memory overhead on the end users - having the code to create the sheets at run-time takes up less size than the XLAM containing all the sheets themselves)
    2. Use of Templates (Not all users on same servers or even same countries)

  77. #77
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Create code to recreate ActiveSheet contents at run-time

    Quote Originally Posted by Kyle123 View Post
    copy and paste .....
    Kyle...

  78. #78
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,644

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Now where all the people who said this couldn't be done?!
    I don't think they were ever here; the consensus is that it's a waste of time.

    ... having the code to create the sheets at run-time takes up less size than the XLAM containing all the sheets themselves
    There is no way on God's green earth that you are going to create code that duplicates the content and formatting of non-trivial worksheets in a smaller footprint than the workbook saved as a binary file. I will happily put up a $1000 donation to your favorite charity if you'd like to match it: I'll provide the workbook, you do the code, and you have six months to perform.

    You haven't even skimmed the surface.
    Last edited by shg; 07-02-2013 at 06:57 PM.

  79. #79
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,644

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    My five-minute list:

    Font face, size, style, color, underline, and strikethrough
    Rich-text formatting for all of the above
    Array formulas
    Fill color & pattern
    Borders
    Conditional formatting
    Workbook styles
    Cell styles
    Cell protection
    Borders
    Number formatting
    Horizontal and vertical alignment
    Named constants, formulas, and ranges
    Tables
    Data validation
    1D and 2D data tables
    Filters
    Solver models
    Page setup -- headers & footers (including rich-text formatting), margins, scaling
    Themes
    Scenarios
    Views

  80. #80
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by shg View Post
    I don't think they were ever here; the consensus is that it's a waste of time.
    Well then why is there so much negativity on this thread about what I am trying to achieve? (For that matter - why can't all of us Forum users simply get along? )
    As for waste of time - aren't all theoretical exercises?



    Quote Originally Posted by shg View Post
    There is no way on God's green earth that you are going to create code that duplicates the content and formatting of non-trivial worksheets in a smaller footprint than the workbook saved as a binary file. I will happily put up a $1000 donation to your favorite charity if you'd like to match it: I'll provide the workbook, you do the code, and you have six months to perform.
    Firstly +1 for your generosity and also for making your point in spades.

    Secondly I decline your challenge as I know that you are right and that this challenge is impossible.
    (The apparent conflict between what I am stating here and what I stated earlier in post #76 is the context of the quote from post #76 - the worksheets I need this solution for are what you would class as trivial and, as such, I believe that the module to recreate these sheets would take up less size then the sheets themselves).


    Quote Originally Posted by shg View Post
    You haven't even skimmed the surface.
    I humbly accept what you say.
    For what it's worth - I am not trying - and never intended - to be able to recreate every possible item/object/content on a worksheet. (See post #65 for my remaining intended outcomes. These are doable I believe).
    Last edited by mc84excel; 07-02-2013 at 07:54 PM.

  81. #81
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,644

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    As for waste of time - aren't all theoretical exercises?
    Not at all -- but you said somewhere, either in this protracted thread or a PM, that this had an important purpose that you were totally unable to articulate, but it was important. Had you cast it as an exercise, I think it would have been benignly ignored.

    I am not trying - and never intended - to be able to recreate every possible item/object/content on a worksheet
    That's why there is no mention of charts, shapes, pictures, embedded files, ...

    In the words of Robert Heinlein, "Never try to teach a pig to sing; it's a waste of time, and it annoys the pig."
    Last edited by shg; 07-02-2013 at 08:12 PM.

  82. #82
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by shg View Post
    Not at all -- but you said somewhere, either in this protracted thread or a PM, that this had an important purpose that you were totally unable to articulate, but it was important. Had you cast it as an exercise, I think it would have been benignly ignored..
    I'm sorry shg but I do not recall ever stating (in thread or PM) that this thread was important (nor likely to state this either as all along I've firmly believed that very few people would ever have a use for this solution).


    Quote Originally Posted by shg View Post
    That's why there is no mention of charts, shapes, pictures, embedded files, ....
    As for charts, shapes etc. - these and the challenge you offered me (post #78) stemmed from a misunderstanding of post #76.
    I already acknowledged that you were right and that your challenge was impossible to achieve (see post #80).


    Quote Originally Posted by shg View Post
    In the words of Robert Heinlein, "Never try to teach a pig to sing; it's a waste of time, and it annoys the pig."
    I accept I am a rookie to VBA but there is no call to be rude (every learner has to start somewhere).

  83. #83
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,644

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    It was not my intent to be rude, but you do ask for suggestions, and then get annoyed if people try to point you in a different direction, to the extent that you now have it encapsulated in amber in your sig:

    It is OK to offer alternative solutions to an OP (unless OP specifies otherwise).

  84. #84
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    That is because I believe that saves time and effort for everyone.

    To give an example: suppose an OP is looking for a solution to a practical problem, he/she considers possible solution X and possible solution Y and dismisses these because it wouldn't work in their situation. By stating in their thread that they specifically do not want solution X or solution Y - that saves the time and effort of other forum users posting 'I know. Why don't you try solution X?'.

  85. #85
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Create code to recreate ActiveSheet contents at run-time

    Latest progress attached. I'm not looking forward to doing the borders.
    Attached Files Attached Files

  86. #86
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Reading a book on VBA recently and it has given me an idea. Special cells. Implementing this would improve the formula/constant cell loops.

  87. #87
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Exclamation Macro Unrecorder

    I know I've had this project on hold for a while but now I have a pressing need for it. From the time of this post, I have less than 26 hours left to get this to ~80%~90% working.

    If anyone wishes to help, I'd appreciate it. (To those who think this is a stupid idea or can't be done - I've already been told so you don't need to tell me that)

    Attached is my latest version.
    • It now has weak support for comments (text only) and data validation (which is not completely working - I need to handle the first argument differently to the rest)
    • Coding changed to my current coding conventions
    • Tried to implement generic functions to make it easier to add new read/writes (non-generic functions use Select Case and a public enum to determine how to handle the current 'mode')
    • I know my tests for data validation is weak (only matching on same text) but it is sufficient for my current needs.


    Current goals:
    • Add support for named ranges (UPDATE: Solved for WS ranges)
    • Add support for Borders (UPDATE: working but the output code is terrible)
    • Add support for cell formatting and font styles (not interested in capturing different fonts)
    Attached Files Attached Files
    Last edited by mc84excel; 03-10-2014 at 01:02 AM. Reason: Update

  88. #88
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Unhappy Macro Unrecorder

    Less than 21 hours to go...

    Attached is latest version.

    Good = Replaced over-cumbersome cell formula/value code with the generic functions

    Bad = I had the Borders working (albeit with horrible code output) and I had what seemed a good idea at the time - to separate the Border loops into Horizontal borders & Vertical borders. And well take a look. I must have screwed up badly on the new NC version of the loops (NC = next column checked instead of NR = next row checked). I will start debugging the border screw-up tomorrow morning...
    Attached Files Attached Files

  89. #89
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Macro Unrecorder

    Borders now solved. Added Cell Protection mode.

    Less than 10 hours to go...

  90. #90
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,182

    Re: Create code to recreate ActiveSheet contents at run-time

    I don't have a My Documents folder.

  91. #91
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Create code to recreate ActiveSheet contents at run-time

    Quote Originally Posted by Norie View Post
    I don't have a My Documents folder.
    Unusual. Well what do you get if you type this into the immediate window?
    Please Login or Register  to view this content.

  92. #92
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Cell Alignments

  93. #93
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,182

    Re: Create code to recreate ActiveSheet contents at run-time

    Why do you want to know that?

  94. #94
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Macro Unrecorder

    Quote Originally Posted by Norie View Post
    Why do you want to know that?
    Because that is where the code is saving the text file output. If you search that location after running the code, it should be there?

    (The default file path is usually the My Documents folder so it is simpler to tell non-technical end users to look in My Documents)

  95. #95
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Macro Unrecorder

    I'm making progress. But encountering lots of problems.

    PROBLEMS
    • borders patchy - some borders are being missed by code. why?
    • cell format alignment = impossible? (unless design loop to expand to new range each time?)
    • print area is being detected as valid worksheet name
    • cell styles picking up custom cell styles e.g. Normal 2 (could ask end user to run macro to erase non-custom styles before start?)(I don't need custom styles for my current projects)


    TO DO
    • font color
    • font size
    • set print range
    • cell colors/shading
    Attached Files Attached Files
    Last edited by mc84excel; 03-10-2014 at 09:41 PM.

  96. #96
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Create code to recreate ActiveSheet contents at run-time

    Quote Originally Posted by Norie View Post
    I don't have a My Documents folder.
    You can edit the line
    Please Login or Register  to view this content.
    to whatever folder path you want.

  97. #97
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Cool Macro Unrecorder

    Finally. I loathe crunchtimes.

    I have achieved ~ over 95% of what I wanted to.

    If you run the macro in the attached and then run the output .bas, you should get a sheet that is very close to the original.

    (Most of the following is noted in the Main module):
    The code does make some assumptions on how the worksheet was designed and it also (deliberately) skips most changes made to a range which has an inbuilt Style applied.
    The code won't work if the original sheet is protected or has custom Styles applied. (A macro is included to delete all custom Styles).


    There is a large number of things that it doesn't do - either because I had no need for it and/or because I consider it impossible. A non-exhaustive list of these below:
    • Font Name (didn't need)(Could be easily added)
    • Font strikethrough (didn't need)(Could be easily added)
    • Cell Patterns (didn't need)(Could be easily added)
    • Page SetUp margins (didn't need)(Could be easily added)
    • Conditional formatting (would like but impossible?)
    • Controls/Shapes/Pictures (would like but impossible?)
    • Array formulas (didn't need + impossible?)
    • Tables (didn't need + impossible?)
    • Filters (didn't need + impossible?)
    • Charts (didn't need + impossible?)
    • SparkLines (didn't need + impossible?)


    I will note the bugs in the next post...
    Attached Files Attached Files

  98. #98
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Macro Unrecorder

    Further to the previous post...

    Below are the bugs/things that need improving before I can mark the thread solved.

    1. There is an unusual bug with the borders. If you compare the output sheet with the original test sheet, you will note that the last two lines of the form are missing a border between them. If it was a problem with the loop code then why are only borders affected? [UPDATE: SOLVED]


    2. My cell loop code is very badly written. I have rewritten these loops at least 3 times now and am still unhappy with them.

    3. Further to problem 2 - I loathe my functions for determining whether range is part of an existing range. These need rewriting but I don't like every alternative version I produce of these. Anybody want to try?


    UPDATE: Found a bug in the Unrecorder_Start procedure.
    Please Login or Register  to view this content.
    Both uses of True need to be changed to False.
    Last edited by mc84excel; 03-23-2014 at 06:53 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1