Excel VB to convert cell values to hyperlinks

Update 2/24/09: I've noticed increased traffic to this post using keywords that make it appear that some of you are just looking how to create a hyperlink in an Excel cell, without the need for any VB code. If that's all you want to do, right-click on the cell you want and select "Hyperlink...". At the top you can enter the text you want to have visible to the user and displayed within the cell, and at the bottom you can enter the actual URL the user will be directed to by clicking on the cell. If you're looking to automatically convert a bunch of cells with URL's into hyperlinks, then go ahead and continue on to the original article.

I was presented with an issue yesterday. A user was getting a .csv export that contained a column with URLs, but those URLs were coming across as plain text in the export, rather than being clickable. They also wanted to hide the URL and display different text in the cell instead. So I did some digging, and some programming, and came up with the following for them:

Function CreateLink() As Integer
Dim row As Integer 'used to track the row we're in
Dim strLink As String 'used to store the URL that already exists as text in a cell
Dim column As String 'used to track the column we're in
Dim Cell As String 'used to combine the row and column into a cell reference value
Dim displayTxt As String 'used to set the text we want the hyperlink to display

displayTxt = "IMAGE" 'hyperlink will always show up as "IMAGE"
column = "G" 'existing text hyperlink values are all in column G
row = 2 'I had a header row so actual values start in row 2
Cell = column & row 'combine row and column in this case to get the Cell = "G2"

While Range(Cell).Value <> "" 'continue looping until the value in the cell is blank
strLink = Range(Cell).Value 'set the hyperlink value to be what's in the cell (G2 first time)

Range(Cell).Hyperlinks.Add _ 'add the hyperlink
Anchor:=Cells(row, column), _ 'sets which cell to add the link in
Address:=strLink, _ 'sets the hyperlink URL
TextToDisplay:=displayTxt 'sets the text to display. We wanted "IMAGE" only

row = row + 1 'go to the next row in the next iteration of the loop
Cell = column & row 'set the new cell value with the new row number
Wend

End Function

In our case, the url text existed in column "G", which is why I was able to set it statically above. Then I wanted to loop through the sheet to get through all the cells in the G column until there weren't any more values, which is where the While loop comes in. As I looped through the cells, I also wanted to convert the value within the cell to be a clickable hyperlink using the text that was already in the cell, and also change the visible text in the cell to read "IMAGE" so that's all the person would see.

I just put a call to this function in the SelectionChange portion of the worksheet properties. After it ran and converted the cells, I removed the VB code and saved the worksheet again. This ran pretty quickly and saved the user from manually going in and changing the display text on all the cells, and from changing the plain text representing the hyperlink to an actual hyperlink