Hyperlink in Excel not Clickable

Question

In my Excelsheet, i use a table with datasource from sql-server. One of the column includes the phone-number, another one the mailaddress. Now i want to create a hyperlink like tel:09999999 or mailto:test@test.com to open the default windows-application for starting a call or writeing an email. The format of the cell is general.

Unfortunally the hyperlink comes as plain-text not as hyperlink. I see the formula like =hyperlink("tel:09999999";"09999999"). When I click in the cell, press the [F2]-key and press return, then excel change the cell-value to a "correct" hyperlink.

The second idea I trying was to write a macro for all this cells. but the performance is not ok, I have about 20000 rows with 5 columns, so excel run about 1minute only for creating the hyperlink. The Code looks like:

lastrow = ActiveSheet.Cells(Cells.Rows.Count, 1).End(xlUp).Row
For i2 = 4 To lastrow
    'Phone1
    Worksheets("Report").Cells(i2, 11).Select
    If Len(Selection.Value) > 0 Then
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="tel:" + CStr(Selection), TextToDisplay:=CStr(Selection)
    End If

    'Phone2
    Worksheets("Report").Cells(i2, 12).Select
    If Len(Selection.Value) > 0 Then
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="tel:" + CStr(Selection), TextToDisplay:=CStr(Selection)
    End If

    'Phone3
    Worksheets("Report").Cells(i2, 13).Select
    If Len(Selection.Value) > 0 Then
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="tel:" + CStr(Selection), TextToDisplay:=CStr(Selection)
    End If
    'Mail1
    Worksheets("Report").Cells(i2, 14).Select
    If Len(Selection.Value) > 0 Then
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="mailto:" + CStr(Selection), TextToDisplay:=CStr(Selection)
    End If

    'Mail2
    Worksheets("Report").Cells(i2, 15).Select
    If Len(Selection.Value) > 0 Then
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="mailto:" + CStr(Selection), TextToDisplay:=CStr(Selection)
    End If
Next i2

Are you sure about [`;`]? Original (USA) version of formula is using a [`,`] as a separator. — Maciej Los, May 29 '18 at 06:18
@MaciejLos `;` is the european way as `,` is the normal decimal seperator. — Luuklag, May 29 '18 at 07:23
You realise there are a number of adjustments you can make to speed up your code? http://www.cpearson.com/excel/optimize.htm And ***avoiding*** [select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) should be first on your list. You should see significant improvement in speed. — QHarr, May 29 '18 at 08:30
replacing of the select-statements with a range variable helps a lot! thank for this cool idea! but i would like to have more speed! :) also whlie the loop is running the excel aplication "freeze" until the end of the loop. — Thomas, May 29 '18 at 12:06

score 0 · Answer 1 · answered May 30 '18 at 10:13

What i was trying to point out is that there's small different between Office's versions for every country. In other words, they uses different settings, such as date settings, separators and finally... different formulas.

For example, a "standard" (original, universal) formula for hyperlink is:

=HYPERLINK("tel:09999999","09999999")

and local formula, which we can use in Polish version of Excel application is:

=HIPERŁĄCZE("tel:09999999";"09999999")

As you can see:

the name of function is different than original
a "formula-separator" is different than original

More details about it you'll find here:

Range.Formula

Range.FormulaLocal

Application.International Property (Excel).

So, if you would like to insert formula via VBA code, which uses [;], you have to use FormulaLocal property. In case you want to insert formula by using Formula property, you have to care about usage of correct separator!

So, the save way to insert formula should get a form:

'English (universal way)
oSheet.Range("A1").Formula = "=HYPERLINK(""tel:00009"",""000009"")"
'German
oSheet.Range("A1").FormulaLocal = "=HYPERLINK(""tel:00009""" & Application.International(xlListSeparator) & """000009"")"

As to your code... you've been warned about using Select method.

Good luck!

Hyperlink in Excel not Clickable

1 Answers1