Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-28-2012, 08:55 PM
omahadivision omahadivision is offline Method to remove overlap in data labels for single series Windows 7 32bit Method to remove overlap in data labels for single series Office 2007
Novice
Method to remove overlap in data labels for single series
 
Join Date: Oct 2012
Posts: 28
omahadivision is on a distinguished road
Default Method to remove overlap in data labels for single series

Hi guys,

I've created a fairly simple algorithm to remove overlapping data labels on a single series in Excel. It's based on this Stack Overflow page, and it's quite effective in dealing with graphs with sometimes close-spaced single series. I'm posting it in hopes that some will find it useful and that others might have suggestions for making it more efficient.

First: I use a loop to detect which datapoints have labels, and then store the points in an array and the number of points as long:
Code:
Sub DataArray()
Dim dLabels() As DataLabel
Dim i As Long
Dim k As Long
ReDim dLabels(1 To 1868) 'my range
Set FTIRLine = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)

k = 1
For i = 1 To FTIRLine.Points.Count

   If FTIRLine.Points(i).HasDataLabel = _
         True Then 'tests to see if point has datalabel
             Set dLabels(k) = FTIRLine.Points(i).DataLabel
             k = k + 1
         End If
         Next

Call AdjustLabels(dLabels, k) 'sends labels, number of labels, to function
End sub
AdjustLabels is a function which detects overlapping labels and then separates them by raising one label up by half and lowering the other label by half. There are many different ways the labels could be moved depending on the application, and the ' left lines correspond to coding to move the labels side-to-side instead of up and down. Hight and Wdth are two arbitrary margins that determine the space the labels must be separated by and can be adjusted in twips to give greater of less distances. The code essentially tests each data label against its adjacent data label to see if they overlap.



Code:
  
Sub AdjustLabels(ByRef v() As DataLabel, ByVal m As Long)
 Dim o As Long

hight = 8.5
Wdth = 35
o = 1

Do
If v(o).Left < (v(o + 1).Left + Wdth) And v(o).Top < (v(o + 1).Top + hight) And v(o).Top > (v(o + 1).Top - hight) Then

v(o).Top = v(o).Top + hight / 2
v(o + 1).Top = v(o).Top - hight / 2

'v(o).Left = v(o).Left + Wdth / 2
'v(o + 1).Left = v(o).Left - Wdth / 2

Else

End If

o = o + 1

Loop Until o = m - 1

End sub
Now often, especially with multiple data points to a series, the labels even when they have been moved will still be overlapping. So I've created another sub which essentially repeats the first sub 5 times. I'm sure this step could be much more efficient:

Code:
Sub DataArrayCaller()
Dim q As Long
q = 1
Do
DataArray
q = q + 1
Loop Until q = 5

End Sub
The finished result below:
Attached Images
File Type: png Demonstration.png (32.1 KB, 31 views)
Reply With Quote
Reply

Tags
charts, datalabels, overlap

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Method or data member not found gbaker Excel Programming 5 07-06-2012 05:20 AM
Remove Portion of Field Labels sbianco Word 5 03-07-2012 04:52 AM
Remove header/footers from a single page qwerty2k12 Word 1 03-01-2012 08:14 PM
Powerpoint: adding data to trend lines w/o data labels HaiLe PowerPoint 0 04-11-2011 09:21 AM
Data Series Zach Excel 1 11-05-2010 02:45 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:22 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft