Sometimes I like to display numbers along with text in the same cell. To do this, I custom format the cell to something like 0.00" test", e.g. a cell A1 with formula =PI(), but formatted with custom format 0.00" test" would return a displayed result of 3.14 test.
Crucially, the value of the cell is unchanged by the formatting - you can still do =A1 * 3 in another cell and get the result - since the value of A1 is still Pi, only it's display has changed.
For a UDF that returns a numerical value (in my case, Long, but it could be any number), is there a way of returning a cell such that it is displayed 1 way, but it's actual value (.Value2 in VBA I believe) is a number, not text.
I've tried Format( in VBA, but it returns a text string. I would just format my cell how I want it manually, but as you can see from the below code, the formatting is dependent on intermediate results (I'm trying to return the value formatted with the time the calculation took).
UDF_RESULT = Format(valueResult.Length, IIf(tElapsed < timeout, "0" & " """ & Round(tElapsed, 2) & "s""", "0"))
This would be easy to do with a Macro, but within a UDF it's harder. I could declare all of the intermediate values at a module level, then a Worksheet_Calculate() macro can access those values and apply the custom formatting, but a UDF-wrapped approach would be much better.
No.
You're confusing a cell's value with its number format. A UDF can compute a value, and the cell is free to format that value as needed.
So if a UDF returns a number, the cell's value is the result of that function - a number.
Just format the cell as needed. A function doesn't format anything.