Setting a value is slow
 

Hi,

I've wrote a function to calc some part of a worksheet, but when I set
a value in a cell this is really slow. If I check if the value have
changed before setting it, my function run pretty fast (My values
change rarely).

The ScreenUpdating And EnableEvents are setting to false, should I set
another thing to solve my problem?

Thanks

Luc

Setting a value is slow
 

What event/trigger are you using to call the function?

" wrote:

Hi,

I've wrote a function to calc some part of a worksheet, but when I set
a value in a cell this is really slow. If I check if the value have
changed before setting it, my function run pretty fast (My values
change rarely).

The ScreenUpdating And EnableEvents are setting to false, should I set
another thing to solve my problem?

Thanks

Luc

Setting a value is slow
 

By now it's a function call by a button, but same problem with
Worksheet_Calculate

Dan Hatola wrote:
What event/trigger are you using to call the function?

" wrote:

Hi,

I've wrote a function to calc some part of a worksheet, but when I set
a value in a cell this is really slow. If I check if the value have
changed before setting it, my function run pretty fast (My values
change rarely).

The ScreenUpdating And EnableEvents are setting to false, should I set
another thing to solve my problem?

Thanks

Luc

Setting a value is slow
 

Hi Luc,

can you show your code?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message ups.com...
| Hi,
|
| I've wrote a function to calc some part of a worksheet, but when I set
| a value in a cell this is really slow. If I check if the value have
| changed before setting it, my function run pretty fast (My values
| change rarely).
|
| The ScreenUpdating And EnableEvents are setting to false, should I set
| another thing to solve my problem?
|
| Thanks
|
| Luc
|

Setting a value is slow
 

Hi Niek,

Here a part of my code.

Without the condition to verify if the value of ColN have changed, it
takes 31 seconds to execute, else it takes only 11 seconds. I dont't
worry to leave the condition, but I'd like to understand what I doing
wrong.

Thanks

Luc

Private Sub btnCalculate_Click()
Dim TransactRow As Long
Dim Policy As String
Dim PolicySheet As Worksheet
Dim TransactionSheet As Worksheet
Dim PolicyRow As Range
Dim ColN As Range

Application.ScreenUpdating = False
Application.EnableEvents = False

