some of the images insert in the right place, but most of the images becomes so small…
- User
some of the images insert in the right place, but most of the images becomes so small and placed in other column. may i know the reason ?
Assistant
Great question — and you're absolutely right to be concerned. When images in Excel VBA appear small, misplaced, or inserted in the wrong column, it's usually due to one or more of the following common issues:
✅ Root Causes of Your Problem
1. Incorrect Width Calculation
In your original code (and even in the fixed version earlier), you're setting:
.Width = targetCell.WidthBut the actual cell width in points is not the same as what Excel's
.ColumnWidthreturns. The.ColumnWidthis measured in characters, not pixels/points. So when you set.Width = targetCell.Width, it's not reliable for precise image placement.➡️ Excel's
.ColumnWidthis not equal to.Width.For example: A column width of 15 might be only ~112.5 points wide depending on font.
2. Image is Inserted Relative to Active Window or Zoom Level
Sometimes, image position is affected by:
- Excel zoom level
- Scroll position
- Active window view
This is especially true if the image is inserted before the target cell is scrolled into view.
3. Image Sizing Happens Before Positioning
When you resize the image after setting its
.Leftand.Top, Excel may not behave predictably if the image is too large or small. Also, the image may be scaled down to fit, but the scaling factor is applied incorrectly.
4. Images Are Inserted at Default Location First
When
Pictures.Insert()is called, Excel briefly places the image at the top-left corner of the sheet before you move it. If this movement is interrupted (e.g., due to screen updating or timing), the image can land in the wrong place.
✅ Recommended Fixes
Here’s an updated and fully working version of your macro that:
- Converts column width to points
- Ensures image size is based on actual cell dimensions
- Sets
.Placement = xlMoveAndSizeafter correct size and location are set - Includes zoom-level-safe behavior
Sub AngelInsertImages_Fixed() Dim ws As Worksheet Dim imgName As String Dim cell As Range Dim pic As Picture Dim imageFolder As String Dim fullImagePath As String Dim extensions As Variant Dim ext As Variant Dim imageFound As Boolean Dim targetCell As Range Dim colWidthPoints As Double Dim rowHeightPoints As Double Dim desiredPicWidth As Double Dim originalWidth As Double Dim originalHeight As Double Set ws = ThisWorkbook.Sheets("Acc List") imageFolder = "\\Apac\hkdl\data\Merchandise\Common\Internal Share\Product Photo\Pic with white background\Accessories\" extensions = Array(".jfif", ".jpg", ".png") Application.ScreenUpdating = False ' Convert column width from characters to points With ws.Columns("F") .ColumnWidth = 15 colWidthPoints = .ColumnWidth * 7.5 ' Approx. conversion factor (depends on default font) End With ' Loop through each product name in column G For Each cell In ws.Range("G2:G" & ws.Cells(ws.Rows.Count, "G").End(xlUp).Row) imgName = Trim(cell.Value) imageFound = False ' Search for matching image file For Each ext In extensions fullImagePath = imageFolder & imgName & ext If Dir(fullImagePath) <> "" Then imageFound = True Exit For End If Next ext If imageFound Then Set targetCell = ws.Cells(cell.Row, "F") ' Insert the image temporarily Set pic = ws.Pictures.Insert(fullImagePath) ' Get original dimensions originalWidth = pic.Width originalHeight = pic.Height ' Calculate new width (based on column width in points) desiredPicWidth = colWidthPoints - 4 ' Add small padding ' Resize proportionally With pic .Width = desiredPicWidth .Height = originalHeight * (desiredPicWidth / originalWidth) ' Reposition the image exactly over the target cell .Left = targetCell.Left + 2 .Top = targetCell.Top + 2 ' Ensure image stays with the cell .Placement = xlMoveAndSize ' Adjust row height to fit image rowHeightPoints = .Height + 4 If targetCell.RowHeight < rowHeightPoints Then targetCell.RowHeight = rowHeightPoints End If End With Else Debug.Print "Image not found: " & imgName & " in row " & cell.Row End If Next cell Application.ScreenUpdating = True MsgBox "All images processed!", vbInformation End Sub⚠️ Wait! There's a catch with the
ColumnWidth * 7.5formula — it's an approximation and depends on your default font and settings. To make it 100% accurate, we need to calculate column width in points using.Parent.PageSetupor WinAPI, but that's more complex.
✅ Even Better: Use
.Shapes.AddPictureInsteadInstead of
Pictures.Insert, useShapes.AddPicture, which gives you better control and doesn’t flash the image before placing it.Here's a quick version:
Set pic = ws.Shapes.AddPicture( _ Filename:=fullImagePath, _ LinkToFile:=msoFalse, _ SaveWithDocument:=msoTrue, _ Left:=targetCell.Left + 2, _ Top:=targetCell.Top + 2, _ Width:=desiredPicWidth, _ Height:=0) ' Height = 0 means keep aspect ratioThen you don’t need to worry about moving/resizing afterward.
✅ Summary of Fixes
Issue Fix Image too small Use correct column width in points, not .ColumnWidthdirectlyImage misplaced Use .Shapes.AddPictureinstead of.Pictures.InsertAspect ratio broken Calculate new height using originalHeight * (newWidth / originalWidth)Row height too small Auto-adjust row height after inserting image
Would you like me to rewrite your entire macro using
Shapes.AddPicturefor best results?
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.