custimise the "IF" formula function
 

I am trying to write a UDF that will let me make my own customised IF
formula

I have my customised part working fine, but do not know how to do the
code for the IF part of the function. This part i want to act exactly
like the ordinary IF formula from the worksheets.

Can anyone tell me how to write the code for the IF formula?

I can input the values ok (If_test, If_True, If_False), but when it
comes to the cell value displaying the results, i cannot change the
cell.value as this will then remove the formula.

any help or guidance appreciated.
George

custimise the "IF" formula function
 

Hi George,

Please post the code of your UDF and an example of the function being
called, input values, expected and actual results.

--
Kind regards,

Niek Otten

wrote in message
oups.com...
I am trying to write a UDF that will let me make my own customised IF
formula

I have my customised part working fine, but do not know how to do the
code for the IF part of the function. This part i want to act exactly
like the ordinary IF formula from the worksheets.

Can anyone tell me how to write the code for the IF formula?

I can input the values ok (If_test, If_True, If_False), but when it
comes to the cell value displaying the results, i cannot change the
cell.value as this will then remove the formula.

any help or guidance appreciated.
George

custimise the "IF" formula function
 

You can use something like this:
Dim myvar as Double
myvar = 10

If myvar 10 Then
'Do something
Elseif myvar < 10 Then
'Do something
Else
'Do something
End if

You could also use SELECT CASE statements.


--
a7n9


------------------------------------------------------------------------
a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149
View this thread: http://www.excelforum.com/showthread...hreadid=519013

custimise the "IF" formula function
 

Hi George,
How about something like this example:
If..Then...End If
When there is only one condition and one action, you will use the
simple statement:
If Selection.Value 10 Then
Selection.Offset(1,0).Value = 100
End If

In plain English: if the value of the selected cell is greater than 10
then the value of the cell below is 100 if not do nothing.

or

If..Then...End If (multiple tiers)
When there are only two conditions that you want to check sequentially,
you will use the statement:
If Selection.Value 10 Then
If Selection.Value = 12 Then
Selection.Offset(1,0).Value = 100
End If
End If

In plain English: first check if the value of the selected cell is
greater that 10. If it is not do nothing. If it is check if the value
of the selected cell is equal to 12. If so set the value of the cell
below at 100 else do nothing.

and on more
If..Then...Else...End If
When there is only one condition but two actions, you will use the
statement:
If Selection.Value 10 Then
Selection.Offset(1,0).Value = 100
Else
Selection.Offset(1,0).Value = 50
End If

In plain English: if the value of the selected cell is greater than 10
then the value of the cell below is 100 else the value of the cell
below is 50.

If..Then..ElseIf...End If
When there are more than one condition linking each to a different
action you will use the statement:
If Selection.Value = 1 Then
Selection.Offset(1, 0).Value = 10
ElseIf Selection.Value = 2 Then
Selection.Offset(1, 0).Value = 20
ElseIf Selection.Value = 3 Then
Selection.Offset(1, 0).Value = 30
ElseIf Selection.Value = 4 Then
Selection.Offset(1, 0).Value = 40
ElseIf Selection.Value = 5 Then
Selection.Offset(1, 0).Value = 50
End If

In plain English: If the value of the selected cell is 1 then the value
of the cell below is 10 but if the value of the selected cell is 2 then
the value of the cell below is 20 but if the value of the selected cell
is 3 then the value of the cell below is 30 but if the value of the
selected cell is 4 then the value of the cell below is 40 but if the
value of the selected cell is 5 then the value of the cell below is 50
but then if the value of the selected cell is not 1, 2, 3, 4 or 5 do
nothing.


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=519013

custimise the "IF" formula function
 

Thanks for all the replies.

Unfortunately i was wanting to do this as a UDF rather than just
putting the formula into VBA and working it out with a macro.

I have a workbook that is too complex to go into detail with, but
basically in an example:
If i enter a value into cell A1, i want SOME of the formulas in a
certain column (say B) to wait 2 secs before displaying their results.
These formulas are NOT all the same. That is why i was hoping to have
a customised IF function.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.address = "$A$1" Then
Application.Wait (Now + TimeValue("00:00:02"))
End If
End Sub

This is where i was going to get the delay from, and

Function timedelay(Delay_IF As String, If_True As String, If_False As
String)
Dim x As Range
Set x = Application.Caller
If x.Column = 2 Then

'x.Value = "=IF(" & Delay_IF & "," & IfTrue & "," & If_False & ")"
End If
End Function

This is the function i did not know how to complete. Just wanted this
to act exactly the same as the worksheet IF function