Set PolicySheet = Workbooks("VPC System
Transactions.xls").Sheets("Policy listing")
Set TransactionSheet = Application.Workbooks("VPC System
Production.xls").Sheets("Transaction")

TransactRow = 8

Do
Policy = TransactionSheet.Range("A" +
Format(TransactRow)).Value

If Policy < "" Then
Set PolicyRow = PolicySheet.Range("A8:A" +
Format(PolicySheet.UsedRange.Rows.Count)).Find(Pol icy,
LookIn:=xlValues)
If Not PolicyRow Is Nothing Then
Set ColN = TransactionSheet.Range("N" +
Format(TransactRow))

ColNValue = PolicyRow.Columns(39).Value
If ColN.Value < ColNValue Then
ColN.Value = ColNValue
End If
End If
End If
TransactRow = TransactRow + 1
Loop While Policy < ""
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


Niek Otten wrote:
Hi Luc,

can you show your code?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message ups.com...
| Hi,
|
| I've wrote a function to calc some part of a worksheet, but when I set
| a value in a cell this is really slow. If I check if the value have
| changed before setting it, my function run pretty fast (My values
| change rarely).
|
| The ScreenUpdating And EnableEvents are setting to false, should I set
| another thing to solve my problem?
|
| Thanks
|
| Luc
|

Setting a value is slow
 

A quick thought is that if the sheet has many formulas, you might try turning
off calculation with:
Application.Calculation = xlCalculationManual

Be sure to remember to turn it back on again when you are finished:

Application.Calculation = xlCalculationAutomatic

Dan

" wrote:

Hi Niek,

Here a part of my code.

Without the condition to verify if the value of ColN have changed, it
takes 31 seconds to execute, else it takes only 11 seconds. I dont't
worry to leave the condition, but I'd like to understand what I doing
wrong.

Thanks

Luc

Private Sub btnCalculate_Click()
Dim TransactRow As Long
Dim Policy As String
Dim PolicySheet As Worksheet
Dim TransactionSheet As Worksheet
Dim PolicyRow As Range
Dim ColN As Range

Application.ScreenUpdating = False
Application.EnableEvents = False

Set PolicySheet = Workbooks("VPC System
Transactions.xls").Sheets("Policy listing")
Set TransactionSheet = Application.Workbooks("VPC System
Production.xls").Sheets("Transaction")

TransactRow = 8

Do
Policy = TransactionSheet.Range("A" +
Format(TransactRow)).Value

If Policy < "" Then
Set PolicyRow = PolicySheet.Range("A8:A" +
Format(PolicySheet.UsedRange.Rows.Count)).Find(Pol icy,
LookIn:=xlValues)
If Not PolicyRow Is Nothing Then
Set ColN = TransactionSheet.Range("N" +
Format(TransactRow))

ColNValue = PolicyRow.Columns(39).Value
If ColN.Value < ColNValue Then
ColN.Value = ColNValue
End If
End If
End If
TransactRow = TransactRow + 1
Loop While Policy < ""
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


Niek Otten wrote:
Hi Luc,

can you show your code?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message ups.com...
| Hi,
|
| I've wrote a function to calc some part of a worksheet, but when I set
| a value in a cell this is really slow. If I check if the value have
| changed before setting it, my function run pretty fast (My values
| change rarely).
|
| The ScreenUpdating And EnableEvents are setting to false, should I set
| another thing to solve my problem?
|
| Thanks
|
| Luc
|

Setting a value is slow
 

The calculation is already set to manual in the options.

Luc

Dan Hatola wrote:
A quick thought is that if the sheet has many formulas, you might try turning
off calculation with:
Application.Calculation = xlCalculationManual

Be sure to remember to turn it back on again when you are finished:

Application.Calculation = xlCalculationAutomatic

Dan

" wrote:

Hi Niek,

Here a part of my code.

Without the condition to verify if the value of ColN have changed, it
takes 31 seconds to execute, else it takes only 11 seconds. I dont't
worry to leave the condition, but I'd like to understand what I doing
wrong.

Thanks

Luc

Private Sub btnCalculate_Click()
Dim TransactRow As Long
Dim Policy As String
Dim PolicySheet As Worksheet
Dim TransactionSheet As Worksheet
Dim PolicyRow As Range
Dim ColN As Range

Application.ScreenUpdating = False
Application.EnableEvents = False

Set PolicySheet = Workbooks("VPC System
Transactions.xls").Sheets("Policy listing")
Set TransactionSheet = Application.Workbooks("VPC System
Production.xls").Sheets("Transaction")

TransactRow = 8

Do
Policy = TransactionSheet.Range("A" +
Format(TransactRow)).Value

If Policy < "" Then
Set PolicyRow = PolicySheet.Range("A8:A" +
Format(PolicySheet.UsedRange.Rows.Count)).Find(Pol icy,
LookIn:=xlValues)
If Not PolicyRow Is Nothing Then
Set ColN = TransactionSheet.Range("N" +
Format(TransactRow))

ColNValue = PolicyRow.Columns(39).Value
If ColN.Value < ColNValue Then
ColN.Value = ColNValue
End If
End If
End If
TransactRow = TransactRow + 1
Loop While Policy < ""
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


Niek Otten wrote:
Hi Luc,

can you show your code?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message ups.com...
| Hi,
|
| I've wrote a function to calc some part of a worksheet, but when I set
| a value in a cell this is really slow. If I check if the value have
| changed before setting it, my function run pretty fast (My values
| change rarely).
|
| The ScreenUpdating And EnableEvents are setting to false, should I set
| another thing to solve my problem?
|
| Thanks
|
| Luc
|

Setting a value is slow
 

My only other thoughts (although I doubt any will have a major impact):

1) Dimension ColNValue

2) Create a variable like SearchRng and set it = PolicySheet.Range("A8:A" +
PolicySheet.UsedRange.Rows.Count) outside of the loop. Inside the loop use
SearchRng.Find(Policy, LookIn:=xlValues)

3) If ColN is only used to determine the Row number for matching, the let
ColN be a value instead of a range. It might make the comparison faster.

ColN = TransactionSheet.Range("N" + Format(TransactRow)).value
ColNValue = PolicyRow.Columns(39).Value
If ColN < ColNValue Then
ColN = ColNValue
End If
End If

4) I think it is slightly faster to use the "&" instead of the "+" with the
format() function when creating the ranges. Try: TransactionSheet.Range("N"
& TransactRow). It is in several places.

I agree that I do not understand why it is faster to check first then to
just change all of the values. I know this is true with certain formats, but
I didn't think it was true with values. I suspected that it was because you
had calculation on and Excel was recalculating after each value changed, but
you said that was not the case.

Dan

" wrote:

The calculation is already set to manual in the options.

Luc

Dan Hatola wrote:
A quick thought is that if the sheet has many formulas, you might try turning
off calculation with:
Application.Calculation = xlCalculationManual

Be sure to remember to turn it back on again when you are finished:

Application.Calculation = xlCalculationAutomatic

Dan

" wrote:

Hi Niek,

Here a part of my code.

Without the condition to verify if the value of ColN have changed, it
takes 31 seconds to execute, else it takes only 11 seconds. I dont't
worry to leave the condition, but I'd like to understand what I doing
wrong.

Thanks

Luc

Private Sub btnCalculate_Click()
Dim TransactRow As Long
Dim Policy As String
Dim PolicySheet As Worksheet
Dim TransactionSheet As Worksheet
Dim PolicyRow As Range
Dim ColN As Range

Application.ScreenUpdating = False
Application.EnableEvents = False

Set PolicySheet = Workbooks("VPC System
Transactions.xls").Sheets("Policy listing")
Set TransactionSheet = Application.Workbooks("VPC System
Production.xls").Sheets("Transaction")

TransactRow = 8

Do
Policy = TransactionSheet.Range("A" +
Format(TransactRow)).Value

If Policy < "" Then
Set PolicyRow = PolicySheet.Range("A8:A" +
Format(PolicySheet.UsedRange.Rows.Count)).Find(Pol icy,
LookIn:=xlValues)
If Not PolicyRow Is Nothing Then
Set ColN = TransactionSheet.Range("N" +
Format(TransactRow))

ColNValue = PolicyRow.Columns(39).Value
If ColN.Value < ColNValue Then
ColN.Value = ColNValue
End If
End If
End If
TransactRow = TransactRow + 1
Loop While Policy < ""
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


Niek Otten wrote:
Hi Luc,

can you show your code?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message ups.com...
| Hi,
|
| I've wrote a function to calc some part of a worksheet, but when I set
| a value in a cell this is really slow. If I check if the value have
| changed before setting it, my function run pretty fast (My values
| change rarely).
|
| The ScreenUpdating And EnableEvents are setting to false, should I set
| another thing to solve my problem?
|
| Thanks
|
| Luc
|