Converting macro into function

[Closed]
Report
-
Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
-
Hello,

How to change the below macro into a function, so that, we could invoke this macro as =image()

Sub Picture()

Range("A6").Select 'This is where picture will be inserted
Dim picname As String
picname = Range("B6") 'This is the picture name
ActiveSheet.Pictures.Insert("C:\Users\admin\Desktop\New folder\manasa\" & picname & ".jpg").Select 'Path to where pictures are stored
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This resizes the picture
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
With Selection
.Left = Range("A6").Left
.Top = Range("A6").Top
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = 100#
.ShapeRange.Width = 80#
.ShapeRange.Rotation = 0#
End With

Range("A10").Select
Application.ScreenUpdating = True

Exit Sub

ErrNoPhoto:
MsgBox "Unable to Find Photo" 'Shows message box if picture not found
Exit Sub
Range("B20").Select
End Sub

1 reply

Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
484
Hi Ram,

Here you go, a User Defined Function based on your macro.

The picture will appear in the cell where the formula =Image() is placed based on the picture name in B6.

Function Image()
Dim picname As String
picname = Range("B6") 'This is the picture name
ActiveSheet.Pictures.Insert("C:\Users\admin\Desktop\New folder\manasa\" & picname & ".jpg").Select 'Path to where pictures are stored
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This resizes the picture
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
With Selection
    .Left = .Left
    .Top = .Top
    .ShapeRange.LockAspectRatio = msoFalse
    .ShapeRange.Height = 100#
    .ShapeRange.Width = 80#
    .ShapeRange.Rotation = 0#
End With
End Function

Best regards,
Trowa