To be sure I understand, you have a cell with the formula =A1:G1 in it. As observed and explained, this formula will return one value to the cell. When you pass that cell to a UDF, what your UDF's variable contains will depend on exactly what data type the argument is.If I target that cell with a VBA function, or with an external plug-in, whatsoever, what I will have as information?
- the value of F3 in the spreadsheet?
- the string "=A1:G1"?
a) If the argument is typed as a numeric data type (double, long, etc.), then the variable will contain that single value stored in the cell.
b) If the argument is typed as string, then the variable will contain that value converted to a text string.
c) If the argument is typed as range or variant, then the argument will contain a Range object referencing that cell. Your UDF will then be able to read any of the property values that apply to that single cell range object (range object help file with the list of properties: https://docs.microsoft.com/en-us/off....Range(object) ). This obviously includes the cell's value (the .value property), but would also include the formula string (.formula property) and a whole list of other properties.
The easiest way to see what you would have in different scenarios is to use VBA's debugging tools (specifically the locals window http://www.cpearson.com/Excel/DebuggingVBA.aspx ). As I explain in this tutorial (https://www.excelforum.com/tips-and-...uild-udfs.html ), one of the first things I do when developing a UDF is to add a stop statement to the top of the procedure so that it will enter debug mode. Once you get into debug mode, you can look in the locals window and see exactly what your argument contains. Hope that helps.
Bookmarks