Solved

Formulas with vba calls are not always recalculated

Posted on 2013-06-24
13
259 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:BACapta
Comment Utility
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
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 24

Expert Comment

by:Steve
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
It works!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

771 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now