Compile a Value in VBA Instead of Cell

**titanxt** · 08-06-2008, 09:54 PM

On my worksheet I have a series of formulas in sperate cells as follows:

Cell F22 =If(A22=1, 2)
Cell G22 =If(A22=2, 1)
Cell H22 =If(A22=3, 0)
Cell I22 =Sum(F22:H22)

I then use the value in I22 in other functions by using "I22" in their formulas. Is there a way in VBA to perform the same task so I can refer to the value in other formulas without assinging the value to a cell in the worksheet?

Also, how do I set the macro to always update the calculations when new numbers are entered on the spreadsheet?

Thanks!

**mudraker** · 08-07-2008, 08:40 AM

I do not fully understand what, how & why you want to use VBA if you can use formulas

You can have a macro run whenever a cell value is changed or when a sheet is calculated. The macro can do actions in the place of formulas.

**royUK** · 08-07-2008, 01:32 PM

Use a Named Formula, see

http://www.tushar-mehta.com/excel/ne..._formulas.html

**titanxt** · 08-21-2008, 05:39 PM

Mudraker,

I did have a lot of formulas running in several different cells that calculated information that I needed for other displayed cells. Most of the cells containing the formulas do not need to be displayed for the end-user of the worksheet. I initially highlighted all of the cells that contained the formulas that I did not want visible and changed the font color to white so that they would not be displayed. However, this seemed a little cheesy to me and, if the cells were highlighted, would become visible.

Is there a simple way to hide several formulas that will run in real-time in the worksheet that will not be made visible to the end-user? The reason I went the VBA route was because of this....

Thanks!

**shg** · 08-21-2008, 05:48 PM

You can protect the cells, hide their contents, and protect the workbook - then users will not be able to see the formulas. Nothing you've said so far warrants the use of VBA.

If you just want to because you want to, that's fine.