Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Problem with VBA returning the contents of a long formula.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
 
Posts: n/a
Default Problem with VBA returning the contents of a long formula.
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
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

Reply With QuoteReply With Quote
  #2   Report Post  
Jason Morin
 
Posts: n/a
Default
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
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

.

Reply With QuoteReply With Quote
  #3   Report Post  
 
Posts: n/a
Default
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 Morin wrote:
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
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

.


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Array Formulas and ISNUMBER Henrik Excel Worksheet Functions 1 February 10th 05 12:31 AM
Freeze Pane problem in shared workbooks JM Excel Discussion (Misc queries) 1 February 1st 05 12:04 AM
Find nth instance of a character in a string Francis Hayes (The Excel Addict) Excel Discussion (Misc queries) 7 January 21st 05 03:44 PM
Row Autofit problem Excel 2003 Matthias Klaey Excel Discussion (Misc queries) 0 January 19th 05 05:33 PM
Problem Code: Retrieving Stored Access 03 Query Bettergains Excel Discussion (Misc queries) 2 December 7th 04 04:11 PM


All times are GMT +1. The time now is 02:44 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement