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 |
Pass function as argument to UDF
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" wrote in message ... 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 |
Pass function as argument to UDF
Hi Ron,
=MyUDF(SUBSTITUTE(A1,"a","x"),A2,A3) Or did I misunderstand your question? -- Kind regards, Niek Otten "Ron Rosenfeld" wrote in message ... 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 |
Pass function as argument to UDF
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" wrote in message ... 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 |
Pass function as argument to UDF
On Wed, 08 Feb 2006 16:33:58 -0500, Ron Rosenfeld
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 |
Pass function as argument to UDF
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" wrote in message ... On Wed, 08 Feb 2006 16:33:58 -0500, Ron Rosenfeld 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 |
Pass function as argument to UDF
On Wed, 8 Feb 2006 18:58:55 -0800, "Zack Barresse"
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 |
Pass function as argument to UDF
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" wrote in message ... On Wed, 8 Feb 2006 18:58:55 -0800, "Zack Barresse" 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 |
Pass function as argument to UDF
On Wed, 8 Feb 2006 20:06:17 -0800, "Zack Barresse"
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 |
Pass function as argument to UDF
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" wrote in message ... On Wed, 8 Feb 2006 20:06:17 -0800, "Zack Barresse" 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 |
Pass function as argument to UDF
On Thu, 9 Feb 2006 00:44:52 -0500, "Tom Ogilvy" 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 |
All times are GMT +1. The time now is 10:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com