Writing an Array to a Range

[grogs84]

I am testing different ways to use and write arrays. I can't figure out why when I write my array to a range it give me all 0's in the first column and the corrrect number in the second. The array is not 2D so i'm not sure why it is producing 2 columns worth of data. When I use a For Loop it works like want it to. Does "range.value = array" has some tricks to it?

Sub Pct_Chg()
Dim RowNum As Long
Dim i As Long
Dim InMdlArray() As Double
Dim InTtlArray() As Double
Dim PctOffArray() As Double

RowNum = ActiveSheet.UsedRange.rows.Count - 1

ReDim InMdlArray(1 To RowNum, 1)
ReDim InTtlArray(1 To RowNum, 1)
ReDim PctOffArray(1 To RowNum, 1)

For i = 1 To RowNum
InMdlArray(i, 1) = Cells(i + 1, 5)
InTtlArray(i, 1) = Cells(i + 1, 6)
Next i

For i = 1 To RowNum
On Error Resume Next
InAcct = InTtlArray(i, 1)
InModel = InMdlArray(i, 1)
PctOffArray(i, 1) = pct_off(InAcct, InModel)
Next

Range("j2").Resize(RowNum, 2) = PctOffArray
/*** If I use .Resize(Rownum, 1) it gives me a column full of 0's ***/

For i = 1 To RowNum
Cells(i + 1, 12).Value = PctOffArray(i, 1)
Next


End Sub

Function pct_off(InAcct, InModel)

pct_off = (((InAcct) / (InModel)) - 1)

End Function

 

[Ruddles]

The array is not 2D so i'm not sure why it is producing 2 columns worth of data.

ReDim InMdlArray(1 To RowNum, 1)
ReDim InTtlArray(1 To RowNum, 1)
ReDim PctOffArray(1 To RowNum, 1)

All these arrays are 2D. The lower bound of the array is normally zero, so your second dimension is actually 0 To 1, i.e. two elements.

Try REDIMming the arrays as (1 To RowNum, 1 To 1) or head up your code module with an Option Base 1 directive which sets the default lower bound of an array to 1.

 

[John_w]

As stated by Ruddles, you have declared 2D arrrays, but declaring them as (1 To RowNum, 1 To 1) will also give you 2D arrays. Try something based on this example:

Sub ex1()
    Dim RowNum As Long
    Dim i As Long
    Dim PctOffArray() As Double
    
    RowNum = 5
    
    ReDim PctOffArray(1 To RowNum)
    
    For i = 1 To RowNum
        PctOffArray(i) = 2# * i
    Next
    
    'Convert (transpose) array from columns to rows to populate 5 rows in 1 column
    Range("J2").Resize(RowNum, 1).Value = Application.Transpose(PctOffArray)
    
    'Compare with the following which populates 5 columns in 1 row
    Range("J1").Resize(1, RowNum).Value = PctOffArray
End Sub

 

[Ruddles]

As stated by Ruddles, you have declared 2D arrrays, but declaring them as (1 To RowNum, 1 To 1) will also give you 2D arrays.

Yes, I should have omitted the second dimension. But if it's 1, doesn't that produce a 1D array?

 

[mirabeau]

Yes, I should have omitted the second dimension. But if it's 1, doesn't that produce a 1D array?

To your first sentence No! Not necessarily.
To your second sentence, Yes!

For an array x(), ReDim x(1 to n) gives x on the worksheet as a set of items arrayed along one row. How large n can be depends on your version of Excel. If you've got lots of items and want to array them down a column, then (in this form of Dim) you have to use
Startcell.Resize = Application.Transpose(x).

However there's a limit on the size of the transpose function to 65536 even for Excel 2007 (I don't know about later versions). With more than this you may get a Runtime Error 13 or something like, if you try transposing.

To display a million or so entries of the array x down a single column you really have to use ReDim x(1 to n, 1 to 1) - or equivalent of you want to change Option Base. In this case there's no problem with Transpose limitations.

 

[Ruddles]

Thanks for the helpful info.

And incidentally, using .Transpose for 50k rows is approximately 17 faster than using a loop in VBA. That's definitely one for my armoury alongside the wonderfully versatile and powerful .Resize which I only learned a few days ago!

 

[mirabeau]

And incidentally, using .Transpose for 50k rows is approximately 17 faster than using a loop in VBA. That's definitely one for my armoury

Now I find that interesting.

Transposing 50k rows should give 50k columns, which won't show on any Excel worksheet that I've ever seen.

So presumably this comparison is entirely in your machine's memory rather on a worksheet?

 

[Ruddles]

Following the example in Post #3 above:-

Range("J2").Resize(RowNum, 1).Value = Application.Transpose(PctOffArray)

I built an array(50000) and transposed it into Range("A1:A50000").

 

[mirabeau]

And incidentally, using .Transpose for 50k rows is approximately 17 faster than using a loop in VBA. That's definitely one for my armoury ...

Hmm.

That's one I thought maybe you shouldn't be left to live with (aka How strongly do you need to be armoured?)

Try the following code on a blank worksheet.

If you look through the code I hope it's pretty self-explanatory. As set up it generates a simple, small string array and transposes it a couple of ways, one of which is the Transpose Function. The code also gives time taken for each transposition method separately, and compares.

You can next change the RowNum and/or ColNum entries near the top (to say 50k or whatever) and consider what happens with the relative timing.

Sub comparez()
Dim RowNum&, ColNum&, ir&, ic&, t#, time1#
Dim arry() As String, arrytr() As Variant, time2#
'****************************************
ActiveSheet.UsedRange.ClearContents
Cells.ColumnWidth = 4#
'GENERATE ARRAY
RowNum = 10   'set to whatever number you like
ColNum = 3     'set to whatever number you like
ReDim arry(1 To ColNum, 1 To RowNum)
ReDim arrytr(1 To RowNum, 1 To ColNum)
For ir = 1 To RowNum
For ic = 1 To ColNum
arry(ic, ir) = _
    Chr(Int(Rnd * 26) + 65) & Chr(Int(Rnd * 26) + 97)
Next ic, ir
On Error Resume Next
Cells(1, 1).Resize(ColNum, RowNum) = arry
On Error GoTo 0
MsgBox "Array generated.  Continue ..."

'TRANSPOSE USING TRANSPOSE FUNCTION
Cells(ColNum + 2, 1) = "Transpose Fn"
Columns(1).AutoFit
t = Timer
'arrytr = Application.Transpose(arry)
'Cells(ColNum + 3, 1).Resize(RowNum, ColNum).Value = arrytr
Cells(ColNum + 3, 1).Resize(RowNum, ColNum).Value = Application.Transpose(arry)
time1 = Timer - t
MsgBox "Transpose Function took " & _
    Format(time1, "0.000 secs")

'TRANSPOSE USING INDEX SWAP
Cells(ColNum + 2, ColNum + 2) = "Index Swap"
Columns(ColNum + 2).AutoFit
t = Timer
For ir = 1 To RowNum: For ic = 1 To ColNum
    arrytr(ir, ic) = arry(ic, ir)
Next ic: Next ir
Cells(ColNum + 3, ColNum + 2).Resize(RowNum, ColNum) = arrytr
time2 = Timer - t
MsgBox "Index Swap took " & _
    Format(time2, "0.000 secs")

'TIMING COMPARISON
If time1 > 0 Then _
MsgBox "Index Swap took " & _
    Format(time2 / time1, "0.00 as long as Transpose Function")

End Sub