slowdown in performance when I write the result of the macro in cell

[samisamih]

Hi ,
i have problem with my macro that there is slowdown in performance when I write the result of the macro in cell.

my code is :

Public n1 As Integer
Public changeflag as boolean
public st as string
dim arr(1 to 10) as integer
Private Sub Worksheet_Change(ByVal Target As Range)
If changeflag = true then exit sub
changeflag = true
If Cells(1, 1).Value <> st Then
st = Cells(1, 1).Value
n1 = n1 + 1
End If

arr(n1)=*number* related to other macro...............
cells(n1,2).value=arr(n1)

Changeflag = false

End Sub

the problem in " cells(n1,2).value=arr(n1) " the result i get in the cell is printed in delay which cause to be miss data sometimes(its happend when there is very fast update of data)

i tried to add the command "application.screenupdating=false/true" at the Beginning and the end but did not help.
is there a way to display the arry value in the cell without delay data , without using msgbox .?

best regards

[Paul_Hossler]

I have no idea as to what else is happening, but the usual way to handle avoid an event handler calling itself or other is to .EnableEvents = False

Target is the range that changed, but you're not using that





Option Explicit
Public n1 As Integer
Public st As String
Dim arr(1 To 10) As Integer

Private Sub Worksheet_Change(ByVal Target As Range)

If Cells(1, 1).Value <> st Then
st = Cells(1, 1).Value
n1 = n1 + 1
End If
arr(n1) = 12345

Application.EnableEvents = False
Cells(n1, 2).Value = arr(n1)
Application.EnableEvents = True
End Sub

[samisamih]

thank for your answer , but the problem with enableevent=false that maybe i will lose some data if happend two changes or more Sequence , (the data updated in the sheet very fast).

i also tried to use application.calculation=xc......... but didnt help
the problem not with the (n1) its work ok , only write the result of the macro to the cells is the problem (delay writing the value)
any way to solve this?
thank in advance

[Paul_Hossler]

thank for your answer , but the problem with enableevent=false that maybe i will lose some data if happend two changes or more Sequence , (the data updated in the sheet very fast).


I wouldn't think so since you're only updating one cell: Cells(n1, 2) and you enable events right after you update the sheet

If that's a concern, you might need to re-think your approach

[samisamih]

thank for your help

i tested now the macro with msgbox , instead to write
cells(n1,2).value=arr(n1)

i wrote
msgbox arr(n1)
And there was a difference in quickly update and was no delay with the msgbox

but with msgbox need all the time to press ok to edit again the macro and resume it.

its possible to get the msgbox without to pause/stop the macro ?



best regards

[Paul_Hossler]

I'd get rid of the Msgbox and use the Application.EnableEvents = False