Put images in comments

On the ms_excel yahoo group, someone asked if you can put images in comments (on an Excel worksheet, of course). I went ahead and wrote the following VBA code to do so.

This code takes five parameters:

A string literal representing a single cell range for the comment
Path and filename of the image you want in the comment box
Width and
height of the image
A boolean variable indicating whether you want the comment to remain visible.

Function AddCommentPic(strRange As String, strFilePath As String, _
width As Double, height As Double, IsVisible As Boolean) As Boolean

' adds image to comment box
' by Jimmy Pena, http://www.jpsoftwaretech.com/, 2/13/2009
' strRange = A string literal representing a single cell range for the comment
' strFilePath = Path and filename of the image you want in the comment box
' width, height = Width and height you want for the image (respectively)
' IsVisible = A boolean variable indicating whether you want the comment to remain visible.

Application.ScreenUpdating = False

' check if file exists
If FileLen(strFilePath) = 0 Then
  AddCommentPic = False
  GoTo ExitProc
End If

' make sure height and width are positive
If (width < 1) Or (height < 1) Then
  AddCommentPic = False
  GoTo ExitProc
End If

' check if range is valid
Dim rng As Excel.Range
On Error Resume Next
  Set rng = Range(strRange)
  If Err <> 0 Then
    AddCommentPic = False
    GoTo ExitProc
  End If
On Error GoTo 0

' scale down
height = height / 100
width = width / 100

' if we made it this far, insert the comment and picture
On Error GoTo ErrorHandle
With rng
  .AddComment
  With .Comment
    .Visible = IsVisible
    With .Shape
      .Fill.UserPicture strFilePath
      .ScaleHeight height, msoFalse
      .ScaleWidth width, msoFalse
    End With
  End With
End With

' if we made it this far, assume success
AddCommentPic = True
GoTo ExitProc

ErrorHandle:
AddCommentPic = False

ExitProc:
Set rng = Nothing
Application.ScreenUpdating = True
End Function

The function itself returns true if it runs successfully (i.e. a comment is added to the worksheet with an image in it) and should be called as follows:

Sub test()

Dim filen As String
Dim success As Boolean

filen = "C:\MyFolder\SomePicture.JPG"

success = AddCommentPic("A1", filen, 160, 600, True)
Debug.Print success

End Sub

The height and width are just estimates, if anyone has a better way of calculating the appropriate height and width, I'd be glad to hear it. The ScaleHeight and ScaleWidth properties only take very small numbers indicating how many times larger the comment image size should be.