Como otimizar uma função do VBA no Excel

Question

Eu escrevi uma função no VBA e forneci uma versão simplificada abaixo. Basicamente, ele recebe um argumento, pré-forma um vlookup em um intervalo nomeado na planilha usando o valor do argumento, passa o valor de vlookedup para outra função e finalmente retorna o resultado.

Eu uso muito essa função ... como 50.000 vezes em minha pasta de trabalho. Como resultado, minha pasta de trabalho é bem lenta para calcular.

Há algumas mudanças simples que eu poderia fazer nessa função para otimizá-la em termos de velocidade?

A legibilidade não é uma preocupação, eu só quero fazer essa coisa correr mais rápido. O código deve permanecer no 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

Primeira estratégia: otimizar a função em si

Deve dobrar a velocidade

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

Isso porque você só pesquisa o intervalo com o nome "LookupRange" uma vez em vez de duas vezes e procura apenas a linha certa uma vez em vez de duas vezes.

Segunda estratégia: recuperar o intervalo apenas uma vez adiantado

Provavelmente 4 vezes mais rápido

Se recuperarmos o intervalo no código que usa a função yield , só precisamos fazer isso uma vez

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

Há uma variante dessa estratégia na qual você declara Lookup fora de todas as rotinas, como eu faço com o dicionário abaixo.

Terceira estratégia: coloque todos os valores relevantes em um dicionário

Uma ordem de grandeza mais rápida se você chamar Yield MUITO vezes.

• Você consulta o intervalo nomeado
• Você solicita todos os valores do excel de uma só vez
• Você procura o Name s em um dicionário, que é muito mais eficiente do que procurar em um intervalo

Este é o código:

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

Algumas coisas que eu faria -

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

Quando você passa argumentos você, por padrão, os passa ByRef que é mais lento que ByVal e vendo como você não precisa da referência basta passá-los ByVal .

Não tenho certeza de que match é muito mais rápido que vlookup , mas usando match você reduz seus processos pela metade e apenas faz referência à linha de que precisa.

Também converti as variáveis para Convenção de nomenclatura padrão do VBA nomes.

Você também não precisa do Application.run para chamar sua macro. Certifique-se de que também esteja passando argumentos ByVal