excel - Need to stop UDFs recalculating when unrelated cells deleted -


i've noticed udfs recalculate whenever delete cells. causes massive delays when deleting entire columns, because udf gets called each , every cell used in. if you're using 1000 udfs, deleting column or cell call 1000 times.

by way of example, put following udf in module, call worksheet bunch of times =helloworld()

function helloworld() helloworld = "helloworld" debug.print now() end function

then delete row. if experience mine, you'll see gets called once every instance of use.

anyone have ideas whether behavior can stopped? i'd interested why should called. seems flaw in excel's dependency tree me, there may reason.

edit: after experimentation, i've found more actions trigger udfs:

  1. any change number of columns listobject (i.e. excel table) spans through resizing (but not rows). if udfs aren't in listobject concerned, or in fact in any listobject @ all.
    1. adding new cells or columns anywhere in sheet (but not rows).

note manual calc mode isn't option on several fronts.

firsty, given application-level setting, presents great risk use output of 1 of spreadsheets happen have open without realizing in manual calculation mode.

secondly, i'm not designing particular spreadsheet rather writing book how non-developers can utilize well-written off-the-shelf code such udfs things otherwise beyond them. examples include dynamically concatenating or splitting text, or exact match binary search udf charles williams outlines @ https://fastexcel.wordpress.com/2011/07/22/developing-faster-lookups-part-2-how-to-build-a-faster-vba-lookup/ (and yes, give them warning native formula-based solution outperform udf. you'll see thread i've referenced above, written functions can perform well).

i don't know how users employ these.

in absence of programming solution, looks i'll have point out in book users may experience significant delay when adding or deleting cells or resizing listobjects if have resource-intensive udfs employed. if udfs efficiently written.

unfortunately, don't believe it's possible prevent udf being recalculated when "unrelated" cells deleted. reason argument passed udf in fact range object (not value within cell(s)). deleting "unrelated" cells can modify range.

for example, users can write kind of udf:

function func1(rng)     func1 = rng.address & " (" & format(now, "hh:mm:ss") & ")" end function

admittedly, not common (and recommended) approach write udf. should depend on content (value) , not container (range).

here i'm returning address of argument. append timestamp signal when udf recalculated. if delete column on worksheet, cells udf recalculated. not if insert column, leaving cells on right (of new column) unchanged , wrong value (cell address). results same inserting/deleting rows. strangely, inserting single cell force recalculation of udf's.

i tried remove "dependency" on range. behavior same if udf's argument typed as double (instead of leaving variant in example).

as explained, deleting column force udfs recalculated. makes sense because udf can depend on range argument. wether smart design udf different matter.


-

Popular posts from this blog

c# - ODP.NET Oracle.ManagedDataAccess causes ORA-12537 network session end of file -

overview i want replace oracle.dataaccess orcale. managed dataaccess, opening connection latter throws ora-12537 network session end of file exception. exception message / stack trace {oracleinternal.network.networkexception (0x000030f9): ora-12537 : netzwerksession: dateiende @ oracleinternal.network.readerstream.read(orabuf ob) @ oracleinternal.ttc.orabufreader.getdatafromnetwork() @ oracleinternal.ttc.orabufreader.read(boolean bignoredata) @ oracleinternal.ttc.marshallingengine.unmarshalub1(boolean bignoredata) @ oracleinternal.ttc.ttcprotocolnegotiation.readresponse()} i trying connect oracle 11g database , not have client installed on local machine. working test application (unmanaged) using oracle.dataaccess works fine . using system; using oracle.dataaccess.client; namespace app.odp.unmanaged { internal class program { private static void main(string[] args) { //dummy connection string...
Read more

matlab - Compression and Decompression of ECG Signal using HUFFMAN ALGORITHM -

i want compress , decompress ecg signal stored text message text file , find out file size, cr,prd ratio,and qs. here code compression , decompression of ecg signal using huffman algorithm. i'm trying find out file size, compression ratio,prd ratio,qs. but error i'm getting the ecg file in text format. %clearing variables , screen enter code here clear all; close all; clc; [file1]=uigetfile('*.txt'); %fprintf('enter ecg file name :%s',file1); p=file1; t=sprintf('%s',p); b1=dlmread([file1]); len=length(b1); lead=input('enter lead number :'); %lead=str2double(lead); col2=b1(1:end,lead); e=fix(sqrt(len)); m=1; i=1:e j=1:e g2(i,j)=fix(col2(m)*1000); %amplifying 1000 m=m+1; end end g3=g2; i=1:e j=1:e ...
Read more

utf 8 - split utf-8 string into bytes in python -

i trying split utf-8 string bytes in python 3. problem is, when use bytearray, byte, encode etc functions array size of element 14 bytes, not 1 byte expected. need split text file sequence of bytes , send them byte after byte using sockets. tried this: infile = open (file, "r") str = infile.read() byte_str = bytes(str, 'utf-8') print("size of byte_str",sys.getsizeof(byte_str[0])) print gives me 14, need 1... suggestion? quoting official documentation : sys.getsizeof(object[, default]) return size of object in bytes. object can type of object. built-in objects return correct results, not have hold true third-party extensions implementation specific. only memory consumption directly attributed object accounted for, not memory consumption of objects refers to. if given, default returned if object not provide means retrieve size. otherwise typeerror raised. getsizeof() calls object’s __sizeof__ method , adds ...
Read more