So optimieren Sie eine VBA-Funktion in Excel

928
rvictordelta

Ich habe eine Funktion in VBA geschrieben und nachfolgend eine vereinfachte Version bereitgestellt. Grundsätzlich nimmt es ein Argument an, vlookupformuliert einen Wert in einem benannten Bereich im Arbeitsblatt mit dem Wert des Arguments, übergibt den Wert vlookedup an eine andere Funktion und gibt schließlich das Ergebnis zurück.

Ich verwende diese Funktion sehr oft, wie etwa 50.000 Mal in meinem Arbeitsbuch. Daher ist meine Arbeitsmappe ziemlich langsam zu berechnen.

Gibt es einige einfache Änderungen, die ich an dieser Funktion vornehmen könnte, um die Geschwindigkeit zu optimieren?

Lesbarkeit ist kein Problem, ich möchte, dass dieses Ding schneller läuft. Der Code muss jedoch in VBA bleiben.

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
1
Sind Sie sicher, dass vlookup tatsächlich für lange Zeit verantwortlich ist, oder kann es die "otherCustomFunction" sein? Máté Juhász vor 8 Jahren 0
otherCustomFunction hat fast sicher etwas damit zu tun, aber ich kann es in diesem Szenario nicht bearbeiten. Ich möchte nur die Suche und die variable Zuordnung optimieren. rvictordelta vor 8 Jahren 0
Diese Frage sollte auf codereview und nicht auf Superuser stehen Dirk Horsten vor 8 Jahren 0

2 Antworten auf die Frage

0
Dirk Horsten

Erste Strategie: Die Funktion selbst optimieren

Sollte die Geschwindigkeit verdoppeln

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

Dies liegt daran, dass Sie den Bereich mit dem Namen "LookupRange" nur einmal und nicht zweimal suchen und nur einmal und nicht zweimal die richtige Linie suchen.

Zweite Strategie: Abruf der Reichweite nur einmal im Voraus

Wahrscheinlich 4 mal so schnell

Wenn wir den Bereich im Code abrufen, der die yieldFunktion verwendet, müssen wir das nur einmal tun

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

Es gibt eine Variante dieser Strategie, bei der Sie Lookup außerhalb aller Routinen deklarieren, wie ich es mit dem nachstehenden Wörterbuch mache.

Dritte Strategie: Tragen Sie alle relevanten Werte in ein Wörterbuch ein

Eine Größenordnung schneller, wenn Sie Yieldsehr oft anrufen .

  • Sie suchen im benannten Bereich nach
  • Sie verlangen alle Werte gleichzeitig von Excel
  • Sie suchen die Names in einem Wörterbuch, was viel effizienter ist als das Nachschlagen in einem Bereich

Dies ist der Code:

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
Wenn dies nicht ausreicht, sagen Sie mir, welche Dimensionen von LookupRange verwendet werden, wie oft Sie diese Funktion in einem Verarbeitungszyklus aufrufen und wie viele verschiedene `Name's normalerweise in einem Verarbeitungszyklus nachschlagen. Dirk Horsten vor 8 Jahren 0
Dirk, ich schätze die gründliche Antwort. Leider ist "Preis" eine kontinuierliche Variable, so dass die Berechnung des Universums der "Ertrag" -Ausgaben nicht durchführbar ist. Ich gehe mit Ihrer ersten Antwort und minimiere meine Nachforschungen. Hoffentlich findet jemand anderes den Rest Ihrer Antwort hilfreich! rvictordelta vor 8 Jahren 0
0
Raystafarian

Ein paar Dinge, die ich tun würde - 

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

Wenn Sie Argumente übergeben, übergeben Sie sie standardmäßig ByRef. Dies ist langsamer als ByVal, und Sie müssen die Referenz nicht übergeben ByVal.

Ich bin mir nicht sicher, dass matches viel schneller ist, vlookupaber indem matchSie Ihre Prozesse um die Hälfte reduzieren und nur auf die Zeile verweisen, die Sie benötigen.

Ich habe die Variablen auch in Standard-VBA-Namenskonventionsnamen konvertiert .

Sie benötigen auch keine Application.runfür das Aufrufen Ihres Makros. Stellen Sie sicher, dass auch die Argumente ByVal übergeben werden

Ray, du musst `LookupRange` für die Übereinstimmung anpassen, wenn` foundRow` definiert wird rvictordelta vor 8 Jahren 0

Verwandte Probleme