#Histogram using excel for mac software
#Histogram using excel for mac code
#Histogram using excel for mac series

If the series has no valid data label, then zeros are stored in the array. It first loops through the series of the chart, and if the series has a valid label on the point being tested (we text point 1 for the left side labels and point 2 for the right), then the series number and the top position of the label are stored in the array. The routine that does the work is FixTheseLabels. After a few trials, I set an OverlapTolerance of 0.45, meaning I would let labels overlap by 45% of their height. If I want to run this on a Mac, I must change MoveIncrement to 1, because this is how the Mac measures screen distances (one of the myriad incompatibilities we have to deal with). When I move labels I do so by a pixel at a time, which in Windows is specified by MoveIncrement = 0.75 (0.75 points, to be exact). I declare a couple of constants used in the positioning. Then it loops through the series numbers in a nested loop, to compare each label with every other label.

#Histogram using excel for mac code

The code bubble-sorts this array by the second column. To compile all the labels, the program builds a two-column VBA array, with series numbers in the first column and vertical position in the second.

#Histogram using excel for mac software

I decided to build a slightly different routine, because I already have a slope chart feature in my commercial software (which will be released in a new build soon), and I’d like something that worked with it.

After looping until all labels were separated by a certain amount, the code finishes. This must be done iteratively because moving two labels apart might move either one closer to a third label. Then the code loops through the labels: if two were to overlap, the upper label is moved up a tiny bit and the lower one down a little bit. His routine inserts each DataLabel object into a VBA Collection in such a way that the Collection is sorted by vertical position.

I tested it and liked it, then I thought about how I would build my own.Īndy’s routine used a class module to create a DataLabel object for each label that had to be realigned. But what he sent me now was much better than a prototype. I remembered that long ago Andy had a prototype label untangler, and I know I played with it. He sent an email with a nifty VBA routine he’d worked out. My friend and colleague Andy Pope came to the rescue. Also, I should allow some overlap, since labels include a small white margin around their text. I would have to sort the labels by vertical position, then see if a label overlapped any subsequent label. Since each set of labels is aligned horizontally with the points they describe, I would only have to worry about vertical positioning. But even with that helpful tool, it is still a tedious process.Ī more inclusive VBA procedure that would take care of overlapping data labels is a much more difficult task. I showed a feature in my commercial software that lets you fine-tune the position of individual data labels. The problem is that often points are located close to each other the result: overlapping data labels. I recently wrote a post called Slope Chart with Data Labels which provided a simple VBA procedure to add data labels to a slope chart the procedure simplified the problem caused by positioning each data label individually for each point in the chart. This means the labels have to be tediously selected one by one, even to apply “standard” alignments. Data labels are terribly tedious to apply to slope charts, since these labels have to be positioned to the left of the first point and to the right of the last point of each series.