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.

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

Thanks Dave

I cant test it until tomorrow but i think i understand how the variables are
assembled. In your second option should the array be

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

or should the order change as you have listed it? if so can you explain why
or maybe it doesnt matter which way around?

Regards

Antony

"Dave Peterson" wrote:

> 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
>

Why do that! You've always got the latest up to date properties of all
pictures in the collection "mySheet.Pictures". The only reason to assign
their properties elsewhere would be to keep a record of what they were
before changing them - is that the objective?

Regards,
Peter T




"Jive" <(E-Mail Removed)> wrote in message
news:302454DE-4FFC-4D4D-958B-(E-Mail Removed)...
>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.

You're right.

This code would work:

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

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

But so would this code:

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

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

The difference would be how those arrays are structured.

The top one is equivalent to x Rows by 2 columns.
The bottom one is equivalent to 2 rows by x columns.

And as long as you use them correctly <vbg>, it wouldn't matter which one you
used!


Jive wrote:
>
> Thanks Dave
>
> I cant test it until tomorrow but i think i understand how the variables are
> assembled. In your second option should the array be
>
> ReDim picArray(1 To .Pictures.Count, 1 To 2)
>
> or should the order change as you have listed it? if so can you explain why
> or maybe it doesnt matter which way around?
>
> Regards
>
> Antony
>
> "Dave Peterson" wrote:
>
> > 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
> >

--

Dave Peterson

Yes that is the objective. I'm looking to construct a record of what
pictures have been moved and where.

I was planning to look for changes in the vlaues and then list out the
sequence of moves.

Are you asking as you can suggest a better approach?

"Peter T" wrote:

> Why do that! You've always got the latest up to date properties of all
> pictures in the collection "mySheet.Pictures". The only reason to assign
> their properties elsewhere would be to keep a record of what they were
> before changing them - is that the objective?
>
> Regards,
> Peter T
>
>
>
>
> "Jive" <(E-Mail Removed)> wrote in message
> news:302454DE-4FFC-4D4D-958B-(E-Mail Removed)...
> >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.
>
>
>

OK if it's to keep a record of picture properties "were" indeed you are
going the right way and use Dave's examples. I had wrongly speculated the
intension was to use the properties immediately.

Only for "amusement", as you had put it that way, here's another approach
(not better just different)

Private Type tPicProps
Name As String
Left As Double
Top As Double
rTLcell As Range
End Type

Private arrPicProps() As tPicProps ' at module level for later recall

Sub LetPicProps()
Dim i As Long, cntPics As Long
Dim pic As Picture

cntPics = ActiveSheet.Pictures.Count
If cntPics = 0 Then
Exit Sub
End If

ReDim arrPicProps(1 To cntPics)
For i = 1 To cntPics
Set pic = ActiveSheet.Pictures(i)
With arrPicProps(i)
Set .rTLcell = pic.TopLeftCell
.Left = pic.Left
.Top = pic.Top
.Name = pic.Name
End With
Next
End Sub

Sub GetOldPicProps()

For i = 1 To UBound(arrPicProps)
With arrPicProps(i)
Debug.Print .Name, .rTLcell.Address, .Left, .Top
End With
Next

End Sub

Sub ErasePicProps() ' call this when done
Erase arrPicProps
End Sub

' note the rTLcell range-object is not really a "record", it could have been
be cut and moved in the interim. Just to illustrate how you can use a "Type"

Regards,
Peter T


"Jive" <(E-Mail Removed)> wrote in message
news7CC526A-7D7A-4D7B-BCE7-(E-Mail Removed)...
> Yes that is the objective. I'm looking to construct a record of what
> pictures have been moved and where.
>
> I was planning to look for changes in the vlaues and then list out the
> sequence of moves.
>
> Are you asking as you can suggest a better approach?
>
> "Peter T" wrote:
>
>> Why do that! You've always got the latest up to date properties of all
>> pictures in the collection "mySheet.Pictures". The only reason to assign
>> their properties elsewhere would be to keep a record of what they were
>> before changing them - is that the objective?
>>
>> Regards,
>> Peter T
>>
>>
>>
>>
>> "Jive" <(E-Mail Removed)> wrote in message
>> news:302454DE-4FFC-4D4D-958B-(E-Mail Removed)...
>> >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.
>>
>>
>>

typo

> OK if it's to keep a record of picture properties "were" indeed you are
> ...

hopefully this makes more sense -

OK, if it's to keep a record of *what* picture properties "were", indeed you
are ...

Peter T

I think I have a new strapline for VB

One problem, infinate solutions.

Some new uses of proceedures in there for me to adapt. Sure they will come
in handy later on. Still cant decide if i should get some formal training in
visual basic or just keep learning as i go.

"Peter T" wrote:

> OK if it's to keep a record of picture properties "were" indeed you are
> going the right way and use Dave's examples. I had wrongly speculated the
> intension was to use the properties immediately.
>
> Only for "amusement", as you had put it that way, here's another approach
> (not better just different)
>
> Private Type tPicProps
> Name As String
> Left As Double
> Top As Double
> rTLcell As Range
> End Type
>
> Private arrPicProps() As tPicProps ' at module level for later recall
>
> Sub LetPicProps()
> Dim i As Long, cntPics As Long
> Dim pic As Picture
>
> cntPics = ActiveSheet.Pictures.Count
> If cntPics = 0 Then
> Exit Sub
> End If
>
> ReDim arrPicProps(1 To cntPics)
> For i = 1 To cntPics
> Set pic = ActiveSheet.Pictures(i)
> With arrPicProps(i)
> Set .rTLcell = pic.TopLeftCell
> .Left = pic.Left
> .Top = pic.Top
> .Name = pic.Name
> End With
> Next
> End Sub
>
> Sub GetOldPicProps()
>
> For i = 1 To UBound(arrPicProps)
> With arrPicProps(i)
> Debug.Print .Name, .rTLcell.Address, .Left, .Top
> End With
> Next
>
> End Sub
>
> Sub ErasePicProps() ' call this when done
> Erase arrPicProps
> End Sub
>
> ' note the rTLcell range-object is not really a "record", it could have been
> be cut and moved in the interim. Just to illustrate how you can use a "Type"
>
> Regards,
> Peter T
>
>
> "Jive" <(E-Mail Removed)> wrote in message
> news7CC526A-7D7A-4D7B-BCE7-(E-Mail Removed)...
> > Yes that is the objective. I'm looking to construct a record of what
> > pictures have been moved and where.
> >
> > I was planning to look for changes in the vlaues and then list out the
> > sequence of moves.
> >
> > Are you asking as you can suggest a better approach?
> >
> > "Peter T" wrote:
> >
> >> Why do that! You've always got the latest up to date properties of all
> >> pictures in the collection "mySheet.Pictures". The only reason to assign
> >> their properties elsewhere would be to keep a record of what they were
> >> before changing them - is that the objective?
> >>
> >> Regards,
> >> Peter T
> >>
> >>
> >>
> >>
> >> "Jive" <(E-Mail Removed)> wrote in message
> >> news:302454DE-4FFC-4D4D-958B-(E-Mail Removed)...
> >> >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.
> >>
> >>
> >>
>
>
>