[Excel][VBA] 차트 일반작업

VBA

[Excel][VBA] 차트 일반작업

어린왕자1234 2021. 11. 26. 14:35

■ 차트 종류

- 워크시트에 있는 차트

: 차트로 만들 영역 선택 후 Alt + F1 , 워크시트에 삽입된 차트, 워크시트에 여러 차트가 삽입 된 경우

- 차트 시트

: 차트로 만들 영역 선택 후 F11, 차트 시트에는 차트 하나만 삽입된 경우

■ 차트 만들기

워크시트 차트	차트시트
Sub aaa() Dim Mychart As Chart Dim DataRange As Range Set DataRange = ActiveSheet.Range("a1:b3") 'worksheet에 차트 생성 Set Mychart = ActiveSheet.Shapes.AddChart.Chart Mychart.SetSourceData Source:=DataRange Mychart.ChartType = xlBarStacked End Sub	Sub aaa() Dim Mychart As Chart Dim DataRange As Range Set DataRange = ActiveSheet.Range("a1:b3") '차트시트 만들기 Set Mychart = Charts.Add Mychart.SetSourceData Source:=DataRange Mychart.ChartType = xlBarStacked End Sub

■ 차트 활성화

워크시트 차트	차트시트
ActiveSheet.ChartObjects("차트 1").Activate ActiveSheet.Shapes("차트 1").Select	Sheets("chart1").Activate

■ 차트 이동

워크시트 차트 -> 차트시트	차트시트 -> 워크시트 차트
ActiveSheet.ChartObjects(1).Chart.Location xlLocationAsNewSheet, "MyChart"	Charts("Chart1").Location xlLocationAsObject, "sheet1"

■ 차트 비활성화

ActiveWindow.RangeSelection.Select

■ 차트 활성상태 확인

Function ChartIsSlected() As Boolean
ChartIsSlected = Not ActiveChart Is Nothing
End Function

■ 차트 삭제

	워크시트 차트	차트시트
일부 차트	ActiveSheet.ChartObjects(1).Delete ActiveSheet.Shapes(1).Delete	Charts(1).Delete
전체 차트	ActiveSheet.ChartObjects.Delete ActiveSheet.Shapes.Delete	Charts.Delete
전체 차트	ActiveWorkbook.Charts.Delete
경고창 비활성화	Application.DisplayAlerts = False

■ 모든차를 한 번에 조작

Sub FormatAllCharts()
    Dim ChtObj As ChartObject
    For Each ChtObj In ActiveSheet.ChartObjects
      With ChtObj.Chart
        .ChartType = xlLineMarkers
        .ApplyLayout 3
        .ChartStyle = 12
        .ClearToMatchStyle
        .SetElement msoElementChartTitleAboveChart
        .SetElement msoElementLegendNone
        .SetElement msoElementPrimaryValueAxisTitleNone
        .SetElement msoElementPrimaryCategoryAxisTitleNone
        .Axes(xlValue).MinimumScale = 0
        .Axes(xlValue).MaximumScale = 1000
      End With
    Next ChtObj
End Sub

Sub FormatAllCharts()
    Dim ChtObj As Chart
    For Each ChtObj In ActiveWorkbook.Charts
      With ChtObj
        .ChartType = xlLineMarkers
        .ApplyLayout 3
        .ChartStyle = 12
        .ClearToMatchStyle
        .SetElement msoElementChartTitleAboveChart
        .SetElement msoElementLegendNone
        .SetElement msoElementPrimaryValueAxisTitleNone
        .SetElement msoElementPrimaryCategoryAxisTitleNone
        .Axes(xlValue).MinimumScale = 0
        .Axes(xlValue).MaximumScale = 1000
      End With
    Next ChtObj
End Sub

■ 차트크기변경 후 재배열

Sub SizeAndAlignCharts()
    Dim W As Long, H As Long
    Dim TopPosition As Long, LeftPosition As Long
    Dim ChtObj As ChartObject
    Dim i As Long, NumCols As Long

    If ActiveChart Is Nothing Then
        MsgBox "Select a chart to be used as the base for the sizing"
        Exit Sub
    End If

    'G2t columns
    On Error Resume Next
    NumCols = InputBox("How many columns of charts?")
    If Err.Number <> 0 Then Exit Sub
    If NumCols < 1 Then Exit Sub
    On Error GoTo 0

    'Get size of active chart
    W = ActiveChart.Parent.Width
    H = ActiveChart.Parent.Height

    'Change starting positions, if necessary
    TopPosition = 100
    LeftPosition = 20
    For i = 1 To ActiveSheet.ChartObjects.Count
        With ActiveSheet.ChartObjects(i)
            .Width = W
            .Height = H
            .Left = LeftPosition + ((i - 1) Mod NumCols) * W
            .Top = TopPosition + Int((i - 1) / NumCols) * H
        End With
    Next i
End Sub

■ 차트를 그래픽 파일로 내보내기 (현재파일 위치에 GIF, PNG권장)

Sub SaveChartToGif()
    Dim Fname As String
    If ActiveChart Is Nothing Then Exit Sub
    Fname = ThisWorkbook.Path & "\" & ActiveChart.Name & ".gif"
    ActiveChart.Export Filename:=Fname, filtername:="GIF"
End Sub

■ 모든 그래픽 파일 내보내기

Sub SaveAllGraphics()
    Dim FileName As String
    Dim TempName As String
    Dim DirName As String
    Dim gFile As String

    FileName = ActiveWorkbook.FullName
    TempName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & "graphics.htm"
    DirName = Left(TempName, Len(TempName) - 4) & "_files"

'   Save active workbookbook as HTML, then reopen original
    ActiveWorkbook.Save
    ActiveWorkbook.SaveAs FileName:=TempName, FileFormat:=xlHtml
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Workbooks.Open FileName

'   Delete the HTML file
    Kill TempName

'   Delete all but *.PNG files in the HTML folder
    gFile = Dir(DirName & "\*.*")
    Do While gFile <> ""
        If Right(gFile, 3) <> "png" Then Kill DirName & "\" & gFile
        gFile = Dir
    Loop

'   Show the exported graphics
    Shell "explorer.exe " & DirName, vbNormalFocus
End Sub

저작자표시 (새창열림)