# Formulas with vba calls are not always recalculated

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.

B Anders
###### Who is Participating?

Commented:
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
``````

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

Commented:
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 Commented:
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

Commented:
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 Commented:
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.

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

Commented:
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)
``````
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 Commented:
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

Commented:
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

Author Commented:
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 Commented:
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?

B Anders
0

Commented:
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.

* 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 Commented:
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 Commented:
It works!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.