Formula too long in VBA

[forgetso]

I have a formula which is too long. I have tried to span it over more than one line by typing a space and then an underscore but I still get a syntax error.

Here is the formula:

<code>

"=IF(ISNA(INDEX(StationInfo!$A$2:" & StationEndCell & ",MATCH(Summary!" & Station1 & ",StationInfo!$B$2:" & StationNameEndCell & ",0),3)),0, INDEX(StationInfo!$A$2:" & StationEndCell & ",MATCH(Summary!" & Station1 & ",StationInfo!$B$2:" & StationNameEndCell & ",0),3)) * IF(ISNA(INDEX(DailyImpacts!$I$3:$o$50,MATCH(Summary!" & Station1 & ",DailyImpacts!$o$3:$o$50,0),1)),0, INDEX(DailyImpacts!$I$3:$o$50,MATCH(Summary!" & Station1 & ",DailyImpacts!$o$3:$o$50,0),1)) +IF(ISNA(INDEX(StationInfo!$A$2:" & StationEndCell & ",MATCH(Summary!" & Station2 & ",StationInfo!$B$2:" & StationNameEndCell & ",0),3)),0, INDEX(StationInfo!$A$2:" & StationEndCell & ",MATCH(Summary!" & Station2 & ",StationInfo!$B$2:" & StationNameEndCell & ",0),3)) * IF(ISNA(INDEX(DailyImpacts!$I$3:$o$50,MATCH(Summary!" & Station2 & ",DailyImpacts!$o$3:$o$50,0),1)),0, INDEX(DailyImpacts!$I$3:$o$50,MATCH(Summary!" & Station2 & ",DailyImpacts!$o$3:$o$50,0),1)) + IF(ISNA(INDEX(StationInfo!$A$2:" & StationEndCell & ",MATCH(Summary!" & Station3 & ",StationInfo!$B$2:" & StationNameEndCell & ",0),3)),0, INDEX(StationInfo!$A$2:" & StationEndCell & ",MATCH(Summary!" & Station3 & ",StationInfo!$B$2:" & StationNameEndCell & ",0),3)) * IF(ISNA(INDEX(DailyImpacts!$I$3:$o$50,MATCH(Summary!" & Station3 & ",DailyImpacts!$o$3:$o$50,0),1)),0, INDEX(DailyImpacts!$I$3:$o$50,MATCH(Summary!" & Station3 & ",DailyImpacts!$o$3:$o$50,0),1))”

</code>

Any ideas?

EDIT:

I have since changed the variable names: StationNameEndCell to SNEC and StationEndCell to SEC but it is still too long.

 

[xld]

Break it up into separate cells, each of the IF statements, and then have afinal cell that multiplies and adds as required.

 

[forgetso]

Thanks. Due to character restriction of 1024 that is the only possible solution.

 

[Jonmo1]

I wouldn't say that's the ONLY solution....

Since these ranges
DailyImpacts!$o$3:$o$50
DailyImpacts!$i$3:$o$50
StationInfo!$A$2
StationInfo!$B$2

are used MANY times in the formula, considier using a named ranges for them. that would cut down the length of the formula considerably.

By replacing those 4 ranges with 2 letter named ranges, I reduced the length of the formula by 420 characters...

 

[xld]

I wouldn't say that's the ONLY solution....

Since these ranges
DailyImpacts!$o$3:$o$50
DailyImpacts!$i$3:$o$50
StationInfo!$A$2
StationInfo!$B$2

are used MANY times in the formula, considier using a named ranges for them. that would cut down the length of the formula considerably.

By replacing those 4 ranges with 2 letter named ranges, I reduced the length of the formula by 420 characters...

but far more inefficient that splitting the formula up.

 

[forgetso]

I would eventually be accommodating for 10 stations so even if I did make all the ranges named it would still get too long.

Cheers for help

 

[Damon Ostrander]

Hi forgetso,

Another option is to write a user-defined function (UDF) in VBA. Usually this drastically reduces the length of the cell formula, and also makes it much more readable and understandable. In the case of your formula it would also increase efficiency by eliminating redundant operations, such as the multiple MATCH and INDEX operations.

Often in very large workbooks with lots of long formulas this can also reduce the file size considerably.

If you would like to explore this or need some help with this, let me know.

Damon

 

[forgetso]

Hi,

I have written a function as follows:

<code>

Option Explicit

Public Function indexmatch(c As Integer)

Dim PhoneMax As Integer
Dim a As Integer
Dim mylastrow As Long
Dim impact_row, cpt_row As Integer
Dim Cost As Long
Dim impact As Long
Dim cpt As Long


Sheets("StationInfo").Select

mylastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
PhoneMax = Application.WorksheetFunction.Max(Range(Cells(2, 4), Cells(mylastrow, 4)))

Sheets("Summary").Select

Cost = 0

For a = 2 To PhoneMax

If Not Sheets("DailyImpacts").Cells.Find(Sheets("Summary").Cells(a, c)) Is Nothing And Not IsEmpty(Sheets("Summary").Cells(a, c)) Then

impact_row = Sheets("DailyImpacts").Cells.Find(Sheets("Summary").Cells(a, c)).Row

cpt_row = Sheets("StationInfo").Cells.Find(Sheets("Summary").Cells(a, c)).Row

impact = Sheets("DailyImpacts").Cells(impact_row, 9)

cpt = Sheets("StationInfo").Cells(cpt_row, 3)

Cost = Cost + impact * cpt

Else

Cost = Cost

End If

Next

End Function

</code>

The code runs, however, no values are ever submitted into the cells. Any ideas?


EDIT COST ISN'T BEING ASSIGNED ANY VALUE. I have changed impact, cpt and cost variable types to variant.

 

[Jonmo1]

I'm guessing the last line should be

indexmatch = Cost