PDA

View Full Version : VBA Macro - Use relative ranges to graph



Faiz
08-22-2017, 08:59 AM
So I currently have a piece of code that only functions on one of the sheets in my workbook. I want to modify it so it can work on any of the sheets.


The macro is essentially using relative ranges to graph the data. I have tried solving it myself but can't seem to get it to work, refer to "Error 1". I also run into a second error where when it works on that one sheet, it's graphing an extra "series" I will attach an image of it below- Refer to Error 2



Sub ChartTHIS()


'get offset location from active cell to new chart position
Dim Col, Row
Col = Split(ActiveCell(1).Offset(0, 9).Address(1, 0), "$")(0) 'nine right of active col
Row = Split(ActiveCell(1).Offset(2, 0).Address(1, 0), "$")(1) 'two below active row
'MsgBox Col & " " & Row
'this row,col is used to position the chart

'example chart data range from 21-g-1028
'='21-G-1028'!$K$8:$O$8,'21-G-1028'!$K$10:$O$11,'21-G-1028'!$K$31:$O$31

'MsgBox ActiveChart.Parent.Index


Dim rngSourceData As Range, wsData As Worksheet, wsChart As Worksheet
Set wsData = Sheets(ActiveSheet.Name)
Set wsChart = Sheets(ActiveSheet.Name)

'Set rngSourceData = Union(wsData.Range("K10:O10"), wsData.Range("K13:O13"))
Set rngSourceData = wsData.Range("'21-G-1028'!$K$8:$O$8,'21-G-1028'!$K$10:$O$11,'21-G-1028'!$K$31:$O$31")


'declare a ChartObject
Dim oChObj As ChartObject

'the Add method (of the ChartObjects object) is used to create a new empty embedded
'chart and add it to the collection (ChartObjects object) in the specified sheet
Set oChObj = wsChart.ChartObjects.Add(Left:=2, Width:=350, Top:=5, Height:=200)

'using the Chart Property of the ChartObject object returns a Chart object
'which refers to a chart
With oChObj.Chart
'use ChartType Property of the Chart object to set type of chart - Line with Markers
.ChartType = xlLineMarkers
'use SetSourceData Method of the Chart object to set the range of source data for the chart
.SetSourceData Source:=rngSourceData, PlotBy:=xlRows

'the Parent property of the Chart object returns its Parent object ie. ChartObject object (oChObj)
'the ChartTitle object exists and can be used only if the HasTitle property (of the Chart object) is True
.HasTitle = True
'get sheet name into chart title:
.ChartTitle.Characters.Text = ActiveSheet.Name & " STD Wear Trend"

'calc position of chart relative to active cell
Col = Split(ActiveCell(1).Offset(0, 9).Address(1, 0), "$")(0) 'nine right of active col
Row = Split(ActiveCell(1).Offset(2, 0).Address(1, 0), "$")(1) 'two below active row

'here use the calculated row and col offset to position the chart:
With .Parent
'set the embedded chart to be free-floating so that it does not move or size with its underlying cells
.Placement = xlFreeFloating
'align the left edge of the embedded chart with the left edge of the worksheet's column B
.Left = wsChart.Columns(Col).Left
.Top = wsChart.Rows(VBA.Val(Row)).Top
'set rounded corners for the embedded chart
.RoundedCorners = True
'using the Name Property of the ChartObject object - set the name of the embedded chart to "AnnualSalesProfit"

End With

.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time (hours)"
.Axes(xlCategory, xlPrimary).CategoryType = xlTimeScale
'.Axes(xlCategory, xlPrimary).MinimumScaleIsAuto = True
'.Axes(xlCategory, xlPrimary).MaximumScaleIsAuto = True

.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Wear (mm)"

.Axes(xlValue).MinimumScale = 0
.Axes(xlCategory).MinimumScale = 0
.Axes(xlCategory).MaximumScale = 4000
.Axes(xlCategory).MajorUnit = 1000
.Axes(xlCategory).MinorUnit = 1000
.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "0"

