Excel Vba Code to Read Image Size
While information technology is not often that we take to control pictures or images within Excel, based on the number of Google searches for this topic, information technology's clearly something people want to know more than about. This post serves equally a reference to cover most of the scenarios we're probable to encounter to re-create, insert, move, delete and control pictures.
This code tin can be used in conjunction with my post about using a user defined function to insert and change pictures based on a cell value, which can be establish here: https://exceloffthegrid.com/automatically-alter-film/
Download the case file
I recommend you download the example file for this post. Then yous'll be able to piece of work along with examples and see the solution in activeness, plus the file will exist useful for future reference.
Download the file: 0042 VBA copy insert, motility, delete pictures.zero
Adapting the code to your needs
It is unlikely that whatsoever of the codes will meet your exact requirements. Every code snippet uses variables to hold either the image, the worksheet, a range or an object. By irresolute those variables, the lawmaking can easily exist changed and combined with other code snippets to meet your specific requirements.
Insert an epitome into a worksheet
The following code will insert an image into the active cell of the active worksheet, keeping the original prototype's size.
Sub InsertImage() Dim ws As Worksheet Dim imagePath Equally String Dim imgLeft As Double Dim imgTop Equally Double Ready ws = ActiveSheet imagePath = "C:\Users\marks\Documents\myImage.png" imgLeft = ActiveCell.Left imgTop = ActiveCell.Elevation 'Width & Height = -1 means go along original size ws.Shapes.AddPicture _ fileName:=imagePath, _ LinkToFile:=msoFalse, _ SaveWithDocument:=msoTrue, _ Left:=imgLeft, _ Height:=imgTop, _ Width:=-i, _ Height:=-1 End Sub
Depending on our needs, information technology may exist better to create an prototype straight into an object variable. So we can refer to the paradigm by using the variable and exercise not demand to know the proper name of the paradigm. The following code is an case of this technique.
Sub InsertImageToDeclaredVariable() Dim myImage Equally Shape Dim ws As Worksheet Dim imagePath Equally String Dim imgLeft As Double Dim imgTop Equally Double Set ws = ActiveSheet imagePath = "C:\Users\marks\Documents\myImage.png" imgLeft = ActiveCell.Left imgTop = ActiveCell.Peak Set myImage = ws.Shapes.AddPicture( _ Filename:=imagePath, _ LinkToFile:=msoFalse, _ SaveWithDocument:=msoTrue, _ Left:=imgLeft, _ Top:=imgTop, _ Width:=-1, _ Elevation:=-1) 'Use the variable for the created image MsgBox myImage.Proper name Finish Sub
Image names
The code below will display the proper name of the last inserted paradigm.
The bulletin box is to illustrate that the code works. Once we have captured the shape equally an object in the real world, nosotros would perform other actions on the shape.
Sub GetNameOfLastInsertedImage() Dim myImage As Shape Set myImage = ActiveSheet.Shapes(ActiveSheet.Shapes.Count) MsgBox myImage.Name Stop Sub
The lawmaking below renames an existing image.
Sub RenameImage() Dim myImage As Shape Dim ws As Worksheet Set ws = ActiveSheet Gear up myImage = ws.Shapes("Moving-picture show two") myImage.Name = "New Prototype Name" Terminate Sub
Get image properties
The post-obit code demonstrates how to retrieve common image backdrop
Sub GetImageProperties() Dim myImage As Shape Dim ws As Worksheet Fix ws = ActiveSheet Set myImage = ws.Shapes("Picture 1") MsgBox "Top: " & myImage.Top & vbNewLine & _ "Left: " & myImage.Left & vbNewLine & _ "Width: " & myImage.Width & vbNewLine & _ "Peak: " & myImage.Height & vbNewLine & _ "Z-Society: " & myImage.ZOrderPosition & vbNewLine & _ "Name: " & myImage.Name & vbNewLine & _ "Top Left Cell: " & myImage.TopLeftCell & vbNewLine Finish Sub
Delete an image
The following code will delete an image called Film i from the active worksheet.
Sub DeleteImage() Dim myImage As Shape Dim ws Equally Worksheet Prepare ws = ActiveSheet Fix myImage = ws.Shapes("Pic i") myImage.Delete Cease Sub
Make images invisible
Images can exist made invisible. They still be and are role of the workbook, just they are not visible to the user.
Sub MakeImageInvisible() Dim myImage As Shape Dim ws As Worksheet Set ws = ActiveSheet Set myImage = ws.Shapes("Film one") myImage.Visible = msoFalse 'Make the image visible again 'myImage.Visible = msoTrue End Sub
Loop through all images on a worksheet
The following code will loop through all the images on the active sheet.
Sub LoopThroughImagesOnWs() Dim shp As Shape Dim ws As Worksheet Set ws = ActiveSheet For Each shp In ws.Shapes If shp.Type = msoPicture Then 'Do something to the image 'Example, show message box MsgBox shp.Name & " is a moving-picture show" End If Next shp End Sub
Delete an prototype
The code below volition delete a specific named pic.
Sub DeletePicture() Dim myImage As Shape Set myImage = ActiveSheet.Shapes("Picture 1") myImage.Delete End Sub
Confirm if the selected object is a picture
The code below volition check if a specific object is a Picture show.
Sub CheckIfSelectionIsPicture() Dim affair As Object Set matter = Pick If TypeName(thing) = "Picture" And then MsgBox "Selection is a picture" Else MsgBox "Selection is Not a picture show" End If End Sub
Linked pictures
Images can be linked to cells or named ranges. This makes the image dynamic; when the contents of the cells modify, and then does the moving-picture show.
Sub MakeImageLinkedPicture() Dim ws As Worksheet Set ws = ActiveSheet ws.Pictures("Picture 1").Formula = "=A1:D10" End Sub
Image placement and locking options
Image beliefs tin be controlled using the placement option.
Sub ImagePlacementAndLockingOptions() Dim myImage Every bit Shape Dim ws As Worksheet Fix ws = ActiveSheet Set up myImage = ws.Shapes("Picture 1") 'Epitome placement options myImage.Placement = xlFreeFloating 'The other placement options are: 'xlMoveAndSize 'xlMove 'Locking images (forestall editing paradigm when worksheet protected) myImage.Locked = True 'The other placement options are: 'myImage.Locked = False End Sub
Rotate images
The following lawmaking rotates the image by a specific corporeality
Sub RotateImageIncremental() Dim myImage Every bit Shape Dim rotationValue Every bit Integer Prepare myImage = ActiveSheet.Shapes("Picture show ane") rotationValue = 45 'Rotate the image past the amount specified by the rotationValue myImage.IncrementRotation (rotationValue) End Sub
The post-obit code rotates the epitome to a specific amount.
Sub RotateImageAbsolute() Dim myImage As Shape Dim rotationValue As Integer Set up myImage = ActiveSheet.Shapes("Film two") rotationValue = 90 'Rotate the image to the amount specified by the rotationValue myImage.rotation = rotationValue End Sub
Fix image position to the heart of a jail cell
An image is positioned based on the top and left of that image. The following code will set the position and then that it appears centered within a specific cell.
Sub CenterInCell() Dim myImage Equally Shape Dim cellLocation As Range Prepare myImage = ActiveSheet.Shapes("Picture 1") Gear up cellLocation = ActiveSheet.Range("B4") myImage.Top = cellLocation.Top + (cellLocation.Height / two) - (myImage.Height / 2) myImage.Left = cellLocation.Left + (cellLocation.Width / 2) - (myImage.Width / 2) End Sub
Flipping an image horizontally or vertically
Flip the paradigm horizontally:
Sub FlipImageHorizontal() Dim myImage Equally Shape Set myImage = ActiveSheet.Shapes("Pic one") myImage.Flip msoFlipHorizontal End Sub
Flip the image vertically:
Sub FlipImageVertical() Dim myImage Every bit Shape Set myImage = ActiveSheet.Shapes("Picture ane") myImage.Flip msoFlipVertical End Sub
Resize an image
The code below locks the aspect ratio; therefore, resizing the width or height will maintain the image'due south proportions.
Sub ResizeImageLockAspectRatio() Dim myImage As Shape Dim imageWidth Every bit Double Set myImage = ActiveSheet.Shapes("Picture show 1") imageWidth = 100 myImage.LockAspectRatio = msoTrue myImage.Width = imageWidth Cease Sub
When setting the aspect ratio to msoFalse, the height and width operate independently.
Sub ResizeImageHeightOrWidth() Dim myImage As Shape Dim imageWidth Every bit Double Dim imageHeight as Double Fix myImage = ActiveSheet.Shapes("Picture one") imageWidth = 100 imageHeight = fifty myImage.LockAspectRatio = msoFalse myImage.Width = imageWidth myImage.Height = imageHeight Terminate Sub
The following code positions an epitome and stretches information technology to perfectly cover a specified range.
Sub StretchImageToCoverCells() Dim myImage Every bit Shape Dim ws Every bit Worksheet Dim rng As Range Set ws = ActiveSheet Fix myImage = ws.Shapes("Picture 1") Set rng = ws.Range("A2:D10") myImage.LockAspectRatio = msoFalse myImage.Left = rng.Left myImage.Top = rng.Top myImage.Width = rng.Width myImage.Top = rng.Pinnacle Terminate Sub
Cropping
The lawmaking below crops an image based on the distance from the superlative, left, bottom or right.
Sub CropImage() Dim myImage As Shape Fix myImage = ActiveSheet.Shapes("Picture1") myImage.PictureFormat.CropLeft = 50 myImage.PictureFormat.CropTop = 50 myImage.PictureFormat.CropRight = 50 myImage.PictureFormat.CropBottom = 50 Cease Sub
Irresolute Z-Order
The image tin can exist moved forward or backward within the stack of objects (known every bit the Z-Society).
Sub ChangeZOrderRelative() Dim myImage Equally Shape Set myImage = ActiveSheet.Shapes("Picture i") myImage.ZOrder msoBringForward 'Culling send backward 'myImage.ZOrder msoSendBackward End Sub
The Z-Society position cannot exist set straight. Outset, send the image to the back, and then move the epitome forwards with a loop. Continue looping until the image reaches the right Z-Order Position.
Sub ChangeZOrderAbsolute() Dim myImage As Shape Dim imageWidth As Double Dim imageZPosition As Integer Set myImage = ActiveSheet.Shapes("Picture 1") imageZPosition = 3 'Force z-gild to zero and then bring frontward myImage.ZOrder msoSendToBack Do While myImage.zOrderPosition < imageZPosition myImage.ZOrder msoBringForward Loop End Sub
Set the groundwork epitome
The groundwork image appears behind the cells in the spreadsheet.
Sub SetImageBackground() Dim ws As Worksheet Dim imgPath Equally String Ready ws = ActiveSheet imgPath = "C:\Users\marks\Documents\myImage.png" ws.SetBackgroundPicture fileName:=imgPath 'Remove the background prototype 'ws.SetBackgroundPicture fileName:=" End Sub
Save picture from Excel
If we have a motion-picture show in an Excel workbook, there is no straightforward style to save information technology to deejay equally a picture. A mutual workaround is to set the motion-picture show as the background of a nautical chart surface area, and then export the chart as an epitome.
Sub SavePictureFromExcel() Dim myPic As Shape Dim tempChartObj Every bit ChartObject Dim savePath As String Ready myPic = ActiveSheet.Shapes("Picture show ane") Ready tempChartObj = ActiveSheet.ChartObjects.Add together(0, 0, myPic.Width, myPic.Height) savePath = "C:\Users\marks\Downloads\mySavedPic.jpg" 'Copy picture into nautical chart, then export chart myPic.Copy tempChartObj.Chart.ChartArea.Select tempChartObj.Chart.Paste tempChartObj.Chart.Export savePath tempChartObj.Delete Finish Sub
Get our FREE VBA eBook of the 30 well-nigh useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
Claim your free eBook
Don't forget:
If y'all've found this mail useful, or if yous accept a better arroyo, then delight leave a comment below.
Practise you need assist adapting this to your needs?
I'm guessing the examples in this mail service didn't exactly meet your situation. We all apply Excel differently, and so it'southward incommunicable to write a mail service that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) y'all should be able to adapt information technology to your needs.
Simply, if y'all're still struggling y'all should:
- Read other blogs, or watch YouTube videos on the same topic. Yous will benefit much more than by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. Information technology's astonishing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for gratuitous. So accept care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel issues.
What adjacent?
Don't go yet, in that location is plenty more to learn on Excel Off The Grid. Check out the latest posts:
Source: https://exceloffthegrid.com/vba-code-to-insert-move-delete-and-control-pictures/
0 Response to "Excel Vba Code to Read Image Size"
Post a Comment