UserPicture Method dificulties when using optional parameters

[Mihai Dulca]

I am trying to set some additional parameters when filling the 3D columns in a 3D chart in Excel 2010 with JPG images. I can use the full file name (PictureFile parameter), but as soon as I add one of the optional parameters, e.g. PictureFormat the method fails with run-time error 450 (wrong number of arguments or invalid property assignment). Any advice?

With ch3DCS
.ChartType = xl3DColumn

' Generate series from the desired input range
For jSeries = 2 To rgDataIn.Rows.Count
.SeriesCollection.NewSeries ' Create data series
With .SeriesCollection(jSeries - 1) ' Fill in name, x-axis data, series value data and format points
.Name = rgDataIn.Cells(jSeries, 1)
.Values = Range(rgDataIn.Cells(jSeries, 2), rgDataIn.Cells(jSeries, rgDataIn.Columns.Count))
.XValues = Range(rgDataIn.Cells(1, 2), rgDataIn.Cells(1, rgDataIn.Columns.Count))
End With ' Series
Next jSeries

' Fill columns with the file content
For jSeries = 2 To rgDataIn.Rows.Count
With .SeriesCollection(jSeries - 1) ' Fill in name, x-axis data, series value data and format points
Select Case jSeries Mod 2
Case 0
stgFileName = stgRoot & "0.jpg"
Case 1
stgFileName = stgRoot & "1.jpg"
End Select
.Format.Fill.Visible = msoTrue
.Format.Fill.UserPicture stgFileName, xlStretch, , xlFrontSides
End With ' Series
Next jSeries

End With

I also tried to specify the parameters e.g. PictureFormat: = xlStretch, but then I got run-time error 448 (named argument not found). Using the value of the xl constant, 5, instead of the name did not change anything.

 

[SirJB7]

Hi, Mihai Dulca!

Had looked at the built-in or online VBA help you should have noticed that the UserPicture Method has 2 syntax:

1) FillFormat.UserPicture Method (Excel)
Fills the specified shape with an image.
expression .UserPicture(PictureFile)
expression A variable that represents a FillFormat object.

2) UserPicture Method: Fills the specified shape with an image.
expression.UserPicture(PictureFile, PictureFormat, PictureStackUnit, PicturePlacement)
expression Required. An expression that returns one of the objects in the Applies To list.
Example;

With myChart.ChartArea.Fill
.UserPicture PictureFile:="C:\My Documents\brick.bmp"
.Visible = True
End With

Even if both methods are different there's only a posted example for the last one, but curiously it only uses the 1st parameter, like the first one. A bit disappointing, but there's nothing more from the official sources.

So my suggestion is to use the built-in macro recorder to get the code that Excel VBA should create properly, which it's supposed to be error free, and then analyze it and check what was going wrong with your original intents.

Regards!

 

[Mihai Dulca]

Thanks for you prompt reply, SirJB87. Unfortunately, I went down the macro recording way even before asking for community help, but I missed to mention about it. Recording the macro would add only the required PictureFile parameter, but neither of the optional parameters. So, no success on this path either. Keep me posted if you find other ways to resolve this. I will keep looking as well.

 

[SirJB7]

Hi, Mihai Dulca!
If I were you I'd try to fill any possible shape using the macro recorder, simple shapes, charts, wherever Excel lets fill a shape with picture, with the hope that the reluctant arguments arise.
Sorry for not being of much help and of course if I find it out I'll post it here. So do you.
Regards!