Jak zoptymalizować funkcję VBA w programie Excel


1

Napisałem funkcję w VBA i poniżej przedstawiłem uproszczoną wersję. Zasadniczo wymaga argumentu, wykonuje preformat vlookupna nazwanym zakresie w arkuszu przy użyciu wartości argumentu, przekazuje wartość vlookedup do innej funkcji, a na koniec zwraca wynik.

Korzystam z tej funkcji bardzo często ... jak 50 000 razy w moim skoroszycie. W rezultacie mój skoroszyt jest dość powolny do obliczenia.

Czy mogę wprowadzić kilka prostych zmian w tej funkcji, aby zoptymalizować ją pod kątem szybkości?

Czytelność nie jest problemem, chcę tylko, aby ta rzecz działała szybciej. Kod musi jednak pozostać w VBA.

Public Function Yield(Name As String, Price As Double)
    Dim DDate As Double
    Dim ConversionFactor As Double
    DDate = Application.WorksheetFunction.VLookup(Name, Range("LookupRange"), 3, 0)
ConversionFactor = Application.WorksheetFunction.VLookup(Name, Range("LookupRange"), 7, 0)
Yield = 100 * Application.Run("otherCustomFunction",DDate,ConversionFactor,Price)
End Function

czy jesteś pewien, że rzeczywiście vlookup jest odpowiedzialny za długi czas, czy może to być „otherCustomFunction”?
Máté Juhász

otherCustomFunction prawie na pewno ma coś z tym wspólnego, ale nie mogę tego edytować w tym scenariuszu. Chciałbym po prostu zoptymalizować wyszukiwanie i przypisanie zmiennych.
rvictordelta

To pytanie powinno dotyczyć widoku kodu, a nie superużytkownika
Dirka Horstena

Odpowiedzi:


0

Pierwsza strategia: zoptymalizuj samą funkcję

Powinien podwoić prędkość

Public Function Yield(Name As String, Price As Double)
    Dim Lookup As Range, rw As Integer
    Set Lookup = Range("LookupRange")
    rw = Application.WorksheetFunction.Match(Name, Lookup.Resize(ColumnSize:=1), 0)

    Yield = 100 * Application.Run("otherCustomFunction", Lookup.Cells(rw, 3), Lookup.Cells(rw, 7), Price)
End Function

Wynika to z tego, że wyszukujesz zakres o nazwie „LookupRange” tylko raz zamiast dwa razy i tylko raz szukasz właściwej linii zamiast dwa razy.

Druga strategia: odzyskaj zasięg tylko raz z góry

Prawdopodobnie 4 razy szybciej

Jeśli pobieramy zakres w kodzie używającym yieldfunkcji, musimy to zrobić tylko raz

Public Function Yield(Lookup As Range, Name As String, Price As Double)
    rw = Application.WorksheetFunction.Match(Name, Lookup.Resize(ColumnSize:=1), 0)

    Yield = 100 * Application.Run("otherCustomFunction", Lookup.Cells(rw, 3), Lookup.Cells(rw, 7), Price)
End Function

Public Sub CallingRoutine()
    Dim Lookup As Range, rw As Integer
    Set Lookup = Range("LookupRange")

    ' Some code

    For Each someItem In someSet
        Dim amount As Double, Name As String, Price As Double

        ' Some code to deter;ine name and price

        amount = Yield(Lookup, Name, Price)

        ' Some code that used the yield
    Next someThing
End Sub

Istnieje wariant tej strategii, w którym zadeklarujesz wyszukiwanie poza wszystkimi procedurami, tak jak w przypadku poniższego słownika.

Trzecia strategia: umieść wszystkie istotne wartości w słowniku

O rząd wielkości szybciej, jeśli Yieldczęsto dzwonisz do BARDZO.

  • Sprawdzasz nazwany zakres
  • Pytasz wszystkie wartości od Excela naraz
  • Wyszukiwanie Namesłownika odbywa się w słowniku, który jest o wiele bardziej wydajny niż wyszukiwanie zakresu

To jest kod:

Public Function Yield(Name As String, Price As Double)
    If LookDict Is Nothing Then
        Set LookDict = New Dictionary

        Dim LookVal As Variant, rw As Integer, ToUse As ToUseType
        LookVal = Range("LookupRange").Value

        For rw = LBound(LookVal, 1) To UBound(LookVal, 1)
            Set ToUse = New ToUseType
            ToUse.Row3Val = LookVal(rw, 3)
            ToUse.Row7Val = LookVal(rw, 7)
            LookDict.Add LookVal(rw, 1), ToUse
        Next rw
    End If

    Set ToUse = LookDict.Item(Name)
    Yield = 100 * Application.Run("otherCustomFunction", _
                  ToUse.Row3Val, ToUse.Row7Val, Price)
End Function

Public Sub CallingRoutine()
    ' Some code

    For Each someItem In someSet
        Dim amount As Double, Name As String, Price As Double

        ' Some code to deter;ine name and price

        amount = Yield(Name, Price)

        ' Some code that used the yield
    Next someThing
End Sub

Jeśli to nie wystarczy, powiedz mi, jakie są wymiary LookupRange, ile razy wywołujesz tę funkcję w jednym cyklu przetwarzania i ile różnych Namezwykle przeglądasz w jednym cyklu przetwarzania.
Dirk Horsten

Dirk, doceniam dokładną odpowiedź. Niestety „cena” jest zmienną ciągłą, dlatego obliczenie wszechświata wyników „Wydajność” jest niewykonalne. Idę z twoją pierwszą odpowiedzią i minimalizuję moje wyszukiwania. Mam nadzieję, że ktoś inny uzna resztę twojej odpowiedzi za pomocną!
rvictordelta

0

Kilka rzeczy, które bym zrobił -

Option Explicit

Public Function Yield(ByVal lookupName As String, ByVal price As Double)
    Dim dDate As Double
    Dim conversionFactor As Double
    Dim foundRow As Long
    foundRow = Application.WorksheetFunction.Match(lookupName, Range("LookupRange"))
    dDate = Range("lookuprange").Cells(foundRow, 3)
    converstionfactor = Range("LookupRange").Cells(foundRow, 7)
    Yield = 100 * otherCustomFunction(dDate, conversionFactor, price)
End Function

Gdy przekazujesz argumenty, domyślnie przekazujesz je ByRef, który jest wolniejszy niż ByVal i widząc, że nie potrzebujesz referencji, po prostu je przekaż ByVal.

Nie jestem pewien, czy matchjest to znacznie szybsze niż, vlookupale używając matchcięcia skrócisz procesy o połowę i po prostu odniesiesz się do potrzebnego wiersza.

Przekształciłem również zmienne na standardowe nazwy konwencji VBA .

Nie potrzebujesz również Application.runwywoływania makra. Upewnij się, że przekazuje również argumenty ByVal


Ray, musisz zmienić rozmiar, LookupRangeaby dopasowanie działało podczas definiowaniafoundRow
rvictordelta
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.