Controling recalculation - or how to solve my speed problem?
 

Folks,

I am currently working on a budgeting-sheet. I have personel data on one
worksheed, expected income on another, and want to get an monthly overview
out of this. Since doing this by lookups etc. was difficult or impossible, I
started writing a VBA-function that does work properly and seems simple
enough. When doing that, Excel started becoming slooooww because of frequent
recalculations and this now takes too long. Any idea why this is the case?
Here comes my function (I have two very similar ones ...). Any hints are
appreciated!

Peter

Public Function GehaltSumme(datum, projekt) As Currency
Dim i As Integer
Dim Summe As Currency

Application.Calculation = xlCalculationManual

With Worksheets("Personal")
For i = 1 To .Range("VonSpalte").Rows.Count
If projekt = .Range("ProjektSpalte").Cells(i) Then
If datum = .Range("VonSpalte").Cells(i) Then
If (CLng(.Range("BisSpalte").Cells(i)) = 0) Or (datum <=
..Range("BisSpalte").Cells(i)) Then
Summe = Summe + .Range("BruttoSpalte").Cells(i)
End If
End If
End If
Next
End With
GehaltSumme = Summe

Application.Calculation = xlCalculationAutomatic


End Function

Controling recalculation - or how to solve my speed problem?
 

Peter,
Maybe?...
Jim Cone
San Francisco, USA

'----------------
Public Function GehaltSumme(ByRef datum As Double, _
ByRef projekt As Variant) As Double
'Assumes all named ranges are single column ranges.
Dim i As Long
Dim Summe As Double
Dim dblBS As Double
Dim rngVS As Excel.Range
Dim rngPS As Excel.Range
Dim rngBS As Excel.Range

Set rngVS = Worksheets("Personal").Range("VonSpalte").Cells
Set rngPS = Worksheets("Personal").Range("ProjektSpalte").Cell s
Set rngBS = Worksheets("Personal").Range("BruttoSpalte").Cells
Application.Calculation = xlCalculationManual

For i = 1 To rngVS.Count
If projekt = rngPS(i).Value Then
If datum = rngVS(i).Value Then
dblBS = rngBS(i).Value
If (CLng(dblBS) = 0) Or (datum <= dblBS) Then
Summe = Summe + dblBS
End If
End If
End If
Next 'i
GehaltSumme = Summe

Set rngVS = Nothing
Set rngPS = Nothing
Set rngBS = Nothing
Application.Calculation = xlCalculationAutomatic
End Function
'--------------------


"Peter Lipp"
wrote in message
t
Folks,
I am currently working on a budgeting-sheet. I have personel data on one
worksheed, expected income on another, and want to get an monthly overview
out of this. Since doing this by lookups etc. was difficult or impossible, I
started writing a VBA-function that does work properly and seems simple
enough. When doing that, Excel started becoming slooooww because of frequent
recalculations and this now takes too long. Any idea why this is the case?
Here comes my function (I have two very similar ones ...). Any hints are
appreciated!
Peter

Public Function GehaltSumme(datum, projekt) As Currency
Dim i As Integer
Dim Summe As Currency

Application.Calculation = xlCalculationManual
With Worksheets("Personal")
For i = 1 To .Range("VonSpalte").Rows.Count
If projekt = .Range("ProjektSpalte").Cells(i) Then
If datum = .Range("VonSpalte").Cells(i) Then
If (CLng(.Range("BisSpalte").Cells(i)) = 0) Or (datum <=
..Range("BisSpalte").Cells(i)) Then
Summe = Summe + .Range("BruttoSpalte").Cells(i)
End If
End If
End If
Next
End With
GehaltSumme = Summe
Application.Calculation = xlCalculationAutomatic
End Function

Controling recalculation - or how to solve my speed problem?
 

Maybe?...
Hmm, yes - it made things somewhat faster, but not as much as I would like
it to be. The major problem currently seems that it calculates too much
anyway, probably there is something else ongoing.

The results of my calculations are in one worksheet, the source in another.
Whenever I switch, it calculates (%age display), but while debugging I
noticed that it did not always call my function (didn't reach my
breakpoints, didn't change the results). Can I control better when it
recaluclates ???
Peter

Controling recalculation - or how to solve my speed problem?
 

Peter,

Are the named ranges dynamically sized or are they a
fixed size with unused rows in them?
Charles Williams says it all about Excel calculation...
http://www.decisionmodels.com/optspeedb.htm

Regards,
Jim Cone
San Francisco, USA


"Peter Lipp"
wrote in message

Maybe?...
Hmm, yes - it made things somewhat faster, but not as much as I would like
it to be. The major problem currently seems that it calculates too much
anyway, probably there is something else ongoing.

The results of my calculations are in one worksheet, the source in another.
Whenever I switch, it calculates (%age display), but while debugging I
noticed that it did not always call my function (didn't reach my
breakpoints, didn't change the results). Can I control better when it
recaluclates ???
Peter

Controling recalculation - or how to solve my speed problem?
 

Are the named ranges dynamically sized or are they a
fixed size with unused rows in them?
They are fixed (not going through all 65535 of them ;-)

Charles Williams says it all about Excel calculation...
http://www.decisionmodels.com/optspeedb.htm
Interesting. I was now able to turn the autmatic calculation off, which
partially solves my problem - but doesn't answer my question what takes so
long. Probably worth taking a look at C.Ws tool...

Peter