cant understand this custom function

[bigdan]

I'm learning VBA at the moment but don't fully understand it yet. In this case below we're learning how to create a custom function. Can someone explain what's happening here? I'm not following.

I get what the function is trying to do. I'm good there. But it seems to me we're creating a function called IF_GREEN, and in that, if the cell color is green, then we're running the IF_GREEN function. How does that make sense? It seems like a self-referencing definition?

 

[Artik]

A function is a procedure that (generally) returns some value. In order for it to be able to return a value, we insert its name on the left side of the assignment, and the calculated value on the right.

Artik

 

[StephenCrump]

It's not self-referencing. The function is called from Excel, like this:

	A	B	C	D
1
2		100		$100
3		200		$200
4		300		-
5		400		$400
6		500		-
7		600		$600
8		700		$700
9		800		-
10		900		$900
11		1000		-
12		1100		$1100
Sheet4

Cell Formulas
Range	Formula
D2:D12	D2	=IF_GREEN(C2,B2)

But it's not a great example. The Application.Volatile method means that the function recalculates whenever the worksheet recalculates. You don't want too much of this happening.

Also, simply changing a cell's colour will not trigger a recalculate, so if you start changing cell colours the results will be incorrect until a recalculation occurs.