-2

In this particular answer regarding faster way of vlookup - https://stackoverflow.com/a/18656809/8036266

A faster way of vlookup is mentioned, defined as vbalookup. The code for this is working perfectly fine. However, it is executing everytime I change any cell in the workbook. Can somebody please tell me what the issue could be?

Function vbalookup(lookupRange As Range, refRange As Range, dataCol As Long) 
As Variant
Dim dict As New Scripting.Dictionary
Dim myRow As Range
Dim I As Long, J As Long
Dim vResults() As Variant

' 1. Build a dictionnary
For Each myRow In refRange.Columns(1).Cells
' Append A : B to dictionnary
dict.Add myRow.Value, myRow.Offset(0, dataCol - 1).Value
Next myRow

' 2. Use it over all lookup data
ReDim vResults(1 To lookupRange.Rows.Count, 1 To lookupRange.Columns.Count) 
As Variant
For I = 1 To lookupRange.Rows.Count
For J = 1 To lookupRange.Columns.Count
  If dict.Exists(lookupRange.Cells(I, J).Value) Then
    vResults(I, J) = dict(lookupRange.Cells(I, J).Value)
  End If
Next J
Next I

vbalookup = vResults
End Function
Abhinav Sharma
  • 19
  • 5
  • You've probably got the code executing unconditionally in a `worksheet_change` event handler, but it's really, really hard to tell without seeing your code. [Edit] your post to include your code, then while you're waiting for a response, take the [tour] and read the [help] to gain a better understanding of how to help [so] help you. – FreeMan Sep 12 '18 at 19:04
  • I am using a dynamic named range (made using offset) in the refRange section of the lookup. I noticed that if I replace this with a static range, this issue does not arise. Why is the macro getting triggered if I've a dynamic range? – Abhinav Sharma Sep 12 '18 at 19:06
  • 4
    how are you using that dynamic range? Things like OFFSET,INDIRECT are volatile and will recalc every time Excel recalcs. – Scott Craner Sep 12 '18 at 19:08
  • 1
    [Edit] your code into your original post. Otherwise people are just making guesses as to what your code is doing. You'll get a workable response much faster if you do that. – FreeMan Sep 12 '18 at 19:11
  • If the reference range has empty rows, the macro does not work. So I've used offset to make a dynamic range. There's a counta formula in the offset height category to count non empty cells. The issue is that this macro is getting executed if any cell is changed in the workbook. These cells have nothing to do with the dynamic range or cells being affected by the vbalookup macro – Abhinav Sharma Sep 12 '18 at 19:13
  • @FreeMan I have added the code – Abhinav Sharma Sep 12 '18 at 19:23
  • That shouldn't execute on its own. Include the call site code - likely in a `worksheet_change` procedure. – FreeMan Sep 12 '18 at 19:26
  • 1
    @FreeMan it is the fact that the OP is using OFFSET to return a dynamic range to the UDF. This is causing the UDF to become volatile. OP: Please show how you are calling it so we can help give you a non-volatile approach if possible. – Scott Craner Sep 12 '18 at 19:29
  • @ScottCraner - ah, gotcha. Volatile and, I'd imagine, rather slow! – FreeMan Sep 12 '18 at 19:34
  • Basically it is not the code that is doing the recalc but the fact that you are using OFFSET. OFFSET will recalc EVERY TIME Excel recalcs. So currently we cannot help you as you have not provided the method by which you call the UDF. – Scott Craner Sep 12 '18 at 19:40
  • @ScottCraner i am using vbalookup(lookuprange, Dynamic range, column) as an array formula. This dynamic range is made using offset(reference,,,counta(range),width). This dynamic range is causing the macro trigger everytime I change any cell. Works perfectly if I replace this with a fixed range. – Abhinav Sharma Sep 12 '18 at 19:40
  • Need more specifics on the named range part like actual references. You can replace the OFFSET with and INDEX formula `=reference:INDEX(referencecolumn,COUNTA(range))` – Scott Craner Sep 12 '18 at 19:43
  • @ScottCraner AdjRange : =OFFSET('Country Data'!$AE$2,,,COUNTA('Country Data'!$A$2:$A$300),2) VBA lookup formula: =vbalookup('Country Data'!S2:S118735,AdjRangeTest,2) – Abhinav Sharma Sep 12 '18 at 19:52
  • `='Country Data'!$AE$2:INDEX('Country Data'!$AF:$AF,COUNTA('Country Data'!$A$2:$A$300)) ` – Scott Craner Sep 12 '18 at 19:55
  • @ScottCraner same issue with the dynamic range made using index :( – Abhinav Sharma Sep 12 '18 at 20:17
  • Then someone smarter than me, and that is not hard to find, will need to finish, as I have exhausted my meager knowledge. Sorry I could not help. – Scott Craner Sep 12 '18 at 20:19
  • @ScottCraner Got it! I had other named dynamic ranges made using offset in the workbook. Removing them has solved the issue. Thanks! – Abhinav Sharma Sep 12 '18 at 20:25
  • There few, very few, times that I would use OFFSET over INDEX for this exact reason. – Scott Craner Sep 12 '18 at 20:38

1 Answers1

0

I was using a dynamic range made using offset formula. Redefining the range using a non-volatile approach: using index, solved the issue. Other dynamic ranges present in the workbook (not being used in the vbalookup formula) were also triggering the macro. I had to remove them as well.

Thanks to @ScottCraner for taking his time to solve this issue :)

Abhinav Sharma
  • 19
  • 5