current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 1 down vote favorite

I'm currently writing a program that will graph an x-ray spectrum, and then calculate where all of the peaks are, and what they should be labeled. However, I'm not sure how I can add the labels of each peak using excel vba. I know the x and y coordinate of the tip of the peak on the chart (based on the axes units). Is there any way to use that information to place a label slightly above, or slightly to the right of that point?

Below, I've included an example of what I'm going for.

The peaks will be different elements based on what data is input. The tips of the peaks will also be in different x and y positions. So I think it would be best if there was some way to add the label based on x and y coordinates of the tips of the peaks, since I always know those values.

Looking around on the internet, I can't find any real information on how to do this, if it's even possible. I'm hoping you guys can help me out!

An X-Ray Spectrum

share|improve this question
2  
Definitely do-able. You can add shapes (like textboxes) to the ChartArea object. However, in this case it seems like it would be easier to just hijack the points' DataLabel object and manipulate the text therein. Selectively delete the DataLabel (or set a null-string text) on those points which you do not want to display. – David Zemens Jul 31 '14 at 14:52
up vote 2 down vote accepted

Definitely do-able. You can add shapes (like textboxes) to the ChartArea object. However, in this case it seems like it would be easier to just hijack the points' DataLabel object and manipulate the text therein. 

Dim pt As Point
Dim p As Long
Dim dl As DataLabel
Dim srs As Series
Dim cht As Chart
Set cht = ActiveSheet.ChartObjects(1).Chart
Set srs = cht.SeriesCollection(1)
srs.HasDataLabels = False '## Turn off the data labels
For Each pt In srs.Points
    p = p + 1
    If [logic or function to determine your peaks] Then  
        pt.HasDataLabel = True
        Set dl = pt.DataLabel
        dl.Text = "whatever you want it to be"
    End If
Next
share|improve this answer
3  
For a chart like this with a large number of points it might be faster to turn on labels one-by-one. See msdn.microsoft.com/en-us/library/office/… – Tim Williams Jul 31 '14 at 15:08
1  
@TimWilliams bingo. modified my answer in light of that. – David Zemens Jul 31 '14 at 15:17
    
Thank you! This is already amazing. How would this code change if I already knew my peak value? Like if I had a variable named peakvalue before going into a labeling loop? Is there a way to find the point that equals that specific value, and only label that one? – TheTreeMan Jul 31 '14 at 15:22
1  
I think you would still need to loop unless you know the point's index position (which is what we can obtain by looping, with the iterator variable p in the answer). The If statement could be like If srs.Values(p) = peakValue Then... – David Zemens Jul 31 '14 at 15:30
    
Is there a way that I can change this code to allow me to run it multiple times to label multiple peaks? Right now, if I run it a second time on another peak, it wipes the label from the previous peak. Probably due to the srs.HasDataLabels = False line. I have multiple graphs in multiple sheets, so I can't use a global variable to ensure that line only happens for the first time the code is ran though, since it'll be used in the same program and same workbook on a graph in another sheet. Any suggestions? – TheTreeMan Jul 31 '14 at 19:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.