1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Calculating VBA functions twice in Excel

Discussion in 'Business Applications' started by Megadyne, Apr 2, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. Megadyne

    Megadyne Thread Starter

    Joined:
    Apr 2, 2009
    Messages:
    7
    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.:mad:

    OS: Windows XP SP3 + updates

    Thanks for any help.
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    Why VBA Functions, why not do it all with VBA code?
     
  3. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    VBA procedures or classes would be more efficient.

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

    Megadyne Thread Starter

    Joined:
    Apr 2, 2009
    Messages:
    7
    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? :)
     
  5. Megadyne

    Megadyne Thread Starter

    Joined:
    Apr 2, 2009
    Messages:
    7
    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
     
  6. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    See if this helps:

    Code:
    Application.ScreenUpdating = False
    before your code, and
    Code:
    Application.ScreenUpdating = True
    at the end - this means, it won't update until the calculation is complete.
     
  7. Megadyne

    Megadyne Thread Starter

    Joined:
    Apr 2, 2009
    Messages:
    7
    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.
     
  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    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.
     
  9. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    With that many workbooks it sounds like it should be in Access :D.
     
  10. Megadyne

    Megadyne Thread Starter

    Joined:
    Apr 2, 2009
    Messages:
    7
    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. :mad:
     
  11. nesr

    nesr

    Joined:
    Nov 5, 2008
    Messages:
    51
    Dear Megadyne
    One of the most important speedup actions is to do:
    1- Follow the MRdNk advice to put the code
    Code:
    [B]application.screenupdating= false[/B][U]
    on  the top[/U] of ALL your subs and Functions, and put 
    [B]application.screenupdating= true[/B]
    [U]before[/U] 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 :
    Code:
    ---
    ---
    ---
    ---
    'Some Calculations to find [B]Variable01 [/B]using [B]Range1 [/B]and [B]Range2[/B]
    ---
    ---
    ---
    [B]Worksheets("WorksheetName").Cells(6,11)=Variable01[/B]' column K is the 11'th column.
    ---
    ---
    
    This will speed up your code effectively
    and Peace be upon you all...
    ---
    Dr. Mohammad ElNesr
     
  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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.

    Code:
    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...
    Code:
    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?
     
  13. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744

    Ekkk:eek:

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

    nesr

    Joined:
    Nov 5, 2008
    Messages:
    51
    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:
    Code:
    '[COLOR=SeaGreen][B]Remember this for .net only[/B][/COLOR]
    Dim i, re, kk, lll, mao, eee as double
    but still you can save typing too in VBA and VB6 too by typing
    Code:
    [B][COLOR=SeaGreen]'This could be applied to VBA and VB6[/COLOR][/B]
    defInt I-M[COLOR=SeaGreen] ' all variables starts with letters I, J, K, L, and M 
                               are considered Integer[/COLOR]
    defDbl A-H [COLOR=SeaGreen]' all variables starts with letters A, B, ... G, H
                               are considered Double[/COLOR]
    
    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
    Code:
    defSng A-Z
    easier isn't it?
     
  15. Megadyne

    Megadyne Thread Starter

    Joined:
    Apr 2, 2009
    Messages:
    7
    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:
    - great tip, thanks; and thanks for the code snippet. First job for today.

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

    Zack:
    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:
    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:
    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).
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Calculating functions twice
  1. chetmorrell
    Replies:
    3
    Views:
    315
  2. Gevans0666
    Replies:
    5
    Views:
    269
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/815142

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice