Thread: Pass function as argument to UDF

How would I pass a function as an argument to a UDF?

For example:

=MyUDF(arg1, arg2, arg3)

where arg1 might be a literal text string, a reference to a cell containing a
string, OR a formula that returns a string:

SUBSTITUTE(A1,old_text,new_text)

I want to manipulate the resultant string in my UDF.

Thanks.
--ron

Hi,

Not sure I *fully* understand what you're asking, but you pass arguments
just as you would any other procedure/method in VBA ..

=MyUDF("string1",A1,SUBSTITUTE(A1," ",""))

Just remember to name your arguments/variables in the code of your function

Function MyUDF(arg1 as String, arg2 as Range, arg3 as Variant)
'...
End Function

Not sure if you want to change the types to Variants to consider multiple
sources, but as I'm not sure of the entire scope of your UDF, it's hard to
say.

HTH

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)
To email, remove the NO SPAM. Please keep correspondence to the board, as
to benefit others.


"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:gioku15i7eg1a1cnkrfubjmavd7t93frji@4ax.com...
> How would I pass a function as an argument to a UDF?
>
> For example:
>
> =MyUDF(arg1, arg2, arg3)
>
> where arg1 might be a literal text string, a reference to a cell
> containing a
> string, OR a formula that returns a string:
>
> SUBSTITUTE(A1,old_text,new_text)
>
> I want to manipulate the resultant string in my UDF.
>
> Thanks.
> --ron

Hi Ron,

=MyUDF(SUBSTITUTE(A1,"a","x"),A2,A3)

Or did I misunderstand your question?

--
Kind regards,

Niek Otten

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:gioku15i7eg1a1cnkrfubjmavd7t93frji@4ax.com...
> How would I pass a function as an argument to a UDF?
>
> For example:
>
> =MyUDF(arg1, arg2, arg3)
>
> where arg1 might be a literal text string, a reference to a cell
> containing a
> string, OR a formula that returns a string:
>
> SUBSTITUTE(A1,old_text,new_text)
>
> I want to manipulate the resultant string in my UDF.
>
> Thanks.
> --ron

Hey Ron,

If you are only talking strings, your UDF would be declared like so

Function MyUDF(StartType As String, EndType As String, SomeOther As String)

If StartType = "XXX" Then
'....
End If
End Function

When you call it, you can pass anything that resolves to a string, such as
text, a cell reference, or a formula, such as

=MyUDF("XXX",A1,SUBSTITUTE(A1,old_text,new_text))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:gioku15i7eg1a1cnkrfubjmavd7t93frji@4ax.com...
> How would I pass a function as an argument to a UDF?
>
> For example:
>
> =MyUDF(arg1, arg2, arg3)
>
> where arg1 might be a literal text string, a reference to a cell
containing a
> string, OR a formula that returns a string:
>
> SUBSTITUTE(A1,old_text,new_text)
>
> I want to manipulate the resultant string in my UDF.
>
> Thanks.
> --ron

On Wed, 08 Feb 2006 16:33:58 -0500, Ron Rosenfeld <ronrosenfeld@nospam.org>
wrote:

>How would I pass a function as an argument to a UDF?
>
>For example:
>
>=MyUDF(arg1, arg2, arg3)
>
>where arg1 might be a literal text string, a reference to a cell containing a
>string, OR a formula that returns a string:
>
> SUBSTITUTE(A1,old_text,new_text)
>
>I want to manipulate the resultant string in my UDF.
>
>Thanks.
>--ron

Thanks for all of your responses.

I did think it should work, and, with your postings indicating that it should
work, I've narrowed down the problem a bit further.

To simplify:

================
Function MyUDF(str)
MyUDF = str
End Function
================

