Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
array dimensioning
In C++ one can expand or decrease the dimension an array. Can the same thing
be done in VBA? -- Thanks for your help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
array dimensioning
If you have a dynamic array (one in which the bounds are not specified in
the Dim statement), you can change the number of elements in the array with the ReDim statement. Dim Arr() As Long ReDim Arr(0 To 9) You can ReDim the array as often as you want. ReDim will cause the existing contents of the array to be lost unless you use the Preserve modifier. ReDim Preserve Arr(0 To 11) You can change the number of dimensions of a dynamic array if you first Erase the array. Dim Arr() As Long ReDim Arr(0 To 9) ' populate the array and do something Erase Arr ReDim Preserve Arr(0 To 9, 0 To 3) None of this will work if the array is statically declared by specifying the bounds in the Dim declaration. Dim Arr(0 To 9) As Long ReDim Arr(0 To 11) '<<<<< COMPILER ERROR -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "LesHurley" wrote in message ... In C++ one can expand or decrease the dimension an array. Can the same thing be done in VBA? -- Thanks for your help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
array dimensioning
Ok chip, but if you ReDim it again the origional contents of the array are
set to zeros. I need to increase the size of the array to make room for more data without losing the old data. for example, in a quick sort routine there is no way to know how much space will be needed for each section of the data. -- Thanks for your help "Chip Pearson" wrote: If you have a dynamic array (one in which the bounds are not specified in the Dim statement), you can change the number of elements in the array with the ReDim statement. Dim Arr() As Long ReDim Arr(0 To 9) You can ReDim the array as often as you want. ReDim will cause the existing contents of the array to be lost unless you use the Preserve modifier. ReDim Preserve Arr(0 To 11) You can change the number of dimensions of a dynamic array if you first Erase the array. Dim Arr() As Long ReDim Arr(0 To 9) ' populate the array and do something Erase Arr ReDim Preserve Arr(0 To 9, 0 To 3) None of this will work if the array is statically declared by specifying the bounds in the Dim declaration. Dim Arr(0 To 9) As Long ReDim Arr(0 To 11) '<<<<< COMPILER ERROR -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "LesHurley" wrote in message ... In C++ one can expand or decrease the dimension an array. Can the same thing be done in VBA? -- Thanks for your help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
array dimensioning
Then you missed the part where Chip wrote about the Preserve keyword.
You can ReDim the array as often as you want. ReDim will cause the existing contents of the array to be lost unless you use the Preserve modifier. ReDim Preserve Arr(0 To 11) Rick |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
array dimensioning
Rick Rothstein (MVP - VB) wrote:
Then you missed the part where Chip wrote about the Preserve keyword. You can ReDim the array as often as you want. ReDim will cause the existing contents of the array to be lost unless you use the Preserve modifier. ReDim Preserve Arr(0 To 11) Rick A bit more needs to be said. If Arr is a true variant() type array [e.g., Dim Arr() ReDim Arr(1 to 10, 1 to 2)] then with the Preserve keyword you can change only the upper bound of the last dimension [e.g., Dim Arr() Redim Arr(1 to 10, 1 to 2) ReDim Preserve Arr(1 to 10, 1 to 4)] In this case, if you try to change either bound of the 1st dimension, or the lower bound of the 2nd dimension, you will get a Subscript out of range error. If, however, Arr is a an array contained within a Variant type variable [e.g., Dim Arr Redim Arr(1 to 10, 1 to 2)] then with the Preserve keyword you can change either or both bounds of the last dimension [e.g., Dim Arr Redim Arr(1 to 10, 1 to 2) ReDim Preserve Arr(1 to 10, 0 to 3)] If the functions in the freely downloadable file at http:/home.pacbell.net/beban are available to your workbook, then you can use the ResizeArray function to preserve the values of the array that is passed to it and change the lower and/or upper bounds of any or all of the dimensions of a one-, two- three- or four-dimensional array, or increase (up to 4) or decrease the number of the array's dimensions (whether or not the array is contained within a Variant type variable). Alan Beban |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
array dimensioning
Thanks everyone
-- "Alan Beban" wrote: Rick Rothstein (MVP - VB) wrote: Then you missed the part where Chip wrote about the Preserve keyword. You can ReDim the array as often as you want. ReDim will cause the existing contents of the array to be lost unless you use the Preserve modifier. ReDim Preserve Arr(0 To 11) Rick A bit more needs to be said. If Arr is a true variant() type array [e.g., Dim Arr() ReDim Arr(1 to 10, 1 to 2)] then with the Preserve keyword you can change only the upper bound of the last dimension [e.g., Dim Arr() Redim Arr(1 to 10, 1 to 2) ReDim Preserve Arr(1 to 10, 1 to 4)] In this case, if you try to change either bound of the 1st dimension, or the lower bound of the 2nd dimension, you will get a Subscript out of range error. If, however, Arr is a an array contained within a Variant type variable [e.g., Dim Arr Redim Arr(1 to 10, 1 to 2)] then with the Preserve keyword you can change either or both bounds of the last dimension [e.g., Dim Arr Redim Arr(1 to 10, 1 to 2) ReDim Preserve Arr(1 to 10, 0 to 3)] If the functions in the freely downloadable file at http:/home.pacbell.net/beban are available to your workbook, then you can use the ResizeArray function to preserve the values of the array that is passed to it and change the lower and/or upper bounds of any or all of the dimensions of a one-, two- three- or four-dimensional array, or increase (up to 4) or decrease the number of the array's dimensions (whether or not the array is contained within a Variant type variable). Alan Beban |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
array dimensioning
Just curiosity - how in C++ do you increase/decrease the dimension of
an array? C++ doesn't even know what an array is, from what I understand. On Mar 15, 12:23*pm, LesHurley wrote: Thanks everyone -- "Alan Beban" wrote: Rick Rothstein (MVP - VB) wrote: Then you missed the part where Chip wrote about the Preserve keyword. You can ReDim the array as often as you want. ReDim will cause the existing contents of the array to be lost unless you use the Preserve modifier. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
array dimensioning
ilia; sory for the long delay in answering your question. In C/C++ you
create dynamic arrays and change their dimensions using the malloc() function to create and expand and the free() function to release previously allocated memory. Such a capability is essential in database programs where arrays of large user defined class objects are the normal. Ordinarily no one knows in advance how many iems the array will hold or how much memory will be required, thus the need for dynamic allocation. It's been 5 or 6 years since I did any C++ programming and I had to go back and look it up. HTH. -- Thanks for your help "ilia" wrote: Just curiosity - how in C++ do you increase/decrease the dimension of an array? C++ doesn't even know what an array is, from what I understand. On Mar 15, 12:23 pm, LesHurley wrote: Thanks everyone -- "Alan Beban" wrote: Rick Rothstein (MVP - VB) wrote: Then you missed the part where Chip wrote about the Preserve keyword. You can ReDim the array as often as you want. ReDim will cause the existing contents of the array to be lost unless you use the Preserve modifier. ReDim Preserve Arr(0 To 11) Rick A bit more needs to be said. If Arr is a true variant() type array [e.g., Dim Arr() ReDim Arr(1 to 10, 1 to 2)] then with the Preserve keyword you can change only the upper bound of the last dimension [e.g., Dim Arr() Redim Arr(1 to 10, 1 to 2) ReDim Preserve Arr(1 to 10, 1 to 4)] In this case, if you try to change either bound of the 1st dimension, or the lower bound of the 2nd dimension, you will get a Subscript out of range error. If, however, Arr is a an array contained within a Variant type variable [e.g., Dim Arr Redim Arr(1 to 10, 1 to 2)] then with the Preserve keyword you can change either or both bounds of the last dimension [e.g., Dim Arr Redim Arr(1 to 10, 1 to 2) ReDim Preserve Arr(1 to 10, 0 to 3)] If the functions in the freely downloadable file at http:/home.pacbell.net/beban are available to your workbook, then you can use the ResizeArray function to preserve the values of the array that is passed to it and change the lower and/or upper bounds of any or all of the dimensions of a one-, two- three- or four-dimensional array, or increase (up to 4) or decrease the number of the array's dimensions (whether or not the array is contained within a Variant type variable). Alan Beban |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
array dimensioning
Just FYI...
VB/VBA doesn't use the same type arrays as does native C/C++. Instead, it uses SAFEARRAYs (available in C/C++ with MFC or various APIs). -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "LesHurley" wrote in message ... ilia; sory for the long delay in answering your question. In C/C++ you create dynamic arrays and change their dimensions using the malloc() function to create and expand and the free() function to release previously allocated memory. Such a capability is essential in database programs where arrays of large user defined class objects are the normal. Ordinarily no one knows in advance how many iems the array will hold or how much memory will be required, thus the need for dynamic allocation. It's been 5 or 6 years since I did any C++ programming and I had to go back and look it up. HTH. -- Thanks for your help "ilia" wrote: Just curiosity - how in C++ do you increase/decrease the dimension of an array? C++ doesn't even know what an array is, from what I understand. On Mar 15, 12:23 pm, LesHurley wrote: Thanks everyone -- "Alan Beban" wrote: Rick Rothstein (MVP - VB) wrote: Then you missed the part where Chip wrote about the Preserve keyword. You can ReDim the array as often as you want. ReDim will cause the existing contents of the array to be lost unless you use the Preserve modifier. ReDim Preserve Arr(0 To 11) Rick A bit more needs to be said. If Arr is a true variant() type array [e.g., Dim Arr() ReDim Arr(1 to 10, 1 to 2)] then with the Preserve keyword you can change only the upper bound of the last dimension [e.g., Dim Arr() Redim Arr(1 to 10, 1 to 2) ReDim Preserve Arr(1 to 10, 1 to 4)] In this case, if you try to change either bound of the 1st dimension, or the lower bound of the 2nd dimension, you will get a Subscript out of range error. If, however, Arr is a an array contained within a Variant type variable [e.g., Dim Arr Redim Arr(1 to 10, 1 to 2)] then with the Preserve keyword you can change either or both bounds of the last dimension [e.g., Dim Arr Redim Arr(1 to 10, 1 to 2) ReDim Preserve Arr(1 to 10, 0 to 3)] If the functions in the freely downloadable file at http:/home.pacbell.net/beban are available to your workbook, then you can use the ResizeArray function to preserve the values of the array that is passed to it and change the lower and/or upper bounds of any or all of the dimensions of a one-, two- three- or four-dimensional array, or increase (up to 4) or decrease the number of the array's dimensions (whether or not the array is contained within a Variant type variable). Alan Beban |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
array dimensioning
Hello;
I've just reviewed with much interest the replies here and found them to be very useful and related to my recent question. Could someone please review the thread: "Array Declaration Problem" and my latest summary post (yesterday, June 22,'08), and identify where the array declaration went wrong ?? Thank you kindly, will very much appreciate your help. "Chip Pearson" wrote: Just FYI... VB/VBA doesn't use the same type arrays as does native C/C++. Instead, it uses SAFEARRAYs (available in C/C++ with MFC or various APIs). -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "LesHurley" wrote in message ... ilia; sory for the long delay in answering your question. In C/C++ you create dynamic arrays and change their dimensions using the malloc() function to create and expand and the free() function to release previously allocated memory. Such a capability is essential in database programs where arrays of large user defined class objects are the normal. Ordinarily no one knows in advance how many iems the array will hold or how much memory will be required, thus the need for dynamic allocation. It's been 5 or 6 years since I did any C++ programming and I had to go back and look it up. HTH. -- Thanks for your help "ilia" wrote: Just curiosity - how in C++ do you increase/decrease the dimension of an array? C++ doesn't even know what an array is, from what I understand. On Mar 15, 12:23 pm, LesHurley wrote: Thanks everyone -- "Alan Beban" wrote: Rick Rothstein (MVP - VB) wrote: Then you missed the part where Chip wrote about the Preserve keyword. You can ReDim the array as often as you want. ReDim will cause the existing contents of the array to be lost unless you use the Preserve modifier. ReDim Preserve Arr(0 To 11) Rick A bit more needs to be said. If Arr is a true variant() type array [e.g., Dim Arr() ReDim Arr(1 to 10, 1 to 2)] then with the Preserve keyword you can change only the upper bound of the last dimension [e.g., Dim Arr() Redim Arr(1 to 10, 1 to 2) ReDim Preserve Arr(1 to 10, 1 to 4)] In this case, if you try to change either bound of the 1st dimension, or the lower bound of the 2nd dimension, you will get a Subscript out of range error. If, however, Arr is a an array contained within a Variant type variable [e.g., Dim Arr Redim Arr(1 to 10, 1 to 2)] then with the Preserve keyword you can change either or both bounds of the last dimension [e.g., Dim Arr Redim Arr(1 to 10, 1 to 2) ReDim Preserve Arr(1 to 10, 0 to 3)] If the functions in the freely downloadable file at http:/home.pacbell.net/beban are available to your workbook, then you can use the ResizeArray function to preserve the values of the array that is passed to it and change the lower and/or upper bounds of any or all of the dimensions of a one-, two- three- or four-dimensional array, or increase (up to 4) or decrease the number of the array's dimensions (whether or not the array is contained within a Variant type variable). Alan Beban |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
array dimensioning
I can't find your post referred to - can you send it again with the answer -
I think I have a problem with using Redim but if its already got a solution .... Thanks Rod "monir" wrote: Hello; I've just reviewed with much interest the replies here and found them to be very useful and related to my recent question. Could someone please review the thread: "Array Declaration Problem" and my latest summary post (yesterday, June 22,'08), and identify where the array declaration went wrong ?? Thank you kindly, will very much appreciate your help. "Chip Pearson" wrote: Just FYI... VB/VBA doesn't use the same type arrays as does native C/C++. Instead, it uses SAFEARRAYs (available in C/C++ with MFC or various APIs). -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "LesHurley" wrote in message ... ilia; sory for the long delay in answering your question. In C/C++ you create dynamic arrays and change their dimensions using the malloc() function to create and expand and the free() function to release previously allocated memory. Such a capability is essential in database programs where arrays of large user defined class objects are the normal. Ordinarily no one knows in advance how many iems the array will hold or how much memory will be required, thus the need for dynamic allocation. It's been 5 or 6 years since I did any C++ programming and I had to go back and look it up. HTH. -- Thanks for your help "ilia" wrote: Just curiosity - how in C++ do you increase/decrease the dimension of an array? C++ doesn't even know what an array is, from what I understand. On Mar 15, 12:23 pm, LesHurley wrote: Thanks everyone -- "Alan Beban" wrote: Rick Rothstein (MVP - VB) wrote: Then you missed the part where Chip wrote about the Preserve keyword. You can ReDim the array as often as you want. ReDim will cause the existing contents of the array to be lost unless you use the Preserve modifier. ReDim Preserve Arr(0 To 11) Rick A bit more needs to be said. If Arr is a true variant() type array [e.g., Dim Arr() ReDim Arr(1 to 10, 1 to 2)] then with the Preserve keyword you can change only the upper bound of the last dimension [e.g., Dim Arr() Redim Arr(1 to 10, 1 to 2) ReDim Preserve Arr(1 to 10, 1 to 4)] In this case, if you try to change either bound of the 1st dimension, or the lower bound of the 2nd dimension, you will get a Subscript out of range error. If, however, Arr is a an array contained within a Variant type variable [e.g., Dim Arr Redim Arr(1 to 10, 1 to 2)] then with the Preserve keyword you can change either or both bounds of the last dimension [e.g., Dim Arr Redim Arr(1 to 10, 1 to 2) ReDim Preserve Arr(1 to 10, 0 to 3)] If the functions in the freely downloadable file at http:/home.pacbell.net/beban are available to your workbook, then you can use the ResizeArray function to preserve the values of the array that is passed to it and change the lower and/or upper bounds of any or all of the dimensions of a one-, two- three- or four-dimensional array, or increase (up to 4) or decrease the number of the array's dimensions (whether or not the array is contained within a Variant type variable). Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel VBA Compile Error when dimensioning jagged array | Excel Programming | |||
Range Naming, dimensioning | New Users to Excel | |||
dimensioning variables | Excel Programming | |||
dimensioning across modules | Excel Programming | |||
Dimensioning Arrays | Excel Programming |