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

800
rvictordelta

Я написал функцию в VBA и предоставил упрощенную версию ниже. По сути, он принимает аргумент, преформирует a 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
1
Вы уверены, что vlookup действительно отвечает за долгое время, или это может быть "otherCustomFunction"? Máté Juhász 8 лет назад 0
otherCustomFunction почти наверняка имеет к этому какое-то отношение, но это не то, что я могу редактировать в этом сценарии. Я просто хотел бы оптимизировать поиск и назначение переменных. rvictordelta 8 лет назад 0
Этот вопрос должен быть на codereview, а не на суперпользователя Dirk Horsten 8 лет назад 0

2 ответа на вопрос

0
Dirk Horsten

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
Если этого недостаточно, скажите мне, каковы размеры LookupRange, сколько раз вы вызываете эту функцию в одном цикле обработки и сколько разных имен вы обычно просматриваете в одном цикле обработки. Dirk Horsten 8 лет назад 0
Дирк, я ценю тщательный ответ. К сожалению, «Цена» - это непрерывная переменная, поэтому вычисление совокупности выходов «Доходности» невозможно. Я иду с вашим первым ответом и минимизирую количество поисков. Надеюсь, кто-то еще найдет ваш отзыв полезным! rvictordelta 8 лет назад 0
0
Raystafarian

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

Рэй, тебе нужно изменить размер «LookupRange», чтобы совпадение работало при определении «foundRow» rvictordelta 8 лет назад 0

Похожие вопросы