=MyUDF((SUBSTITUTE(A6,"-","",1))

Initially it seemed that if the length of the string being generated by the
SUBSTITUTE worksheet function is greater than 255, an error is generated.

With the function as written, a #VALUE! error is returned and a breakpoint at
the 2nd line does not "break" the routine.

On closer examination, it appears that a further requirement has to do with the
manner of generating the long string. If the long string is generated by
various functions or operations (e.g. =REPT("This is a long string. ",30)),
then MyUDF will --> #VALUE!

However, if the one merely types in more than 255 characters, then the function
works OK.

The function will also work OK, regardless of how the long string is generated,
if one limits the string being generated by the SUBSTITUTE function to 255.

e.g.

A6: =REPT("-",300)

=myudf(SUBSTITUTE(A6,"-","",1)) --> #VALUE!

=myudf(SUBSTITUTE(LEFT(A6,257),"-","",1)) --> #VALUE!

=myudf(SUBSTITUTE(LEFT(A6,256),"-","",1)) --> a string of 255 hyphens


Any comments would be appreciated. In a limited search, I did not find
documentation of this "feature".


--ron

If you have the value in a cell, you can just refer to the cell. There is a
difference between what a VBA function can receive for text length and what
you can put in a cell. Do a search on Excel's cell length limitations for
more information.

As an example, I can use the Rept function in a cell and have it's cell
length 32,767 characters long. I then use your UDF to point to that cell
"=MyUDF(A1)" and I get the same value, non-errored.

HTH

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)
To email, remove the NO SPAM. Please keep correspondence to the board, as
to benefit others.



