How to Speed Up the File Processing i.e Formulas Vs VBA ?

Dear Forum,

I have made a WorkBook which has a lot of Formulas such as SUMPRODUCT & INDIRECT and there are several such sheets..

Now this File has become extremely slow and for every change it goes into Calculation Mode, now how do I change the approach and get the desired result and still make it faster.

If I make these Formulas into VBA will that make the File faster?

I can also adapt to an approach where the Formulas are still written in Formulas but in the VBA Mode and then convert it to Values.

Need advise on an urgent basis..

Warm Regards
e4excel

Thanks for the Inputs Mike...

How do I get the FUNCTIONALITY of having the Drop-Downs and other Formulas which are predominantly using the OFFSET function and also the INDIRECT function in certain cases..

Is there an Alternative of doing the same thing in VBA and then expediting the Processing..

Please see the Formula and the Coding for the Formatting PART...

Sub ColAFormatting()    'Title :- Sr. No
Dim ColARng As Range
Dim ColB As Long

With ActiveSheet
        ColB = .Cells(.Rows.Count, "B").End(xlUp).Row
        Set ColARng = .Range("A2:A" & CStr(ColB))

End With

'ColARng.Formula = "=IF($B2="""","""",ROWS($B$2:$B2))"
Const ColA_SrNo As String = "=IF($B2="""","""",ROWS($B$2:$B2))"

    With Range("A2:A" & ColB)
    
        .Formula = Replace(ColA_SrNo, "<row>", ColB)
        .Value = .Value
        
    End With

    With ColARng.Font
        .Name = "Book Antiqua"
        .Size = 12
        .Bold = True
        .Color = -16777024
        .TintAndShade = 0
    End With
    
    HorizontalCenterAlignment ColARng
    ColARng.NumberFormat = "General"
    
End Sub

Will this help if I convert the Formulas into Values , in this example I have not mentioned any OFFSET & INDIRECT Formulas but I have a lot of Defined Names using them..

So using them via the VBA mode will it speed up the process..

Warm Regards
e4excel

If the calculations are slowing you down, you can change it to Manual. Anytime you need the calculations to be performed, you can press F9.

Thanks Arlu, I was not aware of this feature however, is there a way that by changing the approach here and there still get the work getting done a little faster..

The earlier VBA code was just an example but I would like to know writing the same formulas through VBA do they save on the memory by any chance..

As I have a lot of SUMPRODUCTS, INDIRECT , OFFSET & MULTILOOKUP INDEX ARRAY FUNCTIONS..

Warm Regards
e4excel

I havent encountered a problem like this...but its worth giving a try. And you can compare based on time taken to execute.

If it works out good for you, its gr8. Let me know if you need any help in converting any part of your file to VBA.

VBA can be faster than formulas, depending on the formula and how well written the code is, of course.

