Size limit on VBA formulas?

[Nibbles]

Hi,

I'm writing some code that checks through a couple of hundred identical workbooks. I need to insert a value in a status column of each of these workbooks dependant on some dates contained within them.

To do this I am tying to input the following nested if formula:

IF(O2>NOW(),"Invalid - Actual RFS is in the future",IF(Q2>NOW(),"Invalid - Actual Cessation is in the future",IF(Q2>0,"Ceased",IF(P2>0,"Pending Cessation",IF(AND(Q2="",AND(P2="",AND(N2>0,O2=""))),"Planned",IF(AND(Q2="",AND(O2>0,P2="")),"In Service","Check Dates and Status"))))))

There are only 6 IFs so it is not exceeding Excel's limit of 7, and yet if I try to record a macro inputting this formula in a cell, the 'Unable to Record' message comes up. If I enter the formula directly through the VBE, it still does not work properly.

Is there a size limit on the formulas which can be entered in VBA?

Any help greatly appreciated,

Nibbs

[Nibbles]

Hi,

No worries, I've fixed the problem by typing:

ActiveCell.FormulaR1C1 = _
"IF(O2>NOW(),""Invalid - Actual RFS is in the future"",IF(Q2>NOW(),""Invalid - Actual Cessation is in the future"",IF(Q2>0,""Ceased"",IF(P2>0,""Pending Cessation"",IF(AND(Q2="""",AND(P2="""",AND(N2>0,O2=""""))),""Planned"",IF(AND(Q2="""",AND(O2>0,P2="""")),""In Service"",""Check Dates and Status""))))))"
ActiveCell.FormulaR1C1 = _
"=IF(RC[-4]>NOW(),""Invalid - Actual RFS is in the future"",IF(RC[-2]>NOW(),""Invalid - Actual Cessation is in the future"",IF(RC[-2]>0,""Ceased"",IF(RC[-3]>0,""Pending Cessation"",IF(AND(RC[-2]="""",AND(RC[-3]="""",AND(RC[-5]>0,RC[-4]=""""))),""Planned"",IF(AND(RC[-2]="""",AND(RC[-4]>0,RC[-3]="""")),""In Service"",""Check Dates and Status""))))))"

directly into the VBE. Not sure what the problem was before, appears to work now.

Nibbs

[s-o-s]

On 2002-05-09 06:14, Nibbles wrote:
Hi,

I'm writing some code that checks through a couple of hundred identical workbooks. I need to insert a value in a status column of each of these workbooks dependant on some dates contained within them.

To do this I am tying to input the following nested if formula:

IF(O2>NOW(),"Invalid - Actual RFS is in the future",IF(Q2>NOW(),"Invalid - Actual Cessation is in the future",IF(Q2>0,"Ceased",IF(P2>0,"Pending Cessation",IF(AND(Q2="",AND(P2="",AND(N2>0,O2=""))),"Planned",IF(AND(Q2="",AND(O2>0,P2="")),"In Service","Check Dates and Status"))))))

There are only 6 IFs so it is not exceeding Excel's limit of 7, and yet if I try to record a macro inputting this formula in a cell, the 'Unable to Record' message comes up. If I enter the formula directly through the VBE, it still does not work properly.

Is there a size limit on the formulas which can be entered in VBA?

Any help greatly appreciated,

Nibbs

Hi Nibbles,

Have you considered writing your own UDF to solve this for you rather than using so many nested If's. then entering the formula would be much simpler.

eg. ChkStatus(O2,Q2,P2,N2)

Function ChkStatus(Dat1, Dat2, Dat3, Dat4)
ChkStatus = "Check Dates and Status"

Tim = Now

If Dat1 > Tim Then
    ChkStatus = "Invalid - Actual RFS is in the future"
Else
    If Dat2 > Tim Then
        ChkStatus = "Invalid - Actual Cessation is in the future"
    Else
        If Dat2 > 0 Then
            ChkStatus = "Ceased"
        Else
            If Dat3 > 0 Then
                ChkStatus = "Pending Cessation"
            Else
                If Dat2 = "" And Dat3 = "" And Dat1 = "" And Dat4 > 0 Then
                    ChkStatus = "Planned"
                Else
                    If Dat2 = "" And Dat1 > "" And Dat3 = "" Then
                        ChkStatus = "In Service"
                    End If
                End If
            End If
        End If
    End If
End If

End Function

I have not got the files/data to test so please let me know if this works...

_________________




[ This Message was edited by: s-o-s on 2002-05-09 07:22 ]