Behold minimal VBA code that successfully does one instance of using a shape as the marker for an Excel chart series.

Function TestFormatting(Optional ignoredParameter As Variant)
    With ThisWorkbook.Sheets("Sheet1")
        .Shapes("Shape1").Copy
        .ChartObjects("Chart1").Chart.SeriesCollection(1).Paste
    End With
End Function  ' TestFormatting

Alas, this code vandalises the clipboard. Please, do readers know of any way to achieve this task without changing the clipboard? My two candidate solutions have both failed.

1. Not damaging the clipboard by some sort of .Copy Dest:=. Happily that compiles; unhappily does not execute.

2. Some means of, with respect to the clipboard, that which a PostScript programmer might call save … restore. It might be possible to save the clipboard if I knew it contained just text (which I haven’t managed to make work), but anyway the clipboard might contain anything: perhaps small, perhaps big, perhaps not from Excel.

Aside, for those wondering why it is a function. So that the correct formatting of a chart can be part of Excel’s recalc sequence: if the name of a series changes, its formatting would correctly follow. The actual function would take some parameters including ChartObject.Name and Series.Name. The ignoredParameter might be something that would change if the desired formatting were to change, so triggering re-execution.

My target is that it work on most modern(ish) Excel versions, but anyway I’m using Mac Excel 16.43.

CodePudding user response：

Please, try the next ways:

1. Load SVG file from computer path:

Sub ApplySVGPictOnSeriesCollection()
   Dim ch As ChartObject, rng As Range
   
   Set rng = ActiveSheet.Range("I2:J4") 'place here the SourceData
   
   Set ch = ActiveSheet.ChartObjects.Add(left:=1, top:=1, width:=100, height:=100)
   ch.Chart.SetSourceData rng
   
   With ch.Chart.SeriesCollection(1).Fill
        .UserPicture PictureFile:=ThisWorkbook.Path & "\sample_640×426.svg" 'use here your svg file path
        .Visible = True
   End With
End Sub

2. Convert the SVG files to bmp or (jpg, gif, Wmf, emf) then, insert sheet ActiveX Image controls and place the pictures in the above format on them. They can be used as in the next piece of code:

Sub copyPictureToSeriesCollectionNoClipboard()
    Dim s As Shape, ch As ChartObject, rng As Range, img As Image
    
    Set rng = ActiveSheet.Range("I2:J4")
    
    Set s = ActiveSheet.Shapes("Image1") 'an ActiveX Image control shape 
    
    Set img = s.OLEFormat.Object.Object 'image
    stdole.SavePicture img.Picture, ThisWorkbook.Path & "\myPicture123.jpg"
      
   'create a new chart:
   Set ch = ActiveSheet.ChartObjects.Add(left:=1, top:=1, width:=100, height:=100)
   
   ch.Chart.SetSourceData rng
   
   With ch.Chart.SeriesCollection(1).Fill 'place the pictures:
        .UserPicture PictureFile:=ThisWorkbook.Path & "\myPicture123.jpg"
        .Visible = True
   End With
End Sub

Please, send some feedback after testing them.