Sub Initialize
' ### create workbook ###
' create an excel instance
Dim xlApp As Variant ' excel application object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
' add a workbook
Dim xlWorkbooks As Variant ' workbook list object
Dim xlWorkbook As Variant ' workbook object
Set xlWorkbooks = xlApp.Workbooks
Set xlWorkbook = xlWorkbooks.Add
' ### create table ###
' get the first sheet of the new workbook
Dim xlSheets As Variant ' worksheet list object of one workbook object
Dim xlSheet As Variant ' worksheet object
Set xlSheets = xlWorkbook.Sheets
Set xlSheet = xlSheets.Item(1) ' or type Set xlSheet = xlSheets(1)
' write row titles
Dim rowtitles As Variant ' range object
Dim var_rowtitles As Variant ' values of the range object rowtitles
Set rowtitles = xlSheet.Range(xlSheet.Cells(2, 1), xlSheet.Cells(5, 1)) ' get the range "A2:A5"
var_rowtitles = rowtitles.Value
var_rowtitles(1, 1) = "Fehler1"
var_rowtitles(2, 1) = "Fehler2"
var_rowtitles(3, 1) = "Fehlersumme"
var_rowtitles(4, 1) = "Verbrauch"
rowtitles.Value = var_rowtitles
' write first column
Dim column As Variant ' range object
Dim var_column As Variant ' values of the range object column
Set column = xlSheet.Range(xlSheet.Cells(1, 2), xlSheet.Cells(5, 2)) ' get the range "B1:B5"
var_column = column.Value
var_column(1, 1) = "A"
var_column(2, 1) = 4
var_column(3, 1) = 3
var_column(4, 1) = 7
var_column(5, 1) = 70
column.Value = var_column
' write second column
Set column = xlSheet.Range(xlSheet.Cells(1, 3), xlSheet.Cells(5, 3)) ' get the range "C1:C5"
var_column = column.Value
var_column(1, 1) = "B"
var_column(2, 1) = 3
var_column(3, 1) = 2
var_column(4, 1) = 5
var_column(5, 1) = 50
column.Value = var_column
' ### create chart ###
Const xlLocationAsObject = 2
Const xlColumnClustered = 51
Const xlRows = 1
' add new chart to workbook
Dim xlCharts As Variant ' charts list object of one workbook object
Dim xlChart As Variant ' chart object
Dim sc As Variant ' series collection object
Set xlCharts = xlWorkbook.Charts ' get the chart collection of xlWorkbook
Set xlChart = xlCharts.Add ' create a new chart in xlCharts
xlChart.ChartType = xlColumnClustered
Call xlChart.SetSourceData(xlSheet.Range(rowtitles, column), xlRows) ' form row titles to last wridden column
xlChart.HasTitle = True
xlChart.ChartTitle.Characters.Text = "Vergleich: "
xlChart.HasLegend = False
xlChart.PlotArea.Interior.ColorIndex = "0"
' data table
xlChart.HasDataTable = True
xlChart.DataTable.ShowLegendKey = True
xlChart.DataTable.font.size = 8
' series properties
Set sc = xlChart.SeriesCollection ' get the speries collection of the xlChart obcect
xlChart.ChartGroups(1).GapWidth = 10
sc(1).Interior.ColorIndex = 27
sc(2).Interior.ColorIndex = 32
xlChart.Location xlLocationAsObject, xlSheet.Name
' ### set shape properties ###
Const msoScaleFromTopLeft = 0
Const msoFalse = 0
Dim xlShape As Variant ' shape object
Set xlShape = xlSheet.Shapes(1) ' get the first shape of xlSheet's shape collection
xlShape.ScaleWidth 1.50, msoFalse, msoScaleFromTopLeft
xlShape.ScaleHeight 1.24, msoFalse, msoScaleFromTopLeft
xlShape.IncrementLeft -183
xlShape.IncrementTop -122.25
End Sub