#Excel vba debug print array code
#Excel vba debug print array windows

The first is with a single XY pair at row number 100000. I ran each sub 4 times using different densities of data.

#Excel vba debug print array code

The code gets the Range into a Variant, creating a 2-dimensional array, and then loops down the array looking for X and Y. Using a Variant Arrayĭebug.Print 'Var array ' & n & ' ' & (MicroTimer - dTime) * 1000 The loop terminates either when nothing is found (Worksheetfunction.Match raises an error) or the Resize fails, also raising an error. This Sub works by using Worksheetfunction.Match to find the first occurrence of X within the Range object oRng.Īfter each X is found oRng is resized to exclude the range already searched. Set oRng = oRng.Resize( - j, 1).Offset(j, 0)ĭebug.Print 'Match ' & n & ' ' & (MicroTimer - dTime) * 1000 If oRng(j, 2).Value2 = 'Y' Then n = n + 1 Here is the code using WorksheetFunction.MATCH When completed the number of rows with both X and Y (this is less than the number of times an X was found) and the time in milliseconds is shown in the Immmediate window.

Then this is repeated using FindNext until we have looped back to the first range address. The Sub does a Range.Find on the 100 thousand rows looking for X, and then checks if column 2 for that cell also contains Y. Loop While Not oRng Is Nothing And oRng.Address FirstAddressĭebug.Print 'Find ' & n & ' ' & (MicroTimer - dTime) * 1000 If cyFrequency = 0 Then getFrequency cyFrequency

#Excel vba debug print array windows

' uses Windows API calls to the high resolution timer Private Declare Function getTickCount Lib 'kernel32' Alias _ Private Declare Function getFrequency Lib 'kernel32' Alias _ 'QueryPerformanceCounter' (cyTickCount As Currency) As Long Private Declare PtrSafe Function getTickCount Lib 'kernel32' Alias _ 'QueryPerformanceFrequency' (cyFrequency As Currency) As Long Private Declare PtrSafe Function getFrequency Lib 'kernel32' Alias _ I am using the MicroTimer high-resolution timer API to give timing accuracy at the microsecond (millionths of a second) level, but the timing results will all be in milliseconds.

This makes it easy to test how the various methods compare with different densities of data. Since the X and the Y are using different random numbers there will be rows with X but no Y and Y but no X, as well as rows with both X and Y. This code randomly generates X in column 1 and Y in column 2 in a range of 100 thousand rows, with the number of Xs and Ys controlled by the constant 0.5Ĭhanging the 0.5 to 0.9 will give few Xs and Ys, and changing it to 0.001 will give lots. Range('A1').Resize(UBound(Data), UBound(Data, 2)) = Data Generating Test DataĪndreas Killer came up with a nice routine for generating test data: MsgBox 'The first item is: ' & m圜ollection.When searching unsorted data in VBA I have always tended to use MATCH rather than FIND, mainly because early versions of Excel before Excel 2003 did not allow you to use FIND in UDFs.īut prompted by a discussion in one of the Excel forums I thought it was about time I revisited Find and Match to see which performs better in Excel 2010, and for good measure lets compare them to getting the data into a variant array and looping through the array. MsgBox 'The first item is: ' & myArray(LBound(myArray))įor i = LBound(myArray) To UBound(myArray) & CStr((UBound(myArray) - LBound(myArray)) + 1) & ' items' ReDim Preserve myArray(UBound(myArray) + ARRAY_START) 'Array indexes start at 0, unless explicitly redefined The following two example subroutines illustrate the differences. This can also result in code that is cleaner and easier to maintain. Many developers use collections instead of arrays for storing groups of related items to eliminate the hassles and extra code to set and verify array bounds. Unlike arrays, a single collection can store items of different types because each item in a collection is stored as a Variant. Collections in VBA are objects that can store groups of related items, much like an array.