ActiveSheet Hyperlinks Add don t show Text To Display

Question

I came across an unexpected behavior of the command ActiveSheet.Hyperlinks.Add in Excel VBA

If I use the following code

Sub Hyperlinks_Test()
Dim Link As String
Dim Text As String
Link = "www.google.com"
Text = "Link To Google"
ThisWorkbook.Worksheets("Sheet1").Activate

Cells(2, 2).Value = 3.14
ActiveSheet.Hyperlinks.Add Cells(2, 2), Address:="", SubAddress:=Link, TextToDisplay:=Text
Cells(3, 2).Value = #3/31/2023#
ActiveSheet.Hyperlinks.Add Cells(3, 2), Address:="", SubAddress:=Link, TextToDisplay:=Text
Cells(4, 2).Value = "String"
ActiveSheet.Hyperlinks.Add Cells(4, 2), Address:="", SubAddress:=Link, TextToDisplay:=Text
End Sub

I only get the right answer in cell (4,2), which has the text "Link To Google" and a hyperlink to www.google.com. Before I used the command "ActiveSheet.Hyperlinks.Add," if the cell contained a number or a date, the output was the number or the date instead of the title "Link To Google," but it correctly contained a hyperlink to www.google.com. (https://i.stack.imgur.com/RzBEe.jpg)

Is it a bug? ".Hyperlinks.Add "command or did I use the function incorrectly?

Answer 1

Here's how Excel functions. Not a bug, that is.
Texttodisplay does not function when a cell contains a number value that is "text" like or a number value.
You will notice that text to show is disabled if you manually attempt to insert a hyperlink in these cells in Excel.

Here, we first add the hyperlink before changing the cell's value.