ReDim an array

[davesmith202]

I want to create an array where I know there is 4 units of data on one dimension but an unspecified on the other.

Can I do this?

e.g. dim myarray(0,4) and then just use myarray(1,4)="test" - would that come up with an error?

Thanks,

Dave

 

[DJkarl]

I want to create an array where I know there is 4 units of data on one dimension but an unspecified on the other.

Can I do this?

e.g. dim myarray(0,4) and then just use myarray(1,4)="test" - would that come up with an error?

Thanks,

Dave

Your example would generate an error.

dim myarray(0,4) creates a 2-dimensional array, the first dimension has 1 element with an index of 0, the second dimension has 5 elements with indexes 0-4. If you type myarray(1,4)="test" you will get an out of bound exception because 1 is not a valid index for the first dimension of the array.

What you want to do is

dim myarray() as variant
 
redim myarray(4,0)

Then

 redim preserve myarray(4,ubound(myarray,2)+1)

This will add one onto your array while keeping the data in the array intact. This method is fine if your array is small, if you have a very large amount of data I would look at trying to reduce the number of times you redim, it consumes a lot of memory.

 

[davesmith202]

Why would you need to do:

dim myarray() as variant
redim myarray(4,0)

rather then just

dim myarray(4,0) as variant

 

[DJkarl]

Why would you need to do:

dim myarray() as variant
redim myarray(4,0)

rather then just

dim myarray(4,0) as variant

You can try it and find out...

Basically if you say dim myarray(4,0) as variant that's all that variable can ever be, if you try to redim the array you will get an error saying the array is already dimensioned.

 

[davesmith202]

I must be doing something wrong as I am getting a Subscript out of range error. What have I missed?

    Dim arrMyArray() As Variant
    Dim ii As Integer

    ReDim arrMyArray(0, 5)

    ii = 0
    If Ad1Headline <> "" And Not IsNull(Ad1Headline) Then
        arrMyArray(ii, 1) = ""
        arrMyArray(ii, 2) = Ad1Headline
        arrMyArray(ii, 3) = Ad1Line1
        arrMyArray(ii, 4) = Ad1Line2
        ii = ii + 1
        ReDim Preserve arrMyArray(ii + 1, 4)
    End If

 

[DJkarl]

I must be doing something wrong as I am getting a Subscript out of range error. What have I missed?

    Dim arrMyArray() As Variant
    Dim ii As Integer
 
    ReDim arrMyArray(0, 5)
 
    ii = 0
    If Ad1Headline <> "" And Not IsNull(Ad1Headline) Then
        arrMyArray(ii, 1) = ""
        arrMyArray(ii, 2) = Ad1Headline
        arrMyArray(ii, 3) = Ad1Line1
        arrMyArray(ii, 4) = Ad1Line2
        ii = ii + 1
        ReDim Preserve arrMyArray(ii + 1, 4)
    End If

You can only change the last dimension of a multi-dimesional array.

 

[gemma-the-husky]

what are you trying to use the array for

perhaps there is a better way?

 

[davesmith202]

I've sorted it now using a slightly different approach. Thanks guys.