VBA TIPS
Dividing an array

Since values can be handled at high speed by using an array, we tends to use it a lot, there is also a point which it should be careful of when using an array.

For example, although an array can be specified as an argument of a worksheet function, it may not work depending on the version of Excel. As an example, here is MAX worksheet function. Since the number of the maximum elements was restricted to 5461 in Excel 97 or Excel2000, when an array that has more than 5461 elements is specified as an argument of the MAX function, an error returns.

How can you make the function work in this case? it works for the dividing an array into two or more arrays of the 5461 or less of elements.

Let's say, an array has 65536 elements can be divided as 12 arrays has 5461 elements and an array has 4 elements.

5461 x 12 + 4 = 65536

dividing an array


Get the maximum values from the divided arrays using the MAX worksheet function, then compare them by a loop, you can get the maximum value at last.


Example

Please note, the MAX worksheet function is just a sample for explaining how to divide an array.

The sub TestingFunction would output numbers from 1 to 65536 into the column A then get those values into a variant named buf as an array. If this program is running on Excel97 or Excel2000, Excel returns an error, but you can use a worksheet function using divided arrays.

The following UDF returns an array of arrays as a variant type.

With regards to how to combining arrays, please have a look at Juan Pablo's ArrayUnion function posted on Dick's blog. http://www.dicks-blog.com/excel/2004/08/combining_array.html

Place the following in a standard module.

Option Explicit

Sub TestingFunction()
    Dim i As Long
    Dim lngMax As Long
    Dim buf As Variant
    Dim myArray As Variant

    'Make a sample data in the worksheet
    With [A1]
        .EntireColumn.Clear
        .Value = 1
        .DataSeries Rowcol:=2, Type:=xlLinear, Step:=1, Stop:=65536
    End With

    buf = [A1].Resize(65536).Value

    On Error Resume Next    'turn on the Error trap

    'In Exce 97/2000, the Maximum element of working in Application.WorksheetFunction.Max
    'is 5461. So this code returnes an error.
    Debug.Print Application.WorksheetFunction.Max(buf)
    If Err Then MsgBox "Eroor " & Err.Number & ":" & Err.Description

    Err.Clear
    On Error GoTo 0    'turn off the Error trap

    'So you must devide this array to some arrays have the elements less than 5461.
    myArray = DevideArray(buf, 5461)

    For i = LBound(myArray) To UBound(myArray)
        If lngMax < Application.WorksheetFunction.Max(myArray(i)) Then
        lngMax = Application.WorksheetFunction.Max(myArray(i))
        End If
    Next
    MsgBox lngMax
End Sub

Function DevideArray(ByVal arrArg, Optional ElementMax As Long = 5461) As Variant
'Devide array as a variant array (make an array of arrays)
    Dim lngNumofDiv As Long
    Dim arrTempArray()
    Dim buf()
    Dim i As Long
    Dim j As Long

    lngNumofDiv = Application.WorksheetFunction.RoundUp(UBound(arrArg) / ElementMax, 0)

    ReDim arrTempArray(1 To lngNumofDiv)
    For j = 1 To lngNumofDiv
        If j = lngNumofDiv Then
            ReDim buf(1 To UBound(arrArg) - (lngNumofDiv - 1) * ElementMax)
            For i = 1 To ElementMax
                If UBound(arrArg) - (lngNumofDiv - 1) * ElementMax < i Then Exit For
                buf(i) = arrArg(i + ElementMax * (j - 1), 1)
            Next
        Else
            ReDim buf(1 To ElementMax)
            For i = 1 To ElementMax
                buf(i) = arrArg(i + ElementMax * (j - 1), 1)
            Next
        End If
        arrTempArray(j) = buf
        Erase buf
    Next
    DevideArray = arrTempArray
End Function

| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
ABOUT
WORKS
THE CELL MASTERS
CONTACT
LINKS
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo