Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Alan Beban's ResizeArray
Trying to use Alan Beban's Sub ResizeArray, but I can't get it to work.
There are no errors, but the final array after resizing has all empty elements. It goes wrong at the final bit where the altered array is assigned to the supplied array: MsgBox arr1(1, 2) 'goes wrong here MyArray = arr1 MsgBox MyArray(1, 2) MsgBox SuppliedArray(1, 2) The first 2 messageboxes give the right value, but the third gives a blank. The array supplied to the sub is a 2-dimensional 1-based publicly declared variant array. I must be making a simple mistake, but I can't see it. Thanks for any advice. RBS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Alan Beban's ResizeArray
Hi RB,
I'm not sure what your code looks like, but with 1 through 12 in A1:C4 I ran the following Option Base 1 Public SuppliedArray() As Variant _________________________________ Sub TestArrayFill() ReDim SuppliedArray(1 To 4, 1 To 3) Set rng = Sheets(4).Range("A1:C4") For i = 1 To 4 For j = 1 To 3 SuppliedArray(i, j) = rng(i, j) Next Next ResizeArray SuppliedArray, 5, 3 For i = 1 To 5: For j = 1 To 3 Debug.Print SuppliedArray(i, j)'<-returned 1 thru 12 plus 3 blanks Next Next Debug.Print UBound(SuppliedArray)'<-returned 5 End Sub Where are we not running the same thing? Alan Beban RB Smissaert wrote: Trying to use Alan Beban's Sub ResizeArray, but I can't get it to work. There are no errors, but the final array after resizing has all empty elements. It goes wrong at the final bit where the altered array is assigned to the supplied array: MsgBox arr1(1, 2) 'goes wrong here MyArray = arr1 MsgBox MyArray(1, 2) MsgBox SuppliedArray(1, 2) The first 2 messageboxes give the right value, but the third gives a blank. The array supplied to the sub is a 2-dimensional 1-based publicly declared variant array. I must be making a simple mistake, but I can't see it. Thanks for any advice. RBS |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Alan Beban's ResizeArray
Alan,
Thanks. I had my SuppliedArray declared as a fixed array: Public SuppliedArray(1 To 200, 1 To 35) As Variant That was the difference. After changing to: Public SuppliedArray() As Variant and doing ReDim SuppliedArray(1 To 200, 1 To 35) As Variant on initializing the form it works. I knew it would be something simple. Bart "Alan Beban" wrote in message ... Hi RB, I'm not sure what your code looks like, but with 1 through 12 in A1:C4 I ran the following Option Base 1 Public SuppliedArray() As Variant _________________________________ Sub TestArrayFill() ReDim SuppliedArray(1 To 4, 1 To 3) Set rng = Sheets(4).Range("A1:C4") For i = 1 To 4 For j = 1 To 3 SuppliedArray(i, j) = rng(i, j) Next Next ResizeArray SuppliedArray, 5, 3 For i = 1 To 5: For j = 1 To 3 Debug.Print SuppliedArray(i, j)'<-returned 1 thru 12 plus 3 blanks Next Next Debug.Print UBound(SuppliedArray)'<-returned 5 End Sub Where are we not running the same thing? Alan Beban RB Smissaert wrote: Trying to use Alan Beban's Sub ResizeArray, but I can't get it to work. There are no errors, but the final array after resizing has all empty elements. It goes wrong at the final bit where the altered array is assigned to the supplied array: MsgBox arr1(1, 2) 'goes wrong here MyArray = arr1 MsgBox MyArray(1, 2) MsgBox SuppliedArray(1, 2) The first 2 messageboxes give the right value, but the third gives a blank. The array supplied to the sub is a 2-dimensional 1-based publicly declared variant array. I must be making a simple mistake, but I can't see it. Thanks for any advice. RBS |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Alan Beban's ResizeArray
Thanks for the feedback.
Alan Beban RB Smissaert wrote: Alan, Thanks. I had my SuppliedArray declared as a fixed array: Public SuppliedArray(1 To 200, 1 To 35) As Variant That was the difference. After changing to: Public SuppliedArray() As Variant and doing ReDim SuppliedArray(1 To 200, 1 To 35) As Variant on initializing the form it works. I knew it would be something simple. Bart "Alan Beban" wrote in message ... Hi RB, I'm not sure what your code looks like, but with 1 through 12 in A1:C4 I ran the following Option Base 1 Public SuppliedArray() As Variant _________________________________ Sub TestArrayFill() ReDim SuppliedArray(1 To 4, 1 To 3) Set rng = Sheets(4).Range("A1:C4") For i = 1 To 4 For j = 1 To 3 SuppliedArray(i, j) = rng(i, j) Next Next ResizeArray SuppliedArray, 5, 3 For i = 1 To 5: For j = 1 To 3 Debug.Print SuppliedArray(i, j)'<-returned 1 thru 12 plus 3 blanks Next Next Debug.Print UBound(SuppliedArray)'<-returned 5 End Sub Where are we not running the same thing? Alan Beban RB Smissaert wrote: Trying to use Alan Beban's Sub ResizeArray, but I can't get it to work. There are no errors, but the final array after resizing has all empty elements. It goes wrong at the final bit where the altered array is assigned to the supplied array: MsgBox arr1(1, 2) 'goes wrong here MyArray = arr1 MsgBox MyArray(1, 2) MsgBox SuppliedArray(1, 2) The first 2 messageboxes give the right value, but the third gives a blank. The array supplied to the sub is a 2-dimensional 1-based publicly declared variant array. I must be making a simple mistake, but I can't see it. Thanks for any advice. RBS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing Alan Beban's code on Arrays; Importing a module or a project | Excel Worksheet Functions | |||
Has anyone used arrayfunctions from Alan Beban? | Excel Worksheet Functions | |||
VlookupS-Alan Beban | Excel Worksheet Functions | |||
Array Functions from Alan Beban | Excel Worksheet Functions |