[Megadyne]

Hi everyone ... first post here.

I have a fairly complicated Excel workbook which was getting difficult to maintain so I changed a lot of cell-based calculations to VBA functions. The VBA functions are easy to read, can be commented and use global variables for defining cell, row, column and worksheet locations. Big improvement.

But - it is now so slow it's painful. I can watch the status bar grinding through the progress towards "Calculating cells: 100%". When I run in debug mode with breakpoints, I notice that some of the functions seem to be executing twice. For example I have one function that calculates values for 4 cells and it executes 8 times; I cannot see why this might happen for the life of me. Sometimes the arguments to the function are empty when they clearly should not be. So, I have a few direct questions:

1. Any ideas why functions might calculate twice?
2. Are there any ways of monitoring the way a workbook calculates cells so that I can follow the 'program flow'? (Just trying to step through using Shift+F8 would take far too long)
3. Any suggestions for tools that might help me to analyse the way in which the calculation speed could be improved?

For reasons of compatibility I have to be working with Office '97. I have tried switching calculation mode to manual in Tools|Options, but the client just complains all the time that nothing works. And doesn't seem to be able to learn to use F9.

OS: Windows XP SP3 + updates

Thanks for any help.

[OBP]

Why VBA Functions, why not do it all with VBA code?

[MRdNk]

VBA procedures or classes would be more efficient.

Can you post your code or workbook (remove sensitive data and replace with dummy data)?

[Megadyne]

OBP, MRdNk .. thanks.

OK ... (slowly) ... now I'm going to show my ignorance. What I mean is that whereas before I had hundreds of cells with formulas linking other cells, now I have a VBA module with code like:

Public Function MyCalculation(CellRange as Range) as integer

' do all kinds of stuff with the values in CellRange

End Function

then in the cells I have a formula like

= MyCalculation(A1:A36)

I've got 14 worksheets, each with buttons, checkboxes, etc and VBA code with event handlers for each one.

That's what I mean by using VBA functions. Have I got my terminology wrong as well?

[Megadyne]

MRdNk: it's a huge workbook stuffed with my client's copyright material. It would be very hard to anonymise it. But here's one function:

Public Function DoDataCheck(DataRow As Range, mode As String) As String
' Purpose: Return a 'tick' if data meets criteria, empty string if not
' Author: xxxx
' Date: 15 Mar 2009
' Change Log: 2 Apr 2009 - added AisNum and LisNum to check that the First and
' Last values are numbers (not letters or blank). Changed
' the If ckBx ... test to execute only if the data are valid.

Dim r As Integer
Dim RowName As String
Dim Att, ckBox, AisNum, LisNum As Boolean
Dim First, Last As Integer

r = DataRow.Row

ckBox = Worksheets(ReportPage).Cells(r, 1).Value
RowName = Worksheets(ReportPage).Cells(r, 2).Value

AisNum = IsNumeric(Worksheets(ReportPage).Cells(r, 3).Value)
LisNum = IsNumeric(Worksheets(ReportPage).Cells(r, 4).Value)

DoDataCheck = ""

If ckBox And RowName <> "" And AisNum And LisNum Then
' only assign these values if the data are valid
First = Worksheets(ReportPage).Cells(r, 3).Value
Last = Worksheets(ReportPage).Cells(r, 4).Value

Att = IsOK(First, Last)
If (mode = "Yes" And Att) Or (mode <> "Yes" And Not Att) Then DoDataCheck = Chr(252) ' font is WingDings. This returns a tick
End If

End Function

[MRdNk]

See if this helps:

Application.ScreenUpdating = False

before your code, and

Application.ScreenUpdating = True

at the end - this means, it won't update until the calculation is complete.

[Megadyne]

Thanks MRdNk, I can see the logic. I guess I could put those commands in the Change and SheetCalculate event handlers for all the sheets that contain data that feed into the calculations?

Quite by chance - trying to solve a different problem - I came across this page:

http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm

which has a lot of advice on speeding up calculations. I'll try working through those suggestions as well and see if that helps.

