Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create N variables
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create N variables
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create N variables
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create N variables
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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create N variables
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create N variables
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" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create N variables
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" wrote in message ... 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" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create N variables
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create N variables
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" wrote in message ... 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a Excel bar chart with 3 variables? | Charts and Charting in Excel | |||
Create possible combinations from three variables | Excel Discussion (Misc queries) | |||
Is it possible to create variables in a loop.. | Excel Programming | |||
Create Message Box containing different variables. | Excel Programming | |||
Create Global Variables in a module | Excel Programming |