VBA/COM optimisation

[Spike]

Hello helpful VBA bods - I have a question.

I'm getting an array full of prices using using:

prices = ba.getPrices

Then I loop through that to do my thing - only I'm always trying to cut down the number of loops I do.
Is there any way I can quickly get the following information from the prices array without looping through it?

1: The selection name of the 1st selection on the list.

2: The lowest value in the best available back-price column.
2b: The "row" address of that value

Thanks!

[osknows]

Hi Spike,

What TYPE is 'prices'? Is it a standard array, a collection or a class collection?

Also do you know if it's sorted or indexed?

If you could post or PM an example of the type and what's contained within it I may be able to help.

Unfortunately I've never been able to get the COM reference to work on my computer and haven't really chased it up as I don't need to use it so can't check the variable type myself. (I get exactly the same issue as Here

[GaryRussell]

getPrices() returns an array so you can access the first item by reading index 0.

eg.

Code: Select all

prices = ba.getPrices()
firstPriceItem = prices(0)


BA does not calculate the lowest prices for you so you will need to loop through to find this unfortunately. It's not sorted or indexed.

[osknows]

1: The selection name of the 1st selection on the list.

2: The lowest value in the best available back-price column.
2b: The "row" address of that value

Thanks!

There are 4 methods I know of for search arrays in VBA but it depends on the size of the array and I'd guess that order is also important in context of BA so 1. and 2. are probably not applicable.

1. Copy to excel sheet first and search using Range/Find method
2. Sort the array from highest value to lowest then use Lbound and Ubound to find the max and min
3. Loop the array
4. Use Excel Worksheet functions

The following records the number of ticks for 3. & 4. for each of your questions above using a fictitious array 'strArray = .Range("A1:F17").Value'

Code: Select all

Public Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long

Sub test()

With Sheet1

strArray = .Range("A1:F17").Value

'1st name in array
strname = strArray(LBound(strArray), 1) 'may need to change 1 if name is not first column element

'Find Min - loop method (min in second column element)
tickStart = GetTickCount
LMin = strArray(1, 2)
For i = LBound(strArray) To UBound(strArray)
    If strArray(i, 2) < LMin Then
        LMin = strArray(i, 2)
        LRow = i
    End If
   
Next i
tickLoop = GetTickCount - tickStart

'Find Min - function method (min in second column element)
tickStart = GetTickCount
FMin = Application.WorksheetFunction.Min(Application.WorksheetFunction.Index(strArray, 0, 2))
FRow = Application.WorksheetFunction.Match(FMin, Application.WorksheetFunction.Index(strArray, 0, 2), 0)
tickFunction = GetTickCount - tickStart


MsgBox ("First Name: " & strname & Chr(10) & "Loop Method - " & tickLoop & " ticks, Min = " & _
LMin & " Row = " & LRow & Chr(10) & "Excel Function Method - " & tickFunction & " ticks, Min = " & _
FMin & " Row = " & FRow)

End With
End Sub


[Spike]

Thanks chaps - that's excellent.

I love this forum.