Problem with VBA returning the contents of a long formula.

A

alfred.vachris

I have run across a problem with getting a formula from VBA.
Excel specifications state that a worksheet formula can have a maximum
length of 1024 characters including the leading "=".
From the simple test that I did, it looks like VBA will not return
Click to expand...
formulas that are longer than 1022 characters including the leading
"=".

And an even stranger result - I found a formula of length 901
characters including the leading "=" which could not be entered on a
worksheet?

I have included the VBA code that I used to create the examples.

Any ideas as to what is going on?
Regards, Al Vachris

Option Explicit
'======================================
Sub IllustrateFormulaIssues()
'======================================
'-----------------------------------------------
'add this macro to to a new workbook and run it.
'My examples are from Microsoft ExcelXP Version 11# Build 5612
'-----------------------------------------------
'run this macro to create a table of examples
'that illustrate two problems with formulas in Excel
'first issue: although the formula length can have a maximum length of
1023 characters
'the VBA cell.formula has a problem returning a string larger than
1021 characters
'and in fact there are times when it will fail even for a string of
1021 characters
'-----------------------------------------------
'second issue: found an example of a function that could not be
'entered into a worksheet even thought the formula length is less than
900 characters
'-----------------------------------------------
Dim BigString As String
Dim ThisPartIndex As Integer
Dim ResultString As String
'-----------------------------------------------
Range("A1") = "VBA MyCell.formula Issue"
'-----------------------------------------------
Range("A2") = "Description"
Range("B2") = "Result"
Range("C2") = "Formula"
Range("D2") = "Length" & Chr(10) & "of string"
Range("E2") = "String"
'-----------------------------------------------
Range("A4") = "340 Tens" 'String length 1019 - OK
'-----------------------------------------------
BigString = "10"
For ThisPartIndex = 2 To 340
BigString = BigString & "+10"
Next ThisPartIndex
Range("c4").FormulaR1C1 = "=" & BigString
Range("E4").FormulaR1C1 = BigString
Range("D4").FormulaR1C1 = "=LEN(RC[1])"
ResultString = ""
On Error Resume Next
ResultString = Range("C4").Formula
On Error GoTo 0
If Len(ResultString) > 0 Then
Range("B4") = "OK"
Else
Range("B4") = "VBA Runtime Error 1004"
End If
'-----------------------------------------------
Range("A5") = "339 Tens Plus 1 One Hundred" 'String length 1020 - OK
'-----------------------------------------------
BigString = "10"
For ThisPartIndex = 2 To 339
BigString = BigString & "+10"
Next ThisPartIndex
BigString = BigString & "+100"
Range("c5").FormulaR1C1 = "=" & BigString
Range("E5").FormulaR1C1 = BigString
Range("D5").FormulaR1C1 = "=LEN(RC[1])"
ResultString = ""
On Error Resume Next
ResultString = Range("C5").Formula
On Error GoTo 0
If Len(ResultString) > 0 Then
Range("B5") = "OK"
Else
Range("B5") = "VBA Error 1004"
End If
'-----------------------------------------------
Range("A6") = "339 Tens Plus 1 One Thousand" 'String length 1021 - OK
'-----------------------------------------------
BigString = "10"
For ThisPartIndex = 2 To 339
BigString = BigString & "+10"
Next ThisPartIndex
BigString = BigString & "+1000"
Range("c6").FormulaR1C1 = "=" & BigString
Range("E6").FormulaR1C1 = BigString
Range("D6").FormulaR1C1 = "=LEN(RC[1])"
ResultString = ""
On Error Resume Next
ResultString = Range("C6").Formula
On Error GoTo 0
If Len(ResultString) > 0 Then
Range("B6") = "OK"
Else
Range("B6") = "VBA Error 1004"
End If
'-----------------------------------------------
Range("A7") = "339 Tens Plus 1 One Ten Thousand" 'String length 1022
- NG
'-----------------------------------------------
BigString = "10"
For ThisPartIndex = 2 To 339
BigString = BigString & "+10"
Next ThisPartIndex
BigString = BigString & "+10000"
Range("c7").FormulaR1C1 = "=" & BigString
Range("E7").FormulaR1C1 = BigString
Range("D7").FormulaR1C1 = "=LEN(RC[1])"
ResultString = ""
On Error Resume Next
ResultString = Range("C7").Formula
On Error GoTo 0
If Len(ResultString) > 0 Then
Range("B7") = "OK"
Else
Range("B7") = "VBA Error 1004"
End If
'-----------------------------------------------
Range("A10") = "Worksheet formula issue"
'-----------------------------------------------
Range("A11") = "450 One's" 'String length 899 - OK
'-----------------------------------------------
BigString = "1"
For ThisPartIndex = 2 To 450
BigString = BigString & "+1"
Next ThisPartIndex
Range("c11").FormulaR1C1 = "=" & BigString
Range("E11").FormulaR1C1 = BigString
Range("D11").FormulaR1C1 = "=LEN(RC[1])"
ResultString = ""
On Error Resume Next
ResultString = Range("C11").Formula
On Error GoTo 0
If Len(ResultString) > 0 Then
Range("B11") = "OK"
Else
Range("B11") = "VBA Error 1004"
End If
'-----------------------------------------------
Range("A12") = "451 One's" 'String length 901 - NG
'-----------------------------------------------
BigString = "1"
For ThisPartIndex = 2 To 451
BigString = BigString & "+1"
Next ThisPartIndex
Range("B12") = "Formula is too long"
'-----------------------------------------------
'I commented out the next line because
'it causes a runtime error:
'Application-defined or object-defined error
'-----------------------------------------------
' Range("c12").FormulaR1C1 = "=" & BigString
'-----------------------------------------------
Range("E12").FormulaR1C1 = BigString
Range("D12").FormulaR1C1 = "=LEN(RC[1])"
ResultString = ""
On Error Resume Next
ResultString = Range("C12").Formula
On Error GoTo 0
If Len(ResultString) > 0 Then
Range("B12") = "OK"
Else
Range("B12") = "VBA Error 1004"
End If

