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

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

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

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

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

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

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

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

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