+ Reply to Thread
Page 6 of 7 FirstFirst ... 4 5 6 7 LastLast
Results 76 to 90 of 98

Create code to recreate ActiveSheet contents at run-time

  1. #76
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    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)
    *******************************************************

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

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

  2. #77
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    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...

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

    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.
    Entia non sunt multiplicanda sine necessitate

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

    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

  5. #80
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    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.

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

    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.

  7. #82
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    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).

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

    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).

  9. #84
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    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?'.

  10. #85
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    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

  11. #86
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    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.

  12. #87
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    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

  13. #88
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    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

  14. #89
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    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...

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

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

    I don't have a My Documents folder.
    If posting code please use code tags, see here.

+ Reply to Thread
Page 6 of 7 FirstFirst ... 4 5 6 7 LastLast

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