Range("D12").FormulaR1C1 = "=LEN(RC[1])"
'-----------------------------------------------
'format the Illustrations
'-----------------------------------------------
Columns("A:D").Columns.AutoFit
With Columns("E:E")
.ColumnWidth = 50
.HorizontalAlignment = xlGeneral
.WrapText = True
End With
With Columns("A:E")
.Rows.AutoFit
.VerticalAlignment = xlTop
End With
Range("A1").Select
End Sub
 
J

Jason Morin

I would suggest first creating defined names for your
formulas, or parts of your formulas. There is also
another method in VBA that would allow you to "piece"
together a formula and then execute it. It was posted on
Dick Kusleika's blog around array formulas. See:

http://www.dicks-blog.com/archives/2005/01/10/entering-
long-array-formulas-in-vba/

HTH
Jason
Atlanta, GA
-----Original Message-----
I have run across a problem with getting a formula from VBA.
Excel specifications state that a worksheet formula can have a maximum
length of 1024 characters including the leading "=".
From the simple test that I did, it looks like VBA will
Click to expand...
not return
formulas that are longer than 1022 characters including the leading
"=".

And an even stranger result - I found a formula of length 901
characters including the leading "=" which could not be entered on a
worksheet?

I have included the VBA code that I used to create the examples.

Any ideas as to what is going on?
Regards, Al Vachris

