Store formulas in vba in order to reduce file size

[Panos]

Hi all, I have a file of 7 mb having 100 columns and unlimited rows (user defined- approx 500). At all these cells in that area, I refresh (copy/paste) the same formula for all cells, read as: [=IF($DF47=0,"",IF(ISERROR(MATCH(HE$45,$Q$22:$Q$26,0)),INDEX($DF$12:$HE$31,MATCH($DF47,$DF$12:$DF$31,0),1+HE$11-$DF$11),INDEX($Q$22:$S$26,MATCH(HE$45,$Q$22:$Q$26,0),IF($DA47=GROUPSHIP,2,3)))) ].

Refresh is necessary bcs I insert/delete/sort rows.

In order to reduce file size and instead of saving all these formulas into the approx 5000 cells, I would prefer if I could have a function in VBA and call it (with copy/paste) with a smaller formula at all these 5000 cells as: [= myfunction], so as to have this simple formula in each of 5000 cells, hoping that this will reduce size.

What I am asking assistance is how can I write the long formula above to a function in VBA?

Your assistance would be highly appreciated.

Thank you
Panos/Greece

 

[montecarlo2079]

I would record the macro yourself typing this formula into one cell. Then you can copy and paste to certain cells.

You could add an open workbook event to automatically do this everytime the workbook is opened.

Im sure there is a better answer out there, but this is the simplest one.

 

[Panos]

Montecarlo, thanks for replying but this is what I already do. I have the formula stored in a cell and I copy/paste it after some buttons are pressed, in order to be refreshed. The problem is that I need these formulas always working for monitoring the changes of my database, so paste values is not effective for my case. I need the formulas there, for that, I need to make them smaller and call a function from vba.