Find if range has pictures [Solved with ongoing improvements?]

I have to find if each cell in a range has a picture in it. I have the script below running in VB and need to figure out how to make it work for a range of cells. Function HASpic(Cell...

excel

Formulas and Functions

mtarler

May 27, 2022

Jagodragon good point. this should address that issue (i.e. search for pictures on the same sheet as the range your select)

Function HASpicR(x As Range) As Variant

'  Yields TRUE if the cell identified by row and col contains a picture,
'  otherwise FALSE
    Application.Volatile
    
   Dim p As Range
   Dim r, c         As Long
    Dim s() As Variant
   
   r = x.Rows.Count
   c = x.Columns.Count
   ReDim s(1 To r, 1 To c)
   
   For Each Pict In x.Parent.Pictures
      Set p = Pict.TopLeftCell
      If Not Intersect(p, x) Is Nothing Then
         s(p.Row - x.Row + 1, p.Column - x.Column + 1) = True
      End If
   Next Pict
   HASpicR = s
   
End Function

mtarler

Silver Contributor

May 24, 2022

It doesn't work because it doesn't loop through the range or because you haven't assigned the T/F to anything? I would have expected something like:
HASpicR = FALSE
For Each Cell In x
HASpicR = HASpicR OR HASpic (Cell))
Next Cell

EDIT: and noticed that HASpicR would also need to be a FUNCTION of boolean type like the original HASpic function

Jagodragon

Iron Contributor

May 24, 2022

This didn't get me there. but thank you. I'm Editing the main post to contain the solution right now.

Forum Discussion

Find if range has pictures [Solved with ongoing improvements?]

Resources