☰

第一个策略：优化函数本身

tag-icon • tag-icon microsoft-excel worksheet-function vba optimize
第一个策略：优化函数本身

我在 VBA 中编写了一个函数，下面提供了一个简化版本。基本上，它接受一个参数，vlookup使用该参数的值在工作表中的命名范围上执行，将 vlookedup 值传递给另一个函数，最后返回结果。

我经常使用这个函数，在我的工作簿中用了 50,000 次左右。因此，我的工作簿计算起来很慢。

我可以对此函数进行一些简单的更改来优化其速度吗？

可读性不是问题，我只想让这个东西运行得更快。不过代码必须保留在 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

答案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 中获取所有值
  • 你可以在字典中查找Names，这比在范围内查找要高效得多

这是代码：

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

答案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

当你传递参数时，默认情况下，你传递它们按引用比拜瓦尔并且你不需要参考只需将它们传递下去ByVal。

我不确定match是否比这更快，vlookup但是通过使用match您可以将流程减少一半并仅引用所需的行。

我还将变量转换为标准 VBA 命名约定名字。

你也不需要Application.run调用你的宏。确保它也传递参数拜瓦尔

相关内容