Optimizing _ExcelReadSheetToArray()

[footswitch]

Hi there,

The _ExcelReadSheetToArray() function is quite slow.

This is most probably due to the fact that it reads and stores information on a cell by cell basis.

A while back I found this line of code that allowed me to read a whole sheet (used range) to an array:

$array_output = $oExcel.transpose($oExcel.ActiveSheet.UsedRange.Value)

This method can be around 150 to 200 times faster than the built-in UDF function (give or take, it's not easy to perform a proper measure, given the variables involved and the huge difference observed).

Nonetheless, either one of them are "dangerous" to use if you are actually considering reading the entire sheet at once.

Let's say your sheet has 90.000 rows and 55 columns. We can easily be talking about 350 MB of memory, if not more, just to temporarily store this data into an array.

I never had this problem before, I was dealing with a maximum of 6.000 rows at a time (the purpose of my script is to load "chunks" of data into a SQLite database).

But the need eventually came, and due to the size of some sheets I can't deal with UsedRange anymore.

So here's the solution:

Based on the same faster method, load x rows at a time until we reach the end of the used range.

$oExcel=_ExcelBookOpen("file")

$timer_global=TimerInit() ; this will measure how long it takes to read the entire used range in the sheet
$aExcelUsedRange=__ExcelGetUsedRange($oExcel)
If Not IsArray($aExcelUsedRange) Then
    ConsoleWrite("-> Excel file is empty"&@CRLF)
    Exit
EndIf
ConsoleWrite("-> "&$aExcelUsedRange[0]&" rows and "&$aExcelUsedRange[1]&" columns."&@CRLF)

$sExpression=""
$timer_cycle=TimerInit(); this will measure how long it takes to read each chunk of data
$iStep=1000 ; get 1000 rows at a time
For $r=1 To $aExcelUsedRange[0] Step $iStep+1
    If $r+$iStep>$aExcelUsedRange[0] Then $iStep=$aExcelUsedRange[0]-$r
    $sExpression="$oExcel.transpose(.Range(.Cells("&$r&",1),.Cells("&$r+$iStep&","&$aExcelUsedRange[1]&")).Value)"
    With $oExcel.Activesheet
        $array_output=Execute($sExpression)
    EndWith
    ; place your actions here, for each chunk of data loaded
    ConsoleWrite("> Loading "&$iStep&" rows took "&Int(TimerDiff($timer_cycle))&" ms  (row "&$r&" to "&$r+$iStep&")"&@CRLF)
    $timer_cycle=TimerInit()
Next
ConsoleWrite("-> Finalized in "&Int(TimerDiff($timer_global))&" ms."&@CRLF)
_ExcelBookClose($oExcel,0,0) ; close file without saving and without alerts
Exit

The following function was inspired on the Excel UDF (a nice way of saying copied from)

Func __ExcelGetUsedRange(ByRef $oExcel)
    ; Get size of current sheet as R1C1 string
    ; -4150 specifies that the address is returned in R1C1 string format
    ; SpecialCells(11) refers to the last used cell in the active worksheet
    Local $sLastCell = $oExcel.Application.Selection.SpecialCells(11).Address(True, True, -4150)

    ; Extract integer last row and col
    $sLastCell = StringRegExp($sLastCell, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3)
    Local $iLastRow = $sLastCell[0]
    Local $iLastColumn = $sLastCell[1]
    
    ; Return 0 if the sheet is blank
    If $sLastCell = "R1C1" And $oExcel.Activesheet.Cells($iLastRow, $iLastColumn).Value = "" Then
        Return 0
    EndIf

    Dim $aUsedRange[2]
    $aUsedRange[0]=$sLastCell[0]
    $aUsedRange[1]=$sLastCell[1]
    Return $aUsedRange
EndFunc

May it be useful to you someday as it is for me now

Probably worth to take a look at and who knows at some point update the Excel UDF. (I hope I'm not missing something seriously huge in respect to data integrity)

footswitch

[dmob]

Great work, can't believe how quick this is compared to the original function.

My tests:

your code = Under 1s (97ms)

ExcelReadSheetToArray = 14s

That's what I'll be using from now...

Thanks for sharing.

[drapdv]

You know, I was pretty pleased with myself when I came up with a function that dropped the array-read time by a factor of 6. Then someone pointed out this thread. This is really great work!

So I was looking at it, and I don't mean to roll up in anyone's Kool Aid, but I thought it would be able to be used by more people if it conformed to the layout in _ExcelSheetReadToArray(). It doesn't iterate through 1000 lines at a time, so as was pointed out, that may leave people with a lot of memory being eaten up by the array, so the original script could be faster in some cases.

PROS: Conforms to existing standards, so people can use this in place of _ExcelReadSheetToArray() without having to change the rest of their script.

Way, way, WAY faster than the original function included in the UDF.

You can specify a 0-based or 1-based return array (it defaults to a 1-based array).

CONS: It won't go with an obfuscated script, I don't think. Obfuscation and Execute don't get along, as I recall.

Because it has to insert a column and a row (and then remove them), the spreadsheet won't close without saving first or suppressing alerts.

You can avoid that by specifying a 0-based return array, but it will not conform to R1C1 standards.

I stole the error-checking and information from the original _ExcelReadSheetToArray function, and all the guts that makes this little puppy go from footswitch, so all credit goes to SEO, PsaltyDS, and footswitch.

P.S., you will need to add two constants to your Excel UDF file (if you haven't already):

Const $xlShiftToLeft = -4159

Const $xlShiftUp = -4162

expandcollapse popup

;===============================================================================
;
; Description:  Create a 2D array from the rows/columns of the active worksheet.
; Syntax:       _ExcelReadSheetToArrayInstant($oExcel, [$iStartRow , $iStartColumn [, $iRowCnt, $iColCnt [, $iArrayBase]]])
; Parameter(s):     $oExcel -       Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $iStartRow -    Row number to start reading, defaults to 1 (first row)
;                   $iStartColumn - Column number to start reading, defaults to 1 (first column)
;                   $iRowCnt -      Count of rows to read, defaults to 0 (all)
;                   $iColCnt -      Count of columns to read, defaults to 0 (all)
;                   $iArrayBase -   Determines whether to return a 0-based or 1-based array (Defaults to a 1-based array)
; Requirement(s):   Requires ExcelCOM_UDF.au3
; Return Value(s):  On Success - Returns a 2D array with the specified cell contents
;                   On Failure - Returns 0 and sets @error on errors:
;                       @error=1 -          Specified object does not exist
;                       @error=2 -          Start parameter out of range
;                           @extended=0 -   Row out of range
;                           @extended=1 -   Column out of range
;                       @error=3 -          Count parameter out of range
;                           @extended=0 -   Row count out of range
;                           @extended=1 -   Column count out of range
; Author(s):        SEO <locodarwin at yahoo dot com> (original _ExcelReadArray() function)
; Modified:         PsaltyDS 01/04/08 - 2D version _ExcelReadSheetToArray()
; Modified Redux:   footswitch 04/30/11 - Faster execution _ExcelReadSheetToArrayInstant()
; Note(s):          If $iArrayBase = 1, row 0 and col 0 of the returned array are empty, and
;                   cell data starts at [1][1] to match R1C1 numbers. If $iArrayBase = 0,
;                   cell data starts at [0][0].
;                   By default the entire sheet is returned.
;                   You will not be able to automatically close the spreadsheet after performing
;                   this operation without suppressing alerts (it makes then reverses 2 changes)
;
;===============================================================================
Func _ExcelReadSheetToArrayInstant($oExcel, $iStartRow = 1 , $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0, $iArrayBase = 1)
Dim $sExpression
Dim $letters
Dim $avRET[1][2] ; Dummy return array in case the spreadsheet is blank
    $avRET[0][0] = 0
    $avRET[0][1] = 0

    ; Test inputs
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $iStartRow < 1 Then Return SetError(2, 0, 0)
    If $iStartColumn < 1 Then Return SetError(2, 1, 0)
    If $iRowCnt < 0 Then Return SetError(3, 0, 0)
    If $iColCnt < 0 Then Return SetError(3, 1, 0)

    ; Get size of current sheet as R1C1 string
    ; Note: $xlCellTypeLastCell and $x1R1C1 are constants declared in ExcelCOM_UDF.au3
    Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)

    ; Extract integer last row and col
    Local $iLastRow = StringInStr($sLastCell, "R")
    Local $iLastColumn = StringInStr($sLastCell, "C")
    $iLastRow = Number(StringMid($sLastCell, $iLastRow + 1, $iLastColumn - $iLastRow - 1))
    $iLastColumn = Number(StringMid($sLastCell, $iLastColumn + 1))

    ; Return 0's if the sheet is blank
    If $sLastCell = "R1C1" And $oExcel.Activesheet.Cells($iLastRow, $iLastColumn).Value = "" Then Return $avRET

    ; Make sure the $iArrayBase value is valid
    If Not IsNumber($iArrayBase) Then $iArrayBase = 0
    If $iArrayBase > 1 Then $iArrayBase = 1
    If $iArrayBase < 0 Then $iArrayBase = 0

    ; Invert the values so the math works
    If $iArrayBase = 0 Then
        $iArrBase = 1
    ElseIf $iArrayBase = 1 Then
        $iArrBase = 0
    EndIf

    ; Check input range is in bounds
    If $iStartRow > $iLastRow Then Return SetError(2, 0, 0)
    If $iStartColumn > $iLastColumn Then Return SetError(2, 1, 0)
    If $iStartRow + $iRowCnt - 1 > $iLastRow Then Return SetError(3, 0, 0)
    If $iStartColumn + $iColCnt - 1 > $iLastColumn Then Return SetError(3, 1, 0)

    If $iRowCnt = 0 Then
        $iRowCnt = $iLastRow + 1 - $iArrBase
    ElseIf $iStartRow + $iRowCnt - 1 > $iLastRow Then
        $iRowCnt = $iLastRow + 1 - $iArrBase
    Else
        $iRowCnt = ($iStartRow + $iRowCnt) - $iArrBase
    EndIf

    If $iColCnt = 0 Then
        $iColCnt = $iLastColumn + 1 - $iArrBase
    ElseIf $iStartColumn + $iColCnt - 1 > $iLastColumn Then
        $iColCnt = $iLastColumn + 1 - $iArrBase
    Else
        $iColCnt = ($iStartColumn + $iColCnt) - $iArrBase
    EndIf

    $sExpression="$oExcel.Transpose(.Range(.Cells("&$iStartRow&","&$iStartColumn&"),.Cells("&$iRowCnt&","&$iColCnt&")).Value)"
    $iColumn = $iStartColumn

    While $iColumn
        $x = Mod($iColumn, 26)
        If $x = 0 Then $x = 26
        $letters = Chr($x + 64) & $letters
        $iColumn = ($iColumn - $x) / 26
    WEnd

    With $oExcel.Activesheet
        If $iArrayBase = 1 Then
            .Range($letters&":"&$letters).Insert($xlShiftToRight)
            .Range($iStartRow&":"&$iStartRow).Insert($xlShiftDown)
        EndIf

        $array_output=Execute($sExpression)

        If $iArrayBase = 1 Then
            .Range($letters&":"&$letters).Delete($xlShiftToLeft)
            .Range($iStartRow&":"&$iStartRow).Delete($xlShiftUp)
        EndIf
    EndWith

    If $iArrayBase = 1 Then
        $array_output[0][0] = UBound($array_output, 1)
        $array_output[0][1] = UBound($array_output, 2)
    EndIf

    Return $array_output
EndFunc ;-> _ExcelReadSheetToArrayInstant

Edited May 5, 2011 by drapdv

[digibread]

Thanks drapdv

It's an usefull UDF

I have a larg CSV File 71MB auout 200687 rows and 50 columns.

I get an error When i use your function:

-----------------------------------------------------------------------

$array_output[0][0] = UBound($array_output, 1)

$array_output^ ERROR

-----------------------------------------------------------------------

is the csv file too large?

Can you help me?

Thanks

i'm so sorry for my poor english

[laffo16]

heres another method, its incomplete at the moment, scraps of code from here n their.

uses the find method to searching to locate the last cell. $icolcnt currently required.

feel free to make any updates or adjustments.

Global $CNE_array = StringSplit("A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z", ",")
$CNE_array[0] = ""

Func _ExcelReadSheetToArrayNew($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0)
    $iStartColumn = _ExcelCol($iStartColumn)
    $iColCnt = _ExcelCol($iColCnt)
    
    $oExcel.Range("A1:A65535").Select
    $find_oFound = $oExcel.Selection.Find("*", $oExcel.ActiveCell, Default, 2, $xlByRows, $xlPrevious, False, Default)
    $find_oFound.Activate
    $find_get = $oExcel.ActiveCell.Address(True, True, $xlR1C1)
    $find_reg = StringRegExp($find_get, "[RZ]([^CS]*)[CS](.*)",3)
    $find_last_row = Number($find_reg[0])
    
    If $iRowCnt = 0 Then
        $iRowCnt = $find_last_row
    EndIf
    
    $aArray = $oExcel.Activesheet.Range($iStartColumn & $iStartRow & ":" & $iColCnt & $iRowCnt).Value
    
    Dim $aArray_n[UBound($aArray,2) +1][UBound($aArray) +1]
    For $er_x = 0 To UBound($aArray,2) -1
        For $er_y = 0 To UBound($aArray) -1
            $aArray_n[$er_x + 1][$er_y + 1] = $aArray[$er_y][$er_x]
        Next
    Next
    
    $oExcel.Cells(1, 1).Select
    Return $aArray_n
EndFunc

Func _ExcelCol($CNA_num)
    $char1 = int(($CNA_num-1)/26)
    $char2 = $CNA_num - $char1*26
    Return $CNE_array[$char1] & $CNE_array[$char2]
EndFunc  ;==>_Convert_Num_to_ExcelCol

i hope you dont mind me posting this here footswitch, i just wanted to keep a copy online incase i need it in the future and this seemed like the most appropriate topic to place it in. i did try ur version and it does run like greese lightning :] gj

Edited June 20, 2011 by laffo16

[drapdv]

Thanks drapdv

It's an usefull UDF

I have a larg CSV File 71MB auout 200687 rows and 50 columns.

I get an error When i use your function:

-----------------------------------------------------------------------

$array_output[0][0] = UBound($array_output, 1)

$array_output^ ERROR

-----------------------------------------------------------------------

is the csv file too large?

Can you help me?

Thanks

i'm so sorry for my poor english

Don't worry, your English is great!

I have had a couple of different errors come up from time to time. One of them was "Data type mismatch." Oddly...I had that problem with a certain worksheet, and now it no longer occurs. I've also gotten that error you mention. It happens when the Execute statement fails to return an array.

So, I have three suggestions. #1, if your data is not sensitive, please feel free to send it to me and I will give it a whirl, just to see what may be causing the error. #2, try breaking it up a bit, because Excel only supports more than 65,536 rows on .xlsx files. I don't know if that would actually cause a problem in this situation, but it wouldn't hurt to remove the possibility.

The last option is, I have a UDF that works every single time, no errors. It is not nearly as fast as this function, but it is 5-6 times faster than _ExcelReadSheetToArray(), so it could be helpful. It needs a function created by DaLiMan, MRDev, and SEO, so I'm including that in here as well. May not be perfect, but it's reliable, and if it is taking 20 - 30 minutes to read your sheet now, this function should do it in 4 or 5 minutes.

Hope this helps, and let me know if you would like me to take a look at that file.

David

expandcollapse popup

Func _ExcelReadSheetToArrayFast($oExcel, $firstRow=1, $firstCol=1, $lastRow=0, $lastCol=0, $vSheet="")
    $usedRange = _ExcelSheetUsedRangeGet($oExcel, $vSheet)
    If IsArray($usedRange) Then
        If $lastCol = 0 Then $lastCol = $usedRange[2]
        If $lastRow = 0 Then $lastRow = $usedRange[3]
    Else
        Return 0
    EndIf

    Dim $aFixedArray[$lastRow - $firstRow + 2][$lastCol - $firstCol + 2]
    ProgressOn(" Progress", " Reading Excel Sheet...")

    For $i = $firstRow To $lastRow
        ProgressSet($i/($lastRow/100), "Running "&$i&" of "&$lastRow - $firstRow)
        $aTempArray = $oExcel.Activesheet.Range($oExcel.Cells($i, $firstCol), $oExcel.Cells($i, $lastCol)).Value
        $b = $i - $firstRow + 1
        For $a = 0 To ($lastCol - $firstCol)
            $c = $a + 1
            $aFixedArray[$b][$c] = $aTempArray[$a][0]
        Next
    Next

    ProgressOff()
    Return $aFixedArray
EndFunc ;==>_ExcelReadSheetToArrayFast

;===============================================================================
;
; Description:      Return the last cell of the used range in the specified worksheet.
; Syntax:           $array = _ExcelSheetUsedRangeGet($oExcel, $vSheet)
; Parameter(s):     $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $vSheet - The sheet name or number to be checked.
; Requirement(s):   None
; Return Value(s):  On Success - Returns an array of used range values:
;                       $array[0] - The last cell used, in A1 format (if 0 is returned, worksheet is blank)
;                       $array[1] - The last cell used, in R1C1 format
;                       $array[2] - The last column used, as an integer
;                       $array[3] - The last row used, as an integer
;                   On Failure - Returns 0 (as non-array numeric value) and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - Invalid sheet number
;                       @error=3 - Invalid sheet name
; Author(s):        DaLiMan, MRDev, SEO <locodarwin at yahoo dot com>
; Note(s):          Upon return, $array[0] will equal numeric value 0 if the worksheet is blank
;
;===============================================================================
Func _ExcelSheetUsedRangeGet($oExcel, $vSheet="")
    Local $aSendBack[4], $sTemp, $aSheetList, $fFound = 0
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If Not $vSheet = "" Then
        If IsNumber($vSheet) Then
            If $oExcel.Sheets.Count < $vSheet Then Return SetError(2, 0, 0)
        Else
            $aSheetList = _ExcelSheetList($oExcel)
            For $xx = 1 To $aSheetList[0]
                If $aSheetList[$xx] = $vSheet Then $fFound = 1
            Next
            If NOT $fFound Then Return SetError(3, 0, 0)
        EndIf
        $oExcel.Sheets($vSheet).Select
    Else
        $oExcel.ActiveSheet.Select
    EndIf

    $aSendBack[0] = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address
    $aSendBack[1] = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
    $aSendBack[0] = StringReplace($aSendBack[0], "$", "")
    $sTemp = StringRegExp($aSendBack[1], "[RZ]([^CS]*)[CS](.*)",3)
    $aSendBack[2] = Number($sTemp[1])
    $aSendBack[3] = Number($sTemp[0])
    If $aSendBack[0] = "A1" And $oExcel.Activesheet.Range($aSendBack[0]).Value = "" Then $aSendBack[0] = 0
    Return $aSendBack
EndFunc ;==>_ExcelSheetUsedRangeGet

[RichardTwickenham]

I've discovered a limitation in the scripts above which I would like to share.

On my Windows 7 PC with Excel 2010, I find that if any cell of the worksheet has more than 255 characters then the $oExcel.transpose line of the above scripts fail, and rather than bringing back an array, brings back a blank string.

I hope this helps someone.

Edited April 18, 2013 by RichardTwickenham

[water]

I'm about to rewrite the Excel UDF (for download see my signature). This will be addressed in the new functions.

[bp123]

Love the speed of this transpose function.... However it gives me the following COM error on one of my spreadsheets.
http://img17.imageshack.us/img17/7184/egc4.jpg

Any thoughts on how to address that?

The spreadsheet I'm running this on is approximately 7200 cells of text data ranging from 3-140 characters.

Edited August 5, 2013 by bp123

[water]

Which version of Excel do you run?

[sankar18]

awesome  Polymath 

[Wombat]

Love the speed of this transpose function.... However it gives me the following COM error on one of my spreadsheets.

http://img17.imageshack.us/img17/7184/egc4.jpg

Any thoughts on how to address that?

The spreadsheet I'm running this on is approximately 7200 cells of text data ranging from 3-140 characters.

 

For future reference, and to save time, you can printscreen the active window with Alt+PrtScn and not have to crop it

Second, to recieve fruitful answers, you must include more information in your posts....

[water]

The transpose method used by the function has its limitations.

To solve the problem it is necessary to know the Excel version you run!

[Althalus]

This is probably a stupid question as no one else asked it, but as I am a beginner at the language I will anyway.  Is there any good way to either have $array_output get the next 1000 records added to the end of it or add the contents of $array_output to a global array each time it gets new data?

[water]

Function _ArrayConcatenate allows to concatenate two arrays.

[Althalus]

When I use _ArrayConcatenate it appears to just exit and returns:

+>09:26:14 AutoIt3.exe ended.rc:0

+>09:26:14 AutoIt3Wrapper Finished.

I have an _ArrayDisplay right after so that should popup and does without the _ArrayConcatenate function, that is why I through the function was just for 1D arrays.

expandcollapse popup

#include <File.au3>
#include <Excel.au3>
#include <Array.au3>

;-----------------------------------------------------------------------------------------------------------------------------
; Open/Read/Manipulate the Excel File
;-----------------------------------------------------------------------------------------------------------------------------
Global $excelFile = FileOpenDialog("Open File That Is From The Clearinghouse", @ScriptDir & "\", "Excel files (*.xlsx;*.xls)", 1)

; Open User Specified Excel file
;--------------------------------------------------------------------------------------------------------------------------------------------
Global $openExcelFile = _ExcelBookOpen($excelFile, 1, True)

; Message to inform user something is happening...
MsgBox(0, "Reading File", "Please wait, as this might take a while depending on the size of the file.", 2)

; Optimizing _ExcelReadSheetToArray()
;--------------------------------------------------------------------------------------------------------------------------------------------
$aExcelUsedRange=__ExcelGetUsedRange($openExcelFile)
If Not IsArray($aExcelUsedRange) Then
    MsgBox(0,"Error","Excel file is empty. Click OK to Exit.",0)
    Exit
EndIf

Global $excelArray = UBound($aExcelUsedRange)

$sExpression=""
$iStep=1000 ; get 1000 rows at a time
For $r=1 To $aExcelUsedRange[0] Step $iStep+1
    If $r+$iStep>$aExcelUsedRange[0] Then $iStep=$aExcelUsedRange[0]-$r
    $sExpression="$openExcelFile.transpose(.Range(.Cells("&$r&",1),.Cells("&$r+$iStep&","&$aExcelUsedRange[1]&")).Value)"
    With $openExcelFile.Activesheet
        $array_output=Execute($sExpression)
    EndWith
    ; place your actions here, for each chunk of data loaded
    _ArrayConcatenate($excelArray, $array_output)
    _ArrayDisplay($excelArray,"$excelArray")
Next


Exit

Func __ExcelGetUsedRange(ByRef $openExcelFile)
    ; Get size of current sheet as R1C1 string
    ; -4150 specifies that the address is returned in R1C1 string format
    ; SpecialCells(11) refers to the last used cell in the active worksheet
    Local $sLastCell = $openExcelFile.Application.Selection.SpecialCells(11).Address(True, True, -4150)

    ; Extract integer last row and col
    $sLastCell = StringRegExp($sLastCell, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3)
    Local $iLastRow = $sLastCell[0]
    Local $iLastColumn = $sLastCell[1]

    ; Return 0 if the sheet is blank
    If $sLastCell = "R1C1" And $openExcelFile.Activesheet.Cells($iLastRow, $iLastColumn).Value = "" Then
        Return 0
    EndIf

    Dim $aUsedRange[2]
    $aUsedRange[0]=$sLastCell[0]
    $aUsedRange[1]=$sLastCell[1]
    Return $aUsedRange
EndFunc

[water]

You are correct. In the current production version _ArrayConcatenate only works with 1D arrays.

Melba23 has updated the Array UDF. A discussion and the updated UDF can be found on the forum.

[water]

To improve performance of your script you should have a look at my rewrite of the Excel UDF (for download please see my signature).

It works with ranges and is about 20 to 100 times faster when reading a worksheet or a range.