What's the proper way to get another cell's display text in VBA?

[ivorhorton]

For a number 42117, it can be displayed as 42117, or 23-Apr-15, or 42,117.00 etc. I want to reference these displayed text appearance in another cell through VBA. How can I get it? I have below customized function and it uses the Text property. It still not work properly and hope someone can point me the right direction. Thanks in advance.

Public Function DisplayText(r As Range)
    DisplayText = r.Text
End Function

 

[mikerickson]

How does it not work properly? The UDF looks fine.
I would add a declaration that the function return a string and to return the top left cell of r.

 

[ivorhorton]

Yes, that will make the function more robust. But for now, since the range always has a single cell, I'll not improve it at the moment.

I said it doesn't work properly, is because I did some test. It works for the first time. Later when I changed the source's display, the destination doesn't change accordingly. I pressed F9 to recalculate and it doesn't work. A few minutes ago I just found I have to double click the destination cell and make it in the formula editing mode, then I press ctrl + enter it will recalculate behave as I expected. How can I solve this issue?

 

[Fluff]

This will help

Public Function DisplayText(r As Range)
   Application.Volatile
   DisplayText = r.Text
End Function

but you will still need to press F9, as changing the format will not trigger the function.

 

[ivorhorton]

Thanks! It works now.

 

[Fluff]

You're welcome & thanks for the feedback