[bomb #21]

Dim r As Integer
Dim RowName As String
Dim Att, ckBox, AisNum, LisNum As Boolean
Dim First, Last As Integer

I'm pretty sure that Zack recently said Long is far more efficient than Integer.

Give that a go, maybe.

[OBP]

With that many workbooks it sounds like it should be in Access .

[Megadyne]

OBP: Only one workbook, but many sheets. In a sense you are right: my inclination was to write a client/server database application with an embedded database engine, but the client preferred Excel because of the brand recognition and transferable skills angle.

[nesr]

Dear Megadyne
One of the most important speedup actions is to do:
1- Follow the MRdNk advice to put the code

application.screenupdating= false
on  the top of ALL your subs and Functions, and put 
application.screenupdating= true
before End sub or End function.

2- Avoid using DoEvents inside loops
3- Unless debugging, Close the VB editor before testing your code (important step)
4- avoid using the way you design the worksheet by VBA functions, use VBA code instead example:
instead of putting the function =CustomFunc(Range1, Range2) in cell K6
Just remove this function from K6, then put inside your code :

---
---
---
---
'Some Calculations to find Variable01 using Range1 and Range2
---
---
---
Worksheets("WorksheetName").Cells(6,11)=Variable01' column K is the 11'th column.
---
---

This will speed up your code effectively
and Peace be upon you all...
---
Dr. Mohammad ElNesr

[Zack Barresse]

Hi there, welcome!

I've read up on Long vs. Integer, and the data is solid, but I ran some tests myself. It does speed up code, but not a bunch to notice. If you're a stickler, then yes, go with Long (some have called me that...). I also prefer it as it's easier to type.

Instead of just turning off screenupdating property, the important property to turn off is the enableevents. I do this with a sub routine call. It may get old to call every time, but I find the time it saves in efficient coding to be worth it. I think this is why your code is running twice. UDFs are volatile functions, and calculate on any change.

Sub TOGGLEEVENTS(blnState as Boolean)
'Originally written by Zack Barresse
    Application.DisplayAlerts = blnState 
    Application.EnableEvents = blnState 
    Application.Screenupdating = blnState 
    If blnState Then Application.CutCopyMode = False
    If blnState Then Application.StatusBar = False
End Sub

You'll never beat the speed of native Excel functions with using VBA. Comparatively speaking it's a slow language. It'd be a lot faster if you made an xll out of it. Of course that's a lot more work. Trimming your code down and explicitly referencing everything would help too.

Also, dimensioning like this...

Dim First, Last As Integer

.. you've set "First" as a variant because you didn't specify. Some people think that if it's on the same line it takes the variable of the last one set, but this isn't true. If not specified, it is assumed as variant type, which can be inefficient.

And where did the "ReportPage" variable come from in your UDF above? The variable isn't in the scope of the UDF, so I'm assuming it's either a public type or variable set somewhere else. And what is "IsOk()"? Looks like another call, most likely to another UDF?

[turbodante]

Originally Posted by Zack Barresse

Also, dimensioning like this...

Dim First, Last As Integer

.. you've set "First" as a variant because you didn't specify. Some people think that if it's on the same line it takes the variable of the last one set, but this isn't true. If not specified, it is assumed as variant type, which can be inefficient.

...

Ekkk

I've got reems of code that's been dimensioned as described. There's me thinking it looks neater and saves on typing...

[nesr]

Originally Posted by turbodante

Ekkk

I've got reems of code that's been dimensioned as described. There's me thinking it looks neater and saves on typing...

Yes, This actually saves typing. Microsoft realized that, but applied it only to the .net language. So if you make a VB.net project you can dimension a bunch of variables by one declarator. like:

'Remember this for .net only
Dim i, re, kk, lll, mao, eee as double

but still you can save typing too in VBA and VB6 too by typing

'This could be applied to VBA and VB6
defInt I-M ' all variables starts with letters I, J, K, L, and M 
                           are considered Integer
defDbl A-H ' all variables starts with letters A, B, ... G, H
                           are considered Double 

you can even define all variable to be initially single unless changed, to avoid the variant problem
by adding to the top of your code

defSng A-Z

easier isn't it?

[Megadyne]

Thank you all for these helpful suggestions. Let me respond one by one:

Nesr: points 1,2 and 3 - these are in hand. Point 4: Most of the functions are called multiple times so I think it would be hard to do this. However there may be some that I can change.

Zack: Long vs Integer. As you say, I would have been surprised if this had made a lot of difference, but I did it anyway and the change was indeed negligible. But very curiously the change seems to have removed a completely different bug. I was getting a VALUE! error which resolved itself when I put the cursor in the cell and clicked the green tick next to the edit bar to indicate that this was indeed the formula I wanted. If the function used/returned integer -> error; if it used/returned long -> no error. And to avoid any doubt the results are always whole numbers in the range 0 - 100, with checks in the code to handle invalid inputs or results. I am pretty sure that I made no other changes that could have removed the bug. (shrugs) Well, that's it: no more integers for me.

Zack:

enableevents

- great tip, thanks; and thanks for the code snippet. First job for today.

Zack:

dimensioning like this...

. I can only echo TurboDante: Ekkk Ekkk (that's the echo). Correction: that's the first job today!

Zack:

Trimming your code down and explicitly referencing everything..

That also gives me food for thought. My normal 'habitat' is in compiled languages and I guess I have got used to automatic optimisation by the compiler. I must revisit my code with this in mind. Thanks for the reminder.

Zack:

where did the "ReportPage" variable come from in your UDF above? The variable isn't in the scope of the UDF, so I'm assuming it's either a public type or variable set somewhere else. And what is "IsOk()"? Looks like another call, most likely to another UDF?

Yes, I define a whole bunch of Public Constants with names of worksheets, special rows/columns/cells so that if changes are necessary they only need to be made once at the top of one module. And IsOK() is another UDF call. Sorry, I should have explained that a bit more clearly. (Another code optimisation opportunity?)

Nesr:

defInt I-M ' all variables starts with letters I, J, K, L, and M are considered Integer

Gosh, that takes me back more years than I care to count. It is a workable option, but I have got so used to putting type declarations in functions, I'll put up with the extra typing.

Thanks again! Time to try all this out (rolls sleeves up).