Reciprocal Chart Axis Scale

Comments

1. derek says

   Thursday, July 2, 2015 at 1:06 pm

   Great tutorial, Jon, but aren’t those labels backwards? I’d expect the fastest rates (1 every two seconds) to be at the highest temperatures (240°C) on the bottom left and the lowest rates (1 every 100 seconds) to be at the top right (~0°C).
   I wonder if the XL2013 labeling routine assigned the labels left to right even though the points were drawing right to left, and if so, whether you’ll have to format the data table upside down to get it to behave.

2. Bob says

   Thursday, July 2, 2015 at 1:09 pm

   Thanks for this post Jon,

   I’ve been out of the lab for over 20 years, but still remember using log graph paper to draw these types of curves. Great post.

   Bob

3. Jon Peltier says

   Thursday, July 2, 2015 at 4:59 pm

   Derek –

   The axis has been reversed, so the smaller 1/(273+240) or 1.9493 is to the right and the larger 1/(273-20) or 3.9526 is to the left.

4. derek says

   Thursday, July 2, 2015 at 5:48 pm

   I see, so it’s the vertical axis I was misinterpreting. Not 1 per 100 seconds, but 100/sec!

5. Fred Hammers says

   Thursday, September 1, 2016 at 8:33 am

   Jon,
   I apologize in advance for highjacking this topic, but I couldn’t find a way to post this comment on the Scientific Notation axis labels page.
   I develop custom productivity applications for a value engineering department of my division. Your scientific notation axis labels are great. I’ve had this on my to do list for a while now, and I’m glad I found your solution; it saved me the time and trouble.
   Your solution works very well, but I added an enhancement I wanted to share with you. Rather than have a user select the labels after VBA inserts them and move them (left and below, respectively), I added the following:

           If mySeries = 2 Then    'Y Axis series
               .DataLabel.Position = xlLabelPositionLeft
           ElseIf mySeries = 3 Then    'X Axis series
               .DataLabel.Position = xlLabelPositionBelow
           Else
               'do nothing
           End If

   I do have question about the code. Although this code

   If myChart = 0 Then
       Set cht = ActiveChart
   Else
       Set cht = ActiveSheet.ChartObjects(myChart).Chart
   End If

   seems to be intended to handle the exception which occurs when the users hasn’t selected a chart in advance of calling the procedure, the following statement fails:

   Set srs = cht.SeriesCollection(mySeries)

   I’ve done (five minutes) of tracing, but it isn’t clear to me why this fails. Any thoughts?

   Thanks again for all your effort and for posting your very good work.

   Fred Hammers
   ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
   The entire procedure looks like this:

   Public Sub AttachLabelsToPoints(myChart, mySeries, myOffset)
   '------------------------------------------------------------------------------
   ' Procedure : AttachLabelsToPoints
   ' Author    : Jon Peltier
   ' Date      : unkown
   ' Purpose   : This procedure creates and formats log scale plots with true scientific notation
   ' Reference :   https://peltiertech.com/Excel/Charts/ScientificNotation.html
   '------------------------------------------------------------------------------
   '
   ' myChart: Index of ChartObject, 0 for active chart
   ' mySeries: Index of series within SeriesCollection
   ' myOffset: Labels located to right of X data by myOffset columns
   
   '   It would be best to ensure that the only chart in the workbook was selected
   '   prior to running this code
   
   '//////////////////////////////////////////////////////////////////////////////
   'Variable definition
   '//////////////////////////////////////////////////////////////////////////////
   
   Dim Counter As Integer, ChartName As String
   Dim xVals As String
   Dim Counter1 As Integer, Counter2 As Integer
   Dim myString1 As String
   Dim cht As Chart, srs As Series, rng As Range
   
   '******************************************************************************
   'Code
   '******************************************************************************
   ' Disable screen updating while the subroutine is run.
   Application.ScreenUpdating = False
   
   ' Define chart and series
   If myChart = 0 Then
       Set cht = ActiveChart
   Else
       Set cht = ActiveSheet.ChartObjects(myChart).Chart
   End If
   'This fails if a chart isn't selected...
   Set srs = cht.SeriesCollection(mySeries)
   
   'Store the formula for the first series in "xVals"
   xVals = srs.Formula
   
   'Extract the range for the data from xVals
   Counter1 = 0
   Counter2 = 0
   Counter1 = InStr(xVals, ",") ' 27
   xVals = Mid(xVals, Counter1 + 1)
   If InStr(xVals, "(") = 1 Then
       xVals = Mid(xVals, 2)
       Counter1 = InStr(xVals, ")")
   Else
       Counter1 = InStr(xVals, ",")
   End If
   xVals = Left(xVals, Counter1 - 1)
   
   ' Define range with X values
   Set rng = ActiveSheet.Range(xVals)
   
   'Attach a label to each data point in the series
   Counter1 = rng.Cells.Count
   For Counter = 1 To Counter1
       ' Get label text from cell
       myString1 = rng.Cells(Counter, 1).Offset(0, myOffset).Value
       With srs.Points(Counter)
           ' Apply label to point
           .HasDataLabel = True
           .DataLabel.Characters.Text = _
               myString1
   
   'I added this; Jon's original required the user to manually move the data labels
           If mySeries = 2 Then    'Y Axis series
               .DataLabel.Position = xlLabelPositionLeft
           ElseIf mySeries = 3 Then    'X Axis series
               .DataLabel.Position = xlLabelPositionBelow
           Else
               'do nothing
           End If
           
           ' Superscript part of the label
           With .DataLabel.Characters(3, Len(myString1) - 2).Font
               .Superscript = True
   '            .Size = .Size + 1
           End With
       End With
   Next Counter
   
   ' Clean up the mess
   Set rng = Nothing
   Set srs = Nothing
   Set cht = Nothing
   Application.ScreenUpdating = True
   
   End Sub

6. Jon Peltier says

   Thursday, September 1, 2016 at 9:38 am

   Fred –

   Wow, that’s an old post in need or upgrading. The code really should be updated to run explicitly on the active chart, and maybe it could be made smart enough to figure out what data it needs to add to make the dummy axis series and what labels it needs to add to show the scientific notation.

   That code fails because it assumes if you’ve input chart number 0 that you’ve also selected a chart. You could try the following, which will do the first chart on the active sheet:

   If myChart = 0 Then
       If Not ActiveChart Is Nothing Then
           Set cht = ActiveChart
       Else
           Set cht = ActiveSheet.ChartObjects(1).Chart
       End If
   Else
       Set cht = ActiveSheet.ChartObjects(myChart).Chart
   End If

   or this, which will exit more gracefully:

   If myChart = 0 Then
       If Not ActiveChart Is Nothing Then
           Set cht = ActiveChart
       Else
           Exit Sub
       End If
   Else
       Set cht = ActiveSheet.ChartObjects(myChart).Chart
   End If