+ Reply to Thread
Results 1 to 7 of 7

Tidying/ Simplifying Code

  1. #1
    Registered User
    Join Date
    11-24-2008
    Location
    US - Kentucky
    Posts
    13

    Tidying/ Simplifying Code

    Hi everyone,

    I am looking for some advice in simplifying, tiding up some codes and implementing commenting on the VBA itself.

    What I have attached is a sample static data of the stock prices of 3 companies. I have also created a macro button to run the code to calculate the average.

    The codes will be plugged for dynamic numbers/cells very soon, as this is just a testing application so therefore I created a dynamic looping code for testing.

    I was wondering if anyone could help me read the my codes especially on the Average part and help me simplify if there's any possibility to do so.
    Attached Files Attached Files
    Last edited by VBA Noob; 11-28-2008 at 12:33 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    Use these codes and your sheet will update almost instantaneously without all that flickering and unnecessary cell selections, I put the three cleanup macros into one since it appears you do them together every time:
    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    JBeaucaire,

    This code

    Please Login or Register  to view this content.
    Can be changed to
    Please Login or Register  to view this content.
    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  4. #4
    Registered User
    Join Date
    11-24-2008
    Location
    US - Kentucky
    Posts
    13
    Hi VB Noob and JBeaucaire,

    I have edited as shared in the previous few posts, but when I ran the code again, the cells keep flickering.

    I have attached the worksheet with the latest code edit.

    Hope you can help out.

    The whole attachment is more or less complete unlike the first attachment, for a project.
    Attached Files Attached Files

  5. #5
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    Remember, I converted you multiple macros into one? The critical line in that macro to eliminate screen flickering is:
    Please Login or Register  to view this content.
    Add that to the top of all the macros you call.

    Also, look a the way the macro cleanup was simplified after you recorded it. You can do that with all your macros as you add more ranges.

  6. #6
    Registered User
    Join Date
    11-24-2008
    Location
    US - Kentucky
    Posts
    13
    JBeaucaire, thanks for that reminder! I've updated it.

    If I inserted that into the VBA code, will it interfere with the refresh button where it paste special values from the live data to that static page?

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    Quote Originally Posted by bomberchia View Post
    JBeaucaire, thanks for that reminder! I've updated it.

    If I inserted that into the VBA code, will it interfere with the refresh button where it paste special values from the live data to that static page?
    No. All it does is do all the work without making you watch what it's doing.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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