Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Redim Preserve = Epic Fail. 1

Status
Not open for further replies.
JTBorton

JTBorton

Technical User
Jun 9, 2008
345
0
0
DE
I have a sub that receives a byref 2d. dynamic array and a byval string. It switches the string from 'LastName, FirstName' to 'FirstName LastName' to be placed into the dynamic array. I need it to resize the dynamic array one slot bigger and place the new string in it. However I'm having a beast of a time trying to resize my two dimensional array. It will resize the first time but I cant get it to resize on the next run.

Run-time error '9':
Subscript out of range

Code: 
Sub EnterNameValues(ByRef NameList, ByVal strName As String)
On Error Resume Next
N = UBound(NameList)
If Err.Number = 9 Then
    N = -1
End If
On Error GoTo 0
N = N + 1
[highlight]ReDim Preserve NameList(N, 1)[/highlight]
N = UBound(NameList)
NameList(N, 0) = SplitName(strName)
.
. (more code jibberish)
.

The procedure works fine as long as I am not preserving the array, but I need to keep the data. On the first run N is 0 and it will resize to a (0,1) array. The second run, N=1, it crashes saying that the subscript is out of range. I have tried many variations of this and keep getting the same result.

-JTBorton
Another Day, Another Disaster
 
Sort by date Sort by votes
I'm vaguely remembering that declaring a variable with parentheses establishes it has a dynamically dimensionned variable. What if you start with:

Sub EnterNameValues(ByRef NameList(), ByVal strName As String)

(I haven't tried it out)
 
Upvote 0 Downvote
With ReDim Preserve, you are limited in what you can do (largely because of the way arrays are physically stored), and you can only change the last dimension. If you need to change - and preserve - both, consider using an array of arrays instead of a two-dimensional array.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website
 
Upvote 0 Downvote
electricpete - I would have thought that using the () in the Sub declaration would not matter, because the variable is already defined and is merely being passed by reference. I'll look into it though. I dont have access to edit it right now.

TonyJollans - so when you say an array of arrays, do you mean like a user defined type? For Example:

Code: 
Option Base 1
Public Type MyVariable
    List(2) as String
End Type

Dim NameList() as MyVariable (Declared in calling procedure)

Sub EnterNameValues(ByRef NameList, ByVal strName As String)
On Error Resume Next
N = UBound(NameList)
If Err.Number = 9 Then
    N = -1
End If
On Error GoTo 0
N = N + 1
ReDim Preserve NameList(N)
N = UBound(NameList)
NameList(N).List(1) = SplitName(strName)
.
. (more code jibberish)
.
End Sub

Or is there another way to make an array of arrays? What if I want to redim the size of NameList.List() to a 2 dimension? Not that I would need to, just trying to learn. The thought comes that you could redim the inside array by using class modules, but you can't make an array of class modules - or atleast I haven't figured out how.

-JTBorton
Another Day, Another Disaster
 
Upvote 0 Downvote
Okay so I used the moel that I listed above and I finally got it working as long as I specified the lower bound:

Code: 
On Error Resume Next
N = UBound(NameList)
If Err.Number = 9 Then
    N = 0
End If
On Error GoTo 0
N = N + 1
ReDim Preserve NameList([highlight]1 to N[/highlight])

-JTBorton
Another Day, Another Disaster
 
Upvote 0 Downvote
>Or is there another way to make an array of arrays?

You can store arrays in Variants, so you can have an array of Variants where each element contains an array.
 
Upvote 0 Downvote
Oh wow, thats cool. So the question then becomes, which is more memory efficient? Storing arrays in variants or user defined arrays? Storing arrays would probably be easier to code, but don't variants take up a LOT of memory, or am I mistaken?

Here is how I tried it. Is there a more efficient way?
Code: 
Sub Test()
Dim OutsideArray() As Variant
Dim InsideArray(1 To 3) As String
Dim J As Integer, G As Integer
For J = 1 To 3
    For G = 1 To 3
        InsideArray(G) = "Test(" & J & ")." & G
    Next G
    ReDim Preserve OutsideArray(J)
    OutsideArray(J) = InsideArray()
Next J
End Sub

-JTBorton
Another Day, Another Disaster
 
Upvote 0 Downvote
Variants consume more memory and are slower to process than other 'native' Types (re UDTs I guess that may depend on how big the UDT is); they do have a few tricks though! I generally avoid them if I can, sometimes I can't resist.
 
Upvote 0 Downvote
Anyway you may ReDim Preserve the last dimension of an array:
Code: 
ReDim Preserve NameList(1, N)
N = UBound(NameList, 2)
NameList(0, N) = SplitName(strName)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Nick V
  • Locked
  • Question
Ignore Hidden Sheet & AutoFill Fail Safe
Replies
1
Views
33
DjangMan
DjangMan
kjv1611
  • Locked
  • Question
Quick Fail on Cannot Connect to Network Folder 1
Replies
4
Views
12
kjv1611
kjv1611
VicM
  • Locked
  • Question
Error 1004: Select method of Range class failed
Replies
2
Views
31
VicM
VicM
Danny_M
  • Locked
  • Question
VBA Excel- Code that works elsewhere failing in new spreadhseet. 1
Replies
3
Views
18
SkipVought
S
pd2004
  • Locked
  • Question
Pivot Table Fail in VBA
Replies
12
Views
40
pd2004
pd2004

Part and Inventory Search

Sponsor

Back
Top