Q: Format datatable values in VBA Currently working on a vba script that makes charts automatically. I would like to add a datatable which is done using: .HasDataTable = True
However I would like to show the values of series as percentages. Currently the value is defined as a Double containing all the values but not the right formatting. Using Format() or FormatPercent() will give the right values but returned in a String. This works for the datatable but not for the chart itself since it doesn't recognize the values anymore.
My question comes down to whether it is possible to show the values as percentages in both the datatable and the chart? Without VBA it is easily done by formatting the data in the cells itself. The problem is that for formatting a String is returned but for the graph Integers or Doubles are needed.
Below is part of the code. If I dim Ratio as String and use FormatPercent() I get the requested formatting but then the values in Ratio ar no longer doubles so it doesn't give the required chart.
Dim Ratio() As Double   
Dim labels() As String

ReDim Ratio(1 To Height)
ReDim labels(1 To Height)


For Each Column In sArray
        labels(i) = Sheets(DataSheetName).Cells(LabelsRow, Column)
        Ratio(i) = Math.Round(Sheets(DataSheetName).Cells(LabelsRow + 3, Column), 2)
        i = i + 1
Next Column


Set myChtObj = Sheets(DrawSheetName).ChartObjects.Add(Left:=Left, Width:=Width, Top:=Top, Height:=HeightGraph)

Dim srsNew1 As Series

 ' Add the chart
With myChtObj.Chart
    .ChartArea.Fill.Visible = False
    .ChartArea.Border.LineStyle = xlNone
    .PlotArea.Format.Fill.Solid
    .PlotArea.Format.Fill.Transparency = 1
    .HasTitle = True
    .ChartTitle.text = Title
    .HasLegend = False

    .Axes(xlValue).TickLabels.NumberFormat = "0%"
    .Axes(xlCategory, xlPrimary).HasTitle = False

    'add data table
    .HasDataTable = True

     ' Make Line chart
    .ChartType = xlLine


     ' Add series
    Set srsNew1 = .SeriesCollection.NewSeries
    With srsNew1
        .Values = Ratio
        .XValues = labels
        .Name = "Ratio"
        .Interior.Color = clr3 'RGB(194, 84, 57)
    End With


End With

