Insert a VBA code that run without running a macro

[itselflearn]

Is there a way to run VBA code all the time without running a macro. Like if I want to insert a code for Cell C1=A1*B1 and I want it to processed right away without going to a hassle steps ( Tools\Macro\Run Macro). In other words, the code should act like a formula that is entered in cell C1. I hope it is clear. Thank you.

[MrShorty]

You want a user-defined function. I don't know if the current help file system has any good articles to introduce UDF's. They can be as simple or as complex as you feel you need. Key things to know about writing UDF's
1) UDF's cannot modify the spreadsheet environment!!! They cannot write data to cells, they cannot format anything. All they can do is receive arguments from the spreadsheet, perform the calculations, and return a value to the spreadsheet.
2) The best UDF's will receive all needed information from the argument list. This allows Excel to figure out where the function fits in the calculation dependency tree.
3) UDF's are stored in a regular module in VBA. If you have functions that you want available to all spreadsheets, you can store them in your personal workbook or in an add-in.

For the simple example you give, the UDF might look like

Please Login or Register  to view this content.

see if this tutorial helps http://office.microsoft.com/en-us/ex...996.aspx?CTT=1

[itselflearn]

MrShorty...Thank you for your respond. I visited the link you mentioned above. In there, an example of creating a function which I understood but it's not actually what I need. I think am not giving a clear scenario here. I am going to try again:

A1=2
B1=4

On C3, I want to have the answer 8. But I do not want to put or type in the formula (A1*B1) in cell C3. What I want is make the formula invisible by using VBA code. Then that VBA code should not be executed by running a macro, it should right away work or executed when A1 and B1 have value.

[MrShorty]

In that case, what you want is an event procedure. The first step will be to look at the different events available to the desired object (I would guess it will be worksheet or workbook object) and see which events you want to associate the procedure with. (http://msdn.microsoft.com/en-us/libr...ice.14%29.aspx and http://msdn.microsoft.com/en-us/libr...ice.14%29.aspx and http://msdn.microsoft.com/en-us/libr...ice.14%29.aspx). The most common events I see being used for this kind of thing are the change and calculate events.