add a chart in a Add-In

[Ludo]

Hi,

I'm writing a add-in and need to create a chart in the addin.
As long as the IsAddin is set to FALSE, everything works fine, but as
soon as i set the IsAddin to TRUE, my routine bails out where i add a
chart (see <<<<< in the following routine).
I'm pritty sure that the lines following the Charts.Add will also
generate an error because you can't use the syntax ActiveCell,
ActiveChart, ... in a Addin (hidden workbook).

Is it possible to add a chart in a Addin, and if so, can someone help
me on how to change the code below so it will work in the addin?


Here's my routine:

Sub MyProfileGraph()
'
Dim intCntr As Integer
Dim strSSR_Number As String
Dim intSSR_Count As Integer
Dim strReturnAddress As String
Dim strMyChartName As String
Dim dblChartAreaHeight As Double
Dim dblChartAreaWidth As Double
Dim intMyDataSeriesCount As Integer
Dim intRowOffset As Integer
Dim intColumnOffset As Integer
Dim intDataRowCount As Integer
Dim wsGraphData As Worksheet

Set wsGraphData = ThisWorkbook.Sheets("Graph data")

intSSR_Count = 0 'clear intSSR_Count
'fill missing 0 values in top data row
intRowOffset = 2 'preset to row2
intColumnOffset = 1 'preset to column A

