Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
redimensioning arrays in subroutines
Th ehelp file tells me this can't be done but before I read that I had been
doing it successfully - some of the time. Are there any rules about when you can and can't pass an array to a subroutine, change it's dimension (probably with PRESERVE but not necessarily) and using the modified array after the subroutine. -- Les Gordon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
redimensioning arrays in subroutines
To the best of my knowledge, you can ReDim a passed (normal?) variable with
no problem (although if you were to do it within a loop that was iterating the array and the called routine resized the array downward then I guess there might be a problem). Where in the help files does it tell you it can't be done? Rick "Les Gordon" wrote in message ... Th ehelp file tells me this can't be done but before I read that I had been doing it successfully - some of the time. Are there any rules about when you can and can't pass an array to a subroutine, change it's dimension (probably with PRESERVE but not necessarily) and using the modified array after the subroutine. -- Les Gordon |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
redimensioning arrays in subroutines
The last paragraph of the following extract from the help on the REDIM
sTatement Remarks The ReDim statement is used to size or resize a dynamic array that has already been formally declared using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts). You can use the ReDim statement repeatedly to change the number of elements and dimensions in an array. However, you can't declare an array of one data type and later use ReDim to change the array to another data type, unless the array is contained in a Variant. If the array is contained in a Variant, the type of the elements can be changed using an As type clause, unless youre using the Preserve keyword, in which case, no changes of data type are permitted. If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array. The following example shows how you can increase the size of the last dimension of a dynamic array without erasing any existing data contained in the array. ReDim X(10, 10, 10) .. . . ReDim Preserve X(10, 10, 15) Similarly, when you use Preserve, you can change the size of the array only by changing the upper bound; changing the lower bound causes an error. If you make an array smaller than it was, data in the eliminated elements will be lost. If you pass an array to a procedure by reference, you can't redimension the array within the procedure. I take it I have to pass it by reference if I want to change the values (and size). Regards -- Les Gordon "Rick Rothstein (MVP - VB)" wrote: To the best of my knowledge, you can ReDim a passed (normal?) variable with no problem (although if you were to do it within a loop that was iterating the array and the called routine resized the array downward then I guess there might be a problem). Where in the help files does it tell you it can't be done? Rick "Les Gordon" wrote in message ... Th ehelp file tells me this can't be done but before I read that I had been doing it successfully - some of the time. Are there any rules about when you can and can't pass an array to a subroutine, change it's dimension (probably with PRESERVE but not necessarily) and using the modified array after the subroutine. -- Les Gordon |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
redimensioning arrays in subroutines
That's a curious statement... and to the best of my knowledge, not true.
First of all, as far as I know, the only way an array gets passed to a subroutine or function is ByRef, so the implied other choice (ByVal) will never come into play at all. Here is a quick routine I dummied up to ReDim Preserve an array in a subroutine and it works fine... Sub ChangeArray(ByRef ArrayIn() As Long) ReDim Preserve ArrayIn(0 To UBound(ArrayIn) / 2) End Sub Sub Test() Dim X As Long Dim MyArray() As Long ReDim MyArray(0 To 5) For X = 0 To 5 MyArray(X) = X Next Call ChangeArray(MyArray) For X = 0 To UBound(MyArray) Debug.Print MyArray(X) Next End Sub Rick "Les Gordon" wrote in message ... The last paragraph of the following extract from the help on the REDIM sTatement Remarks The ReDim statement is used to size or resize a dynamic array that has already been formally declared using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts). You can use the ReDim statement repeatedly to change the number of elements and dimensions in an array. However, you can't declare an array of one data type and later use ReDim to change the array to another data type, unless the array is contained in a Variant. If the array is contained in a Variant, the type of the elements can be changed using an As type clause, unless youre using the Preserve keyword, in which case, no changes of data type are permitted. If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array. The following example shows how you can increase the size of the last dimension of a dynamic array without erasing any existing data contained in the array. ReDim X(10, 10, 10) . . . ReDim Preserve X(10, 10, 15) Similarly, when you use Preserve, you can change the size of the array only by changing the upper bound; changing the lower bound causes an error. If you make an array smaller than it was, data in the eliminated elements will be lost. If you pass an array to a procedure by reference, you can't redimension the array within the procedure. I take it I have to pass it by reference if I want to change the values (and size). Regards -- Les Gordon "Rick Rothstein (MVP - VB)" wrote: To the best of my knowledge, you can ReDim a passed (normal?) variable with no problem (although if you were to do it within a loop that was iterating the array and the called routine resized the array downward then I guess there might be a problem). Where in the help files does it tell you it can't be done? Rick "Les Gordon" wrote in message ... Th ehelp file tells me this can't be done but before I read that I had been doing it successfully - some of the time. Are there any rules about when you can and can't pass an array to a subroutine, change it's dimension (probably with PRESERVE but not necessarily) and using the modified array after the subroutine. -- Les Gordon |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
redimensioning arrays in subroutines
Thanks for that; I did a similar thing and it also worked. I'll have to go
back to my more complicated example and find out why it fails. -- Les Gordon "Rick Rothstein (MVP - VB)" wrote: That's a curious statement... and to the best of my knowledge, not true. First of all, as far as I know, the only way an array gets passed to a subroutine or function is ByRef, so the implied other choice (ByVal) will never come into play at all. Here is a quick routine I dummied up to ReDim Preserve an array in a subroutine and it works fine... Sub ChangeArray(ByRef ArrayIn() As Long) ReDim Preserve ArrayIn(0 To UBound(ArrayIn) / 2) End Sub Sub Test() Dim X As Long Dim MyArray() As Long ReDim MyArray(0 To 5) For X = 0 To 5 MyArray(X) = X Next Call ChangeArray(MyArray) For X = 0 To UBound(MyArray) Debug.Print MyArray(X) Next End Sub Rick "Les Gordon" wrote in message ... The last paragraph of the following extract from the help on the REDIM sTatement Remarks The ReDim statement is used to size or resize a dynamic array that has already been formally declared using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts). You can use the ReDim statement repeatedly to change the number of elements and dimensions in an array. However, you can't declare an array of one data type and later use ReDim to change the array to another data type, unless the array is contained in a Variant. If the array is contained in a Variant, the type of the elements can be changed using an As type clause, unless youre using the Preserve keyword, in which case, no changes of data type are permitted. If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array. The following example shows how you can increase the size of the last dimension of a dynamic array without erasing any existing data contained in the array. ReDim X(10, 10, 10) . . . ReDim Preserve X(10, 10, 15) Similarly, when you use Preserve, you can change the size of the array only by changing the upper bound; changing the lower bound causes an error. If you make an array smaller than it was, data in the eliminated elements will be lost. If you pass an array to a procedure by reference, you can't redimension the array within the procedure. I take it I have to pass it by reference if I want to change the values (and size). Regards -- Les Gordon "Rick Rothstein (MVP - VB)" wrote: To the best of my knowledge, you can ReDim a passed (normal?) variable with no problem (although if you were to do it within a loop that was iterating the array and the called routine resized the array downward then I guess there might be a problem). Where in the help files does it tell you it can't be done? Rick "Les Gordon" wrote in message ... Th ehelp file tells me this can't be done but before I read that I had been doing it successfully - some of the time. Are there any rules about when you can and can't pass an array to a subroutine, change it's dimension (probably with PRESERVE but not necessarily) and using the modified array after the subroutine. -- Les Gordon |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
redimensioning arrays in subroutines
Les Gordon wrote:. . .
Similarly, when you use Preserve, you can change the size of the array only by changing the upper bound; changing the lower bound causes an error. . . . Just as an aside, this statement from the Help is not precisely correct. It is correct if the array is originally declared as an array, e.g., Dim x() ReDim x(10,10,10) If, however, it is an array contained within a Variant variable, i.e., Dim x ReDim x(10,10,10) then either or both of the bounds of the last dimension can be changed with ReDim Preserve. Alan Beban |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
redimensioning arrays in subroutines
Thanks Alan
Sadly Rick's example shows otherwise. My testing also showed this and I went away and made a simplified example of my problem which also worked. So it is only my unsimplified version that is being perverse. Under your scenario what do you think should happen if you redim a variant a second time. Regards -- Les Gordon "Alan Beban" wrote: Les Gordon wrote:. . . Similarly, when you use Preserve, you can change the size of the array only by changing the upper bound; changing the lower bound causes an error. . . . Just as an aside, this statement from the Help is not precisely correct. It is correct if the array is originally declared as an array, e.g., Dim x() ReDim x(10,10,10) If, however, it is an array contained within a Variant variable, i.e., Dim x ReDim x(10,10,10) then either or both of the bounds of the last dimension can be changed with ReDim Preserve. Alan Beban |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
redimensioning arrays in subroutines
Les Gordon wrote:
Thanks Alan Sadly Rick's example shows otherwise. ???Rick's example shows what otherwise??? My testing also showed this and I went away and made a simplified example of my problem which also worked. So it is only my unsimplified version that is being perverse. Under your scenario what do you think should happen if you redim a variant a second time. I don't understand the question. Do you mean change the upper bound of the last dimension? The lower bound? Change it up or down? Try to change a dimension other than the last? All while preserving the values? Alan Beban |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
redimensioning arrays in subroutines
Hi Alan
I meant what would happen if you redimensioned a variant as an array of a definite size and then, later, in the programme had to redimension it again? Would it be OK because it was a variant or would the first redim make it an array and therefore the second redim would be starting from an array and wouldn't be allowed. Regards -- Les Gordon "Alan Beban" wrote: Les Gordon wrote: Thanks Alan Sadly Rick's example shows otherwise. ???Rick's example shows what otherwise??? My testing also showed this and I went away and made a simplified example of my problem which also worked. So it is only my unsimplified version that is being perverse. Under your scenario what do you think should happen if you redim a variant a second time. I don't understand the question. Do you mean change the upper bound of the last dimension? The lower bound? Change it up or down? Try to change a dimension other than the last? All while preserving the values? Alan Beban |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
redimensioning arrays in subroutines
Les Gordon wrote:
Hi Alan I meant what would happen if you redimensioned a variant as an array of a definite size and then, later, in the programme had to redimension it again? Would it be OK because it was a variant or would the first redim make it an array and therefore the second redim would be starting from an array and wouldn't be allowed. Regards It would be allowed. Although I find it a bit offputting that after the first ReDim e.g., Dim x Redim x(10,10,10) x in a sense refers both to the Variant variable and to the Variant() array contained within the Variant variable, the Variant variable no longer seems to be accessible. For example, Typename(x) will return Variant() and IsArray(x) will return True, each obviously referring to the array and not to the variable. You might want to set up a simple experiment and see how things are characterized in the Locals Window. E.g., Sub testReDim() Dim x(), y ReDim x(10, 10, 10) ReDim y(10, 10, 10) x(1, 1, 1) = "xok" y(1, 1, 1) = "yok" ReDim Preserve x(10, 10, 1 To 11) ReDim Preserve y(10, 10, 2 To 11) Stop End Sub Alan Beban |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
redimensioning arrays in subroutines
I was wondering what would happen if you declared it as Variant then
redimensioned which would give it a definite array size; the later in the code redimensioned again to a different array size. Would it behave since it started life as a variant; or not behave since it now has a definite size and so act like a declared array. My appologies if you get two answers to this but the first response seems to have disappeared in to the ether. -- Les Gordon "Alan Beban" wrote: Les Gordon wrote: Thanks Alan Sadly Rick's example shows otherwise. ???Rick's example shows what otherwise??? My testing also showed this and I went away and made a simplified example of my problem which also worked. So it is only my unsimplified version that is being perverse. Under your scenario what do you think should happen if you redim a variant a second time. I don't understand the question. Do you mean change the upper bound of the last dimension? The lower bound? Change it up or down? Try to change a dimension other than the last? All while preserving the values? Alan Beban |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
redimensioning arrays in subroutines
Les Gordon wrote:
I was wondering what would happen if you declared it as Variant then redimensioned which would give it a definite array size; the later in the code redimensioned again to a different array size. Would it behave since it started life as a variant; or not behave since it now has a definite size and so act like a declared array. My appologies if you get two answers to this but the first response seems to have disappeared in to the ether. My response was in this thread yesterday, 3/31/08, at 6:26pm. Are you not finding it? It was It would be allowed. Although I find it a bit offputting that after the first ReDim e.g., Dim x Redim x(10,10,10) x in a sense refers both to the Variant variable and to the Variant() array contained within the Variant variable, the Variant variable no longer seems to be accessible. For example, Typename(x) will return Variant() and IsArray(x) will return True, each obviously referring to the array and not to the variable. You might want to set up a simple experiment and see how things are characterized in the Locals Window. E.g., Sub testReDim() Dim x(), y ReDim x(10, 10, 10) ReDim y(10, 10, 10) x(1, 1, 1) = "xok" y(1, 1, 1) = "yok" ReDim Preserve x(10, 10, 1 To 11) ReDim Preserve y(10, 10, 2 To 11) Stop End Sub Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
turn off subroutines | Excel Discussion (Misc queries) | |||
Subscript out range error when redimensioning dynamic array | Excel Programming | |||
Subroutines vs. modules | Excel Programming | |||
common subroutines - add-in | Excel Programming | |||
Run method for subroutines | Excel Programming |