I have two VBA Excel functions: IdentifyOutliers and OutlierString. OutlierString calls IdentifyOutliers. IdentifyOutliers finds outliers in a named Excel range (Data_Table_1) in the same workbook. OutlierString then returns a sentence naming the outliers, such as “The outliers are 45, 65.” When I place =OutlierString(Data_Table_1) in a cell and calculate sheet I get value error.

I use ExecuteExcel4Macro function with index function since index can retrieve data from a closed workbook without the need of openning it. But, I want a dynamic function which retrieves the file path, the workbook name, sheet name and range info from a bunch of cells to get the values from the closed workbook.