Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to write Property Let for the single elements of an array
Hi all,
thanks to Bob's and other people's suggestions, I'm now able to assign to all elements of an array property in a class module. But what if I need to assign to a *single* element? It looks like I have to Redim it each time... Example: ' Class Module Option Explicit Option Base 1 Private pYValues() As Double ' not sure if this Class_Initialize Sub makes sense: IMO it would ' in other languages, but maybe in VBA is redundant Private Sub Class_Initialize() ReDim pYValues(1) As Double End Sub Public Property Get YValues() As Double() YValues = pYValues End Property Public Property Let YValues(Values() As Double) pYValues = Values End Property Public Property Get YValue(Index As Long) As Double YValues = pYValues(Index) End Property Public Property Let YValue(Index As Long, Value As Double) ' problem!!! pYValues(Index) = Values End Property The problem is in Property Let YValue: if I add en element to the array, I get a "subscript out of range" error. So I guess I should write it like this: Public Property Let YValue(Index As Long, Value As Double) If UBound(pYValues) < Index Then Redim Preserve pYValues(Index) pYValues(Index) = Values End Property but redimensioning the array each time I add a single element is really slow! Maybe I should write a "Resize" method, something like: Public Sub Resize(Nelements As Long) Redim Preserve pYValues(Nelements) End Sub resize the array before adding each element, and then add. What do you think? Any better ideas? Thanks in advance, Best Regards deltaquattro |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to write Property Let for the single elements of an array
I haven't looked too hard at your code, but here is a thought.
When you initialise the class setup the array big, say 1000 elements. Use a class variable to control the next index and just use this to allocate to the next element. This way, the array will be 1,000 elements in size internally to the class, but to any code using an instance of that class it will look much smaller. When your internal index gets to 1000, bump it up (Redim) by some order of magnitude. This way, you only redim once in a blue moon. -- HTH Bob "deltaquattro" wrote in message ... Hi all, thanks to Bob's and other people's suggestions, I'm now able to assign to all elements of an array property in a class module. But what if I need to assign to a *single* element? It looks like I have to Redim it each time... Example: ' Class Module Option Explicit Option Base 1 Private pYValues() As Double ' not sure if this Class_Initialize Sub makes sense: IMO it would ' in other languages, but maybe in VBA is redundant Private Sub Class_Initialize() ReDim pYValues(1) As Double End Sub Public Property Get YValues() As Double() YValues = pYValues End Property Public Property Let YValues(Values() As Double) pYValues = Values End Property Public Property Get YValue(Index As Long) As Double YValues = pYValues(Index) End Property Public Property Let YValue(Index As Long, Value As Double) ' problem!!! pYValues(Index) = Values End Property The problem is in Property Let YValue: if I add en element to the array, I get a "subscript out of range" error. So I guess I should write it like this: Public Property Let YValue(Index As Long, Value As Double) If UBound(pYValues) < Index Then Redim Preserve pYValues(Index) pYValues(Index) = Values End Property but redimensioning the array each time I add a single element is really slow! Maybe I should write a "Resize" method, something like: Public Sub Resize(Nelements As Long) Redim Preserve pYValues(Nelements) End Sub resize the array before adding each element, and then add. What do you think? Any better ideas? Thanks in advance, Best Regards deltaquattro |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to write Property Let for the single elements of an array
Hi Bob,
so you're suggesting something like this? I omitted all the properties/ methods not directly related to the original question, so as to keep the posted code shorter: ' Class Module Option Explicit Option Base 1 Private pYValues() As Double Private ArraySize As Long Private Sub Class_Initialize() ArraySize = 1000 ReDim pYValues(ArraySize) As Double End Sub Public Property Let YValue(Index As Long, Value As Double) If Index ArraySize Then ArraySize = Index ReDim pYValues(ArraySize) End If pYValues(Index) = Value End Property I'm a bit unsatisfied with the waste of memory: guess it can't be helped, though. Thank you very much for your suggestion, Best Regards deltaquattro On 16 Mar, 14:23, "Bob Phillips" wrote: I haven't looked too hard at your code, but here is a thought. When you initialise the class setup the array big, say 1000 elements. Use a class variable to control the next index and just use this to allocate to the next element. This way, the array will be 1,000 elements in size internally to the class, but to any code using an instance of that class it will look much smaller. When your internal index gets to 1000, bump it up (Redim) by some order of magnitude. This way, you only redim once in a blue moon. -- HTH Bob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to write Property Let for the single elements of an array
A bit more than that, you need to keep an index (a pseudo-upper bound for
the array) as well as the array size, an increment, and load it all '------ Class Module Option Explicit Option Base 1 Private pYValues() As Double Private ArrayIndex As Long Private ArraySize As Long Private ArrayInc As Long Private Sub Class_Initialize() ArraySize = 10 ArrayInc = 5 ReDim pYValues(1 To ArraySize) As Double End Sub Public Property Get YValues() As Double() YValues = pYValues End Property Public Property Let YValues(Values() As Double) pYValues = Values End Property Public Property Get YValue(Index As Long) As Double YValue = pYValues(Index) End Property Public Property Let YValue(Index As Long, Value As Double) If Index ArrayIndex Then ArrayIndex = ArrayIndex + 1 If ArrayIndex ArraySize Then ArraySize = ArraySize + ArrayInc ReDim Preserve pYValues(1 To ArraySize) End If End If pYValues(Index) = Value End Property and use like so Sub testclass() Dim cls As Class1 Dim i As Long Set cls = New Class1 For i = 1 To 12 cls.YValue(i) = i Next i For i = 1 To 12 Debug.Print cls.YValue(i) Next i cls.YValue(11) = 22 For i = 1 To 12 Debug.Print cls.YValue(i) Next i End Sub Quite honestly, 1000 doubles in a modern machine is not going to affect anything. -- HTH Bob "deltaquattro" wrote in message ... Hi Bob, so you're suggesting something like this? I omitted all the properties/ methods not directly related to the original question, so as to keep the posted code shorter: ' Class Module Option Explicit Option Base 1 Private pYValues() As Double Private ArraySize As Long Private Sub Class_Initialize() ArraySize = 1000 ReDim pYValues(ArraySize) As Double End Sub Public Property Let YValue(Index As Long, Value As Double) If Index ArraySize Then ArraySize = Index ReDim pYValues(ArraySize) End If pYValues(Index) = Value End Property I'm a bit unsatisfied with the waste of memory: guess it can't be helped, though. Thank you very much for your suggestion, Best Regards deltaquattro On 16 Mar, 14:23, "Bob Phillips" wrote: I haven't looked too hard at your code, but here is a thought. When you initialise the class setup the array big, say 1000 elements. Use a class variable to control the next index and just use this to allocate to the next element. This way, the array will be 1,000 elements in size internally to the class, but to any code using an instance of that class it will look much smaller. When your internal index gets to 1000, bump it up (Redim) by some order of magnitude. This way, you only redim once in a blue moon. -- HTH Bob |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to write Property Let for the single elements of an array
Hi Bob!
Thanks very much for your suggestion, brilliant as always. I understand that my concern may be exaggerated for a modern PC, but in my class I have also some matrices (2D arrays), and in that case maybe the memory problem could be more relevant. Anyway, your code enables me to handle both cases very easily: clearly, for matrices I have to be sure that I Redim Preserve only the last dimension, but other than that, the code is mainly the same and that's very useful for me. Thanks again, Best Regards deltaquattro On 16 Mar, 15:52, "Bob Phillips" wrote: A bit more than that, you need to keep an index (a pseudo-upper bound for the array) as well as the array size, an increment, and load it all '------ Class Module Option Explicit Option Base 1 Private pYValues() As Double Private ArrayIndex As Long Private ArraySize As Long Private ArrayInc As Long Private Sub Class_Initialize() * * ArraySize = 10 * * ArrayInc = 5 * * ReDim pYValues(1 To ArraySize) As Double End Sub Public Property Get YValues() As Double() * * YValues = pYValues End Property Public Property Let YValues(Values() As Double) * * pYValues = Values End Property Public Property Get YValue(Index As Long) As Double * * YValue = pYValues(Index) End Property Public Property Let YValue(Index As Long, Value As Double) * * If Index ArrayIndex Then * * * * ArrayIndex = ArrayIndex + 1 * * * * If ArrayIndex ArraySize Then * * * * * * ArraySize = ArraySize + ArrayInc * * * * * * ReDim Preserve pYValues(1 To ArraySize) * * * * End If * * End If * * pYValues(Index) = Value End Property and use like so Sub testclass() Dim cls As Class1 Dim i As Long * * Set cls = New Class1 * * For i = 1 To 12 * * * * cls.YValue(i) = i * * Next i * * For i = 1 To 12 * * * * Debug.Print cls.YValue(i) * * Next i * * cls.YValue(11) = 22 * * For i = 1 To 12 * * * * Debug.Print cls.YValue(i) * * Next i End Sub Quite honestly, 1000 doubles in a modern machine is not going to affect anything. -- HTH Bob "deltaquattro" wrote in message ... Hi Bob, so you're suggesting something like this? I omitted all the properties/ methods not directly related to the original question, so as to keep the posted code shorter: ' Class Module Option Explicit Option Base 1 Private pYValues() As Double Private ArraySize As Long Private Sub Class_Initialize() * *ArraySize = 1000 * *ReDim pYValues(ArraySize) As Double End Sub Public Property Let YValue(Index As Long, Value As Double) * *If Index ArraySize Then * * * *ArraySize = Index * * * *ReDim pYValues(ArraySize) * *End If * *pYValues(Index) = Value End Property I'm a bit unsatisfied with the waste of memory: guess it can't be helped, though. Thank you very much for your suggestion, Best Regards deltaquattro On 16 Mar, 14:23, "Bob Phillips" wrote: I haven't looked too hard at your code, but here is a thought. When you initialise the class setup the array big, say 1000 elements. Use a class variable to control the next index and just use this to allocate to the next element. This way, the array will be 1,000 elements in size internally to the class, but to any code using an instance of that class it will look much smaller. When your internal index gets to 1000, bump it up (Redim) by some order of magnitude. This way, you only redim once in a blue moon. -- HTH Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
elements in an array | Excel Programming | |||
elements in an array | Excel Programming | |||
array elements count | Excel Programming | |||
Reference Elements w/in an Array | Excel Discussion (Misc queries) | |||
Shifting Array Elements | Excel Programming |