[Dave Booker]

The arguments for a HYPERLINK() worksheet function seem to be limited to 256
characters. Is there a workaround for this?

I need to embed functions like this
?subject=My Subject&body="&A1)
in a worksheet, and I need it to accept very long body arguments. If the
argument in this case gets much longer than 200 characters, the function
becomes a #VALUE.

Also, I can't use macros because of all the security issues the worksheet
users will encounter.

[ExcelBanter AI]

Yes, there is a workaround for the HYPERLINK argument length limit. One way to do this is by using a combination of the HYPERLINK function and a user-defined function (UDF) in VBA.

Here are the steps to create a UDF that will allow you to use longer arguments in the HYPERLINK function:

1. Open the Visual Basic Editor by pressing Alt + F11.
2. In the Project Explorer window, right-click on the workbook name and select Insert Module.
3. In the new module, paste the following code:
   
   
   Formula:

   Function HyperlinkLong(ByVal link As String, ByVal display_text As String) As String
    Dim s As String
    s = "=HYPERLINK(""" & link & """,""" & display_text & """)"
    HyperlinkLong = s
End Function 

4. Save the module and return to the worksheet.
5. In the cell where you want to create the hyperlink, use the following formula:
   
   
   Formula:

   =HyperlinkLong(&A1, "Click Here") 

   This will create a hyperlink with the display text "Click Here" that will open the user's default email client with the specified subject and body. The body can be as long as necessary, as it is passed as a parameter to the UDF rather than being included in the HYPERLINK function directly.
   
   Note that this workaround requires the use of VBA, but it does not require the use of macros. The UDF is simply a function that can be used in a worksheet formula like any other built-in function.

[Epinn]

Yes, there is a limit to the number of characters (about 248?)

I put certain data/info in a cell. I can refer to the cell direct or use name definition.

InsertNameDefine

e.g. In cell A1 I type http://www.yahoo.com and define A1 as YH.

=Hyperlink(YH) or =Hyperlink(Sheet1!A1)

This saves quite a few characters. You can use multiple cells and concatenate using &.

See if this link helps.

http://exceltips.vitalnews.com/Pages..._in_Excel.html

If it is URL I can use TinyURL (www.TinyURL.com) to convert the lengthy URL to a much shortened version URL (a handle).

Then I hyperlink to the "tinyurl" - much less characters.

Haven't tried it with e-mail address. Same idea, I guess.

Hope this helps.

Epinn

"Dave Booker" wrote in message ...
The arguments for a HYPERLINK() worksheet function seem to be limited to 256
characters. Is there a workaround for this?

I need to embed functions like this
?subject=My Subject&body="&A1)
in a worksheet, and I need it to accept very long body arguments. If the
argument in this case gets much longer than 200 characters, the function
becomes a #VALUE.

Also, I can't use macros because of all the security issues the worksheet
users will encounter.

[Epinn]

Sorry, I missed that you were using a cell already.

There may still be a problem using concatenation of multiple cells if the characters added up to over 248??

I can't remember what I did.

Epinn

"Epinn" wrote in message ...
Yes, there is a limit to the number of characters (about 248?)

I put certain data/info in a cell. I can refer to the cell direct or use name definition.

InsertNameDefine

e.g. In cell A1 I type http://www.yahoo.com and define A1 as YH.

=Hyperlink(YH) or =Hyperlink(Sheet1!A1)

This saves quite a few characters. You can use multiple cells and concatenate using &.

See if this link helps.

http://exceltips.vitalnews.com/Pages..._in_Excel.html

If it is URL I can use TinyURL (www.TinyURL.com) to convert the lengthy URL to a much shortened version URL (a handle).

Then I hyperlink to the "tinyurl" - much less characters.

Haven't tried it with e-mail address. Same idea, I guess.

Hope this helps.

Epinn

"Dave Booker" wrote in message ...
The arguments for a HYPERLINK() worksheet function seem to be limited to 256
characters. Is there a workaround for this?

I need to embed functions like this
?subject=My Subject&body="&A1)
in a worksheet, and I need it to accept very long body arguments. If the
argument in this case gets much longer than 200 characters, the function
becomes a #VALUE.

Also, I can't use macros because of all the security issues the worksheet
users will encounter.

[Dave Booker]

Yes, even if we're using cell references, as soon as the length of the values
of the references exceeds the magic limit -- 248 or whatever it is -- the
function itself fails.

"Epinn" wrote:

Sorry, I missed that you were using a cell already.

There may still be a problem using concatenation of multiple cells if the characters added up to over 248??

[Epinn]

Dave,

Any luck? I would appreciate it if you could post back the solution i.e. if there is one.

I did more research. The limit is about 255/256 which is the column width. But a cell can hold over 1,000.

We both know that =hyperlink(A1&A2) works when the sum of the characters is below the limit. It will return #VALUE if over.

To my surprise =hyperlink(A1)&hyperlink(A2) also works when below the limit. I don't have a long URL to test for the over limit condition. I put 254 characters in A1 and (also 254 characters in) A2 respectively. The formula does not return #VALUE error. It displays blue fonts and the underline i.e. the hyperlink format. I am not hopeful that this actually works. But I just want to let you know. May be you can think of something else.

Looks like FollowHyperlink method is one way but I know that's not something you want.

Epinn

"Dave Booker" wrote in message ...
Yes, even if we're using cell references, as soon as the length of the values
of the references exceeds the magic limit -- 248 or whatever it is -- the
function itself fails.

"Epinn" wrote:

Sorry, I missed that you were using a cell already.

There may still be a problem using concatenation of multiple cells if the characters added up to over 248??