Excel formulas efficiency

  • Thread starter Thread starter Al Jager
  • Start date Start date
A

Al Jager

Can someone please help me with this problem?
In a range from cell D10 to D64 I have a series of
formulas.
The formulas are fairly complex and seem to take up a lot
of space.
All the formulas in this range relate to 1 Entry. For each
additional entry I copy the formulas. The workbook can
have as many as 10 entries. For each entry
the workbook increases in size by about 800 KB.
As a result the workbooks become rather large and slow in
operation.
Is there a way to share the original formulas, rather
then storing identical formulas more than once?.
Entries are entered in adjoining columns in the same ranges
i.e. E10 to E64 etc.
I have run Excel Repair to ensure, my Excel is functiong
O.K.

Many Thanks
Al
 
Al Jager said:
Can someone please help me with this problem?
In a range from cell D10 to D64 I have a series of
formulas.
The formulas are fairly complex and seem to take up a lot
of space.
Click to expand...

Al,
you could try and define your formulas as VBA functions in a separate module
within the workbook.

Example :

Public Function fcustMyFunction(ByVal param1 As Integer) As Double
fcustMyFunction = Sin(param1)
End Function

With this set up, you can go back to your worksheet. Use Insert > Function,
"Function Category" => User Defined
You will get a list of the different functions you defined in your code, and
use them in as many cells / on as many entries as you wish without blowing
up your file size.

cheers,
Markus
 
If the single entrys are adjacent in a single row, you could array enter
your formulas.
 
Back
Top