AW: Säulendiagramm mittels VBA erstellen
08.07.2013 10:32:20
Klaus
Hi Grigor,
ich sehe eine Mustertabelle, in der wahllos Zahlen verteilt sind. Eine Userform existiert zwar, aber es gibt keinen Button, kein Event welches sie aufruft.
Ich muss jetzt raten, welche Zahlen wie und wohin was für ein Säulendiagramm ergeben sollen. Oder du erklärst es nochmal vernünftig!
Im Prinzip erstellst du ein Säulendiagramm so (Makrorekorder-Code)
Sub Macro1()
Range("B30:D40").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("Tabelle1!$B$30:$D$40")
End Sub
etwas eleganter ginge es so:dann so:
Option Explicit
'* Module for quick creation of charts in the active worksheet
'* created: June 2013 by Klaus Meyer von der Twer (Thanks to Rudi @ herber!)
'* every feature after data-Range is optional!
Sub test()
Call MakeSingleChart(Range("B4:C12"), 1, Range("A1"), 300, 200, 10, 90, True)
Call MakeSingleChart(Range("B4:C12"), , , , , , , False)
Call MakeSingleChart(Range("B4:C12"))
End Sub
Sub MakeSingleChart( _
rngData As Range, _
Optional TypeChart As Integer, _
Optional rngTopLeftCell As Variant, _
Optional iHeight As Variant, _
Optional iWidth As Variant, _
Optional iScaleMin As Variant, _
Optional iScaleMax As Variant, _
Optional bNoLegend As Boolean)
'rngData = Range of Data
'TypeChart 1=Line, 2=Pie, 3=Column, 4=ColumnStacked, 5=ColumStacked100, none=Line
'rngTopLeftCell = Move Chart's top left corner to THIS cell, places in mid-screen if not _
choosen
'iHeight and iWidth = Height/Width in Pixels, uses standard if not choosen
'iScaleMin and iScaleMax = FIX X-Axis to this value. If not choosen, chart will autoscale
'bNoLegend = if TRUE, Legend is removed. If FALSE or not choosen, Legend stays in the chart
On Error GoTo hell
'rngTopLeftCell hast to be a valid RANGE, iHeight a valid INTEGER and so on
' Declaration has to be VARIANT to enable the ISMISSING feature. In case of wrong usage
' (example: set height to "hello world") it will end with ONERROR
Dim myCht As Object
Set myCht = ActiveSheet.Shapes.AddChart 'create new Chart
With myCht
With .Chart
'Chart definitions ************************************
.ChartType = xlLine 'set Line as standard
Select Case TypeChart
Case 1
Case 2
.ChartType = xlPie
Case 3
.ChartType = xlColumnClustered
Case 4
.ChartType = xlColumnStacked
Case 5
.ChartType = xlColumnStacked100
Case Else
End Select
.SetSourceData Source:=rngData
' ************************************ Chart Definitions
If bNoLegend Then .Legend.Delete 'Legend or not?
End With
'move and scale ************************************
If Not IsMissing(iScaleMax) Then .Chart.Axes(xlValue).MaximumScale = iScaleMax
If Not IsMissing(iScaleMin) Then .Chart.Axes(xlValue).MinimumScale = iScaleMin
If Not IsMissing(rngTopLeftCell) Then
.Top = rngTopLeftCell.Top
.Left = rngTopLeftCell.Left
End If
If Not IsMissing(iHeight) Then .Height = iHeight
If Not IsMissing(iWidth) Then .Width = iWidth
'************************************ move and scale
End With
GoTo heaven:
hell:
'get rid of wrong chart
myCht.Delete
'display error messge
MsgBox "could not create chart!" & vbCrLf _
& "Error Number: " & Err.Number & _
vbCrLf & "Error: " & Err.Description
heaven:
End Sub
Den Bereich des Diagramms kannst du im Call angeben. Wie der Zustande kommt (aus deiner Userform) weisst nur du.
Grüße,
Klaus M.vdT.