Option Explicit
Sub testme()

Dim picArray() As String
Dim wks As Worksheet
Dim Piece As Picture
Dim pCtr As Long

Set wks = ActiveSheet

With wks
If .Pictures.Count = 0 Then
MsgBox "no pictures!"
Exit Sub
End If

ReDim picArray(1 To .Pictures.Count)

pCtr = 0
For Each Piece In .Pictures
pCtr = pCtr + 1
picArray(pCtr) = Piece.TopLeftCell.Address(0, 0)
Next Piece
End With
End Sub

Another way is to just loop through the pictures -- and maybe keep track of more
stuff (like the name???):


Option Explicit
Sub testme2()

Dim picArray() As String
Dim wks As Worksheet
Dim pCtr As Long

Set wks = ActiveSheet

With wks
If .Pictures.Count = 0 Then
MsgBox "no pictures!"
Exit Sub
End If

ReDim picArray(1 To 2, 1 To .Pictures.Count)

For pCtr = 1 To .Pictures.Count
picArray(pCtr, 1) = .Pictures(pCtr).Name
picArray(pCtr, 2) = .Pictures(pCtr).TopLeftCell.Address(0, 0)
Next pCtr
End With
End Sub




Jive wrote:

I have a piece of vba code which can be simplified to

Dim Piece As Picture
For Each Piece In ActiveSheet.Pictures
******
******
******
Next Piece

Within the piece iteration I wish to retrieve some information about each
picture and store it as a variable i.e. variable(1.....n) =
Piece(1.....n).topleftcell. I am unsure how to automatically generate
variable sequence to the correct number of variables. i.e. so that it stores
a new variable and value for later use off of each loop.

VBA is amusing, there is always something new to learn and when you do you
only have another two things to learn afther that so please help. thanks in
advance.

--

Dave Peterson