CODEWITHSUNDEEP
vbaexcel
Évariste Galois
Évariste Galois

Reputation: 1033

VBA - Method "add" of Chart objects failed

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

Upvotes: 1

Views: 2607

Answers (1)

Vityata
Vityata

Reputation: 43575

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:

Upvotes: 1

Related Questions