In Excel, create hyperlink with unicode "folder" symbol using VBA

Question

In Excel, I'm trying create hyperlinks that display the unicode folder icon ( as well as various other icon). I can do this manually by right clicking --> Hyperlink --> and then "cut and paste" the character from the web browser. My problem is I'm trying to add the hyperlinks with VBA, but I'm not sure how to do it. Here's the code I'm trying, but I just get an error. You can see that in my code to create the hyperlink I'm using the ChrW funtion with TextToDisplay:=ChrW(&H1F4C1). Unfortunately Excel throws an error when it get to that line.

'Purchase Orders Folder
    ActiveSheet.Range("ProjectTable[POs]").Cells(lastTblRowNum).Select                
    ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:=poPath, TextToDisplay:=ChrW(&H1F4C1)

Answer 1

There is an upper limit on the character code accepted by the ChrW function (see MSDN documentation).

The normal range for charcode is 0–255. However, on DBCS systems, the actual range for charcode is -32768–65535.

In you case the character code for the folder symbol is 128193 so it's out of scope for this function.

Taking one of the suggested answers to this question (from Mark Tolonen), your code could be the following (if you are running Excel 2013 or higher):

'Purchase Orders Folder
    ActiveSheet.Range("ProjectTable[POs]").Cells(lastTblRowNum).Select                
    ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:=poPath, TextToDisplay:=WorksheetFunction.Unichar(&H1F4C1)

If you are running Excel 2010 or lower, then you'd have to use one of the other answers provided in the question mentioned above. If you choose milevyo's answer which uses an html workaround, you would have to add the following:

Function GetUnicode(CharCodeString As String) As String
    Dim Doc As New HTMLDocument
    Doc.Body.innerHTML = "&#x" & CharCodeString & ";"
    GetUnicode = Doc.Body.innerText
End Function

and then you code would be:

'Purchase Orders Folder
    ActiveSheet.Range("ProjectTable[POs]").Cells(lastTblRowNum).Select                
    ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:=poPath, TextToDisplay:=GetUnicode("1F4C1")

Also, make sure you add a reference to the Microsoft HTML Object Library.