Keith

You can use Excel functions in your code.

e.g application.worksheetfunction.rounddown(c,2)

However, if the function is available in VB then the VB
function takes precedence.

The code will enter the values as text and so reduce the
size of the workbook and speed up loading.

You can create your own functions but these will always be
slower than Excels, which are created in C. VB is not as
efficient as the C family.

I suppose you have already switched the calculation to
manual with F9 to calculate this will prevent Excel
working while you are entering data

Regards
Peter
-----Original Message-----
Although I also feel fairly comfortable with VBA, I'm
currently working on
a project using just formulas. However, as my workbook
grows (gets more
data), it is slowing exponentially because of the
formulas I'm using (I'll
paste examples below).

Are there any specific worksheet functions that can be
replaced with VBA to
improve performance, or will I be looking at the same
amount of calculation
time regardless of whether I use worksheet functions vs
UDFs or other VBA
code? If there are particular functions that can be sped
up in code, is
there a list anywhere so I know which ones to look out
for?


For each day that data is added, I add (autofill) a new
line that contains
each of the following formulas (note some are array
formulas):

=TEXT(MONTH(A16),"00")&TEXT(DAY(A16),"00")&TEXT(R IGHT(YEAR
(A16),2),"00")

=(SUM(IF(ROUNDDOWN(INDIRECT(B16 & "!F2:F140"),0)
=A16,INDIRECT(B16 &
"!G2:G140")-INDIRECT(B16 & "!F2:F140"),"")))

=AVERAGE(IF(ROUNDDOWN(INDIRECT(B16 & "!F2:F140"),0)
=A16,INDIRECT(B16 &
"!G2:G140")-INDIRECT(B16 & "!F2:F140"),""))

=SUM(IF(ROUNDDOWN(INDIRECT(B16 & "!F2:F14000"),0)
=A16,1,0))

=SUM((IF(ROUNDDOWN(INDIRECT(B16 & "!F2:F140"),0)=A16,1,0))
*(IF(INDIRECT(B16
& "!G2:G140")-INDIRECT(B16 & "!F2:F140")
<=0.00347222222222222,1,0)))
(and about 6 others just like the one above, but with
different comparison
values at the end; 5 min, 10 min, 15 min, etc)

=SMALL((IF(ROUNDDOWN(INDIRECT(B16 & "!F2:F140"),0)
=A16,INDIRECT(B16 &
"!G2:G140")-INDIRECT(B16 & "!F2:F140"),"")),COUNT(IF
(ROUNDDOWN(INDIRECT(B16
& "!F2:F140"),0)=A16,INDIRECT(B16 & "!G2:G140")-INDIRECT
(B16 &
"!F2:F140"),""))*0.8)

=STDEV(IF(ROUNDDOWN(INDIRECT(B16 & "!F2:F140"),0)
=A16,INDIRECT(B16 &
"!G2:G140")-INDIRECT(B16 & "!F2:F140"),""))

I also have some array formulas on another sheet (one for
each day of the
month, plus about 15 others) but I don't add more of
those each time, they
are static and only update their values (when new data is
added to the
workbook)

I only have about a month of data in so far, and it takes
up to 15 seconds
to update the workbook every time I change anything.
Other than buying a
faster computer, would UDF's help from a performance
perspective? any other
ideas? My user isn't particularly computer-savy, so I'm
trying to avoid
turning autocalculation on/off.

Many thanks,
Keith R
XL97
.