Solved

Formulas with vba calls are not always recalculated

Posted on 2013-06-24
13
287 Views
Last Modified: 2013-07-02
Hi,

I have an application with several sheets where the user input data. The data on those sheets are summarized on several sheets and instead of using standard functions like =sum etc, vba code is used.

If the user activates a summary sheet, the worksheet_activate event is used to recalculate the sheet. But often but not always, the vba code is not executed.

The call to vba code have no links to the other sheets, just range or byval parameters that have data from the activesheet.

How to work around this so that the vba code is always executed in the formulas? Anything to set?

The CalculateFull method seems to work better but it slows down the application a lot since all seems to be executed.

I have tried both the automatic calculation to be turned on or off.

Thanks in advance.

B Anders
0
Comment
Question by:BACapta
  • 7
  • 6
13 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 39271773
OK, if you do not pass the cels to be passed into the function it will not automatically re-calculate on chenges (the behaviour you are seeing)

You could add Application.Volatile (True) to the start of the function.
but this can have the effect of increaing the load on the workbook.
0
 

Author Comment

by:BACapta
ID: 39273768
It is true, the volatile is making it unacceptable heavy to use. There must be a work around.

I have tried to use a dummy cell contain a flag (-1 = calculte the value, otherwise use the old). The dummy cell is passed to the vba code as a Range object. The dummy cell shall when the last cell in the row is calculated be cleared. I have no success to clear the dummy cell. How can I come around the problem?

The function

Public Function GetSaljProgress(ByVal vecka As Integer, ByVal ProgressTyper As String, EnHuvudRollTyp, dummy As Range, oldvalue, Optional ExisterandeProgressTyp = "")

and the call in the cell =GetSaljProgress(R1C155;"P1";RIGHT(R3C1;2);RC158;RC)

The dummy is used decide to calculate a new value or not.

The clearings I have tried: dummy.clear or dummy="" or dummy = empty as well as

    Set sht = ActiveSheet
    Set rng = sht.Cells(dummy.Row, dummy.Column)
    rng.Value = ""
0
 
LVL 24

Expert Comment

by:Steve
ID: 39273837
Could you post the entire function in a "dummy" workbook?

You should be OK unless you start to call things like cell.offset(1,0).value

It is always easiest to provide a solution with the assistance of a test workbook.

As for clearing the dummy cell:
Functions cannot interact with cells... they are a sausage machine... stuff incredients in and out comes the right sausage (hopefully)... it cannot make changes to anything other than the answer... it just pops a sausage out based upon your recipie, which then goes into the cell the function is in.

(It is breakfast time here, so reading back that last bit, I think I am hungry)

Thanks,
Steve.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:BACapta
ID: 39273917
Hi Steve,

A few Words about how it is meant to work. First a note, there are originally 3 sheets where the user input data. The Progress of sales ("Progress försäljning", P) is the only one left. The code used in the upper region of the Sales and time report sheet ("Sälj-och tidrapport", ST) is using the P sheet for summering events for each week (row 1 of ST).

My idea is that P sheet set the approriate row to be update by setting -1 in the column after the headings. This is not implemented. This simulated in the worksheet_activate event for ST.

What I am aiming to either let the proc GetSaljProgress to clear the -1, or in the worksheet_activate after the .calculate clear the -1s.

I have also tested to use some like "=If(R5C158, GetSaljProgress ( ....),RC) but ....

Hopefully this will sort out some of the mysteries of the code.

Thanks in advance.

B Anders
Test-25-june-2013.xlsm
0
 
LVL 24

Expert Comment

by:Steve
ID: 39273941
After a quick look there are a few little and one big one...

The big issue is the use of the sht object and rng object:
Set sht = ThisWorkbook.Worksheets("Progress Försäljning")

hdrPstartCol = 1
Set rng = sht.Cells(hdrRow, hdrPstartCol)

Open in new window

you need to pass the rng object to the function rather than calling it from within the function.
Then you would work with the range as you have.

Little ones are:
% = Integer, Row and Column are Long '&'
This will not effect the code unless you get huge data sets.

I will have a look and see if I can get a handle on the code and change the range variable.
It may be best to use a named range to pass the rng.
0
 

Author Comment

by:BACapta
ID: 39274025
Steve,
my main problem is to get the "dummy" to clear, so the update of that row is not performed again until the psheet set that one again. It is not the sht and rng objects, they work perfect and they locals.

I am normally using access.

Regards,
B Anders
0
 
LVL 24

Expert Comment

by:Steve
ID: 39274106
The problem (I assure you) is the Sht and Rng objects.

