+ Reply to Thread
Results 1 to 10 of 10

Simplification of code for Chart Series control

  1. #1
    Registered User
    Join Date
    11-12-2018
    Location
    Cornwall, England
    MS-Off Ver
    Excel 2016 (Office 365 ProPlus)
    Posts
    58

    Question Simplification of code for Chart Series control

    I have a simple macro that looks for the values in some named cells (using a separate Worksheet_SelectionChange sub) and then switches on/off a corresponding series in a chart.

    As this happens very often, I am looking for a way to simplify the code as much as possible to keep performance acceptable. A sample of the code that does the series switching is below, but the full version includes many more lines still. Any advise greatly appreciated.

    Please Login or Register  to view this content.

  2. #2
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Simplification of code for Chart Series control

    you need to look for patterns to get the best efficiency. so, one that I can see right away in your code is this:
    Please Login or Register  to view this content.
    do you see it? you are looking at sequential ranges. thus, you could reduce stuff like this and write it in a loop like this:
    Please Login or Register  to view this content.
    now, that code might not be absolutely perfect because I didn't test it. however, more than likely you can do it.

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Simplification of code for Chart Series control

    If each of those ranges is limited to one of two possible values, then it looks like you could simplify the code to something like this:

    Please Login or Register  to view this content.
    Just change the 6 to however many series you actually have, or use ch.fullseriescollection.count if the ranges and series always match up.
    Rory

  4. #4
    Registered User
    Join Date
    11-12-2018
    Location
    Cornwall, England
    MS-Off Ver
    Excel 2016 (Office 365 ProPlus)
    Posts
    58

    Re: Simplification of code for Chart Series control

    This is just the sort of thing I was hoping for thank you. Only problem is it currently fails on the last line (highlighted below) saying Run-time error '1004' application-defined or object-defined error.

    I can see what this line is attempting to do but still too green with VBA to be able to diagnose. If not immediately apparent to you either I will happy upload a sample file. Oh and yes - the toggle cell value will only ever be a dash "-" or a tick "ChrW(&H2713)" so hopefully this keeps things simple!

    Please Login or Register  to view this content.
    Last edited by mattydboom1; 09-23-2020 at 10:16 AM.

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Simplification of code for Chart Series control

    A sample file would probably be the quickest way to tell what is happening.

  6. #6
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Simplification of code for Chart Series control

    Well guys do you think it is failing on the line because the variable called ""n"" is not defined and you are using it as an index number of the collection? That's what it looks like to me

  7. #7
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Simplification of code for Chart Series control

    Yep, that's what I get for air code. Replace n with counter and it should be good to go.

  8. #8
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Simplification of code for Chart Series control

    Hahaha I got you there rorya! Lol. Hey it's always a pleasure to engage with you Excel professionals. :-)

  9. #9
    Registered User
    Join Date
    11-12-2018
    Location
    Cornwall, England
    MS-Off Ver
    Excel 2016 (Office 365 ProPlus)
    Posts
    58

    Re: Simplification of code for Chart Series control

    Chaps - what else can I say but thank you! This works perfectly. Im sure I will make use of this type of method again, so I appreciate the knowledge.

    From question to full working solution in a little over an hour, such a helpful resource this place is!

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Simplification of code for Chart Series control

    Glad we could help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Form control slider to adjust chart series crashes Excel 365
    By ByteMarks in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-26-2020, 06:59 AM
  2. [SOLVED] Code simplification (format and conditional formatting)
    By dunnobe in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-06-2018, 10:19 AM
  3. Chart Series Hide/Unhide using CheckBox form control
    By saket_47 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-03-2017, 10:15 AM
  4. VBA code to set chart series to 'Automatic'
    By aprimak in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-13-2015, 05:07 PM
  5. How to code rules for control chart?
    By catchaman in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-04-2013, 10:30 AM
  6. Simplification of macro code
    By PvanS in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-17-2009, 10:42 AM
  7. [SOLVED] Using VBA code to change Chart Data Series
    By WilliamI in forum Excel General
    Replies: 1
    Last Post: 08-30-2005, 11:05 AM

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