|
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 |
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 . |
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 . |
| All times are GMT +1. The time now is 12:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com