Option Explicit
'======================================
Sub IllustrateFormulaIssues()
'======================================
'-----------------------------------------------
'add this macro to to a new workbook and run it.
'My examples are from Microsoft ExcelXP Version 11# Build 5612
'-----------------------------------------------
'run this macro to create a table of examples
'that illustrate two problems with formulas in Excel
'first issue: although the formula length can have a maximum length of
1023 characters
'the VBA cell.formula has a problem returning a string larger than
1021 characters
'and in fact there are times when it will fail even for a string of
1021 characters
'-----------------------------------------------
'second issue: found an example of a function that could not be
'entered into a worksheet even thought the formula length is less than
900 characters
'-----------------------------------------------
Dim BigString As String
Dim ThisPartIndex As Integer
Dim ResultString As String
'-----------------------------------------------
Range("A1") = "VBA MyCell.formula Issue"
'-----------------------------------------------
Range("A2") = "Description"
Range("B2") = "Result"
Range("C2") = "Formula"
Range("D2") = "Length" & Chr(10) & "of string"
Range("E2") = "String"
'-----------------------------------------------
Range("A4") = "340 Tens" 'String length 1019 - OK
'-----------------------------------------------
BigString = "10"
For ThisPartIndex = 2 To 340
BigString = BigString & "+10"
Next ThisPartIndex
Range("c4").FormulaR1C1 = "=" & BigString
Range("E4").FormulaR1C1 = BigString
Range("D4").FormulaR1C1 = "=LEN(RC[1])"
ResultString = ""
On Error Resume Next
ResultString = Range("C4").Formula
On Error GoTo 0
If Len(ResultString) > 0 Then
Range("B4") = "OK"
Else
Range("B4") = "VBA Runtime Error 1004"
End If
'-----------------------------------------------
Range("A5") = "339 Tens Plus 1 One Hundred" 'String length 1020 - OK
'-----------------------------------------------
BigString = "10"
For ThisPartIndex = 2 To 339
BigString = BigString & "+10"
Next ThisPartIndex
BigString = BigString & "+100"
Range("c5").FormulaR1C1 = "=" & BigString
Range("E5").FormulaR1C1 = BigString
Range("D5").FormulaR1C1 = "=LEN(RC[1])"
ResultString = ""
On Error Resume Next
ResultString = Range("C5").Formula
On Error GoTo 0
If Len(ResultString) > 0 Then
Range("B5") = "OK"
Else
Range("B5") = "VBA Error 1004"
End If
'-----------------------------------------------
Range("A6") = "339 Tens Plus 1 One Thousand" 'String length 1021 - OK
'-----------------------------------------------
BigString = "10"
For ThisPartIndex = 2 To 339
BigString = BigString & "+10"
Next ThisPartIndex
BigString = BigString & "+1000"
Range("c6").FormulaR1C1 = "=" & BigString
Range("E6").FormulaR1C1 = BigString
Range("D6").FormulaR1C1 = "=LEN(RC[1])"
ResultString = ""
On Error Resume Next
ResultString = Range("C6").Formula
On Error GoTo 0
If Len(ResultString) > 0 Then
Range("B6") = "OK"
Else
Range("B6") = "VBA Error 1004"
End If
'-----------------------------------------------
Range("A7") = "339 Tens Plus 1 One Ten Thousand" 'String length 1022
- NG
'-----------------------------------------------
BigString = "10"
For ThisPartIndex = 2 To 339
BigString = BigString & "+10"
Next ThisPartIndex
BigString = BigString & "+10000"
Range("c7").FormulaR1C1 = "=" & BigString
Range("E7").FormulaR1C1 = BigString
Range("D7").FormulaR1C1 = "=LEN(RC[1])"
ResultString = ""
On Error Resume Next
ResultString = Range("C7").Formula
On Error GoTo 0
If Len(ResultString) > 0 Then
Range("B7") = "OK"
Else
Range("B7") = "VBA Error 1004"
End If
'-----------------------------------------------
Range("A10") = "Worksheet formula issue"
'-----------------------------------------------
Range("A11") = "450 One's" 'String length 899 - OK
'-----------------------------------------------
BigString = "1"
For ThisPartIndex = 2 To 450
BigString = BigString & "+1"
Next ThisPartIndex
Range("c11").FormulaR1C1 = "=" & BigString
Range("E11").FormulaR1C1 = BigString
Range("D11").FormulaR1C1 = "=LEN(RC[1])"
ResultString = ""
On Error Resume Next
ResultString = Range("C11").Formula
On Error GoTo 0
If Len(ResultString) > 0 Then
Range("B11") = "OK"
Else
Range("B11") = "VBA Error 1004"
End If
'-----------------------------------------------
Range("A12") = "451 One's" 'String length 901 - NG
'-----------------------------------------------
BigString = "1"
For ThisPartIndex = 2 To 451
BigString = BigString & "+1"
Next ThisPartIndex
Range("B12") = "Formula is too long"
'-----------------------------------------------
'I commented out the next line because
'it causes a runtime error:
'Application-defined or object-defined error
'-----------------------------------------------
' Range("c12").FormulaR1C1 = "=" & BigString
'-----------------------------------------------
Range("E12").FormulaR1C1 = BigString
Range("D12").FormulaR1C1 = "=LEN(RC[1])"
ResultString = ""
On Error Resume Next
ResultString = Range("C12").Formula
On Error GoTo 0
If Len(ResultString) > 0 Then
Range("B12") = "OK"
Else
Range("B12") = "VBA Error 1004"
End If

