Thread View: alt.comp
3 messages
3 total messages
Started by Peter J. E. ROEK
Tue, 23 Jun 1998 00:00
How to make my own formula in Excel (Visual Basic)
Author: Peter J. E. ROEK
Date: Tue, 23 Jun 1998 00:00
Date: Tue, 23 Jun 1998 00:00
130 lines
6008 bytes
6008 bytes
This is a multi-part message in MIME format.
------=_NextPart_000_0007_01BD9F08.56CEF380
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Dear reader,
Thanks for paying attention to this message.
Working in Excel, I want to design my own formula
with Visual Basic.
Can you tell me how to do this?
For example I want to make a formula named =93COSTS=94:
COSTS(cell1, cell2, cell3)
When I place this own-made formula in a certain cell, the
next procedure has to run:
(RESULT must be the result that has to appear in the cell
with the formula in it=85.)
IF cell1 =3D =93Bank=94 THEN
IF cell2 <=3D 1,50 THEN
IF cell3 <=3D 10 THEN P=3D12 ENDIF
IF cell3 >10 AND cell3 <=3D100 THEN P=3D9 ENDIF
IF cell3 > 100 THEN P=3D7.5 ENDIF
IF P*cell3 < 50 THEN RESULT=3D50 ELSE RESULT=3DP*cell3 ENDIF
ELSE
IF cell3 <=3D 10 THEN P=3D19 ENDIF
IF cell3 >10 AND cell3 <=3D100 THEN P=3D15 ENDIF
IF cell3 > 100 THEN P=3D12.5 ENDIF
IF P*cell3 < 50 THEN RESULT=3D50 ELSE RESULT=3DP*cell3 ENDIF
ENDIF
IF cell1 =3D =93Phone=94 THEN
IF cell2 <=3D 1,50 THEN
IF cell3 <=3D 10 THEN P=3D11 ELSE P=3D6 ENDIF
IF P*cell3 < 50 THEN RESULT=3D50 ELSE RESULT=3DP*cell3
ELSE
IF cell3 <=3D 10 THEN P=3D16 ELSE P=3D11 ENDIF
IF P*cell3 < 50 THEN RESULT=3D50 ELSE RESULT=3DP*cell3
ENDIF
Thanks very much for your cooperation.
Peter Roek
Tel/Fax: (+31)165-514671
Mobile: (+31)653-639890
E-mail: roekp@westbrabant.net
------=_NextPart_000_0007_01BD9F08.56CEF380
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN">
<HTML>
<HEAD>
<META content=3Dtext/html;charset=3Diso-8859-1 =
http-equiv=3DContent-Type>
<META content=3D'"MSHTML 4.72.3110.2"' name=3DGENERATOR>
</HEAD>
<BODY>
<DIV><FONT size=3D1>Dear reader,</FONT></DIV>
<DIV><FONT size=3D1></FONT> </DIV>
<DIV><FONT size=3D1>Thanks for paying attention to this =
message.<BR>Working in=20
Excel, I want to design my own formula<BR>with Visual Basic.<BR>Can you =
tell me=20
how to do this?<BR>For example I want to make a formula named=20
“COSTS”:</FONT></DIV>
<DIV><FONT size=3D1></FONT> </DIV>
<DIV><FONT size=3D1>COSTS(cell1, cell2, cell3)</FONT></DIV>
<DIV><FONT size=3D1></FONT> </DIV>
<DIV><FONT size=3D1>When I place this own-made formula in a certain =
cell,=20
the<BR>next procedure has to run:</FONT></DIV>
<DIV><FONT size=3D1></FONT> </DIV>
<DIV><FONT size=3D1>(RESULT must be the result that has to appear in the =
cell<BR>with the formula in it….)</FONT></DIV>
<DIV><FONT size=3D1></FONT> </DIV>
<DIV><FONT size=3D1>IF cell1 =3D “Bank” =
THEN<BR> =20
IF cell2 <=3D 1,50 THEN<BR> IF cell3 =
<=3D 10=20
THEN P=3D12 ENDIF<BR> IF cell3 >10 AND =
cell3=20
<=3D100 THEN P=3D9 ENDIF<BR> IF cell3 =
> 100 THEN=20
P=3D7.5 ENDIF<BR> IF P*cell3 < 50 THEN =
RESULT=3D50=20
ELSE RESULT=3DP*cell3 ENDIF<BR> =20
ELSE<BR> IF cell3 <=3D 10 THEN P=3D19=20
ENDIF<BR> IF cell3 >10 AND cell3 =
<=3D100 THEN=20
P=3D15 ENDIF<BR> IF cell3 > 100 THEN =
P=3D12.5=20
ENDIF<BR> IF P*cell3 < 50 THEN =
RESULT=3D50 ELSE=20
RESULT=3DP*cell3 ENDIF<BR>ENDIF</FONT></DIV>
<DIV><FONT size=3D1></FONT> </DIV>
<DIV><FONT size=3D1>IF cell1 =3D “Phone”=20
THEN<BR> IF cell2 <=3D 1,50=20
THEN<BR> IF cell3 <=3D 10 THEN P=3D11 =
ELSE P=3D6=20
ENDIF<BR> IF P*cell3 < 50 THEN =
RESULT=3D50 ELSE=20
RESULT=3DP*cell3<BR> =
ELSE<BR> IF=20
cell3 <=3D 10 THEN P=3D16 ELSE P=3D11 =
ENDIF<BR> IF=20
P*cell3 < 50 THEN RESULT=3D50 ELSE =
RESULT=3DP*cell3<BR>ENDIF</FONT></DIV>
<DIV><FONT size=3D1></FONT> </DIV>
<DIV><FONT size=3D1><BR>Thanks very much for your =
cooperation.</FONT></DIV>
<DIV><FONT size=3D1></FONT> </DIV>
<DIV><FONT size=3D1>Peter Roek<BR>Tel/Fax: (+31)165-514671<BR>Mobile:=20
(+31)653-639890<BR>E-mail: <A=20
href=3D"mailto:roekp@westbrabant.net">roekp@westbrabant.net</A><BR></FONT=
></DIV></BODY></HTML>
------=_NextPart_000_0007_01BD9F08.56CEF380--
Re: How to make my own formula in Excel (Visual Basic)
Author: Thomas Ogilvy
Date: Tue, 23 Jun 1998 00:00
Date: Tue, 23 Jun 1998 00:00
63 lines
3094 bytes
3094 bytes
Peter
Public Function Costs(cell1 as text, cell2 as single, cell3 as single) as
Single
Dim result as single
dim P as single
' Your code here
' Assume your code is correct and final result is in the variable result
then
' that value must be assigned to the name of the function
Costs = Result
End Function
HTH,
Tom Ogilvy
Peter J. E. ROEK wrote in message <6mpaqe$fvr@news.euro.net>...
Dear reader,
Thanks for paying attention to this message.
Working in Excel, I want to design my own formula
with Visual Basic.
Can you tell me how to do this?
For example I want to make a formula named �COSTS�:
COSTS(cell1, cell2, cell3)
When I place this own-made formula in a certain cell, the
next procedure has to run:
(RESULT must be the result that has to appear in the cell
with the formula in it�.)
IF cell1 = �Bank� THEN
IF cell2 <= 1,50 THEN
IF cell3 <= 10 THEN P=12 ENDIF
IF cell3 >10 AND cell3 <=100 THEN P=9 ENDIF
IF cell3 > 100 THEN P=7.5 ENDIF
IF P*cell3 < 50 THEN RESULT=50 ELSE RESULT=P*cell3 ENDIF
ELSE
IF cell3 <= 10 THEN P=19 ENDIF
IF cell3 >10 AND cell3 <=100 THEN P=15 ENDIF
IF cell3 > 100 THEN P=12.5 ENDIF
IF P*cell3 < 50 THEN RESULT=50 ELSE RESULT=P*cell3 ENDIF
ENDIF
IF cell1 = �Phone� THEN
IF cell2 <= 1,50 THEN
IF cell3 <= 10 THEN P=11 ELSE P=6 ENDIF
IF P*cell3 < 50 THEN RESULT=50 ELSE RESULT=P*cell3
ELSE
IF cell3 <= 10 THEN P=16 ELSE P=11 ENDIF
IF P*cell3 < 50 THEN RESULT=50 ELSE RESULT=P*cell3
ENDIF
Thanks very much for your cooperation.
Peter Roek
Tel/Fax: (+31)165-514671
Mobile: (+31)653-639890
E-mail: roekp@westbrabant.net
Re: How to make my own formula in Excel (Visual Basic)
Author: Paul V. Atkins and Georgia Ruth ...
Date: Sat, 27 Jun 1998 00:00
Date: Sat, 27 Jun 1998 00:00
101 lines
4098 bytes
4098 bytes
Option Explicit
Function COSTS(Cell1 As String, Cell2 As Double, Cell3 As
Double) As Double
Dim P As Double
If (UCase(Cell1) = UCase("Bank")) Then
If Cell2 < 1.5 Then
If (Cell3 <= 10) Then
P = 12
Else
If ((Cell3 > 10) And (Cell3 <= 100)) Then
P = 9
Else
P = 7.5
End If
End If
Else
If (Cell3 <= 10) Then P = 19 Else If ((Cell3 > 10) And
(Cell3 <= 100)) Then P = 15 Else P = 12.5
End If
COSTS = XX(Cell3:=Cell3, P:=P)
Else
If (UCase(Cell1) = UCase("Phone")) Then
If Cell2 <= 1.5 Then
If Cell3 <= 10 Then
P = 11
Else
P = 6
End If
Else
If Cell3 <= 10 Then
P = 16
Else
P = 11
End If
End If
End If
COSTS = XX(Cell3:=Cell3, P:=P)
End If
End Function
'You may have a much better name for XX knowing the
functionality you want!
Private Function XX(Cell3 As Double, P As Double) As Double
If P * Cell3 < 50 Then XX = 50 Else XX = P * Cell3
End Function
Peter J. E. ROEK wrote:
> Dear reader, Thanks for paying attention to this message.
>
> Working in Excel, I want to design my own formula
> with Visual Basic.
> Can you tell me how to do this?
> For example I want to make a formula named
> “COSTS”: COSTS(cell1, cell2, cell3) When I
> place this own-made formula in a certain cell, the
> next procedure has to run: (RESULT must be the result that
> has to appear in the cell
> with the formula in it….) IF cell1 =
> “Bank” THEN
> IF cell2 <= 1,50 THEN
> IF cell3 <= 10 THEN P=12 ENDIF
> IF cell3 >10 AND cell3 <=100 THEN P=9 ENDIF
> IF cell3 > 100 THEN P=7.5 ENDIF
> IF P*cell3 < 50 THEN RESULT=50 ELSE RESULT=P*cell3
> ENDIF
> ELSE
> IF cell3 <= 10 THEN P=19 ENDIF
> IF cell3 >10 AND cell3 <=100 THEN P=15 ENDIF
> IF cell3 > 100 THEN P=12.5 ENDIF
> IF P*cell3 < 50 THEN RESULT=50 ELSE RESULT=P*cell3
> ENDIF
> ENDIF IF cell1 = “Phone” THEN
> IF cell2 <= 1,50 THEN
> IF cell3 <= 10 THEN P=11 ELSE P=6 ENDIF
> IF P*cell3 < 50 THEN RESULT=50 ELSE RESULT=P*cell3
> ELSE
> IF cell3 <= 10 THEN P=16 ELSE P=11 ENDIF
> IF P*cell3 < 50 THEN RESULT=50 ELSE RESULT=P*cell3
> ENDIF
>
> Thanks very much for your cooperation. Peter Roek
> Tel/Fax: (+31)165-514671
> Mobile: (+31)653-639890
> E-mail: roekp@westbrabant.net
--
*****************************************************
If you think education is expensive,
try ignorance!
Georgia R. Grant & Paul V. Atkins
*****************************************************
Thread Navigation
This is a paginated view of messages in the thread with full content displayed inline.
Messages are displayed in chronological order, with the original post highlighted in green.
Use pagination controls to navigate through all messages in large threads.
Back to All Threads