Rewrite a function to read a list from external file

Hello Excel Forum!

I have a macro that uses the following function.

Please Login or Register  to view this content.

The way I am calling the function is like this:

Please Login or Register  to view this content.

I need to rewrite this function to make it more portable with other people.

Instead of getting the list of flagged account numbers from Select Case, I need it to pull the list from an external file. Let's call the file Z:\Flagged.csv for the sake of convenience. The file will be in .CSV format. Account numbers that need to trigger the MsgBox will be in the A column, one account number per row like in my example: Flagged.csv. There will be data in the B column that needs to be ignored.

Is there a simple way to do this? I suspect there is but I've never worked with making a macro read data from another file and my search results are proving confusing.

Thank you in advance for any help that you can offer!

Please Login or Register  to view this content.

This is what my non-functioning rewrite is looking like so far. Firstloop is a Boolean set to true at the top of the macro in my attempt to not open and reread the file for every account number it tries to compare. I have also tried the following loop without success.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

This is my revised call to the function.

Current issues:

Firstloop seems to empty when I call the function
Type mismatch error on the line: Do Until i = UBound(FlaggedAccts) and/or For i = 1 To UBound(FlaggedAccts)
I know I will need to split the FlaggedAccts array as it will contain the string "1234, Reason" when it's working but I'm still not getting the framework to behave as expected.
I have little to no idea what I'm doing.

I am closing in on my own solution!

First, to make Firstloop keep it's data, I changed it's declaration from DIM to Private. Other than that function call remains the same as above. Here is my almost working function.

Please Login or Register  to view this content.

Current issues:

I suck with arrays. My array FlaggedAccts has the info, just need to figure out how to loop through it properly in this part.

Please Login or Register  to view this content.

I am still getting a type mismatch on the For loop.