Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

How to translate excel macro code for chart object to AbAp code

xiswanto
Active Participant
595

Hello all,

Somehow I can't really find any documentation on how to translate excel macro code for chart object to ABAP code.

I appreciate if anyone can share any link to the translation guide or help me in translating the code below into ABAP code.

Here is the excel macro I wanted to translate ( which is to apply data label to stacked bar char, and to apply color to bar of each series; in my case, there are 3 series and I wanted to change each series color )

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SetElement (msoElementDataLabelNone)
    ActiveChart.SetElement (msoElementDataLabelCenter)
    ActiveChart.SetElement (msoElementDataLabelNone)
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleNone)
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    ActiveChart.SetElement (msoElementDataLabelCenter)
    ActiveChart.SetElement (msoElementDataTableWithLegendKeys)
    ActiveChart.SetElement (msoElementDataTableNone)
    ActiveChart.ApplyDataLabels
    ActiveChart.FullSeriesCollection(1).DataLabels.Select
    ActiveChart.FullSeriesCollection(1).Select
    ActiveChart.FullSeriesCollection(3).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 176, 240)
        .Solid
    End With
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
        .Solid
    End With
    ActiveChart.FullSeriesCollection(2).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent6
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.25
        .Transparency = 0
        .Solid
    End With

I did some attempt to apply the data label; although it show the label, but it disrupt the proses of the chart activation causing the chart object failed to be moved to source data sheet. Meanwhile, my attempt to change the color of fullseriescollection(index) failed.

  GET PROPERTY OF gs_application 'Charts' = gs_charts .
  CALL METHOD OF gs_charts 'Add' = gs_chart .
  CALL METHOD OF gs_chart 'Activate'.
  CALL METHOD OF gs_chart 'SetSourceData'
    EXPORTING
      #1 = gs_cells
      #2 = 1.
  SET PROPERTY OF gs_chart 'ChartType' = p_ch_type .
  SET PROPERTY OF gs_chart 'HasTitle' = p_title.
  SET PROPERTY OF gs_chart 'HasLegend' = p_legend.

  SET PROPERTY OF gs_chart 'ApplyDataLabels' = 'X'.

CALL METHOD OF gs_excel 'WorkSheets' = gs_activesheet EXPORTING #1 = gv_sheet_name. CALL METHOD OF gs_activesheet 'Activate' . CALL METHOD OF gs_chart 'Location' EXPORTING #1 = 2 #2 = gv_sheet_name.

Image 1: excel file run smoothly without the syntax set property of 'ApplyDataLabels'

Image 2 & 3: excel file stuck on the sheet of chart object with the syntax set property of 'ApplyDataLabels', failed to be moved to source data sheet ( although the data label is active )

5 REPLIES 5

xiswanto
Active Participant
0 Kudos
484

*Update 25.07.2023

I found the way to apply data label in chart.

However, I've still looking for way to update the color of each series in the chart. I do find out how to get the property of series until the part 'Selection.Format.Fill' and I am sure of it until this part, reason is because I could update the 'Visible' property which is below 'Fill' property. But weirdly enough, I tried to get the 'ForeColor' property, then I tried to set the 'RGB' property under 'ForeColor' (a.k.a. ForeColor.RGB) but it failed to do so using both 'set property of' and 'call method of', such as below passed value

SET PROPERTY OF gs_fullseries_c 'RGB' = 'RGB(255, 0, 0)'.<br>

I also tried to set another property 'ObjectThemeColor' under 'ForeColor' (a.k.a. ForeColor.ObjectThemeColor) but this one run without any problem using such code as below

SET PROPERTY OF gs_fullseries_c 'ObjectThemeColor' = 5.<br>

Please help suggest how do I fix the code.

Sandra_Rossi
Active Contributor
0 Kudos
484

Could you please record the VBA code corresponding to what you want to do, make sure the VBA code works, and post it here?

It's easy to translate then... (without need to understand what does the VBA code)

xiswanto
Active Participant
0 Kudos
484

sandra.rossi sorry, I never heard the VBA code term before, did a quick search, perhaps this below is the VBA code?

I obtained it from the macro recording I created. (tested it on Microsoft Visual Basic for Application and it works as intended).

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Col 1"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Col 2"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Col 3"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Col 4"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Row 1"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "5"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "7"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "9"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "11"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Row 2"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "5"
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "9"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "15"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "Row 3"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "15"
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "19"
    Range("D4").Select
    ActiveCell.FormulaR1C1 = "20"
    Range("E4").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("A1:E4").Select
    ActiveSheet.Shapes.AddChart2(297, xlColumnStacked).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$E$4")
    ActiveChart.SetElement (msoElementDataLabelCenter)
    ActiveChart.FullSeriesCollection(1).DataLabels.Select
    ActiveChart.SetElement (msoElementDataLabelNone)
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.FullSeriesCollection(1).Select
    Selection.Format.Fill.Visible = msoFalse
    ActiveChart.FullSeriesCollection(2).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 176, 240)
        .Transparency = 0
        .Solid
    End With
    ActiveChart.FullSeriesCollection(3).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Solid
    End With
    Range("H5").Select
End Sub

Sandra_Rossi
Active Contributor
0 Kudos
484

That's right, you have posted VBA code. The language of "Excel macro" is VBA (Visual Basic for Applications).