rather than having =IF(A1<"","YES","NO")
i would have =timedelay(A1<"","YES","NO")

The only thing all the formulas that i want delayed have in common is
that they start with an IF formula

thanks again
George

custimise the "IF" formula function
 

Nothing in your origianl post mentioned that, exept that you wanted to
use a UserForm.
I was thinking that you were going to put the formula into the ok
button or something


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=519013

custimise the "IF" formula function
 

Must admit, i wondered where you were going with the code you posted.

I appreciate the reply, but was looking for a UDF (User Defined
Function) - didn't mention a userform. sorry

davesexcel wrote:
Nothing in your origianl post mentioned that, exept that you wanted to
use a UserForm.
I was thinking that you were going to put the formula into the ok
button or something


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=519013

custimise the "IF" formula function
 

So let me just repeat my question:

Hi George,

Please post the code of your UDF and an example of the function being
called, input values, expected and actual results.

--
Kind regards,

Niek Otten
wrote in message
oups.com...
Must admit, i wondered where you were going with the code you posted.

I appreciate the reply, but was looking for a UDF (User Defined
Function) - didn't mention a userform. sorry

davesexcel wrote:
Nothing in your origianl post mentioned that, exept that you wanted to
use a UserForm.
I was thinking that you were going to put the formula into the ok
button or something


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile:
http://www.excelforum.com/member.php...o&userid=31708
View this thread:
http://www.excelforum.com/showthread...hreadid=519013

custimise the "IF" formula function
 

