add a chart in a Add-In
 

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 Whe=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