How can I create a user defined function in excel?

M

Martinj

If I have a equation eg: =(A1+B1*if(t10=TRUE;1;-1)), how can I define it to
a Myfunction (A1;B1;T10)?
 
B

Bernard Liengme

Use Alt+F11 to open VB Editor
Use command Insert | Module
Copy this to the newly open module sheet
Function Myfunction(x, y, z)
If z Then
multi = 1
Else
multi = -1
End If
Myfunction = x + y * multi
End Function
Return to Excel and type in any cell =Myfunction(A1,B1, T10)

But why not use
Function Myfunction(x, y, z)
If z Then
Myfunction = x + y
Else
Myfunction = x - y
End If
End Function

best wishes
 
T

Tushar Mehta

Or what I would use:

MyFunc = x + IIf(z, y, -y)
MyFunc = x + y * IIf(z, 1, -1)

To the point, no code duplication, and easier to maintain.

It would be even more useful if VB terminated its evaluation as soon as
the result was determined.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
D

Dnereb

Some further comment function where made to return a value
subs don't so write subs like:
Code:
--------------------
Sub MySub(X,Y,Z)

workbooks(Z).sheets(1).cells(X,Y).activate

end sub
--------------------


and function


Code:
--------------------
function Smallest (A,B,optional C) as long

if A < B then
smallest = A
else
Smallest = B
end if

if not ismissing(C) then
if C < Smallest then Smallest = C
end if

end function
 
Top