With wsGraphData
For intCntr = 1 To .Range("A1").CurrentRegion.Columns.Count
If .Cells(intRowOffset, intColumnOffset).value = "" Then
.Cells(intRowOffset, intColumnOffset).value = 0
End If
intColumnOffset = intColumnOffset + 1 'next column
Next
.Cells(2, 1).EntireRow.Insert
'create named ranges to use in the Graph.
.Cells(1, 1).Name = "Temp."
intDataRowCount = .Cells(3, 1).CurrentRegion.Rows.Count 'cell
A3
.Cells(3, 1).Resize(intDataRowCount, 2).Name = "Temperture"
intDataRowCount = .Cells(3, 3).CurrentRegion.Rows.Count 'cell
C3
.Cells(3, 3).Resize(intDataRowCount, 1).Name =
"Vibration_Time"
.Cells(3, 4).Resize(intDataRowCount, 1).Name =
"Vibration_Value"
strReturnAddress = .Cells(3, 6).Address 'cell F3
strSSR_Number = Left(.Range(strReturnAddress).Offset(-2,
0).value, 4)
While .Range(strReturnAddress).value <> ""
intDataRowCount
= .Range(strReturnAddress).CurrentRegion.Rows.Count 'cell F3, ...
.Range(strReturnAddress).Resize(intDataRowCount, 1).Name =
strSSR_Number '"SSR" & intCntr
intColumnOffset = .Range(strReturnAddress).Column + 1
'select ssr status column
.Cells(3, intColumnOffset).Resize(intDataRowCount, 1).Name
= strSSR_Number & "_status"
intColumnOffset = .Range(strReturnAddress).Column + 3
'select next ssr column
strReturnAddress = .Cells(3, intColumnOffset).Address
strSSR_Number = Left(.Range(strReturnAddress).Offset(-2,
0).value, 4)
intSSR_Count = intSSR_Count + 1 'count the
number of used SSR's
Wend
'
Charts.Add '<<<<<<<<<<<
bails out at this point !
strMyChartName = ActiveChart.Name '<<<<<expect here also
problems and in the following lines !
dblChartAreaHeight = ActiveChart.ChartArea.Height
dblChartAreaWidth = ActiveChart.ChartArea.Width
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
'add first data serie : Temperture = column A & B
ActiveChart.SetSourceData Source:=Sheets("Graph
Data").Range("Temperture"), PlotBy:=xlColumns
intMyDataSeriesCount = ActiveChart.SeriesCollection.Count
ActiveChart.SeriesCollection(intMyDataSeriesCount).Name =
"Temperature" 'Serie titel
'add vibration serie
ActiveChart.SeriesCollection.Add
Source:=.Range("Vibration_Value")
intMyDataSeriesCount = ActiveChart.SeriesCollection.Count
With ActiveChart.SeriesCollection(intMyDataSeriesCount)
.XValues = Names("Vibration_Time").RefersToRange
.Values = Names("Vibration_Value").RefersToRange
.Name = "Vibration"
End With
intMyDataSeriesCount = ActiveChart.SeriesCollection.Count
'add SSR series
For intCntr = 1 To intSSR_Count
ActiveChart.SeriesCollection.Add Source:=Range("SSR" &
intCntr)
intMyDataSeriesCount = ActiveChart.SeriesCollection.Count
With
ActiveChart.SeriesCollection(intMyDataSeriesCount)
.XValues = Names("SSR" & intCntr).RefersToRange '
.Values = Names("SSR" & intCntr &
"_Status").RefersToRange
.Name = "SSR" & intCntr
End With
Next
ActiveChart.Location Where:=xlLocationAsObject, Name:="Graph
Data"
'
With ActiveChart
.HasLegend = True
.HasTitle = True
.ChartTitle.Characters.Text = "Profile " &
strUnitFamilyName
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Time"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
"Vib. & Temp. Values"
End With
ActiveChart.Legend.Select
With Selection
.Position = xlTop
.Border.LineStyle = xlNone
End With
ActiveSheet.Shapes(1).ScaleWidth 1, msoFalse,
msoScaleFromBottomRight
ActiveSheet.Shapes(1).ScaleHeight 1, msoFalse,
msoScaleFromBottomRight
'select ssr1 serie
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.ColorIndex = 53
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.SeriesCollection(3).AxisGroup = 2
Select Case intSSR_Count
Case 2
'select ssr2 serie
ActiveChart.SeriesCollection(4).Select
With Selection.Border
.ColorIndex = 10
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.SeriesCollection(4).AxisGroup = 2
ActiveChart.Axes(xlValue, xlSecondary).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
With Selection
.MajorTickMark = xlNone
.MinorTickMark = xlNone
.TickLabelPosition = xlNone
End With
'
End Select
'rescale the SSR graph height
With ActiveChart.Axes(xlValue, xlSecondary)
.MajorTickMark = xlNone
.MinorTickMark = xlNone
.TickLabelPosition = xlNone
.MaximumScale = 12
End With
'select temperture serie
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.ColorIndex = 5 'dark blue
.Weight = xlMedium
.LineStyle = xlContinuous
End With
'select vibration serie
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.ColorIndex = 54
.Weight = xlMedium
.LineStyle = xlContinuous
End With
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = HighestProfileTimeValue
End With
'export chart as GIF picture
' SaveChartAsGIF strUnitFamilyName
'
ActiveWindow.Visible = False
End With
'remove chart from sheet "Graph Data"
ThisWorkbook.Sheets("graph data").ChartObjects.Delete
End Sub

Thanks for looking and any help is apreciated.
Regards,
Ludo

 

[Ludo]

Hi,

I'm writing a add-in and need to create a chart in the addin.
As long as the IsAddin is set to FALSE, everything works fine, but as
soon as i set the IsAddin to TRUE, my routine bails out where i add a
chart (see <<<<< in the following routine).
I'm pritty sure that the lines following the Charts.Add will also
generate an error because you can't use the syntax ActiveCell,
ActiveChart, ... in a Addin (hidden workbook).

Is it possible to add a chart in a Addin, and if so, can someone help
me on how to change the code below so it will work in the addin?

<SNIP>


After day's trying and getting realy frustrated, I guess i found it.

all steps need to be done into your add-in
1) set the IsAddin property to FALSE
2) select the data you want to use in the graph from a worksheet
within the addin (my sheet name = Graph Data)
3) add a chart into this worksheet (i used a line chart with 3 data
series).
- data for serie 1 into column A & B
- data for serie 2 into columns C & D
- data for serie 3 into columns F & G
4)Select all the cells and remove the data from this sheet (Graph
Data) with 'Clear Contents'
5) select now cell A1 into your sheet Graph Data
6) run now the code below to test.
7) if it works, fine, clear the data back as in step 4 & 5
set now the IsAddin property back to TRUE and save your work.

Remark:
You will need to make some changes according to your needs.
Note also that i copy data from sheet2 into the sheet 'Graph Data" for
test purpose.

Here's my routine:

Sub MyGraphTest2()
'test routine voor de grafiek
'************************************************************
'* DO NOT remove the chart from sheet "Graph Data" !!!!!! *
'************************************************************
Dim intCntr As Integer
Dim strMyChartName As String
Dim intRowOffset As Integer
Dim intColumnOffset As Integer
Dim intDataRowCount As Integer
Dim wsGraphData As Worksheet
Dim Rowoffset As Integer
Dim strUnitFamilyName As String
Dim HighestProfileTimeValue As Double
Dim MyChartObj As ChartObject
Dim chMyChart As Chart
Dim n

Set wsGraphData = ThisWorkbook.Sheets("graph data")