This article,
http://blogs.office.com/b/microsoft-...practices.aspx ,
Talks about what to do and not do in regards to VBA. And this article,
http://fastexcel.wordpress.com/2011/...a-udfs-part-1/ ,
talks about UDF specifically and ways to improve performance. Also, you want to avoid creating a volitile UDF so that it only calculates when related data is updated. Keep reading this series in this blog (I think he's up to part 6), it will really help you understand UDF best practices.

Originally Posted by mikerickson

There are non-volatile alternatives to OFFSET.

=OFFSET(A1,0,0,COUNTA(A:A),1)

can be replaced with
=INDEX(A:A,1,1):INDEX(A:A,COUNTA(A:A),1)

for example, but there is no generic replacement.

Thanks for all the inputs ..

I think I am not able to convey correctly what I mean, being fairly new in VBA I use a Formula Approach even while writing codes in VBA...

Me learning VBA is definitely a long process however , if I were to replace every formula written in VBA using the Application,WorkSheetFunctions Approach and then converting it into values is it going to help? to make it faster..

The OFFSET is used in the Dynamic Dropdowns, I have never tired the INDEX though I would be more than happy to try that out..

Since there a lot of Unequal Columns I tend to use the ENtire Columns or ELse Indirect("A"&Counta(B:B))..

So how do i use it sparingly..

Warm Regards
e4excel

INDEX() will return a reference, so

=Index(A:A,Counta(B:B))

will return the same reference as

=Indirect("A"&Counta(B:B)

but is not a volatile function. Indirect will calculate every time there is a change in the worksheet. Since you are converting a string to a cell reference, there is no way for Excel to know what cells may affect the value, without calculating it. So, it has to calculate every time there is a change. Index, on the other hand, does not deal with strings, and so only needs to calculate when the cells referenced inside the parentheses change.


As for your question about converting all functions to UDFs that use the Application.WorksheetFunctions, I cannot see how this would improve speed. You are calling the same function, but adding in the need to send the cell information to the VBE before running the function. This would only slow it down, I expect.

Thanks WHizbang,

I have changed the OFFSET formula to an INDEX Function formula as shown in your post but with a slight Modification as my list also contains the forced blank values such as "" so the Modified code is as below:

=INDEX('Un-Sorted Cities'!$F:$F,1,1):INDEX('Un-Sorted Cities'!$F:$F,1048576-COUNTBLANK('Sorted Cities'!$F:$F)-1,1)

Is there a way of improving this further to make it more faster...No in this case I had to use the Total No of Cells - COUNTBLANK(COLUMN) - 1 ...

Total No of Cells - 1048576 ----- ???
COUNTBLANK(COLUMN)..

Warm Regards
e4excel

Counta will count a cell that evaluates to "". If the cell has a formula at all, it will count it. So there is no need for countblanks. You would only need that if, for some reason, you had truly empty cells mixed in with your range.

Dear Whizbang,

I m getting a list created Automatically by virtue of Formulas and therefore the Formula is dragged down, where there are no values or if error then I am forcing blanks with a pair of double quotes..

However, I need the same list to be used in a Drop-Down and therefore there are Mixed Blanks i.e Some which are Forced by the Formula and some which are Actual After the Formula Ends..

So how do I counter the same...?

I have changed all the formulas to Index and will now refrain using the OFFSET or rather use it sparingly...

Please advise....Inspite of following the advice to the Tee I am still facing the Problems..'

I wanted to ask if there are some Merged Cells which are Colored with Bigger Fonts as Column Headings and they are Merged to create Groups..

Can this be one of the reasons to make the File Slow..

If I have Linked Formulas in each Sheet referencing with VLOOKUPS + MATCH Functions then will it be better to break the several sheets into seperate Files..

If I have made separate sheets which contain the data for the Drop-Down Lists then will be faster to access the Data from Different Files..

I am actually at my Wits End now?

Warm Regards
e4excel

Would you be able to post a workbook so that we can look at the structure and give more accurate and helpful advice? Remember to clean out any sensitive data.

Dear Whizbang,

I think that will be better but I will have to remove the sensitive information and therefore will take some time but definitely will have to upload the workbook..

But does simply merging cells and having lot of colors can make the file slow.

Warm Regards
e4excel

Dear Forum and Whizbang,

Really pressed for time so will take time to upload the Workbook as need to strip the Confidential Information from the same...

But if I break the Sheets into Individual Files, Can it Save Time.?

Please respond asap..!

Warm Regards
e4excel

I do not think that font colors and cell merging can cause slowness. These are static values. Merging cells can cause errors when you try to do calculations on them, but if the merged cells are just labels and do not interfere with any formula calculations, they will not slow your workbook.

I also do not believe splitting the workbook up will help, though I am not certain of this at all. The only way to know for sure would be to test it out. I imagine, though, that at least some of your functions will stop working. INDIRECT(), for instance, does not work on a closed workbook. Neither does VLOOKUP. SO I imagine that there would be a bit of work involved before the split workbooks would work properly and you could know for certain whether or not it is faster split.

I imagine your problem is due to array formulas, or formulas like SUMPRODUCT. Sometimes these formulas are necessary and you just have to take the speed hit, but a lot of times it just needs a redesign of your data, and maybe a helper column or two, to speed things back up.

Without a sample workbook I cannot say anything more than general statements.

Thanks for the valuable inputs Whizbang, unfortunately I am unable to attach the WorkBook at the present moment however I will be doing that for sure over the weekend or during Dwali Holidays..

I wish I could do the re-designing with the Helper Columns but really pressed for time..to even re-work on the thing..

You are right, have got Multilookup Array Formulas and also SUmproduct Formulas..
have eliminated the INDIRECT usage to the minimum and so the chances of the speed staying constant is remote...

If I were to do the calculation on the press of a button for different sheets with VBA and then convert the same values which are arrived with the Formulas into Plain values..

Can that save time?

believe me I hate doing this again without attaching the file but really am in bad shape..

Warm regards
e4excel

Sure. Working with hard values is always faster than formulas. The problem then becomes updating it. If you keep a "Formula" workbook and a "Values" workbook, and generate the "Values" from the "Formulas" I imagine this should be workable. Just label the "Values" workbook with a date and time for the values so there won't be any confusion. Also, don't edit any values in the "Values" workbook, as this would invalidate it, of course.

Dear Whizbang,

The idea of doing this is slightly different than saving it as just values..
There are several sheets linked to each other and then they are not required to always show formulas so what I am thinking is I can either keep a Command Button and then have them updated Automatically an immediately turn them into values now this is after I click the button or else if i keep it on change in one or two sheets is that again going to make the sheet slower..

Like one Sheet is the inventory and has some vital plot information and the other is the Booking details where all of the information is achieved through INDEX from this sheet and then some of the information is typed manually now there are certain sheets which are linked to 2 or more sheets ...

So doing this linking continuously via VBA and then again converting into values be a time-saver?

Warm regards
e4excel.

It would stop the slowness for every time there is a change. But it wouldn't necessarily mean your code would run faster. It would just keep the speed of the workbook up between times when you run your code to update the workbook.

=SUMPRODUCT(--($D:$D=MATCH(L$1,$L$1:$N$1,0)),($F:$F))

Is there a way of improving the formula if i have several types of such formulas..

The range keeps on increasing so cant keep on changing it each time so either can use the Entire Column or AFAIK its just INDIRECT..

How can I replace the entire Columns then without affectung the speed and also maintaining the feature of not explicitly mentioning the Range.

Warm Regards
e4excel

I just reread the thread and realized that using Index for dynamic formulas has already been discussed.

So, perhaps I should stress this point from my last post:

Originally Posted by Whizbang

Also, using named ranges will allow Excel to calculate the size of the range only once, rather than every for every cell that uses your sumproduct formula.

Named ranges are great. They allow you to use a formula to calculate a value, and then use that same value or reference throughout the workbook. As I stated above, using named ranges prevents Excel from calculating the value or reference for every iteration of a formula. If you have copied a formula down a column, and that formula has duplicate calculations, then you need to pull those calculations out and into either a single cell or into a named range. Doing so will allow Excel to do that calculation only once, and speed up your workbook considerably.

Dear Whizbang AND MikeRickson,

This thread has been so far very informative and revolutionary as I was always using the INDIRECT function and now on I can always try the INDEX function to get the Exclusive Range...

Now I was trying to get the Range for the Sumproduct and i changed the code to this but it still gives an VALUE ERROR..

=SUMPRODUCT(--(PhaseRng=MATCH(L$1,$L$1:$N$1,0)),(SQFTRng))

where the PhaseRng is to be D3:D210 and then the Formula is to be used in the CELL - L2

PhaseRng = =INDEX(Inventory!$D:$D,3,1):INDEX(Inventory!$D:$D,COUNTA(Inventory!$D:$D),1)

SQFTRng = =INDEX(Inventory!$G:$G,3,1):INDEX(Inventory!$G:$G,COUNTA(Inventory!$G:$G),1)

Is it because the Starting Range is D3 and the Used Range is L2..

Warm Regards
e4excel