Wednesday, March 16, 2005

More on the last post about arrays

Jon from Peltiertech posted this comment regarding my crude array programming. Don't get me wrong - mine worked - just his is way better!
I am going to learn a lot from picking his code apart - thanks a heap Jon!


A few minor improvements:

' ranges loaded into VBA come in as undimensioned variants

Dim MyArray as Variant

' the first three were declared as variants ' only the last in a comma separated list takes on the type you specify

Dim lowercol As Single
Dim uppercol As Single
Dim lowerrow As Single
Dim upperrow As Single

' This is ignored. The range is imported as a 1-based, 2-D array, regardless of how you dimension it

ReDim MyArray(upperrow - lowerrow, uppercol - lowercol)

' just to be retentive

MyArray = Selection.Value

' even better, don't waste cycles selecting the range, doing the Goto, or even activating that sheet

Dim MyArray As Variant
Dim lowercol As Single, uppercol As Single, lowerrow As Single, upperrow As Single

Sub test_array()
Dim MyRange As Range

Set MyRange = ThisWorkbook.Names("array_start").RefersToRange
lowercol = MyRange.Column
uppercol = MyRange.End(xlToRight).Column
lowerrow = MyRange.Row
upperrow = MyRange.End(xlDown).Row

Set MyRange = Worksheets(MyRange.Parent.Name).Range(MyRange, MyRange.Offset(upperrow - lowerrow, uppercol - lowercol))
MyArray = MyRange.Value
End Sub

One more nice thing about Arrays, If you wanted to work with this one further, you could declare a new array, with counters matching the row and column numbers:

' each element is a double
Dim NewArray() as Double

' dim iRow as long, iCol as Integer

'array counters match row/col numbers!
Redim NewArray(lowerrow to upperrow, lowercol to uppercol)

For iRow = lowerrow to upperrow
For jCol = lowercol to uppercol
NewArray(iRow, jCol) = MyArray(irow+1-lowerrow, jcol+1-lowercol)
Next jCol
Next iRow

' now do the manipulations

' now dump into the target worksheet

With Worksheets("Target")
.Range(.cells(lowerrow, upperrow), .cells(lowercol, uppercol)).Value = NewArray
End With

Wow, I'm long-winded this morning. Nice blog.

- Jon


Posted by: Jethro | 1:23 AM |