How to enter a formula over 250 characters?

[Darren Hill]

I have a routine below,

I have a form in which data is entered. I then cycle through the
textboxes on the form, and paste the contents to a sheet.

It's all working fine, except that at one point, the contents of several
text boxes are pasted together, and pasted into a single cell. This can
often create a formula over 250 characters in length, in the following
format:
"=" & "some text" & D72 & "lots of text" & D74.

For these results, the routine below errors out at the problem line.
Is there a work around that would allow me to paste a simple-but-long
formula of over 256 characters?

Thanks.

Sub UpdateCells(sOnForm As String, ws As Worksheet, iSet As Integer,
sSetLabel As String)
Dim rgToEdit As Range
'the following line uses the parameters to find where the
' current contents of sOnForm should be pasted.

Set rgToEdit = FindSetRange(ws, iSet, sSetLabel)

rgToEdit.Formula = sOnForm '<<< here's the problem.
End Sub

Darren

 

[excelent]

Maby u can use :
txt1="a loooooong text y a realy loooong text"
txt2="This is another looooooong text even looooooonger than the first one"

then put in ur formula :

"=" & txt1 & D72 & txt2 & D74



"Darren Hill" skrev:

 

[Peter T]

A cell formula should accept a length up to (absolute) max 1024.

Not sure why you are limited to 255/6, however AFAIK there is no way to
exceed the 1024 formula length limit other than by changing the formula to a
text value in the cell (max 32k).

Regards,
Peter T

 

[Darren Hill]

Thanks Peter and the excellently named excelent.

I did some more testing with the macro below.
This macro builds text strings of 95, 190, 380, and 760 characters.
It then constructs a formula which will look like

"=" & stringvariable & cellref & stringvariable

In the first two runs, where stringvariable = 95 and 190 characters, it
works fine. With 380 characters it breaks.

Is this supposed to happen?

If so, it looks like I'll have to figure out a function to break down
strings of over 250 characters into 250-character chunks.

-----------------------------------
Sub TestFormula()
Dim s1 As String, s2 As String, s3 As String, s4 As String
Dim sOutput As String, rg As Range
Set rg = ActiveSheet.Range("$P$72")
Dim i As Integer
For i = 1 To 5
s1 = s1 & "This is some text. "
Next i
Debug.Print "s1: " & Len(s1)
s2 = s1 & s1
Debug.Print "s2: " & Len(s2)
s3 = s2 & s2
Debug.Print "s3: " & Len(s3)
s4 = s3 & s3
Debug.Print "s4: " & Len(s4)

sOutput = "=" & Chr(34) & s1 & Chr(34) & " & D72 & " & Chr(34) & s1 &
Chr(34)
Debug.Print "v1: " & Len(sOutput)
Debug.Print sOutput
rg.Formula = sOutput

sOutput = "=" & Chr(34) & s2 & Chr(34) & " & D72 & " & Chr(34) & s2 &
Chr(34)
Debug.Print "v2: " & Len(sOutput)
Debug.Print sOutput
Stop
rg.Formula = sOutput

sOutput = "=" & Chr(34) & s3 & Chr(34) & " & D72 & " & Chr(34) & s3 &
Chr(34)
Debug.Print "v3: " & Len(sOutput)
Debug.Print sOutput
rg.Formula = sOutput '<<<<<<<<<<<<<<< Here's where it breaks

sOutput = "=" & Chr(34) & s4 & Chr(34) & " & D72 & " & Chr(34) & s4 &
Chr(34)
Debug.Print "v4: " & Len(sOutput)
Debug.Print sOutput
rg.Formula = sOutput


End Sub

 

[Peter T]

As I mentioned previously, cell formula length limit is an absolute max
1024, though I'd regard a 'safe' max as say 900.
However you can't put individual strings of 255+ in the formula, which it
seems is what you are trying to do.

Have a go with this -

Function MultiStr(s As String) As String
Dim sOut As String

sOut = Left(s, 250)

If Len(s) > 250 Then
sOut = sOut & """&""" & Mid(s, 250 + 1, 250)
End If

If Len(s) > 250 * 2 Then
sOut = sOut & """&""" & Mid(s, 2 * 250 + 1, 250)
End If

If Len(s) > 250 * 3 Then
sOut = sOut & """&""" & Mid(s, 3 * 250 + 1, 250)
End If

MultiStr = sOut

' MultiStr = Chr(34) & sOut & Chr(34)

End Function

When building your formula replace any potentially long strings with
MultiStr(potentially-long-string). Might as well move the embracing quotes
Chr(34), which will be required in formula, into the function (commented as
posted) but that's up to you.

Regards,
Peter T