Create N variables

[Jive]

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]

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]

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

 

[Peter T]

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

 

[Dave Peterson]

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]

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]

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

 

[Peter T]

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

 

[Jive]

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.