ChartObjects.Add slow

[Av8tordude]

When running this code, the code executes very quick on a sheet with (ex. 300 used range). However, if I run this code on sheet with (ex 750 used range) its bogs down at a ChartObjects.Add. Doing some searching, I came across similar issue here... Creating a Chart Is VERY Slow

Only issue is that I'm not using any data. The code only adds a PNG image to the sheet then copies that image to a chart object so I can load it to a userform. Any idea why its executing slow and what I can do to speed up loading the chart object?

Option Explicit

Private Sub UserForm_Activate()
Dim myPicture As String, FName As String
Dim Pict As Object, oChartObj As ChartObject

Set Wks = ActiveSheet
Wks.Unprotect Password:="Password"
Application.ScreenUpdating = False

lbTitle = Range("A" & ActiveCell.Row) & " - " & Range("B" & ActiveCell.Row)
Set Pict = Wks.Pictures.Insert(ActiveCell.Comment.Text)
Pict.Name = "i" & ActiveCell.Value
myPicture = "i" & ActiveCell.Value

With Pict
    .ShapeRange.LockAspectRatio = msoTrue
    .ShapeRange.Width = 500
    .ShapeRange.Height = 500
End With

Set oChartObj = Wks.ChartObjects.Add(Top:=0, Left:=0, Width:=805, Height:=417)

With Wks
    .Shapes(myPicture).Copy

    With oChartObj.Chart
        .ChartArea.Select
        .Paste
    End With
    
    FName = Environ("temp") & "\temp.gif"
    .ChartObjects(5).Chart.Export FName
    Image2.Picture = LoadPicture(FName)
    oChartObj.Delete
    .Shapes(myPicture).Delete
    Kill FName
End With

Wks.Protect Password:="Password"
Application.ScreenUpdating = True
End Sub

 

[rlv01]

1. There is a lot of good advice at that link you posted. Did you try all the suggestions?

Only issue is that I'm not using any data. The code only adds a PNG image to the sheet then copies that image to a chart object so I can load it to a userform.

2. If you are not using the chart object as a chart, but merely as a mechanism for transfering a graphic image to a userform, why not forget the chart object and just directly load the image to the userform?

 

[Av8tordude]

1. There is a lot of good advice at that link you posted. Did you try all the suggestions?

Their suggestion did not work for me.

2. If you are not using the chart object as a chart, but merely as a mechanism for transfering a graphic image to a userform, why not forget the chart object and just directly load the image to the userform?

The image I'm using is a PNG. To insert a image to a userform, it must be a GIF. However, the image is of low quality compared to PNG. Hence the reason why I've using this coding method. That being said, I may have missed it, but did you have any suggestion as to how I could resolve this issue to my original question?

 

[rlv01]

The image I'm using is a PNG. To insert a image to a userform, it must be a GIF.

Technically not true. The image control or even the userform picture property can support many more image formats besides .gif. You could use a .jpg. for example.

Office Automation Ltd. - Stephen Bullen's Excel Page

Stephen Bullen's Excel Page, containing many tools and examples for Microsoft Excel.

www.oaltd.co.uk

That being said, I may have missed it, but did you have any suggestion as to how I could resolve this issue to my original question?

If you are determined to use the ChartObject, then the only thought I can offer is that if the ChartObject is sensitive the amount of data on your worksheet, and your code only adds a PNG image to the sheet then copies that image to a chart object so you can load it to a userform, why are you doing it on a worksheet with so much data? Instead maybe create a blank worksheet with no data at all, and do the chart add there.

 

[rlv01]

If I were doing this for myself, I would probably try to adapt Stephen Bullen's 'PastePicture' code to convert the .png to a .jpg that could be easily imported into an Image control on my userform.

Office Automation Ltd. - Stephen Bullen's Excel Page

Stephen Bullen's Excel Page, containing many tools and examples for Microsoft Excel.

www.oaltd.co.uk

 

[Av8tordude]

If you are determined to use the ChartObject, then the only thought I can offer is that if the ChartObject is sensitive the amount of data on your worksheet, and your code only adds a PNG image to the sheet then copies that image to a chart object so you can load it to a userform, why are you doing it on a worksheet with so much data? Instead maybe create a blank worksheet with no data at all, and do the chart add there.

This may be an idea I can use. Thank you for the suggestion. Greatly appreciated Will let you know how it works