'for test purpose, copy data from sheet2 to sheet Grapg Data
ThisWorkbook.Sheets("sheet2").Cells.Copy
Destination:=wsGraphData.Range("A1")
'end copy

Set MyChartObj = wsGraphData.ChartObjects(1)
'
'remove named ranges
For Each n In wsGraphData.Names
n.Delete
Next
'
strUnitFamilyName = "DU8x5"
Rowoffset = ThisWorkbook.Sheets("Graph
data").Range("F1").CurrentRegion.Rows.Count
HighestProfileTimeValue = ThisWorkbook.Sheets("Graph
data").Range("F" & Rowoffset).Value
'fill missing 0 values in top data row
intRowOffset = 2 'preset to row2
intColumnOffset = 1 'preset to column A
With wsGraphData
For intCntr = 1 To .Range("A1").CurrentRegion.Columns.Count
If .Cells(intRowOffset, intColumnOffset).Value = "" Then
.Cells(intRowOffset, intColumnOffset).Value = 0
End If
intColumnOffset = intColumnOffset + 1 'next column
Next
.Cells(2, 1).EntireRow.Insert
'create named ranges to use in the Graph.
.Cells(1, 1).Name = "Temp."
intDataRowCount = .Cells(3, 1).CurrentRegion.Rows.Count 'cell
A3
.Cells(3, 1).Resize(intDataRowCount, 1).Name =
"Temperture_Time"
.Cells(3, 2).Resize(intDataRowCount, 1).Name =
"Temperture_Value"
intDataRowCount = .Cells(3, 3).CurrentRegion.Rows.Count 'cell
C3
.Cells(3, 3).Resize(intDataRowCount, 1).Name =
"Vibration_Time"
.Cells(3, 4).Resize(intDataRowCount, 1).Name =
"Vibration_Value"
.Cells(3, 6).Resize(intDataRowCount, 1).Name = "SSR1_Time"
.Cells(3, 7).Resize(intDataRowCount, 1).Name = "SSR1_Value"
'
Set chMyChart = MyChartObj.Chart
With chMyChart
.HasLegend = True
.HasTitle = True
.ChartTitle.Characters.Text = "Profile " &
strUnitFamilyName
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Time"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
"Vib. & Temp. Values"
.ChartType = xlXYScatterLinesNoMarkers
With chMyChart.Legend
.Position = xlTop
.Border.LineStyle = xlNone
End With
'Series 1 - Temperture
With chMyChart.SeriesCollection(1)
.XValues = wsGraphData.Range("Temperture_time")
.Values = wsGraphData.Range("Temperture_Value")
.Name = "Temperture"
End With
'set border style
With chMyChart.SeriesCollection(1).Border
.ColorIndex = 5 'dark blue
.Weight = xlMedium
.LineStyle = xlContinuous
End With
'
'Series 2 - Vibration
With chMyChart.SeriesCollection(2)
.XValues = wsGraphData.Range("Vibration_Time")
.Values = wsGraphData.Range("Vibration_Value")
.Name = "Vibration"
End With
'set border style
With chMyChart.SeriesCollection(2).Border
.ColorIndex = 54
.Weight = xlMedium
.LineStyle = xlContinuous
End With
'
'Series 3 - SSR1
With chMyChart.SeriesCollection(3)
.XValues = wsGraphData.Range("SSR1_Time")
.Values = wsGraphData.Range("SSR1_Value")
.Name = "SSR1"
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
'set border style
With chMyChart.SeriesCollection(3).Border
.ColorIndex = 53
.Weight = xlThin
.LineStyle = xlContinuous
End With

chMyChart.SeriesCollection(3).AxisGroup = 2
End With
'
'rescale the SSR graph height
With chMyChart.Axes(xlValue, xlSecondary)
.MajorTickMark = xlNone
.MinorTickMark = xlNone
.TickLabelPosition = xlNone
.MaximumScale = 12
End With
With chMyChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = HighestProfileTimeValue
End With
strMyChartName = chMyChart.Name
'position the chart always on the same place
With chMyChart.Parent
.Height = 325 ' resize
.Width = 500 ' resize
.Top = 100 ' reposition
.Left = 100 ' reposition
End With
'
'export the chart if needed to a userform Imagecontrol
'you can use the Paste picture code from Stephen Bullen, see link
below
'http://www.oaltd.co.uk/DLCount/DLCount.asp?file=PastePicture.zip
'
End With
Set chMyChart = Nothing
Set wsGraphData = Nothing
End Sub

Hope someone else can use parts of it.