Как оптимизировать функцию VBA в Excel

Question

Я написал функцию в VBA и предоставил упрощенную версию ниже. По сути, он принимает аргумент, предварительно vlookup в именованном диапазоне на листе, используя значение аргумента, передает значение vlookedup другой функции и, наконец, возвращает результат.

Я часто использую эту функцию, например, 50000 раз в своей книге. В результате моя рабочая тетрадь довольно медленно вычисляется.

Могу ли я внести некоторые простые изменения в эту функцию, чтобы оптимизировать ее по скорости?

Читаемость не имеет значения, я просто хочу, чтобы эта вещь работала быстрее. Код должен оставаться в 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

Answer 1

Первая стратегия: оптимизировать саму функцию

Должен удвоить скорость

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

Это потому, что вы ищите диапазон с именем "LookupRange" только один раз, а не дважды, и вы ищете правую строку только один раз, а не дважды.

Вторая стратегия: получить диапазон только один раз авансом

Вероятно, в 4 раза быстрее

Если мы получим диапазон в коде, который использует функцию yield , мы должны сделать это только один раз

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

Есть вариант этой стратегии, где вы объявляете Lookup вне всех подпрограмм, как я делаю со словарем ниже.

Третья стратегия: поместите все соответствующие значения в словарь

На порядок быстрее, если вы звоните Yield ОЧЕНЬ часто.

• Вы смотрите названный диапазон
• Вы спрашиваете все значения из Excel сразу
• Вы ищите Name в словаре, что намного эффективнее, чем поиск в диапазоне

Это код:

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

Answer 2

Несколько вещей, которые я бы сделал -

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

Когда вы передаете аргументы, вы по умолчанию передаете им ByRef, который медленнее, чем ByVal, и, видя, что вам не нужна ссылка, просто передайте им ByVal .

Я не уверен, что match происходит намного быстрее, чем vlookup но с помощью match вы сокращаете свои процессы вдвое и просто ссылаетесь на нужную вам строку.

Я также преобразовал переменные в стандартные имена соглашений именования VBA .

Вам также не нужен Application.run для вызова вашего макроса. Убедитесь, что также передаются аргументы ByVal