"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:nl6lu1t81qsv8m8naln04n4ncu4g76okps@4ax.com...
> On Wed, 08 Feb 2006 16:33:58 -0500, Ron Rosenfeld
> <ronrosenfeld@nospam.org>
> wrote:
>
>>How would I pass a function as an argument to a UDF?
>>
>>For example:
>>
>>=MyUDF(arg1, arg2, arg3)
>>
>>where arg1 might be a literal text string, a reference to a cell
>>containing a
>>string, OR a formula that returns a string:
>>
>> SUBSTITUTE(A1,old_text,new_text)
>>
>>I want to manipulate the resultant string in my UDF.
>>
>>Thanks.
>>--ron
>
> Thanks for all of your responses.
>
> I did think it should work, and, with your postings indicating that it
> should
> work, I've narrowed down the problem a bit further.
>
> To simplify:
>
> ================
> Function MyUDF(str)
> MyUDF = str
> End Function
> ================
>
> =MyUDF((SUBSTITUTE(A6,"-","",1))
>
> Initially it seemed that if the length of the string being generated by
> the
> SUBSTITUTE worksheet function is greater than 255, an error is generated.
>
> With the function as written, a #VALUE! error is returned and a breakpoint
> at
> the 2nd line does not "break" the routine.
>
> On closer examination, it appears that a further requirement has to do
> with the
> manner of generating the long string. If the long string is generated by
> various functions or operations (e.g. =REPT("This is a long string.
> ",30)),
> then MyUDF will --> #VALUE!
>
> However, if the one merely types in more than 255 characters, then the
> function
> works OK.
>
> The function will also work OK, regardless of how the long string is
> generated,
> if one limits the string being generated by the SUBSTITUTE function to
> 255.
>
> e.g.
>
> A6: =REPT("-",300)
>
> =myudf(SUBSTITUTE(A6,"-","",1)) --> #VALUE!
>
> =myudf(SUBSTITUTE(LEFT(A6,257),"-","",1)) --> #VALUE!
>
> =myudf(SUBSTITUTE(LEFT(A6,256),"-","",1)) --> a string of 255 hyphens
>
>
> Any comments would be appreciated. In a limited search, I did not find
> documentation of this "feature".
>
>
> --ron

On Wed, 8 Feb 2006 18:58:55 -0800, "Zack Barresse"
<firefytrNO@SPAMvbaexpress.com> wrote:

>If you have the value in a cell, you can just refer to the cell. There is a
>difference between what a VBA function can receive for text length and what
>you can put in a cell. Do a search on Excel's cell length limitations for
>more information.
>
>As an example, I can use the Rept function in a cell and have it's cell
>length 32,767 characters long. I then use your UDF to point to that cell
>"=MyUDF(A1)" and I get the same value, non-errored.
>
>HTH

Yes, I can do that,too. But that is not the issue.

I don't see how what I've found on Excel's cell length limitation:

---------------------
Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell;
all 32,767 display in the formula bar.
----------------------

Nor the length of a VBA string, whether it is in a variant or a string

-------------------------
A variable-length string can contain up to approximately 2 billion (2^31)
characters

Variant
(with characters) 22 bytes + string length Same range as for variable-length
String
------------------------

really explain why I cannot, under the *specific circumstances* I described,
pass a 255 character string to MyUDF but not be able to pass a 256 character
string.


--ron

Limitations are limitations, Ron. I don't know what to tell you.

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)
To email, remove the NO SPAM. Please keep correspondence to the board, as
to benefit others.


"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:2fclu1tssq5euikjotk788eu2uinmc57ns@4ax.com...
> On Wed, 8 Feb 2006 18:58:55 -0800, "Zack Barresse"
> <firefytrNO@SPAMvbaexpress.com> wrote:
>
>>If you have the value in a cell, you can just refer to the cell. There is
>>a
>>difference between what a VBA function can receive for text length and
>>what
>>you can put in a cell. Do a search on Excel's cell length limitations for
>>more information.
>>
>>As an example, I can use the Rept function in a cell and have it's cell
>>length 32,767 characters long. I then use your UDF to point to that cell
>>"=MyUDF(A1)" and I get the same value, non-errored.
>>
>>HTH
>
> Yes, I can do that,too. But that is not the issue.
>
> I don't see how what I've found on Excel's cell length limitation:
>
> ---------------------
> Length of cell contents (text) 32,767 characters. Only 1,024 display in a
> cell;
> all 32,767 display in the formula bar.
> ----------------------
>
> Nor the length of a VBA string, whether it is in a variant or a string
>
> -------------------------
> A variable-length string can contain up to approximately 2 billion (2^31)
> characters
>
> Variant
> (with characters) 22 bytes + string length Same range as for
> variable-length
> String
> ------------------------
>
> really explain why I cannot, under the *specific circumstances* I
> described,
> pass a 255 character string to MyUDF but not be able to pass a 256
> character
> string.
>
>
> --ron

On Wed, 8 Feb 2006 20:06:17 -0800, "Zack Barresse"
<firefytrNO@SPAMvbaexpress.com> wrote:

>Limitations are limitations, Ron. I don't know what to tell you.

I agree with that statement.

You wrote: "Do a search on Excel's cell length limitations for
more information."

All I was able to find was a 32,000+ limitation for cell contents.

I don't understand how this explains the apparent 255 character limit from
using the SUBSTITUTE function to pass a string to a UDF, when that function is
referring to a cell whose string was constructed by various formulas.

I guess you don't understand that either, or I've not been able to express
myself clearly.


--ron

This isn't identical to your issue, but is certainly in the ballpark

http://support.microsoft.com/kb/213841/en-us
XL: Passed Strings Longer Than 255 Characters Are Truncated

this may be related as well:

http://support.microsoft.com/kb/212013/en-us
XL2000: Calculation of Formula in Formula Bar Returns #VALUE!

You can encounter the 255 limitation in a variety of environments. Search
the knowledge base for excel and 255

--
Regards,
Tom Ogilvy



"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:hjilu1lqf5gibr5gl41sh2slcilf1ej79q@4ax.com...
> On Wed, 8 Feb 2006 20:06:17 -0800, "Zack Barresse"
> <firefytrNO@SPAMvbaexpress.com> wrote:
>
> >Limitations are limitations, Ron. I don't know what to tell you.
>
> I agree with that statement.
>
> You wrote: "Do a search on Excel's cell length limitations for
> more information."
>
> All I was able to find was a 32,000+ limitation for cell contents.
>
> I don't understand how this explains the apparent 255 character limit from
> using the SUBSTITUTE function to pass a string to a UDF, when that
function is
> referring to a cell whose string was constructed by various formulas.
>
> I guess you don't understand that either, or I've not been able to express
> myself clearly.
>
>
> --ron

On Thu, 9 Feb 2006 00:44:52 -0500, "Tom Ogilvy" <twogilvy@msn.com> wrote:

>This isn't identical to your issue, but is certainly in the ballpark
>
>http://support.microsoft.com/kb/213841/en-us
>XL: Passed Strings Longer Than 255 Characters Are Truncated
>
>this may be related as well:
>
>http://support.microsoft.com/kb/212013/en-us
>XL2000: Calculation of Formula in Formula Bar Returns #VALUE!
>
>You can encounter the 255 limitation in a variety of environments. Search
>the knowledge base for excel and 255

Thanks for those references, Tom.

I did note the effect of the second reference with the formula =REPT("-",300)
exactly as was reported.

The first reference, although it clearly discusses a 255 character reference,
is not quite the same. I believe it is discussing passing strings from VBA,
whereas my problem is going in the opposite direction.

However, a common denominator seems to be that if a string > 255 characters is
passed to a UDF using one of the functions listed in the second reference you
provided, an ERROR is generated.

Hmmm


--ron