Mixing
Excel-VBA How do I Add a Static Line onto a Graph?
I'm pretty new to this stuff so it's been rough. I'm trying to create a script that plots values collected from testing and then 2 static values (a requirement spec for QC and for Production) for comparison. I have been able to create a chart that displays the testing values, but I can't seem to get a straight line for the QC and Production specifications unless I type in each Array value like this:
.Values = Array(19, 19, 19, 19)
I want the line to be somewhat dynamic in length to ensure that it stretches across the graph no matter how many rows/columns there are.
Any help in what direction I should take or a better way to do this would be very much appreciated!
Dim myChtObj As ChartObject
Dim rngChtData As Range
Dim rngChtXVal As Range
Dim iColumn As Long
Dim iRow As Long
' make sure a range is selected
If TypeName(Selection) <> "Range" Then Exit Sub
' define chart data
Set rngChtData = Selection
' define chart's X values
With rngChtData
Set rngChtXVal = .Columns(1).Offset(1).Resize(.Rows.Count - 1)
End With
' add the chart
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=250, Width:=375, Top:=75, Height:=225)
With myChtObj.Chart
' make an XY chart
.ChartType = xlXYScatterLines
' remove extra series
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
' add series from selected range, column by column
For iColumn = 3 To rngChtData.Columns.Count
With .SeriesCollection.NewSeries
.Values = rngChtXVal.Offset(, iColumn - 1)
.XValues = rngChtXVal
.Name = rngChtData(1, iColumn)
End With
Next
Set ser = .SeriesCollection.NewSeries
ser.Values = Array(19, 19, 19, 19)
ser.XValues = rngChtXVal
ser.Name = "QC Retraction"
End With
End Sub
Current Output
What I would like to Output
excel vba graph
edited Jan 3 at 0:30
Nick
38.3k132443
asked Jan 3 at 0:11
Eric AguadoEric Aguado
1
add a comment |
I'm pretty new to this stuff so it's been rough. I'm trying to create a script that plots values collected from testing and then 2 static values (a requirement spec for QC and for Production) for comparison. I have been able to create a chart that displays the testing values, but I can't seem to get a straight line for the QC and Production specifications unless I type in each Array value like this:
.Values = Array(19, 19, 19, 19)
I want the line to be somewhat dynamic in length to ensure that it stretches across the graph no matter how many rows/columns there are.
Any help in what direction I should take or a better way to do this would be very much appreciated!
Dim myChtObj As ChartObject
Dim rngChtData As Range
Dim rngChtXVal As Range
Dim iColumn As Long
Dim iRow As Long
' make sure a range is selected
If TypeName(Selection) <> "Range" Then Exit Sub
' define chart data
Set rngChtData = Selection
' define chart's X values
With rngChtData
Set rngChtXVal = .Columns(1).Offset(1).Resize(.Rows.Count - 1)
End With
' add the chart
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=250, Width:=375, Top:=75, Height:=225)
With myChtObj.Chart
' make an XY chart
.ChartType = xlXYScatterLines
' remove extra series
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
' add series from selected range, column by column
For iColumn = 3 To rngChtData.Columns.Count
With .SeriesCollection.NewSeries
.Values = rngChtXVal.Offset(, iColumn - 1)
.XValues = rngChtXVal
.Name = rngChtData(1, iColumn)
End With
Next
Set ser = .SeriesCollection.NewSeries
ser.Values = Array(19, 19, 19, 19)
ser.XValues = rngChtXVal
ser.Name = "QC Retraction"
End With
End Sub
Current Output
What I would like to Output
excel vba graph
edited Jan 3 at 0:30
Nick
38.3k132443
asked Jan 3 at 0:11
Eric AguadoEric Aguado
1
add a comment |
I'm pretty new to this stuff so it's been rough. I'm trying to create a script that plots values collected from testing and then 2 static values (a requirement spec for QC and for Production) for comparison. I have been able to create a chart that displays the testing values, but I can't seem to get a straight line for the QC and Production specifications unless I type in each Array value like this:
.Values = Array(19, 19, 19, 19)
I want the line to be somewhat dynamic in length to ensure that it stretches across the graph no matter how many rows/columns there are.
Any help in what direction I should take or a better way to do this would be very much appreciated!
Dim myChtObj As ChartObject
Dim rngChtData As Range
Dim rngChtXVal As Range
Dim iColumn As Long
Dim iRow As Long
' make sure a range is selected
If TypeName(Selection) <> "Range" Then Exit Sub
' define chart data
Set rngChtData = Selection
' define chart's X values
With rngChtData
Set rngChtXVal = .Columns(1).Offset(1).Resize(.Rows.Count - 1)
End With
' add the chart
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=250, Width:=375, Top:=75, Height:=225)
With myChtObj.Chart
' make an XY chart
.ChartType = xlXYScatterLines
' remove extra series
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
' add series from selected range, column by column
For iColumn = 3 To rngChtData.Columns.Count
With .SeriesCollection.NewSeries
.Values = rngChtXVal.Offset(, iColumn - 1)
.XValues = rngChtXVal
.Name = rngChtData(1, iColumn)
End With
Next
Set ser = .SeriesCollection.NewSeries
ser.Values = Array(19, 19, 19, 19)
ser.XValues = rngChtXVal
ser.Name = "QC Retraction"
End With
End Sub
Current Output
What I would like to Output
excel vba graph
edited Jan 3 at 0:30
Nick
38.3k132443
asked Jan 3 at 0:11
Eric AguadoEric Aguado
1
I'm pretty new to this stuff so it's been rough. I'm trying to create a script that plots values collected from testing and then 2 static values (a requirement spec for QC and for Production) for comparison. I have been able to create a chart that displays the testing values, but I can't seem to get a straight line for the QC and Production specifications unless I type in each Array value like this:
.Values = Array(19, 19, 19, 19)
I want the line to be somewhat dynamic in length to ensure that it stretches across the graph no matter how many rows/columns there are.
Any help in what direction I should take or a better way to do this would be very much appreciated!
Dim myChtObj As ChartObject
Dim rngChtData As Range
Dim rngChtXVal As Range
Dim iColumn As Long
Dim iRow As Long
' make sure a range is selected
If TypeName(Selection) <> "Range" Then Exit Sub
' define chart data
Set rngChtData = Selection
' define chart's X values
With rngChtData
Set rngChtXVal = .Columns(1).Offset(1).Resize(.Rows.Count - 1)
End With
' add the chart
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=250, Width:=375, Top:=75, Height:=225)
With myChtObj.Chart
' make an XY chart
.ChartType = xlXYScatterLines
' remove extra series
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
' add series from selected range, column by column
For iColumn = 3 To rngChtData.Columns.Count
With .SeriesCollection.NewSeries
.Values = rngChtXVal.Offset(, iColumn - 1)
.XValues = rngChtXVal
.Name = rngChtData(1, iColumn)
End With
Next
Set ser = .SeriesCollection.NewSeries
ser.Values = Array(19, 19, 19, 19)
ser.XValues = rngChtXVal
ser.Name = "QC Retraction"
End With
End Sub
Current Output
What I would like to Output
excel vba graph
excel vba graph
edited Jan 3 at 0:30
Nick
38.3k132443
asked Jan 3 at 0:11
Eric AguadoEric Aguado
1
edited Jan 3 at 0:30
Nick
38.3k132443
asked Jan 3 at 0:11
Eric AguadoEric Aguado
1
edited Jan 3 at 0:30
Nick
38.3k132443
edited Jan 3 at 0:30
Nick
38.3k132443
edited Jan 3 at 0:30
Nick
38.3k132443
38.3k132443
asked Jan 3 at 0:11
Eric AguadoEric Aguado
1
asked Jan 3 at 0:11
Eric AguadoEric Aguado
1
asked Jan 3 at 0:11
Eric AguadoEric Aguado
1
1
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You only need to add a series with two points - one at the minimum x axis value, and one at the maximum value (with the same y value). Then format that line as required.
For example:
Set ser = .SeriesCollection.NewSeries
.Legend.LegendEntries(.SeriesCollection.Count).Delete 'remove from legend
With ser
.Values = Array(19, 19)
.XValues = Array(myChtObj.Chart.Axes(xlCategory).MinimumScale, _
myChtObj.Chart.Axes(xlCategory).MaximumScale)
.Name = ""
.MarkerStyle = -4142 'no markers
.Format.Line.ForeColor.RGB = vbBlack
.Points(2).ApplyDataLabels
.Points(2).DataLabel.Format.TextFrame2.TextRange.Characters.Text = "QC Retraction"
End With
Edit - adding the line may itself change the x-axis limits, so you might want to set those directly before adding the series.
answered Jan 3 at 1:22
Tim WilliamsTim Williams
89.3k97087
Thank you so much, that worked perfectly!
– Eric Aguado
Jan 3 at 16:26
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54014818%2fexcel-vba-how-do-i-add-a-static-line-onto-a-graph%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You only need to add a series with two points - one at the minimum x axis value, and one at the maximum value (with the same y value). Then format that line as required.
For example:
Set ser = .SeriesCollection.NewSeries
.Legend.LegendEntries(.SeriesCollection.Count).Delete 'remove from legend
With ser
.Values = Array(19, 19)
.XValues = Array(myChtObj.Chart.Axes(xlCategory).MinimumScale, _
myChtObj.Chart.Axes(xlCategory).MaximumScale)
.Name = ""
.MarkerStyle = -4142 'no markers
.Format.Line.ForeColor.RGB = vbBlack
.Points(2).ApplyDataLabels
.Points(2).DataLabel.Format.TextFrame2.TextRange.Characters.Text = "QC Retraction"
End With
Edit - adding the line may itself change the x-axis limits, so you might want to set those directly before adding the series.
answered Jan 3 at 1:22
Tim WilliamsTim Williams
89.3k97087
Thank you so much, that worked perfectly!
– Eric Aguado
Jan 3 at 16:26
add a comment |
You only need to add a series with two points - one at the minimum x axis value, and one at the maximum value (with the same y value). Then format that line as required.
For example:
Set ser = .SeriesCollection.NewSeries
.Legend.LegendEntries(.SeriesCollection.Count).Delete 'remove from legend
With ser
.Values = Array(19, 19)
.XValues = Array(myChtObj.Chart.Axes(xlCategory).MinimumScale, _
myChtObj.Chart.Axes(xlCategory).MaximumScale)
.Name = ""
.MarkerStyle = -4142 'no markers
.Format.Line.ForeColor.RGB = vbBlack
.Points(2).ApplyDataLabels
.Points(2).DataLabel.Format.TextFrame2.TextRange.Characters.Text = "QC Retraction"
End With
Edit - adding the line may itself change the x-axis limits, so you might want to set those directly before adding the series.
answered Jan 3 at 1:22
Tim WilliamsTim Williams
89.3k97087
Thank you so much, that worked perfectly!
– Eric Aguado
Jan 3 at 16:26
add a comment |
You only need to add a series with two points - one at the minimum x axis value, and one at the maximum value (with the same y value). Then format that line as required.
For example:
Set ser = .SeriesCollection.NewSeries
.Legend.LegendEntries(.SeriesCollection.Count).Delete 'remove from legend
With ser
.Values = Array(19, 19)
.XValues = Array(myChtObj.Chart.Axes(xlCategory).MinimumScale, _
myChtObj.Chart.Axes(xlCategory).MaximumScale)
.Name = ""
.MarkerStyle = -4142 'no markers
.Format.Line.ForeColor.RGB = vbBlack
.Points(2).ApplyDataLabels
.Points(2).DataLabel.Format.TextFrame2.TextRange.Characters.Text = "QC Retraction"
End With
Edit - adding the line may itself change the x-axis limits, so you might want to set those directly before adding the series.
answered Jan 3 at 1:22
Tim WilliamsTim Williams
89.3k97087
You only need to add a series with two points - one at the minimum x axis value, and one at the maximum value (with the same y value). Then format that line as required.
For example:
Set ser = .SeriesCollection.NewSeries
.Legend.LegendEntries(.SeriesCollection.Count).Delete 'remove from legend
With ser
.Values = Array(19, 19)
.XValues = Array(myChtObj.Chart.Axes(xlCategory).MinimumScale, _
myChtObj.Chart.Axes(xlCategory).MaximumScale)
.Name = ""
.MarkerStyle = -4142 'no markers
.Format.Line.ForeColor.RGB = vbBlack
.Points(2).ApplyDataLabels
.Points(2).DataLabel.Format.TextFrame2.TextRange.Characters.Text = "QC Retraction"
End With
Edit - adding the line may itself change the x-axis limits, so you might want to set those directly before adding the series.
answered Jan 3 at 1:22
Tim WilliamsTim Williams
89.3k97087
edited Jan 3 at 5:18
answered Jan 3 at 1:22
Tim WilliamsTim Williams
89.3k97087
answered Jan 3 at 1:22
Tim WilliamsTim Williams
89.3k97087
answered Jan 3 at 1:22
Tim WilliamsTim Williams
89.3k97087
89.3k97087
Thank you so much, that worked perfectly!
– Eric Aguado
Jan 3 at 16:26
add a comment |
Thank you so much, that worked perfectly!
– Eric Aguado
Jan 3 at 16:26
Thank you so much, that worked perfectly!
– Eric Aguado
Jan 3 at 16:26
Thank you so much, that worked perfectly!
– Eric Aguado
Jan 3 at 16:26
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54014818%2fexcel-vba-how-do-i-add-a-static-line-onto-a-graph%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
