The builtin functions supplied for workbooks read only the data contained in the worksheet range, the ."Value" property of the referenced range object.
The hyperlink is not stored as the .Value property of the range; there's another object Hyperlink (which is a member of the worksheet/workbook "Hyperlinks" collection) that holds the address. The only way to return the string value of the hyperlink itself would be to use ActiveX and retrieve it that way. I've not seen any FEX or other submissions that have done this; it would roughly parallel a crude function I put together to retrieve the comments except use the hyperlinks collection instead of the commentsCollection here...
function [cmnts]=xlsComments(Excel, rangeObject)
% return comments from Excel sheet in range given
% Input called rangeObject definition--for reference
% UsedRange is user input range or if not given as found area used
% rangeObject = Excel.Application.ActiveSheet.UsedRange;
% Allocate space same size a the input range object to place comments into
cmnts=cell(rangeObject.Rows.Count,rangeObject.Columns.Count);
rowOffset=rangeObject.Row-1; % Get first row in range
colOffset=rangeObject.Column-1; % Get column row in range
% Get Handle to Comments collection -- this is, unfortunately, worksheet global
commentsCollection=Excel.Application.ActiveSheet.Comments;
nComments=commentsCollection.Count;
for i=1:nComments % Iterate over the collection
comment=invoke(commentsCollection,'Item',i); % Get handle to each in turn
parent=comment.Parent; % Handle to parent cell
addr=parent.Address; % Address string of cell
rnge=parent.Cells; % Parent cell range object
if ~xlsInRange(Excel,rangeObject,rnge),continue,end % Skip if not in range
[r,c]=xlsRowCol(addr,-rowOffset,-colOffset); % Convert to row,col indices
cmnts{r,c}=comment.Text; % Save each in cell array
end
return
At the time I did the above, I was total novice at writing COM to address the Excel object model; I couldn't make anything work to retrieve the comments only from a specific cell; only by iterating over the entire collection and testing that the given comment was in a location I was interested in...hence the klunky appearance.
I subsequently learned more but never had the need to improve this particular function; it was for one specific task only...
Anyways, it will give you the flavor of what will need to do; the VBA doc of relevance is at <vba/api/excel.hyperlinks>
