Spreadsheet resource killers

[trazer985]

Hi guys, I've whacked a fair amount of code and data into my sheet and it's started going mucho slowly. Is anyone aware of a list of the main perpetrators that start killing resources in it? I'm using a fair amount of UDF's, but mostly pretty simple and my strings are quite long (35 items). I'm willing to chop out a few features for a significant performance upgrade.

There is only 1 NOW() function in the sheet, used to timestamp a single event before its taken away into a collection.

Are there any other things that help (hiding rows, columns etc.) that I'm not aware of?

 

[dave3009]

You are using simple UDF's, are these actually necessary, could you look for native functions which will run a lot faster?

 

[trazer985]

Function extractn1(Cell As Range)
Dim no() As String
Dim vread As String
ReDim Preserve no(0)
vread = Cell.Value
no = Split(vread, ",")
extractn1 = no(0)
End Function

is one such function, i have 35 (extractn1-35) of these,one for each bit of the string. I get the feeling these are the main killers (completely unfounded feeling though), the rest is mainly index match and other similar sorting functions.

The other code is mandatory and not overly long.

 

[dave3009]

So the worksheet alternative would be

=LEFT(A21, FIND(",", A21)-1)

Or have I misunderstood the UDF?

 

[trazer985]

each of the 35 bits of the string are separated by a ,

would that understand the difference between one bit of the string and the next one?

 

[gaj104]

Improved code

Function ExtN(r As Range, s As Integer)

ExtN = Split(r.Value, ",")(s - 1)

End Function

Use as =ExtN(A1,1) or =ExtN(A1,2) etc.. =ExtN(A1,35)

 

[dave3009]

OK then have a macro or manually use Text to Columns to seperate the data using the , as the delimiter.

 

[trazer985]

i'll try that gaj, just i'll keep my function name so i dont have to go through the entire sheet to rename everything (there are LOADS of them in it).

Does TextToColumns work with live data? It didnt give me much joy when i tried but i resorted to a UDF without trying too much.

thanks for all replies so far.