VBA - Method "add" of Chart objects failed

Question

I'm attempting to create a function that will email a portion of a sheet, with the code below:

Function PictureToHTML(wbk, Namesheet, nameRange, imgFile)

    wbk.Activate
    Worksheets(Namesheet).Activate

    nameRange = "C7:C10"

    Set Plage = wbk.Worksheets(Namesheet).Range(nameRange)
    Plage.CopyPicture

    TempFilePath = Environ$("temp") & "\" & imgFile

    Set newchart = wbk.Worksheets(Namesheet).ChartObjects.Add(Plage.Left, Plage.Top, Plage.Width, Plage.Height)

    With newchart
        .Activate
        .Chart.parent.Border.LineStyle = 0
        .Chart.Paste
        .Chart.Export TempFilePath, "PNG"
    End With
    Worksheets(Namesheet).ChartObjects(Worksheets(Namesheet).ChartObjects.Count).Delete
    Set Plage = Nothing

    PictureToHTML = "<br><B>" & Namesheet & ":</B><br>" _
                & "<img src='cid:" & imgFile & "'>"

End Function

I've temporarily hardcoded the range I want to cut (even though it shouldn't be an issue...), and I get the error on this line:

Set newchart = wbk.Worksheets(Namesheet).ChartObjects.Add(Plage.Left, Plage.Top, Plage.Width, Plage.Height)

Full error:

Run-time error'-2147417878 (80010108)': Method 'Add' of object 'ChartObjects' failed

Could anyone tell me where my mistake is? Updated code:

Function PictureToHTML(wbk, Namesheet, nameRange, imgFile)

    Dim WeightsSheet As Worksheet
    Dim newChart As ChartObject
    wbk.Activate

    Set WeightsSheet = wbk.Worksheets(Namesheet)

    Set Plage = wbk.Worksheets(Namesheet).Range(nameRange)
    Plage.CopyPicture

    TempFilePath = Environ$("temp") & "\" & imgFile
    Set newChart = WeightsSheet.ChartObjects.Add(Plage.Left, Plage.Top, Plage.Width, Plage.Height)

    With newChart
        .Activate
        .Chart.parent.Border.LineStyle = 0
        .Chart.Paste
        .Chart.Export TempFilePath, "PNG"
    End With
    WeightsSheet.ChartObjects(WeightsSheet.ChartObjects.Count).Delete
    Set Plage = Nothing

    PictureToHTML = "<br><B>" & Namesheet & ":</B><br>" _
                & "<img src='cid:" & imgFile & "'>"

End Function

What is the exact error you are getting? – Rik Sportel Sep 19 '18 at 15:54 — Rik Sportel, Sep 19 '18 at 15:54
@RikSportel updated the post. – Évariste Galois Sep 19 '18 at 17:37 — Évariste Galois, Sep 19 '18 at 17:37

Vityata · Accepted Answer · 2018-09-20T07:37:08.123

The variables are not explicitly declared. Although it should not be a problem here, as the newChart should be considered Variant, try to write Dim newChart as ChartObject on the line before wbk.Activate.

Then calling separately something as small as this one:

Sub TestMe()
    Dim newChart As ChartObject
    Set newChart = Worksheets(1).ChartObjects.Add(100, 100, 100, 100)
End Sub

And then start adding Plage.Left, Plage.Top, Plage.Width, Plage.Height instead of the hardcoded arguments (100). Then add wbk.Worksheets(Namesheet) as well and see whether it works.

Not declaring variables is really a bad practice. This works, if you declare all. In this specific small example, the problem is the nameRange variable:

Option Explicit

Sub TestMe()
    Debug.Print PictureToHTML(ThisWorkbook, "Sheet1", Range("A1:E20"), "probably.png")
End Sub

Function PictureToHTML(wbk As Workbook, Namesheet As String, _
                        nameRange As Range, imgFile As String) As String

    Dim WeightsSheet As Worksheet
    Dim newChart As ChartObject
    Dim Plage As Range
    Dim tempFilePath As String

    Set WeightsSheet = wbk.Worksheets(Namesheet)

    Set Plage = wbk.Worksheets(Namesheet).Range(nameRange.Address)
    Plage.CopyPicture

    tempFilePath = Environ$("temp") & "\" & imgFile
    Set newChart = WeightsSheet.ChartObjects.Add( _
                        Plage.Left, Plage.Top, Plage.Width, Plage.Height)

    With newChart
        .Chart.Parent.Border.LineStyle = 0
        .Chart.Paste
        .Chart.Export tempFilePath, "PNG"
    End With

    WeightsSheet.ChartObjects(WeightsSheet.ChartObjects.Count).Delete
    PictureToHTML = "<br><B>" & Namesheet & ":</B><br>" & "<img src='cid:" & imgFile & "'>"

End Function

As a next step, consider reading the documentation for Option Explicit:

It seems like `wbk.Worksheets(1)` is the solution... why doesn't my version of this seem to work? My value for namesheet is "Weights", which is a sheet that exists in my workbook. — Évariste Galois, Sep 19 '18 at 17:52
@ÉvaristeGalois - no idea. In general, the code lacks explicit declarations of variables and uses a lot of Active and Select, thus probably the error is somewhere there... See the 2 links in the answer, they are quite useful. — Vityata, Sep 19 '18 at 18:01
I added some updated code where I adhere to the idea of explicitly declaring variables. It seems like I still get the same error, could it be because my workbook uses Activate? How would I replace that in the context of my function? — Évariste Galois, Sep 19 '18 at 21:18
@ÉvaristeGalois - the error is from the range variable `nameRange`. See the code in the answer. — Vityata, Sep 20 '18 at 07:37

VBA - Method "add" of Chart objects failed

1 Answers1