Databases
StackOverflow2013
Postsdbo
POExcel VBA - strange behaviour and poor performance when UDF wraps VLOOKUP
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel VBA - strange behaviour and poor performance when UDF wraps VLOOKUP
    primarykey
    data
    text
    <p>I want to write a user defined function that wraps VLOOKUP. All it requires is a reference to the column that data should be imported from, and it will execute a VLOOKUP assuming that the IDs are in column A and there are fewer than 3000 rows to search.</p> <pre><code>Function AutoVlookup( importFrom As Range) As Variant Dim arg1, arg2, arg3, arg4 As Variant Dim arg1Str, arg2Str As String arg1Str = "$A" &amp; Application.Caller.row 'get ID arg1 = Application.Caller.Parent.Range(arg1Str) arg2Str = "$A$1:$" &amp; Split(cells(1, importFrom.column).Address, "$")(1) &amp; "$3000" arg2 = importFrom.Parent.Range(arg2Str) 'get range to search in (in other workbook) arg3 = importFrom.column 'get column to return arg4 = False 'exact match AutoVlookup = Application.WorksheetFunction.VLookup(arg1, arg2, arg3, arg4) End Function </code></pre> <p>I am running into two problems. </p> <p>Firstly, the execution time is terrible. It takes several minutes to run this formula 1000 times, whereas the same VLOOKUP not wrapped in a UDF is very fast. </p> <p>Secondly, when I first fill a column with <code>=AutoVLookup(&lt;column in other workbook&gt;)</code> every row will incorrectly show the same result until something triggers them to recalculate.</p> <p>What am I doing wrong?</p> <hr> <p>edit, answer:</p> <p>Here is the code I made using advice from Santosh and Charles:</p> <pre><code>Function EasyLookup(importFrom As Range) As Variant Application.Volatile False 'does not recalculate whenever cells on sheet change Dim Id As String Dim match As Integer Dim importColumnAddress As String Dim initialCalculationSetting As XlCalculation Dim initialScreenUpdateMode As Boolean Dim initialEnableEventsMode As Boolean 'saving the settings, to be reverted later initialScreenUpdateMode = Application.ScreenUpdating initialCalculationSetting = Application.Calculation initialEnableEventsMode = Application.EnableEvents 'changes screen update and calculation settings for performance Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False 'find ID on formula's sheet Id = Application.caller.Parent.Cells(Application.caller.row, 1).value 'find row with ID on column A of data source sheet match = Application.WorksheetFunction.match(Id, importFrom.Parent.Range("$A$1:$A$4000"), 0) 'assumes no more than 4000 rows. 'retrieve value from importFrom's column, on the row where ID was found importColumnAddress = Split(Cells(1, importFrom.column).Address, "$")(1) importColumnAddress = importColumnAddress &amp; ":" &amp; importColumnAddress EasyLookup = Application.WorksheetFunction.Index(importFrom.Parent.Range(importColumnAddress), match) 'revert performance tweaks Application.ScreenUpdating = initialScreenUpdateMode Application.Calculation = initialCalculationSetting Application.EnableEvents = initialEnableEventsMode End Function </code></pre> <p>It is much faster because it does not read in as much data, as it uses INDEX/MATCH rather than VLOOKUP. It also does not recalculate every time a cell in the sheet changes.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USWoodenKitty
    1. USWoodenKitty
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COQuick note: When you do "Dim arg1Str, arg2Str As String" arg1str is being declared as a variant, not a string ^^
      singulars
      1. POExcel VBA - strange behaviour and poor performance when UDF wraps VLOOKUP
      1. USaevanko
    2. COReally? So the variable type only applies to the last one in the list? :|
      singulars
      1. POExcel VBA - strange behaviour and poor performance when UDF wraps VLOOKUP
      1. USWoodenKitty
    3. COIt applies to whichever variable "As" is contiguous with (so yeh, last in your case).
      singulars
      1. POExcel VBA - strange behaviour and poor performance when UDF wraps VLOOKUP
      1. USLance Roberts
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload