How to optimize a VBA function in Excel

Question

I wrote a function in VBA and have provided a simplified version below. Basically, it takes an argument, preforms a vlookup on a named range in the sheet using the value of the argument, passes the vlookedup value to another function, and finally returns the result.

I use this function a lot.. like 50,000 times in my workbook. As a result, my workbook is pretty slow to calculate.

Are there some simple changes I could make to this function to optimize it for speed?

Readability is not a concern, I just want to make this thing run faster. The code must stay in VBA though.

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

First strategy: optimize the function itself

Should double the speed

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

This because you only lookup the range with name "LookupRange" once instead of twice and you only look for the right line once instead of twice.

Second strategy: retrieve the range only once upfront

Probably 4 times as fast

If we retrieve the range in the code that uses the yield function, we only have to do that once

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

There is a variant of this strategy where you declare Lookup outside all routines, like I do with the dictionary below..

Third strategy: Put all relevant values in a dictionary

An order of magnitude faster if you call Yield VERY often.

• You look up the named range
• You ask all values from excel at once
• You look up the Names in a dictionary, which is way more efficient than a looking up in a range

This is the 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

Answer 2

A few things I'd do -

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

When you pass arguments you, by default, pass them ByRef which is slower than ByVal and seeing as how you don't need the reference just pass them ByVal.

I'm not sure match is much quicker than vlookup but by using match you cut your processes down by half and just reference the row you need.

I also converted the variables to Standard VBA naming convention names.

You also don't need the Application.run for calling your macro. Make sure that is also passing arguments ByVal