XLOOKUP behaviour seems to differ in VBA

[andyseaman]

I have a simple XLOOKUP in an excel sheet, XLOOKUP(1974, NFA.DATE, NFA.PANAMA). The two range names NFA.x refer to external files. This function works without any issues, even if the external files are not loaded.

I have what I thought was a near identical UDF which has the country as an input;

Function ESGDATA(yyear, ycountry) As Variant

Application.Volatile

Dim xcountry() As Variant

Dim xdate() As Variant

Dim xyear As Variant

xcountry = Range("NFA." & ycountry).Value

xdate = Range("NFA.DATE").Value

xyear = yyear

ESGDATA = Application.XLookup(xyear, xdate, xcountry)

End Function

Even though the function uses application.xlookup, which ought to behave in the same way as in the excel sheet, this function will only work if the external files referred to by NFA.x are loaded.

The first question is why is the behaviour of XLOOKUP different in that one works without the referenced files loaded and the VBA equivalent doesnt? Can it be forced to behave in the same way?

And secondly, if that cant be solved how do I get the reference of the file so I can load it if not already loaded. I'd obviously prefer not to have to do the latter if at all possible.

Andy  

[#2]

@andyseaman 

Maybe you will find here the information for it, hope that it helps you as an info.

Do...Loop Statement (Visual Basic)

https://docs.microsoft.com/en-gb/dotnet/visual-basic/language-reference/statements/do-loop-statement

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

[#3]

@andyseaman 

Add Infos:

How it works:

= VLOOKUP   (FORMULA)

Advantages:
+ when the data source is changed, the cell value is updated automatically

Disadvantage:
- When filtering, the cell values are recalculated
- Compared to pure cell values, increased storage requirements
- If the search term does not exist, #NV is output
- The first hit found is always displayed, so the search term must be unique! See error in column D when using the pure company number as a search term
- all cells must be formatted in the same data format
- Search criteria can be a maximum of 256 characters

Use Vlookup in the macro
The reference function can also be used as a macro. This works via the integration of the WorksheetFunction VLookup ("Search criterion", "Matrix", "Column area", "Area reference").

The main advantage over the conventional S-reference formula function is that you can carry out the calculation by clicking a button and then only the cell values, not the formulas, are in the cells. If you want to recalculate the cell values, just click the button again.

Advantages:
+ only values ​​are written into the cells
+ Macro processing faster than variant 2

Disadvantage:
- Offers only the same functions as the reference formula function
- If the search term does not exist, the macro processing aborts ("On Error Resume Next" prevents the abort by skipping the line of code ")
- If the data source is changed, the cell value is not updated automatically
- The first hit found is always displayed, so the search term must be unique!
- Search criteria can be a maximum of 256 characters

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

[#4]

Thanks for this Nikolino. It is the function XLOOKUP, rather than VLOOKUP, which is causing the issue. In VBA using application.xlookup the function does not work with external named ranges unless those files are open when the function is called. However, Xlookup does return a result even when those files are closed.

The problem I’m having is how to get the xlookup function to work in VBA. In fact the issue seems more to do with how external named ranges behave in VBA as I suspect I’d have the same issue if I used a different function.