Excel实战技巧：基于单元格的值显示相应的图片

Sub InsertPicturesIntoChart()
 Dim i As Integer
 Dim selectedCells As Range
 Dim FilePath As String
 Dim fileExtension As String
 Dim chartName As String
 Dim imageFullName As String
'改变下面的赋值为你实际的值
 FilePath = "C:\Users\excelperfect\Desktop\flags\"
 fileExtension = ".png"
 chartName = "Chart 1"
'在运行宏之前选择具有国家/地区名称的单元格
 For Each selectedCells In Selection
   i = i + 1
  'imageFullName是图像的完整文件路径.
  '在列A中国家名必须与图像名匹配.
  imageFullName = FilePath & Cells(i + 1, 1).Value & fileExtension
  '改变图表系列填充.
  ActiveSheet.ChartObjects(chartName).Chart.SeriesCollection(i).Format.Fill.UserPicture imageFullName
 Next selectedCells
End Sub

Public Function PictureLookupUDF(FilePath As String, Location As Range, Index As Integer)
 Dim lookupPicture As Shape
 Dim sheetName As String
 Dim pictureName As String
 pictureName = "PictureLookupUDF"
 ''Dim picTop As Double
 ''Dim picLeft As Double
 sheetName = Location.Parent.Name
'删除具有相同索引的当前图片（如果存在）
 For Each lookupPicture In Sheets(sheetName).Shapes
   If lookupPicture.Name = pictureName & Index Then
     lookupPicture.Delete
   End If
 Next lookupPicture
'在右边位置添加图片
 Set lookupPicture = Sheets(sheetName).Shapes.AddPicture _
   (FilePath, msoFalse, msoTrue, Location.Left, Location.Top, -1, -1)
'重新调整图片大小最好地适应单元格区域
 If Location.Width / Location.Height > lookupPicture.Width / lookupPicture.Height Then
   lookupPicture.Height = Location.Height
 Else
   lookupPicture.Width = Location.Width
 End If
'改变图片名
 lookupPicture.Name = pictureName & Index
  PictureLookupUDF = "图片查找:" & lookupPicture.Name
End Function