What is the 'actual' axis width? I need to draw a custom axis. VBA

[APS2010]

Dear Mr Excel forum.

I'm struggling with this one... I've searched online and not come up with an answer.

I have a semi log chart in excel (x-axis log scale). I would like to draw a 'custom' axis above this chart with VBA. This custom axis needed to be scaled exactly with the graph, however I can't seem to acheive this.

I have tried using the PlotArea.InsideTop .InsideLeft and .insideWidth properties, however these do not seem to return the actual "plot area"?

I've also tried returning the horisontal axis width - but this also isn't returning the "true" width.

If this all sounds a bit vague - I've posted code below. Assuming my code/logic is accurate - why does the last draw box extend beyond the edge of the graph?

Any help would be really appreciated!

Thanks in advance


Alex

Graph details:
x-y plot (faked semi-log using error bars to show gridlines):
x-axis min/max: -6.90776 to 6.39692965522
y-axis min/max: 0 to 100

Code:
' ********************
' Start of code
' ********************
Public Sub testDraw()
AddClassification 0.001, 0.005, "CAT1", 20, True
AddClassification 0.005, 0.05, "CAT2", 20, True
AddClassification 0.05, 5, "CAT3", 20, True
AddClassification 5, 50, "CAT4", 20, True
AddClassification 50, 100, "CAT5", 20, True
AddClassification 100, 600, "CAT6", 20, True
End Sub

Private Sub AddClassification(ByVal minSize As Double, ByVal maxSize As Double, ByVal txt As String, ByVal myHeight As Single, ByVal closeBox As Boolean)
Dim graphsheet As Worksheet
Dim axisMin, axisMax, axisLen, minFactor, maxFactor As Double
Dim myTop, myLeft, myWidth As Double
Dim myPolyLine(1 To 5, 1 To 2) As Single
Dim myShape As String
Set graphsheet = ActiveSheet
For n = 1 To 5
myPolyLine(n, 1) = 0

Next n
With graphsheet.ChartObjects(2).Chart
axisMax = .Axes(xlCategory).MaximumScale
axisMin = .Axes(xlCategory).MinimumScale

axisLen = axisMax - axisMin
minSize = Application.WorksheetFunction.Ln(minSize)
maxSize = Application.WorksheetFunction.Ln(maxSize)

minFactor = (minSize - axisMin) / axisLen
maxFactor = (maxSize - axisMin) / axisLen

myTop = .PlotArea.InsideTop - (myHeight + 5)
myLeft = .Axes(xlCategory).Left
myWidth = .Axes(xlCategory).Width


.Shapes.AddTextbox(msoTextOrientationHorizontal, _
myLeft + (myWidth * minFactor), _
myTop, _
(myWidth * maxFactor) - (myWidth * minFactor), _
myHeight).Select
myShape = Selection.Name

.Shapes.Item(myShape).TextFrame.Characters().Text = txt
.Shapes.Item(myShape).TextFrame.Characters().Font.Size = 10
.Shapes.Item(myShape).Line.Visible = msoFalse
.Shapes.Item(myShape).Fill.Visible = msoFalse
.Shapes.Item(myShape).TextFrame.MarginTop = 0
.Shapes.Item(myShape).TextFrame.MarginLeft = 0
.Shapes.Item(myShape).TextFrame.MarginRight = 0
.Shapes.Item(myShape).TextFrame.MarginBottom = 0
.Shapes.Item(myShape).TextFrame.HorizontalAlignment = xlHAlignCenter
.Shapes.Item(myShape).TextFrame.VerticalAlignment = xlVAlignCenter

myPolyLine(1, 1) = myLeft + (myWidth * minFactor)
myPolyLine(1, 2) = myTop

myPolyLine(2, 1) = myLeft + (myWidth * minFactor)
myPolyLine(2, 2) = myTop + myHeight

myPolyLine(3, 1) = myLeft + (myWidth * maxFactor)
myPolyLine(3, 2) = myTop + myHeight

If closeBox Then
myPolyLine(4, 1) = myLeft + (myWidth * maxFactor)
myPolyLine(4, 2) = myTop

myPolyLine(5, 1) = myLeft + (myWidth * minFactor)
myPolyLine(5, 2) = myTop
Else
myPolyLine(4, 1) = myPolyLine(3, 1)
myPolyLine(4, 2) = myPolyLine(3, 2)

myPolyLine(5, 1) = myPolyLine(3, 1)
myPolyLine(5, 2) = myPolyLine(3, 2)
End If

.Shapes.AddPolyline(myPolyLine).Select

myShape = Selection.Name
.Shapes.Item(myShape).Line.ForeColor.RGB = RGB(0, 0, 0)
.Shapes.Item(myShape).Line.Weight = 0.5
.Shapes.Item(myShape).Fill.Visible = msoFalse
.Shapes.Item(myShape).ZOrder msoBringToFront

End With

End Sub

' ********************
' End of code
' ********************