LinkBack URL
About LinkBacksI 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
Register To Reply
Bookmarks