I have inserted the
Application.Volatile
in my code for a custom formula as follows:

Public Function MyFormula(ARRAY1, ARRAY2, ARRAY3, sSTRING)
MyFormula = Application.Caller.Parent.Evaluate( _
"SUMPRODUCT(--(" & ARRAY1.Address & "=""" & sSTRING & """),--(" & _
ARRAY2.Address & "0)," & ARRAY3.Address & ")")
Application.Volatile
End Function

However, when I change a cell involved in any array the result is not
updated unless I make some OTHER change?

How to make a timely change so that when, for example, I change value of a
cell in ARRAY3 the result of the formula updates itself, instead of making
some other change in the workbook or pressing F9.

--

Best Regards,
FARAZ A. QURESHI

Assuming that ARRAY1 etc are ranges in a worksheet, there is no need for
Application.Volatile, it will update when any of those ranges or any
precedent range changes, which is what you want.

But why bother, why not use a ws function.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"FARAZ QURESHI" wrote in message
...
I have inserted the
Application.Volatile
in my code for a custom formula as follows:

Public Function MyFormula(ARRAY1, ARRAY2, ARRAY3, sSTRING)
MyFormula = Application.Caller.Parent.Evaluate( _
"SUMPRODUCT(--(" & ARRAY1.Address & "=""" & sSTRING & """),--(" & _
ARRAY2.Address & "0)," & ARRAY3.Address & ")")
Application.Volatile
End Function

However, when I change a cell involved in any array the result is not
updated unless I make some OTHER change?

How to make a timely change so that when, for example, I change value of a
cell in ARRAY3 the result of the formula updates itself, instead of making
some other change in the workbook or pressing F9.

--

Best Regards,
FARAZ A. QURESHI

It was actually for a very lengthy formula that I want to customize and
shorten down. But I don't understand why the same code is now working quite
properly?

May be due to some other heavy formulas?

Thanx anyway for the advice regarding there is no need for
Application.Volatile, it will update when any of those ranges or any
precedent range changes, which is what you want.

--

Best Regards,
FARAZ A. QURESHI


"Bob Phillips" wrote:

Assuming that ARRAY1 etc are ranges in a worksheet, there is no need for
Application.Volatile, it will update when any of those ranges or any
precedent range changes, which is what you want.

But why bother, why not use a ws function.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"FARAZ QURESHI" wrote in message
...
I have inserted the
Application.Volatile
in my code for a custom formula as follows:

Public Function MyFormula(ARRAY1, ARRAY2, ARRAY3, sSTRING)
MyFormula = Application.Caller.Parent.Evaluate( _
"SUMPRODUCT(--(" & ARRAY1.Address & "=""" & sSTRING & """),--(" & _
ARRAY2.Address & "0)," & ARRAY3.Address & ")")
Application.Volatile
End Function

However, when I change a cell involved in any array the result is not
updated unless I make some OTHER change?

How to make a timely change so that when, for example, I change value of a
cell in ARRAY3 the result of the formula updates itself, instead of making
some other change in the workbook or pressing F9.

--

Best Regards,
FARAZ A. QURESHI