Solved: Calculate cell

[gdl]

Hello,
I'm trying to make a simple thing but I can't manage to do it [working with excel 2003]

The situation is:
- in sheet n°1 I have some data tables;
- in sheet n° 2 I have to make some calculations based upon data in sheet 1
- to do this, I created some functions with no arguments (something like "Public Function calculate_this()") because I have to take a lot of inputs from sheet 1 (with some for loops)

The problem is:
- i want to re-calculate some results in sheet 2 when the user presses a button

How can I do this? I tried Worksheets(2).Calculate but it's not working.

The general question is: how to calculate a function that has no arguments and so doesn't refresh when the input values change?

Thanks a lot for you replies

Best wishes
Giorgio

[Bob Phillips]

Why/how does the calculate not work?

Have you got a workbook to post, you seem to have got somewhere along the line?

[gdl]

I tried to make a button with the related Sub "Worksheets(x).Calculate" in it but the results don't refresh..

I attached the workbook I'm working on.

As an example, you may look at sheet "E-G-B-C", cell C54.

This cell has a formula like "=Q_calc()"

and the relative code is

[vba]Public Function Q_calc()

Q = 0

For i = 1 To 21

xi = Worksheets("Z").Range("D5").Offset(i, 0).Value
Qi = Worksheets("TabB2").Range("G3").Offset(i, 0).Value
Q = Q + xi * Qi
'If Qi <> 0 Then
'MsgBox ("Qi: " & Qi & vbCrLf & _
' "xi: " & xi)
'End If
Next i

Q_calc = Q

End Function
[/vba]

What I'd like to have, is a button that recalculates the results on demand.

Hope this helps understanding the problem (even if the few comments in the code are in italian).

Thanks for your help

Giorgio

[rbrhodes]

Hi Giorgio,

"how to calculate a function that has no arguments and so doesn't refresh when the input values change?"

You don't need a button, just make the function 'volatile' and it will recalc when the input values change.

[vba]
Public Function Q_calc()

'Application.Calculation = xlCalculationManual

'//Added
Application.Volatile

Q = 0

For i = 1 To 21

xi = Worksheets("Z").Range("D5").Offset(i, 0).Value
Qi = Worksheets("TabB2").Range("G3").Offset(i, 0).Value

Q = Q + xi * Qi

Next i

Q_calc = Q

End Function

[/vba]

[gdl]

Hi rbrhodes,
thanks for your reply.

I tried using Application.Volatile but it has some issues and it doesn't work properly. Doesn't it cause recalculation of the cell only when there is a change in the same worksheet where the function is called?

More than that, I wanted to avoid recalculation at every change because it takes some time to run all the workbook.

That's why I was looking for a "calculate on demand".

I begin to think that it's impossible

Have a nice day

Giorgio

[rbrhodes]

Hi G,

Try this sub. I tested it with Application.calculation = xlmanual, etc but it actually ran slower. This is the fastest version I came up with. It simply replaces all the function formulas on sheet "E-G=B-C".

[vba]
Private Sub cmdDemand_Click()

Application.ScreenUpdating = False

Range("C5151").Formula = "=B_calc(C50)"
Range("C5454").Formula = "=Q_calc()"
Range("C5757").Formula = "=F_calc()"
Range("C6060").Formula = "=G_calc()"
Range("C6463").Formula = "=U_calc()"
Range("J61").Formula = "=K_calc()"

Application.ScreenUpdating = True


End Sub
[/vba]

[gdl]

Thanks a lot rbrhodes!

This is surely a good and simple idea that didn't come to my mind

Best regards

Giorgio