String limit for Range.FormulaArray property

[rdilipk]

I have some vba code that places a UDF formula on a particular Range's FormulaArray property. Problem is the formula length can sometimes exceed 255 chars causing the code to fail. I posted this question at the MSDN forums here:
http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/ed7c5a4c-9fce-4f5b-8249-e588269179d2
(hoping some MSFT guy will lead me to a hot-fix that I never knew about)

Someone answered that this can be worked around using named ranges. I doubt that person is going to return to that thread anymore. So I'd like to ask the experts in this group to help me out here.

I don't quite understand the solution proposed in that thread (don't know if the values to the properties are right). Should we just do:

ActiveSheet.Names.Add Name:="SomeName"
RefersToR1C1:="=MyUDF(super_long_contents_of_formula)"

ActiveCell.FormulaArray = "SomeName"

??

 

[Firefly2012]

Hi

See post #6 here:

http://www.mrexcel.com/forum/showthread.php?t=606203

 

[rdilipk]

If you look at the MSDN link I posted I actually tried that approach. The problem is everytime I do:

With rng
.FormulaArray = "my_partial_formula"
.Replace ...
End With

The assignment to .FormulaArray causes my formula to validate, which calls into my UDF implementation with partially formed parameters.

Did you happen to see that named ranges solution posted in that MSDN thread?

 

[Firefly2012]

Why don't you post up the actual formula you are trying to assign to the range (don't use dummy function names or truncate the actual text) and I can show you how to apply the workaround in the link i gave.

The assignment to .FormulaArray causes my formula to validate, which calls into my UDF implementation with partially formed parameters.

Hopefully won't be an issue as you can run the Replace twice - first time to correctly form your paramters, second to apply your UDF to the cell.

 

[rdilipk]

Hopefully won't be an issue as you can run the Replace twice - first time to correctly form your paramters, second to apply your UDF to the cell.

This is probably what I was missing. So here goes:

I have a UDF implemented in C++. It takes in a variety of optional parameters which is what makes it go beyond 255 chars sometimes. It is called like this:

=MyUDF("param1","param2","param3",........)

Originally I tried like this:

Sub ApplyFormulaToRange(r as Range, formula_contents as string)
Dim dummy_str as string
dummy_str = "dummy_string"
With r
r.FormulaArray = "=MyUDF(" & dummy_str & ")"
r.Replace dummy_str, formula_contents
End With
End Sub

formula_contents as the name implies is everything that goes in as MyUDF's parameters.

The first assignment triggered a call into MyUDF implementation. Since passing just one parameter counts as an error in my case, I return a benign message right away complaining that the input is insufficient. The next call to .Replace replaces everything with the correct stuff which calls into my MyUDF impl. once again. Everything now works as expected.

Problem is this work around also doesn't work if len(formula_contents) > 255. In hindsight it should've been obvious to me but somehow I missed it.

How do I now solve this using your approach?

 

[Firefly2012]

Apologies for delay in replying.

OK, so you just need to amend the construction to something like this:


Say the formula you want to input is

=MyUDF(SomeArgument1,SomeArgument2,SomeArgument3,SomeArgument4)

(truncated for example purposes)

Initially, split the formula arguments up:

Dim sFormula1 As String
 
sFormula1 = "SomeArgument3,SomeArgument4"
 
'now enter formula using FormulaArray with a dummy formula name wrapping the arguments.  use a placeholder for the remaining arguments (ie so 255 character limit isn't breached):
 
Range("A1").FormulaArray = "=XXX(SomeArgument1,SomeArgument2,YYY)"
 
'note that XXX is a placeholder for your UDF name, and YYY is a placeholder for the remaining arguments
 
'now, replace the arguments:
 
Range("A1").Replace "YYY", sFormula1
 
'now replace the function name with your UDF:
 
Range("A1").Replace "XXX", "MyUDF"

Make sense?

 

[rdilipk]

Apologies for delay in replying.

I appreciate your taking time to respond. I am sure you have better things to do than solve VBA problems

Make sense?

It makes perfect sense. I somehow never considered replacing the UDF name at the very end. I presume that you wanted the arguments also split up to ensure each part is <255 chars?

 

[Firefly2012]

I haven't actually tested to see if it is important for each replacement element to be less than 255 characters - the initial assignment of the formulaArray needs to be <255. You've got the idea though I hope it works now!