Grafikoni Excel VBA
Grafikone lahko v VBA označimo kot predmete, podobno kot na delovni list, lahko na enak način vstavimo tudi grafikone v VBA, najprej izberemo podatke in vrsto grafikona, ki ga želimo za zunanje podatke, zdaj imamo dve različni vrsti grafikonov, ki jih ponujamo eno je vdelani grafikon, kjer je grafikon v istem listu podatkov, drugi pa je znan kot list grafikona, kjer je grafikon v ločenem listu podatkov.
Pri analizi podatkov so vizualni učinki ključni kazalniki uspešnosti osebe, ki je analizo opravila. Vizualizacija je najboljši možen način, na katerega lahko analitik prenese svoje sporočilo. Ker smo vsi uporabniki Excela, običajno veliko časa namenimo analiziranju podatkov in sklepanju s številkami in grafikoni. Ustvarjanje grafikona je umetnost za obvladovanje in upam, da dobro poznate ustvarjanje grafikonov z excelom. V tem članku vam bomo pokazali, kako ustvariti grafikone z uporabo kodiranja VBA.
Kako dodati karte z uporabo kode VBA v Excelu?
To predlogo Excel VBA Charts Excel lahko prenesete tukaj - VBA Charts Excel Predloga# 1 - Ustvari grafikon z uporabo kodiranja VBA
Za ustvarjanje katerega koli grafikona bi morali imeti nekakšne numerične podatke. Za ta primer bom uporabil spodnje vzorčne podatke.
Ok, pojdimo na urejevalnik VBA.
1. korak: Začnite podproces.
Koda:
Sub Charts_Example1 () Konec Sub
2. korak: Spremenljivko določite kot graf.
Koda:
Sub Charts_Example1 () Dim MyChart kot Chart End Sub
3. korak: Ker je grafikon predmetna spremenljivka, ga moramo nastaviti .
Koda:
Sub Charts_Example1 () Dim MyChart As Chart Set MyChart = Charts.Add End Sub
Zgornja koda bo dodala nov list kot list grafikona, ne kot delovni list.
4. korak: Zdaj moramo oblikovati grafikon. Odpri z izjavo.
Koda:
Sub Charts_Example1 () Dim MyChart As Chart Set MyChart = Charts.Add With MyChart End With End Sub
5. korak: Prva stvar pri grafikonu je, da nastavimo obseg vira z izbiro metode »Set Source Data«
Koda:
Sub Charts_Example1 () Dim MyChart As Chart Set MyChart = Charts.Add With MyChart .SetSourceData End With End Sub
6. korak: Tu moramo omeniti obseg virov. V tem primeru je moj obseg vira v listu, imenovanem "List1", in obseg "A1 do B7".
Koda:
Sub Charts_Example1 () Dim MyChart As Chart Set MyChart = Charts.Add With MyChart .SetSourceData Sheets ("Sheet1"). Obseg ("A1: B7") End With End Sub 
7. korak: Nato moramo izbrati vrsto grafikona, ki ga bomo ustvarili. Za to moramo izbrati lastnost vrste grafikona .
Koda:
Sub Charts_Example1 () Dim MyChart As Chart Set MyChart = Charts.Add With MyChart .SetSourceData Sheets ("Sheet1"). Obseg ("A1: B7") .ChartType = End with End Sub 
8. korak: Tu imamo različne karte. Izbral bom grafikon " xlColumnClustered ".
Koda:
Sub Charts_Example1 () Dim MyChart As Chart Set MyChart = Charts.Add With MyChart .SetSourceData Sheets ("Sheet1"). Range ("A1: B7") .ChartType = xlColumnClustered End With End Sub 
V redu, v tem trenutku zaženimo kodo s tipko F5 ali ročno in si oglejmo, kako grafikon izgleda.
9. korak: Zdaj spremenite druge lastnosti grafikona. Če želite spremeniti naslov grafikona spodaj, je koda.
Tako imamo z grafikoni veliko lastnosti in metod. Uporabite vsako od njih, da vidite vpliv in se naučite.
Sub Charts_Example1() Dim MyChart As Chart Set MyChart = Charts.Add With MyChart .SetSourceData Sheets("Sheet1").Range("A1:B7") .ChartType = xlColumnClustered .ChartTitle.Text = "Sales Performance" End With End Sub #2 – Create a Chart with Same Excel Sheet as Shape
To create the chart with the same worksheet (datasheet) as shape we need to use a different technique.
Step 1: First Declare threes Object Variables.
Code:
Sub Charts_Example2() Dim Ws As Worksheet Dim Rng As Range Dim MyChart As Object End Sub
Step 2: Then Set the Worksheet reference.
Code:
Sub Charts_Example2() Dim Ws As Worksheet Dim Rng As Range Dim MyChart As Object Set Ws = Worksheets("Sheet1") End Sub 
Step 3: Now set the range object in VBA
Code:
Sub Charts_Example2() Dim Ws As Worksheet Dim Rng As Range Dim MyChart As Object Set Ws = Worksheets("Sheet1") Set Rng = Ws.Range("A1:B7") End Sub 
Step 4: Now set the chart object.
Code:
Sub Charts_Example2() Dim Ws As Worksheet Dim Rng As Range Dim MyChart As Object Set Ws = Worksheets("Sheet1") Set Rng = Ws.Range("A1:B7") Set MyChart = Ws.Shapes.AddChart2 End Sub 
Step 5: Now, as usual, we can design the chart by using the “With” statement.
Code:
Sub Charts_Example2() Dim Ws As Worksheet 'To Hold Worksheet Reference Dim Rng As Range 'To Hold Range Reference in the Worksheet Dim MyChart As Object Set Ws = Worksheets("Sheet1") 'Now variable "Ws" is equal to the sheet "Sheet1" Set Rng = Ws.Range("A1:B7") 'Now variable "Rng" holds the range A1 to B7 in the sheet "Sheet1" Set MyChart = Ws.Shapes.AddChart2 'Chart will be added as Shape in the same worksheet With MyChart.Chart .SetSourceData Rng 'Since we already set the range of cells to be used for chart we have use RNG object here .ChartType = xlColumnClustered .ChartTitle.Text = "Sales Performance" End With End Sub This will add the chart below.
#3 – Code to Loop through the Charts
Like how we look through sheets to change the name or insert values, hide & unhide them. Similarly to loop through the charts we need to use chart object property.
The below code will loop through all the charts in the worksheet.
Code:
Sub Chart_Loop() Dim MyChart As ChartObject For Each MyChart In ActiveSheet.ChartObjects 'Enter the code here Next MyChart End Sub
#4 – Alternative Method to Create Chart
We can use the below alternative method to create charts. We can use the Chart Object. Add method to create the chart below is the example code.
This will also create a chart like the previous method.
Code:
Sub Charts_Example3() Dim Ws As Worksheet Dim Rng As Range Dim MyChart As ChartObject Set Ws = Worksheets("Sheet1") Set Rng = Ws.Range("A1:B7") Set MyChart = Ws.ChartObjects.Add(Left:=ActiveCell.Left, Width:=400, Top:=ActiveCell.Top, Height:=200) MyChart.Chart.SetSourceData Source:=Rng MyChart.Chart.ChartType = xlColumnStacked MyChart.Chart.ChartTitle.Text = "Sales Performance" End Sub