Sorry Niek - though post 5 was enough (as far as i've got anyway)

Please post the code of your UDF
-------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.address = "$A$1" Then
Application.Wait (Now + TimeValue("00:00:02"))
End If
End Sub
'(This is where i was going to get the delay from, and)

Function timedelay(Delay_IF As String, If_True As String, If_False As
String)
Dim x As Range
Set x = Application.Caller
If x.Column = 2 Then
????????????????
'x.Value = "=IF(" & Delay_IF & "," & If_True & "," & If_False & ")"
End If
End Function

This is the part i need help on - a replica of excel's IF formula
function

example of the function being called
-------------------------------------------------------
When cell A1 changes, all the formulas in column B will change.
(The formulas in column B are not all the same)

i want a 2 second delay before this change happens - but only affecting
specific formulas in column B
=timedelay(A1<"","YES","NO")
this is an example of what i would like to be able to enter.
this formula will get a 2sec time delay. Then it will perform a
straight forward IF calculation - as though the cells contained
=IF(A1<"","YES","NO")

I want the formula to remain in the cells in column B incase cell A1
changes again - so i can't just transfer the value "YES"/"NO" to the
cell.

input values
--------------------
The input values will be the same as an IF formula
logical expression - Delay_IF
result if true - If_True
result if false - If_False

expected and actual results
----------------------------------------------
I cannot get it to work so i do not have any results, but it shoud
display YES or NO in the cell without overwriting the original formula.
Basically a replica of the worksheet IF formula function.


Hope this answers your queries.
George

custimise the "IF" formula function
 

<'x.Value = "=IF(" & Delay_IF & "," & If_True & "," & If_False & ")"

You can not change a worksheet from within a function that is called from a
worksheet. Not directly, not inderectly.

It is not clear to me what your requirements for column B are. Why can't you
just use =IF(A1<"","YES","NO")?
Don't try to display something that doesn't correspond to the underlying
formula; you'll get yourself in trouble. But probably I misunderstood your
intention.

--
Kind regards,

Niek Otten


wrote in message
ups.com...
Sorry Niek - though post 5 was enough (as far as i've got anyway)

Please post the code of your UDF
-------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.address = "$A$1" Then
Application.Wait (Now + TimeValue("00:00:02"))
End If
End Sub
'(This is where i was going to get the delay from, and)

Function timedelay(Delay_IF As String, If_True As String, If_False As
String)
Dim x As Range
Set x = Application.Caller
If x.Column = 2 Then
????????????????
'x.Value = "=IF(" & Delay_IF & "," & If_True & "," & If_False & ")"
End If
End Function

This is the part i need help on - a replica of excel's IF formula
function

example of the function being called
-------------------------------------------------------
When cell A1 changes, all the formulas in column B will change.
(The formulas in column B are not all the same)

i want a 2 second delay before this change happens - but only affecting
specific formulas in column B
=timedelay(A1<"","YES","NO")
this is an example of what i would like to be able to enter.
this formula will get a 2sec time delay. Then it will perform a
straight forward IF calculation - as though the cells contained
=IF(A1<"","YES","NO")

I want the formula to remain in the cells in column B incase cell A1
changes again - so i can't just transfer the value "YES"/"NO" to the
cell.

input values
--------------------
The input values will be the same as an IF formula
logical expression - Delay_IF
result if true - If_True
result if false - If_False

expected and actual results
----------------------------------------------
I cannot get it to work so i do not have any results, but it shoud
display YES or NO in the cell without overwriting the original formula.
Basically a replica of the worksheet IF formula function.


Hope this answers your queries.
George

custimise the "IF" formula function
 

Niek - thanks for replying.

You said:
It is not clear to me what your requirements for column B are. Why
can't you
just use =IF(A1<"","YES","NO")?

This is what i want, but for there to be a 2 second delay between the
change in A1 and the calculations in column B to be made. This is to
allow another application time to do something in the spreadsheet.

This delay should only be for certain cells in column B. The only
thing these cells have in common is that they all start with an IF
formula. Their actual formulas vary so it is not really possible to do
the formula calculation in the code.

(Cell A1 changes - 2 second pause (for some calculations in column B) -
the paused calculations are updated).

Hope this makes sense.
George

Niek Otten wrote:
<'x.Value = "=IF(" & Delay_IF & "," & If_True & "," & If_False & ")"

You can not change a worksheet from within a function that is called from a
worksheet. Not directly, not inderectly.

It is not clear to me what your requirements for column B are. Why can't you
just use =IF(A1<"","YES","NO")?
Don't try to display something that doesn't correspond to the underlying
formula; you'll get yourself in trouble. But probably I misunderstood your
intention.

--
Kind regards,

Niek Otten


wrote in message
ups.com...
Sorry Niek - though post 5 was enough (as far as i've got anyway)

Please post the code of your UDF
-------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.address = "$A$1" Then
Application.Wait (Now + TimeValue("00:00:02"))
End If
End Sub
'(This is where i was going to get the delay from, and)

Function timedelay(Delay_IF As String, If_True As String, If_False As
String)
Dim x As Range
Set x = Application.Caller
If x.Column = 2 Then
????????????????
'x.Value = "=IF(" & Delay_IF & "," & If_True & "," & If_False & ")"
End If
End Function

This is the part i need help on - a replica of excel's IF formula
function

example of the function being called
-------------------------------------------------------
When cell A1 changes, all the formulas in column B will change.
(The formulas in column B are not all the same)

i want a 2 second delay before this change happens - but only affecting
specific formulas in column B
=timedelay(A1<"","YES","NO")
this is an example of what i would like to be able to enter.
this formula will get a 2sec time delay. Then it will perform a
straight forward IF calculation - as though the cells contained
=IF(A1<"","YES","NO")

I want the formula to remain in the cells in column B incase cell A1
changes again - so i can't just transfer the value "YES"/"NO" to the
cell.

input values
--------------------
The input values will be the same as an IF formula
logical expression - Delay_IF
result if true - If_True
result if false - If_False

expected and actual results
----------------------------------------------
I cannot get it to work so i do not have any results, but it shoud
display YES or NO in the cell without overwriting the original formula.
Basically a replica of the worksheet IF formula function.


Hope this answers your queries.
George

custimise the "IF" formula function
 

Hi George,

I tested it with simple functions, which did nothing but firing a wait
command. No luck.
It seems that this is one of the many tasks that cannot be performed in a
function.

--
Kind regards,

Niek Otten

wrote in message
oups.com...
Niek - thanks for replying.

You said:
It is not clear to me what your requirements for column B are. Why
can't you
just use =IF(A1<"","YES","NO")?

This is what i want, but for there to be a 2 second delay between the
change in A1 and the calculations in column B to be made. This is to
allow another application time to do something in the spreadsheet.

This delay should only be for certain cells in column B. The only
thing these cells have in common is that they all start with an IF
formula. Their actual formulas vary so it is not really possible to do
the formula calculation in the code.

(Cell A1 changes - 2 second pause (for some calculations in column B) -
the paused calculations are updated).

Hope this makes sense.
George

Niek Otten wrote:
<'x.Value = "=IF(" & Delay_IF & "," & If_True & "," & If_False & ")"

You can not change a worksheet from within a function that is called from
a
worksheet. Not directly, not inderectly.

It is not clear to me what your requirements for column B are. Why can't
you
just use =IF(A1<"","YES","NO")?
Don't try to display something that doesn't correspond to the underlying
formula; you'll get yourself in trouble. But probably I misunderstood
your
intention.

--
Kind regards,

Niek Otten


wrote in message
ups.com...
Sorry Niek - though post 5 was enough (as far as i've got anyway)

Please post the code of your UDF
-------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.address = "$A$1" Then
Application.Wait (Now + TimeValue("00:00:02"))
End If
End Sub
'(This is where i was going to get the delay from, and)

Function timedelay(Delay_IF As String, If_True As String, If_False As
String)
Dim x As Range
Set x = Application.Caller
If x.Column = 2 Then
????????????????
'x.Value = "=IF(" & Delay_IF & "," & If_True & "," & If_False & ")"
End If
End Function

This is the part i need help on - a replica of excel's IF formula
function

example of the function being called
-------------------------------------------------------
When cell A1 changes, all the formulas in column B will change.
(The formulas in column B are not all the same)

i want a 2 second delay before this change happens - but only affecting
specific formulas in column B
=timedelay(A1<"","YES","NO")
this is an example of what i would like to be able to enter.
this formula will get a 2sec time delay. Then it will perform a
straight forward IF calculation - as though the cells contained
=IF(A1<"","YES","NO")

I want the formula to remain in the cells in column B incase cell A1
changes again - so i can't just transfer the value "YES"/"NO" to the
cell.

input values
--------------------
The input values will be the same as an IF formula
logical expression - Delay_IF
result if true - If_True
result if false - If_False

expected and actual results
----------------------------------------------
I cannot get it to work so i do not have any results, but it shoud
display YES or NO in the cell without overwriting the original formula.
Basically a replica of the worksheet IF formula function.


Hope this answers your queries.
George

custimise the "IF" formula function
 

ok :(

Thanks for looking at this Niek. I appreciate the time you've put in
trying to get this to work.

regards
George


Niek Otten wrote:
Hi George,

I tested it with simple functions, which did nothing but firing a wait
command. No luck.
It seems that this is one of the many tasks that cannot be performed in a
function.

--
Kind regards,

Niek Otten

wrote in message
oups.com...
Niek - thanks for replying.

You said:
It is not clear to me what your requirements for column B are. Why
can't you
just use =IF(A1<"","YES","NO")?

This is what i want, but for there to be a 2 second delay between the
change in A1 and the calculations in column B to be made. This is to
allow another application time to do something in the spreadsheet.

This delay should only be for certain cells in column B. The only
thing these cells have in common is that they all start with an IF
formula. Their actual formulas vary so it is not really possible to do
the formula calculation in the code.

(Cell A1 changes - 2 second pause (for some calculations in column B) -
the paused calculations are updated).

Hope this makes sense.
George

Niek Otten wrote:
<'x.Value = "=IF(" & Delay_IF & "," & If_True & "," & If_False & ")"

You can not change a worksheet from within a function that is called from
a
worksheet. Not directly, not inderectly.

It is not clear to me what your requirements for column B are. Why can't
you
just use =IF(A1<"","YES","NO")?
Don't try to display something that doesn't correspond to the underlying
formula; you'll get yourself in trouble. But probably I misunderstood
your
intention.

--
Kind regards,

Niek Otten


wrote in message
ups.com...
Sorry Niek - though post 5 was enough (as far as i've got anyway)

Please post the code of your UDF
-------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.address = "$A$1" Then
Application.Wait (Now + TimeValue("00:00:02"))
End If
End Sub
'(This is where i was going to get the delay from, and)

Function timedelay(Delay_IF As String, If_True As String, If_False As
String)
Dim x As Range
Set x = Application.Caller
If x.Column = 2 Then
????????????????
'x.Value = "=IF(" & Delay_IF & "," & If_True & "," & If_False & ")"
End If
End Function

This is the part i need help on - a replica of excel's IF formula
function

example of the function being called
-------------------------------------------------------
When cell A1 changes, all the formulas in column B will change.
(The formulas in column B are not all the same)

i want a 2 second delay before this change happens - but only affecting
specific formulas in column B
=timedelay(A1<"","YES","NO")
this is an example of what i would like to be able to enter.
this formula will get a 2sec time delay. Then it will perform a
straight forward IF calculation - as though the cells contained
=IF(A1<"","YES","NO")

I want the formula to remain in the cells in column B incase cell A1
changes again - so i can't just transfer the value "YES"/"NO" to the
cell.

input values
--------------------
The input values will be the same as an IF formula
logical expression - Delay_IF
result if true - If_True
result if false - If_False

expected and actual results
----------------------------------------------
I cannot get it to work so i do not have any results, but it shoud
display YES or NO in the cell without overwriting the original formula.
Basically a replica of the worksheet IF formula function.


Hope this answers your queries.
George