The rng needs to be passed into the function for it to be a dependant and thus re-calculate on changes.

There are other small problems...

CurValue cannot be used as it is in it's self the result of the formula, this is circular logic and will not work.
You cannot use a function to set another cell to another value.

The use of rng and sht will work if the function is called from code. This is because the code is the trigger to calculate. But if these are not passed to the function excel does not know them to be dependent and so does not recalculate on changes.

I have attached the formula I have so far from yours, but I am struggling with it's logic.
Could you explain the steps it is going through and the result it should be.

I have no doubt that the function works... it just is not written in a "cell function" freindly manner. After a little re-jiggle of the function I am sure it will do exactly what you desire without the need for extra cell values (which you cannot effect from a function).

ATB
Steve.
Test-25-june-2013.xlsm
0
 
LVL 24

Accepted Solution

by:
Steve earned 250 total points
ID: 39274471
Attached is your workbook with a modified formula.

Option Explicit

Public Function GetSaljProgressEE(ByVal vecka As Integer, ByVal ProgressTyper As String, EnHuvudRollTyp, rng As Range)

Dim iCol%, iRow%
Dim curhdrsCols, p&
Dim RollCol As Long
Dim myArray
myArray = rng.Value

Debug.Print "GetSaljProgressEE", "vecka=" & vecka, "ProgressTyper=" & ProgressTyper, "EnHuvudRollTyp=" & EnHuvudRollTyp
  
curhdrsCols = Split(ProgressTyper, ";")

For p = 0 To UBound(curhdrsCols)

    For iCol = LBound(myArray, 2) To UBound(myArray, 2)
        If myArray(1, iCol) = "Roll" Then RollCol = iCol
        
        If myArray(1, iCol) = curhdrsCols(p) Then
            
            For iRow = LBound(myArray, 1) + 1 To UBound(myArray, 1)
                
                If Right(myArray(iRow, iCol), 2) = vecka Then
                    
                    GetSaljProgressEE = GetSaljProgressEE + 1

                End If
                
            Next iRow
        
        End If
    
    Next iCol

Next p

End Function

Open in new window


This should behave as you wanted (or how I understand you needed)
take a look and see if it needs changing.

you will notice I have added a table to one of the sheets (using Data>insert>Table)
This is to allow the range to be dynamic as easiliy as possible.
Test-25-june-2013.xlsm
0
 

Author Comment

by:BACapta
ID: 39280731
Steve,

You were right. It is the rng that was the big problem. Previously, if calculated at all, it toke 3 - 4 seconds on a workstation, now it takes 0.2 s.

A couple of questions:

* Is it possible to remove the filter dropdown boxes for table you used?
* Is it possible to use any other object or statement like =Listor!R2C18:R4C18 or a named range? I have another part of the application which has quite static ranges - if they change I could change the range of the Name variable.

It seems that the same methods we used in the early 80ies for to get performance still works, I think using an Array for a range of values.

Many thanks for the help!

B Anders
0
 

Author Comment

by:BACapta
ID: 39283500
Another question!

Since it seems I am locked out from using the Name manager to change the table ranges because I use them in Formulas with function calls using the tables. Is it anyway to change the range by VBA?

Thanks in advance.

B Anders
0
 
LVL 24

Expert Comment

by:Steve
ID: 39292297
Hi there B, sorry for taking so long to answer you, but I have been in a field for the past 5 days while the British BP was on. So no internet access.

To answer your two first points:
* Is it possible to remove the filter dropdown boxes for table you used?
Yes, on the table you can simply turn Filter off as normal (under Data tab)

* Is it possible to use any other object or statement like =Listor!R2C18:R4C18 or a named range? I have another part of the application which has quite static ranges - if they change I could change the range of the Name variable.
How you feed the range into the formula is not that important, the important part is that you feed it into the formula. I used a table as it is easiest to maintain as rows are added. You can use named ranges, offset ranges or just select the range (sometimes excel will increment the formula as rows are added)

As for the last question... you cannot change the Range in VBA, as this will stop the function from re-calculating when cells in the ranges change.

The main problem is that a Function may behave like a Subroutine, but for it to work as a Worksheet Function it must have the information from the sheet passed to it for AutoRecalculation to work.

ATB
Steve.
0
 

Author Comment

by:BACapta
ID: 39294103
Thanks Steve,

we have a more serious problem but has Little or nothing to do with this problem for whick I will ask a new qestion.

By the way, I have chosen to not have the table but an ordinary range because of the users choice.

B Anders
0
 

Author Closing Comment

by:BACapta
ID: 39294106
It works!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question