Chart data range selection with VBA

**ArtZ** · 10-17-2023, 09:58 PM

Hi,
The attached workbook uses VBA code which allows the modification of the data range for the embedded chart. Admittedly, I did not create this workbook or the VBA code, but upon finding it in my Excel archives, I thought that it could be useful for a current project.

Although the code seems simple, it uses syntax that I have no familiarity or experience with; OLEFormat.
A snippet of the code:

Please Login or Register  to view this content.

Setting the chart data range is easy, somewhat. Cell B2 OR the scrollbar set the data start value. The data end value is a little less straightforward. Cell B3 sets the length of the data displayed from the data start value.
In order to make the setting of the data range more intuitive, I would like to continue to use Cell B2 OR the scrollbar set the data start value. The functionality I would like to change is to use the value in Cell B3 to set the data end value.

I've hacked at this code and cannot figure out how to make the needed changes to the VBA.

Any suggestions?

Thanks,
Art

**dflak** · 10-30-2023, 11:56 AM

You do not need VBA. This can be done with named dynamic ranges.

When I work with charts, I use PLOT_ as a prefix for the name. This is totally arbitrary on my part.

The first range is Plot_KP and it is a static range =Example!$M$7:$M$32. This named range is used for data validation in cell B2.

The next range is Plot_Len_Data =OFFSET(Example!$M$7,0,0,COUNT(Plot_KP)-Example!$B$2,1) this calculates the maximum length that the range to plot can have based on what is set in cell B2.

The next range is Plot_KP_X that is used to plot the X axis. =OFFSET(Example!$M$7,MATCH(Example!$B$2,Plot_KP,0)-1,0,Example!$B$3,1)
-- Start in M7
-- Then use match to find out where the value selected in cell B2 is found in the range Plot_KP and go down that many rows. (Minus one since offset starts counting at 0 and match starts counting at 1)
-- Go right 0 columns
-- return a number of rows equal to the value selected in cell B3
-- return 1 column

The two other ranges are Plot_WD_0 and Plot_WD_1. These are offsets from Plot_KP_X.

To plot these values by selecting the chart, right clicking and Select Dara. Then Add a series, type in a name and where it indicates series values enter in ='Example'!Plot_KP_0 (or whatever you want to plot.) Do the same for the X-Axis.

The named ranges are shown in columns T & U. They are presented for training purposes only, you can delete them.