I have written a VBA macro to check certain spreadsheets for valid syntax.
Certain cells are supposed to have hyperlinks, whose addresses are related to
the value of the cell, which in the case of cells with hyperlinks is also the
TextToDisplay value. I am currently adding a check for the hyperlinks.
Most of the time, the checking was fine, but I was getting a message for
some of the cells containing hyperlinks, that the hyperlink was wrong. When
I edited the hyperlink, it looked ok.
When I closed the hyperlink w/o making any changes, it then checked out as
I finally pinned down the problem. A clue : if I cancelled the hyperlink
editing, instead of closing it, it continued to get an error msg. I did
compares with Trim, and displayed Len, but could not find a problem there.
It turned out that the hyperlinks which give an error msg have a backward
slash () instead of a forward slash (/). When the hyperlink was edited,
they automatically changed to forward slash "/" in the editing window, and
were saved as such when the hyperlink edit was closed, but were unchanged by
a cancel. But when I did a compare on the hyperlink.address, to a string
generated by the macro which used "/", it of course did not compare as equal.
Also, when I hold the cursor over
the cell (w/o clicking), which shows the
expanded hyperlink address, the separator shown is the backward slash (""),
regardless of what kind of separator is actually in the hyperlink. I am
embarrased that I looked at several Msgbox messages showing the actual
hyperlink.address, which showed the actual type of separator, w/o noticing