Jump to content
Software FX Community

Re: annotation-sample code

User (Legacy)

Recommended Posts

Here's some code I use in Access to load an XY graph and draw a line between two of the data points.


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

On Error GoTo ProcExit 'background printed report

Dim db As Database

Dim rsCmkt As Recordset

Dim j As Integer

Dim i As Integer

Dim nSeries As Integer

Dim nPoints As Integer

Dim objChart1 As ChartfxLib.ChartFX

Dim AnnotX As AnnotateX.AnnotationX

Dim objArrow As AnnArrow

'set referenct to chart object

Set objChart1 = Me.Chart1.Object

'create new annotation object

Set AnnotX = New AnnotationX

'add AnnotX to Chart1

objChart1.AddExtension AnnotX

'turn off toolbar

AnnotX.Toolbar = False

'add arrow object to AnnotX and set options

Set objArrow = AnnotX.Add(OBJECT_TYPE_ARROW)

'Capital Market line variables 'these are the XY coord of the line end points

Dim dblTB90X As Double

Dim dblTB90Y As Double

Dim dblMainX As Double

Dim dblMainY As Double

Dim strLegend As String

Set db = DBEngine(0)(0)

'open recordset on data

Set rsCmkt = db.OpenRecordset("SELECT IndexName, SDev, Return FROM tmpCmkt ORDER BY Return DESC;", dbOpenSnapshot) ' Open table. "

If rsCmkt.EOF Then

Goto ProcExit 'oops!

End If


rsCmkt.MoveFirst ' Leave at first record for loading data series

'record set is in a specific order, Your Fund, Policy, Main Index,Index1,Index2,Index?, 90 Day TBill

'locate left end of Capital Markets Line

rsCmkt.FindFirst "IndexName = 'TB90'"

If Not rsCmkt.NoMatch Then

dblTB90Y = rsCmkt!Return

dblTB90X = rsCmkt!SDev


rsCmkt.MoveLast 'else use lowest pct index

dblTB90Y = rsCmkt!Return

dblTB90X = rsCmkt!SDev

End If

'locate right end of Capital Markets Line

rsCmkt.FindFirst "IndexName = 'Policy Return'"

If Not rsCmkt.NoMatch Then 'Policy Return on Group, SP500 on Accounts

dblMainY = rsCmkt!Return

dblMainX = rsCmkt!SDev


rsCmkt.MoveFirst 'else use highest pct index

rsCmkt.Move 2

dblMainY = rsCmkt!Return

dblMainX = rsCmkt!SDev

End If

'reopen table in table order

'Set rsCmkt = db.OpenRecordset("SELECT IndexName, SDev, Return FROM tmpCmkt;", dbOpenSnapshot) ' Open table. "


rsCmkt.MoveFirst ' Leave at first record for loading data series

'reset axis properties



'set series and points

nSeries = rsCmkt.RecordCount

nPoints = 1

'objChart1.MultipleColors = True

'open data channel

objChart1.OpenDataEx COD_VALUES, nSeries, nPoints

objChart1.OpenDataEx COD_XVALUES, nSeries, nPoints 'this is the one for XY chart

objChart1.OpenDataEx COD_COLORS, nSeries, nPoints

j = 0 'this is a one series chart, xy plot

Do Until rsCmkt.EOF ' Begin loop for each series/row.

objChart1.Series(j).MarkerSize = 8

objChart1.Series(j).LineStyle = CHART_PS_TRANSPARENT

objChart1.Series(j).PointLabels = False

objChart1.Series(j).Border = False

strLegend = rsCmkt.Fields(0).Value

objChart1.Series(j).Legend = strLegend 'first column = Labels

objChart1.Series(j).Color = Eval(ColorLegend(strLegend)) 'custom color

objChart1.Series(j).MarkerShape = MarkerShape(strLegend) 'custom Wingding marker

If Not IsNull(rsCmkt.Fields(0).Value) Then

objChart1.XValueEx(j, 0) = rsCmkt.Fields(1).Value 'StdDev

objChart1.ValueEx(j, 0) = rsCmkt.Fields(2).Value 'Return

End If

rsCmkt.MoveNext ' Locate next record.

j = j + 1

Loop ' End of loop.

'close data channel

objChart1.CloseData COD_VALUES

objChart1.CloseData COD_XVALUES

objChart1.CloseData COD_COLORS

'set chart properties

With objChart1

.Gallery = SCATTER

.Chart3D = False

.MarkerSize = 5


.Palette = "ChartFX 3.0"

.RGBBk = RGB(215, 255, 215) 'light green background

.SerLegBoxObj.Visible = True

.SerLegBoxObj.BorderStyle = BBS_NONE

.SerLegBoxObj.Docked = TGFP_BOTTOM

.SerLegBoxObj.Font.Size = 9


'chart titles

.Axis(AXIS_Y).Title = "Returns (%)"

.Axis(AXIS_X).Title = "Risk ( S-Dev )"

'reset axis min = 0

.Axis(AXIS_X).ForceZero = True

.Axis(AXIS_Y).ForceZero = True

End With

'lastly set properties for the annotation object ( arrow )

objArrow.HeadStyle = ARROW_NONE

objArrow.Color = RGB(255, 153, 0)

objArrow.AllowMove = False

objArrow.BorderWidth = 8

objArrow.Refresh False

objArrow.Attach ATTACH_ELASTIC, CStr(dblTB90X) & "," & CStr(dblTB90Y) & "," & CStr(dblMainX) & "," & CStr(dblMainY)


If Not rs Is Nothing Then rsCmkt.Close

Set rsCmkt = Nothing

Set objChart1 = Nothing

Set objArrow = Nothing

Set AnnotX = Nothing

Set db = Nothing

End Sub

<morgenweck@hotmail.com> wrote in message news:5X6FcBpHAHA.2224@sfxserver.softwarefx.com...

> Do you have any sample vb code that uses annotaton dynamically.. I read the

> help, but I would still like to see some sample vb project.

> Thanks



Link to comment
Share on other sites


This topic is now archived and is closed to further replies.

  • Create New...