Range("D12").FormulaR1C1 = "=LEN(RC[1])"
'-----------------------------------------------
'format the Illustrations
'-----------------------------------------------
Columns("A:D").Columns.AutoFit
With Columns("E:E")
.ColumnWidth = 50
.HorizontalAlignment = xlGeneral
.WrapText = True
End With
With Columns("A:E")
.Rows.AutoFit
.VerticalAlignment = xlTop
End With
Range("A1").Select
End Sub

.
Click to expand...
 
A

alfred.vachris

Hi. Thanks for your input. But my problem is that I want to report on
existing formulas and would like to be able to get the definition of a
formula even when it is 1023 characters long.
Regards, Al Vachris
Jason said:
I would suggest first creating defined names for your
formulas, or parts of your formulas. There is also
another method in VBA that would allow you to "piece"
together a formula and then execute it. It was posted on
Dick Kusleika's blog around array formulas. See:

http://www.dicks-blog.com/archives/2005/01/10/entering-
long-array-formulas-in-vba/

HTH
Jason
Atlanta, GA
-----Original Message-----
I have run across a problem with getting a formula from VBA.
Excel specifications state that a worksheet formula can have a maximum
length of 1024 characters including the leading "=".
From the simple test that I did, it looks like VBA will
Click to expand...
not return
formulas that are longer than 1022 characters including the leading
"=".

And an even stranger result - I found a formula of length 901
characters including the leading "=" which could not be entered on a
worksheet?

I have included the VBA code that I used to create the examples.

Any ideas as to what is going on?
Regards, Al Vachris

