SurveyHouse.com Research IT Application Development

 

Excel Use
.HasFormula before using .Formula

A cell can contain 32,767 characters, but a formula can have at most 1,024 characters, see: http://support.microsoft.com/kb/264626

Referencing the Formula property of a cell which contains more than 1,024 characters will cause the Excel application to fail.

Assume the following Visual Basic declarations:

Dim WkS  As Excel.Worksheet
Dim myCell As Excel.Range
Dim sFormula As String

Suppose we want to look at all the cells on the worksheet WkS, and for each cell whose formula is "=Sheet1!C2" replace it by "=CompanyName." This would be useful if Sheet1!C2 contains the company name, but the range CompanyName, whose range is Sheet1!C2, didn't exist when the worksheet was first developed. Assume WkS is Set to an existing worksheet in an open workbook, and that UsedRange is a range that covers all cells used on the WkS worksheet. You would use the following code:


For Each myCell In WkS.UsedRange
    If myCell.HasFormula Then
        sFormula = myCell.Formula
        If (sFormula = "=Sheet1!C2") Then
            myCell.Formula = "=CompanyName"
        End If
    End If
Next

Failure to protect
myCell.Formula

by the preceding
If myCell.HasFormula Then

would cause the code to fail if the value contents of any myCell is longer than 1,024 characters.  Make sure you protect any cell for which you reference .Formula by first checking its .HasFormula property!  Also, .HasFormula will return False if myCell is empty.

Return to previous page