+ Reply to Thread
Page 5 of 7 FirstFirst ... 3 4 5 6 7 LastLast
Results 61 to 75 of 98

Create code to recreate ActiveSheet contents at run-time

  1. #61
    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

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

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

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

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

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

    My Excel sheet wasn't large.
    If posting code please use code tags, see here.

  3. #63
    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 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.

  4. #64
    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

    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

  5. #65
    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

    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

  6. #66
    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

    Why isn't the button replicated?

  7. #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
    Josie

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

  8. #68
    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 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)

  9. #69
    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
    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.

  10. #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

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

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

    copy and paste .....

  12. #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

  13. #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

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

    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

  15. #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

+ Reply to Thread
Page 5 of 7 FirstFirst ... 3 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