Option Explicit
'======================================
Sub IllustrateFormulaIssues()
'======================================
'-----------------------------------------------
'add this macro to to a new workbook and run it.
'My examples are from Microsoft ExcelXP Version 11# Build 5612
'-----------------------------------------------
'run this macro to create a table of examples
'that illustrate two problems with formulas in Excel
'first issue: although the formula length can have a maximum length of
1023 characters
'the VBA cell.formula has a problem returning a string larger than
1021 characters
'and in fact there are times when it will fail even for a string of
1021 characters
'-----------------------------------------------
'second issue: found an example of a function that could not be
'entered into a worksheet even thought the formula length is less than
900 characters
'-----------------------------------------------
Dim BigString As String
Dim ThisPartIndex As Integer
Dim ResultString As String
'-----------------------------------------------
Range("A1") = "VBA MyCell.formula Issue"
'-----------------------------------------------
Range("A2") = "Description"
Range("B2") = "Result"
Range("C2") = "Formula"
Range("D2") = "Length" & Chr(10) & "of string"
Range("E2") = "String"
'-----------------------------------------------
Range("A4") = "340 Tens" 'String length 1019 - OK
'-----------------------------------------------
BigString = "10"
For ThisPartIndex = 2 To 340
BigString = BigString & "+10"
Next ThisPartIndex
Range("c4").FormulaR1C1 = "=" & BigString
Range("E4").FormulaR1C1 = BigString
Range("D4").FormulaR1C1 = "=LEN(RC[1])"
ResultString = ""
On Error Resume Next
ResultString = Range("C4").Formula
On Error GoTo 0
If Len(ResultString) > 0 Then
Range("B4") = "OK"
Else
Range("B4") = "VBA Runtime Error 1004"
End If
'-----------------------------------------------
Range("A5") = "339 Tens Plus 1 One Hundred" 'String length 1020 - OK
'-----------------------------------------------
BigString = "10"
For ThisPartIndex = 2 To 339
BigString = BigString & "+10"
Next ThisPartIndex
BigString = BigString & "+100"
Range("c5").FormulaR1C1 = "=" & BigString
Range("E5").FormulaR1C1 = BigString
Range("D5").FormulaR1C1 = "=LEN(RC[1])"
ResultString = ""
On Error Resume Next
ResultString = Range("C5").Formula
On Error GoTo 0
If Len(ResultString) > 0 Then
Range("B5") = "OK"
Else
Range("B5") = "VBA Error 1004"
End If
'-----------------------------------------------
Range("A6") = "339 Tens Plus 1 One Thousand" 'String length 1021 - OK
'-----------------------------------------------
BigString = "10"
For ThisPartIndex = 2 To 339
BigString = BigString & "+10"
Next ThisPartIndex
BigString = BigString & "+1000"
Range("c6").FormulaR1C1 = "=" & BigString
Range("E6").FormulaR1C1 = BigString
Range("D6").FormulaR1C1 = "=LEN(RC[1])"
ResultString = ""
On Error Resume Next
ResultString = Range("C6").Formula
On Error GoTo 0
If Len(ResultString) > 0 Then
Range("B6") = "OK"
Else
Range("B6") = "VBA Error 1004"
End If
'-----------------------------------------------
Range("A7") = "339 Tens Plus 1 One Ten Thousand" 'String length 1022
- NG
'-----------------------------------------------
BigString = "10"
For ThisPartIndex = 2 To 339
BigString = BigString & "+10"
Next ThisPartIndex
BigString = BigString & "+10000"
Range("c7").FormulaR1C1 = "=" & BigString
Range("E7").FormulaR1C1 = BigString
Range("D7").FormulaR1C1 = "=LEN(RC[1])"
ResultString = ""
On Error Resume Next
ResultString = Range("C7").Formula
On Error GoTo 0
If Len(ResultString) > 0 Then
Range("B7") = "OK"
Else
Range("B7") = "VBA Error 1004"
End If
'-----------------------------------------------
Range("A10") = "Worksheet formula issue"
'-----------------------------------------------
Range("A11") = "450 One's" 'String length 899 - OK
'-----------------------------------------------
BigString = "1"
For ThisPartIndex = 2 To 450
BigString = BigString & "+1"
Next ThisPartIndex
Range("c11").FormulaR1C1 = "=" & BigString
Range("E11").FormulaR1C1 = BigString
Range("D11").FormulaR1C1 = "=LEN(RC[1])"
ResultString = ""
On Error Resume Next
ResultString = Range("C11").Formula
On Error GoTo 0
If Len(ResultString) > 0 Then
Range("B11") = "OK"
Else
Range("B11") = "VBA Error 1004"
End If
'-----------------------------------------------
Range("A12") = "451 One's" 'String length 901 - NG
'-----------------------------------------------
BigString = "1"
For ThisPartIndex = 2 To 451
BigString = BigString & "+1"
Next ThisPartIndex
Range("B12") = "Formula is too long"
'-----------------------------------------------
'I commented out the next line because
'it causes a runtime error:
'Application-defined or object-defined error
'-----------------------------------------------
' Range("c12").FormulaR1C1 = "=" & BigString
'-----------------------------------------------
Range("E12").FormulaR1C1 = BigString
Range("D12").FormulaR1C1 = "=LEN(RC[1])"
ResultString = ""
On Error Resume Next
ResultString = Range("C12").Formula
On Error GoTo 0
If Len(ResultString) > 0 Then
Range("B12") = "OK"
Else
Range("B12") = "VBA Error 1004"
End If

Range("D12").FormulaR1C1 = "=LEN(RC[1])"
'-----------------------------------------------
'format the Illustrations
'-----------------------------------------------
Columns("A:D").Columns.AutoFit
With Columns("E:E")
.ColumnWidth = 50
.HorizontalAlignment = xlGeneral
.WrapText = True
End With
With Columns("A:E")
.Rows.AutoFit
.VerticalAlignment = xlTop
End With
Range("A1").Select
End Sub

.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel VBA 1
macro to copy following cells in the same column 4
Getting a message box program to run (Page 43 of 'BeginningProgramming with VBA' by Duane Birnham) 11
Macro - Formula RC[?] with ? being a variable number 5
I need help with this program written in Visual Basic for Excel 2007. 4
Worksheet Change by Value problem 3
The code below worked for a few days and just totally stoppedworking. 1
The code below worked for a few days and just totally stoppedworking. What can I do to make sure it 1

Top