Turn off UserDefined Function

[mahmed1]

Hi Guys,

I have a workbook where i have several columns with user defined functions..

I Often need to change Cell A1 to another value and then press and update button to retrieve some data from Access

now each time i change this cell excel calculates and it takes ages

what i want to do is turn off the user defined function calculation when i change this cell

press the update button
Turn cals back on

calculation manual doesn’t turn it off

hoping someone guide me the best work around

 

[CSmith]

Is there a worksheet_change event?

 

[mikerickson]

What is your Update code?
In your desired result, the UDFs will recalculate when you turn calculation back on.
If the Update code is putting a value in one cell, value in another cell, value in a third cell etc... That will trigger many calcuations.
If it could be re-written to put all the values in place at one time

' e.g.

Range("A1:A3").Value = someArray

'vs

Range("A1").Value = someValue
Range("A2").Value = otherValue
Range("A3").Value = thirdValue

That would speed things up.

 

[mahmed1]

Hi I have erlang functions in cells D2:D52

the functions point towards a cell on that row ie A2:A52 that changes - ie incoming calls etc

All the update does it goes to my access data base and replaces A2:A52 with new call data which then changes the cells in D2:D52

thisworkbook.connections(“UpdateCallInfo”).refresh is the update code

this updates the table and the erlang functions. Get calculated each time

i tried to turn off calculation
Refresh table
Turn back on but it still takes long

 

[mahmed1]

A2:A52 have lookup formula that points to the refreshed table.