Why does this VBA code not work consistently?

  • A very simple query function that takes in a path for a source CSV file and a SQL statement as a string (I'm also transposing the data from the VBA function),



    This code works intermittently against a CSV files, some data is retrieved correctly and some is not. An example are these two CSV files - Small and Large. The following SQL query works perfectly on the Small file, but returns #VALUE on the Large file,
    SELECT birthYear FROM [File].


    It's definitely not a data limit/size issue as the Full file only contains 1800 rows. Incidentally if I wrap up the logic into a Sub rather than a UDF then it works perfectly without any errors,



    I am very confused, and would appreciate any pointers. I have also posted this question on Stackoverflow, but no answers yet unfortunately.

  • Re: Why does this VBA code not work consistently?


    The reason is that returned Variant arrays contain NULL records. So these need to be handled prior to returning values to the spreadsheet.