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

ivorhorton

New Member
Joined
Jan 18, 2019
Messages
7
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.

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

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,897
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

New Member
Joined
Jan 18, 2019
Messages
7
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,967
Office Version
365
Platform
Windows
This will help
Code:
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,967
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,086,119
Messages
5,387,938
Members
402,090
Latest member
thomastsiakis

Some videos you may like

This Week's Hot Topics

Top