Store formulas in vba in order to reduce file size

Panos

New Member
Joined
May 14, 2011
Messages
11
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

montecarlo2079

Board Regular
Joined
Feb 9, 2011
Messages
207
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

New Member
Joined
May 14, 2011
Messages
11
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,638
Members
414,398
Latest member
dhune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top