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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!