.SeriesCollection.NewSeries

Dim col2, row2



'data series 1 ----- HOW TO CODE RELATIVE DATA RANGES INTO THE CHART MACRO
'example active K8 to Q8
Col = Split(ActiveCell(1).Offset(0, 0).Address(1, 0), "$")(0) 'nine right of active col
'this row will always be used as the x axis labels
Row = Split(ActiveCell(1).Offset(0, 0).Address(1, 0), "$")(1) 'two below active row
'and K10 to Q10
col2 = Split(ActiveCell(1).Offset(0, 6).Address(1, 0), "$")(0) 'nine right of active col

.SeriesCollection(1).XValues = Range("$" & Col & "$" & Row & ":$" & col2 & "$" & Row)
row2 = Split(ActiveCell(1).Offset(2, 0).Address(1, 0), "$")(1) 'two below active row
.SeriesCollection(1).Values = Range("$" & Col & "$" & row2 & ":$" & col2 & "$" & row2)
.SeriesCollection(1).Name = "zone 0"




'data series 2 -----
'only the row changes relatively
row2 = Split(ActiveCell(1).Offset(3, 0).Address(1, 0), "$")(1) 'two below active row
.SeriesCollection(2).XValues = Range("$" & Col & "$" & Row & ":$" & col2 & "$" & Row)
'next row down:
.SeriesCollection(2).Values = Range("$" & Col & "$" & row2 & ":$" & col2 & "$" & row2)
.SeriesCollection(2).Name = "zone 1"



'data series 3 -----
'so you only need to change the row offset and then the other lines are the same:
'see this one has a long offset of 23
row2 = Split(ActiveCell(1).Offset(23, 0).Address(1, 0), "$")(1) 'two below active row

.SeriesCollection(3).XValues = Range("$" & Col & "$" & Row & ":$" & col2 & "$" & Row)
.SeriesCollection(3).Values = Range("$" & Col & "$" & row2 & ":$" & col2 & "$" & row2)
.SeriesCollection(3).Name = "zone 12"


'and you can add as many zones of data as you want...



'using constant values in MsoChartElementType Enumeration to specify if the chart elements are to be displayed and how to display them
'Display chart title above chart
.SetElement msoElementChartTitleAboveChart
'Display major gridlines along primary value axis
.SetElement msoElementPrimaryValueGridLinesMajor
'turn off legend
'.SetElement msoElementLegendRight

End With

End Sub


The piece of code I think that needs modifying is: [Error 1]
20136


'Set rngSourceData = Union(wsData.Range("K10:O10"), wsData.Range("K13:O13"))
Set rngSourceData = wsData.Range("'21-G-1028'!$K$8:$O$8,'21-G-1028'!$K$10:$O$11,'21-G-1028'!$K$31:$O$31")


The second error that I get is, where its graphing an extra "series" I am not sure how to fix that. [Error 2]
20137

SamT
08-22-2017, 11:23 AM
That is a way invalid Range Address. Try

Set rngSourceData = wsData.Range("K8:O31")

YasserKhalil
08-22-2017, 11:26 AM
Cross-Post at this link
https://www.excelguru.ca/forums/showthread.php?8251-Using-relative-cell-ranges-to-graph-a-new-chart

Faiz
08-22-2017, 12:07 PM
So with that, I can use the function in another sheet, but the graph is all messed up. 20140

Faiz
08-22-2017, 12:07 PM
Am I not allowed to post on other forums? If so I did not know, I apologize.

YasserKhalil
08-22-2017, 12:50 PM
No problem of posting on other forums but it would be better to put the links of other threads ...

SamT
08-22-2017, 04:37 PM
Allowed with courtesy: see What is multiposting? (http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3)
Best to wait 2 days for an answer before multiposting.

If you are in a hurry, WE are the best .:blush

SamT
08-22-2017, 04:40 PM
Sorry, I don't do graphs. wait a bit and a graph expert will answer