sbrown1016
New Member
Hi,
I have several workbooks with data on them and would like to automate the creation of charts given certain user defined inputs. I have attached a sample file that includes a sheet with the output I'd like the vba routine to generate.
The data is stored on a sheet called "data" and I've defined dynamic named ranges for each data series labeled var_0, var_1, var_2, etc. The number of variables will vary by workbook.
The workbooks also have a sheet called "control". This is a sheet in which a user can input certain parameters. First, they can input the number of charts they wish to create. Then, for each chart, they can specify: the chart's name, the chart's top & bottom title lines (I use 2 text boxes instead of the title bc I need to use different formats for each line), the chart's x-variable, the chart's y variable or variables on the primary axis, the chart's y variable to be plotted on the secondary axis.
Some of these parameters are recorded under named ranges. For example, the chart name for the first chart is stored under the Ch1_N and the top and bottom lines of the titles are named Ch1_TT and Ch1_BT. Also, the "control" sheet has a table that lists the legend name for each var_0 to var_(total # of vars) under named ranges var_0_LN through var_(total # of vars)_LN. The variables to be plotted on chart 4 for example are recorded under the following named ranges: Ch4_xvar; Ch4_yvar_L (primary axis); Ch4_yvar_R (secondary axis).
I would like help with the following:
1) What is the optimal way to include several values in a cell that will become a named range if I then want a VBA macro to parse through the list of variables? In particular, I wonder what the ideal way of listing the variables to be plotted on the Y axis when there is more than one (see cells F5 through F8 on the "control" sheet)
2) How can one write a VBA routine that uses the values stored by the named ranges on the "control" sheet to generate the charts on the "charts" sheet? The charts I'd like the vba routine to generate have its titles and series linked to named ranges. I would like the routine to also rename the charts using the relevant named ranges (Ch1_N through Ch4_N in the example worksheet).
The reason for insisting on named ranges is that for some of my workbooks I'll seek to generate only a couple of charts whereas for others I'll need over a dozen. Also, the number of variables will vary across workbooks.
Finally, my workbooks already have a the named ranges on them. If the way I have defined them isn't the best way to do so for these purposes, I'm happy to re-define them so although I'd like the VBA procedure to take those as a given, if the charitable soul who'll help me with this has a suggestion on how to re-name those ranges, let me know as it wouldn't be difficult for me to re-define them.
I have several workbooks with data on them and would like to automate the creation of charts given certain user defined inputs. I have attached a sample file that includes a sheet with the output I'd like the vba routine to generate.
The data is stored on a sheet called "data" and I've defined dynamic named ranges for each data series labeled var_0, var_1, var_2, etc. The number of variables will vary by workbook.
The workbooks also have a sheet called "control". This is a sheet in which a user can input certain parameters. First, they can input the number of charts they wish to create. Then, for each chart, they can specify: the chart's name, the chart's top & bottom title lines (I use 2 text boxes instead of the title bc I need to use different formats for each line), the chart's x-variable, the chart's y variable or variables on the primary axis, the chart's y variable to be plotted on the secondary axis.
Some of these parameters are recorded under named ranges. For example, the chart name for the first chart is stored under the Ch1_N and the top and bottom lines of the titles are named Ch1_TT and Ch1_BT. Also, the "control" sheet has a table that lists the legend name for each var_0 to var_(total # of vars) under named ranges var_0_LN through var_(total # of vars)_LN. The variables to be plotted on chart 4 for example are recorded under the following named ranges: Ch4_xvar; Ch4_yvar_L (primary axis); Ch4_yvar_R (secondary axis).
I would like help with the following:
1) What is the optimal way to include several values in a cell that will become a named range if I then want a VBA macro to parse through the list of variables? In particular, I wonder what the ideal way of listing the variables to be plotted on the Y axis when there is more than one (see cells F5 through F8 on the "control" sheet)
2) How can one write a VBA routine that uses the values stored by the named ranges on the "control" sheet to generate the charts on the "charts" sheet? The charts I'd like the vba routine to generate have its titles and series linked to named ranges. I would like the routine to also rename the charts using the relevant named ranges (Ch1_N through Ch4_N in the example worksheet).
The reason for insisting on named ranges is that for some of my workbooks I'll seek to generate only a couple of charts whereas for others I'll need over a dozen. Also, the number of variables will vary across workbooks.
Finally, my workbooks already have a the named ranges on them. If the way I have defined them isn't the best way to do so for these purposes, I'm happy to re-define them so although I'd like the VBA procedure to take those as a given, if the charitable soul who'll help me with this has a suggestion on how to re-name those ranges, let me know as it wouldn't be difficult for me to re-define them.