Having difficulty saving VBA array to a range in Excel

Question

I've played around with this for several hours and am no closer to a solution.

When I create an array the following way, it outputs to a range without any difficulties:

Dim Destination As Range
Set Destination = NewSheet.Range("A1")

ReDim OutArray(1 To 1, 1 To NumArrayCols) As Variant
OutArray(1, 1) = "hello"
Destination.Resize(UBound(OutArray, 1), UBound(OutArray, 2)).Value = OutArray

However, when I create an output array in the following manner, it simply pastes a big blank array onto my spreadsheet. The first section of the code is probably mostly irrelevant, but I want to include it in case I'm missing anything:

ReDim OutArray(1, 1 To NumArrayCols) As Variant
Set ThisAtt = Wells.CurrWell.FirstAttribute(Skip:=False)
k = 1
OutArray(1, k) = "UWI"
Do
    ElevOffset = 0
    Set ThisAtt = Wells.CurrWell.CurrAttribute
    If InStr(LCase(ThisAtt.Name1), "elevation") Then
        OutArray(1, k + 1) = ThisAtt.Name1
        OutArray(1, k + 2) = ""
        OutArray(1, k + 3) = ThisAtt.Name2
        OutArray(1, k + 4) = ""
        ElevOffset = ElevOffset + 2
    Else
        OutArray(1, k + 1) = ThisAtt.Name1
        OutArray(1, k + 2) = ThisAtt.Name2
    End If
    OutArray(1, k + ElevOffset + 3) = "Recommend"
    OutArray(1, k + ElevOffset + 4) = "Rec. Value"
    OutArray(1, k + ElevOffset + 5) = "Comments"
    k = k + ElevOffset + 2 + AdditionalColumns
Loop While Not (Wells.CurrWell.NextAttribute(EnableSkipping:=False) Is Nothing)

Dim Destination As Range
Set Destination = NewSheet.Range("A1")
Destination.Resize(UBound(OutArray, 1), UBound(OutArray, 2)).Value = OutArray

It's strange, because every element in OutArray, upon inspection, seems to be there. My hand-generated array works fine, but the automatically-generated array--which seems similar in almost every way--doesn't work. Anyone know why?

just food for thought (doesn't answer your question, though): maybe you're still holding back some other code, but I don't see why you're using a multidimensional array when you're using it as a single-dimension one. — Joseph
– Joseph, Commented Jan 30, 2013 at 19:10
Yeah, good point. OutArray was multi-dimensional until I realized it wasn't going to hold all of my rows. I suppose I should just give it a single dimension. — John von No Man
– John von No Man, Commented Jan 30, 2013 at 19:28

RBarryYoung · Accepted Answer · 2013-01-30 19:08:21Z

5

I suspect that it is just your REDIM statements. In your first example you have this:

ReDim OutArray(1 To 1, 1 To NumArrayCols) As Variant

but in the second example you do this:

ReDim OutArray(1, 1 To NumArrayCols) As Variant

Notice the difference? When you say ReDim A(1 To 1) both the upper and lower bounds are 1, but when you say just Redim(1) only the upper bound is 1, the lower bound is set to the default, which is zero (0). Thus the two arrays are not the same shape/size and therefore in your second case your array does not fit correctly into the Destination Range.

answered Jan 30, 2013 at 19:08

RBarryYoung

57k15 gold badges99 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John von No Man Over a year ago

Dang, that was probably it. I went ahead and converted OutArray to a one-dimensional array, though, because I didn't need the second dimension anymore, and that fixed the problem.

Collectives™ on Stack Overflow

Having difficulty saving VBA array to a range in Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related