Q：我原先在工作表中放置了一个图片，现在我想使用新的图片来替换该图片，但是要保持图片的位置和大小不变，如何使用VBA代码实现？

A：在VBE中插入一个标准模块，输入如下代码：

SubChangePicLoc()

On Error GoTo ErrHandle

Dim blnFlag As Boolean

Dim shp As Shape

Dim dblTop As Double

Dim dblLeft As Double

Dim dblHeight As Double

Dim dblWidth As Double

Dim FileToOpen As Variant

‘检查所选取的是形状还是单元格

blnFlag = False

For Each shp In ActiveSheet.Shapes

If shp.Name = Selection.Name Then

blnFlag = True

Exit For

Else

blnFlag = False

End If

Next shp

ErrHandle:

On Error GoTo endHandle

If blnFlag = True Then

With Selection.ShapeRange

‘保存所选图片的属性

dblTop = .Top

dblLeft = .Left

dblHeight = .Height

dblWidth = .Width

End With

‘删除所选图片

Selection.Copy

Selection.Delete

‘插入新图片

FileToOpen =Application.GetOpenFilename

If FileToOpen <> False Then

ActiveSheet.Pictures.Insert(FileToOpen).Select

Else

MsgBox “没有选择图片文件!”

ActiveSheet.Paste

End If

‘将原图片属性应用于新图片

With Selection.ShapeRange

.Top = dblTop

.Left = dblLeft

.Height = dblHeight

.Width = dblWidth

End With

Else

MsgBox “请选择图片,不要选择单元格区域.”

End If

endHandle:

End Sub

首先选取工作表中要替换的图片，然后运行代码，效果如下所示。

阅读全文