Translating spreadsheet formula to VBA
 

I have this formula in worksheet:

=COS(ATAN(E13/E14))

I want to create this formula in VBA but its not
available in the library.

I'm new to this VBA. I'll really appreciate if anybody
could help me out in this.

Thanks.

Sheela

Translating spreadsheet formula to VBA
 

First, check HELP for the correct functions.
Generally, you could use EITHER the VB function OR the
Worksheet function, so check them both out.

1) Using the VB function
Function MyATAN(V1 As Double, V2 As Double) As Double
Dim first As Double
Application.Volatile
first = Tan(V1 / V2)
MyATAN = Cos(first)
End Function

2) Using the worksheet function
Function MyATAN(V1 As Double, V2 As Double) As Double
Dim first As Double
Application.Volatile
first = Application.WorksheetFunction.Atan2(V1, V2)
MyATAN = Application.WorksheetFunction.Cosh(first)
End Function


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
I have this formula in worksheet:

=COS(ATAN(E13/E14))

I want to create this formula in VBA but its not
available in the library.

I'm new to this VBA. I'll really appreciate if anybody
could help me out in this.

Thanks.

Sheela
.

Translating spreadsheet formula to VBA
 

Hello,

Thanks for your help. I guess I can use the first
function. How do I call this function to that particular
textbox. I'm not linking VB with the spreadsheet but I
have designed a form using the dialog box and when I run
excel, it will popup this form.

My question is, can I use the VB function which was
suggested to me or is there another way of doing it?

Thanks.

Sheela


-----Original Message-----
First, check HELP for the correct functions.
Generally, you could use EITHER the VB function OR the
Worksheet function, so check them both out.

1) Using the VB function
Function MyATAN(V1 As Double, V2 As Double) As Double
Dim first As Double
Application.Volatile
first = Tan(V1 / V2)
MyATAN = Cos(first)
End Function

2) Using the worksheet function
Function MyATAN(V1 As Double, V2 As Double) As Double
Dim first As Double
Application.Volatile
first = Application.WorksheetFunction.Atan2(V1, V2)
MyATAN = Application.WorksheetFunction.Cosh
(first)
End Function


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
I have this formula in worksheet:

=COS(ATAN(E13/E14))

I want to create this formula in VBA but its not
available in the library.

I'm new to this VBA. I'll really appreciate if anybody
could help me out in this.

Thanks.

Sheela
.

.

Translating spreadsheet formula to VBA
 

Your question bears no relation to your initial question.

In a sheet use =MyAtan(A1,A2)
replacing MyAtan with your function name and A1 and A2 by
whatever cell references you want.
In VB

MyResult = MyAtan(Var1,Var2)


If you wnat the result in a textbox on a userform then
Textbox1.Text = Format$(MyResult,"0.0")

HTH
Patrick Molloy
Microsoft Excel MVP



-----Original Message-----
Hello,

Thanks for your help. I guess I can use the first
function. How do I call this function to that particular
textbox. I'm not linking VB with the spreadsheet but I
have designed a form using the dialog box and when I run
excel, it will popup this form.

My question is, can I use the VB function which was
suggested to me or is there another way of doing it?

Thanks.

Sheela


-----Original Message-----
First, check HELP for the correct functions.
Generally, you could use EITHER the VB function OR the
Worksheet function, so check them both out.

1) Using the VB function
Function MyATAN(V1 As Double, V2 As Double) As Double
Dim first As Double
Application.Volatile
first = Tan(V1 / V2)
MyATAN = Cos(first)
End Function

2) Using the worksheet function
Function MyATAN(V1 As Double, V2 As Double) As Double
Dim first As Double
Application.Volatile
first = Application.WorksheetFunction.Atan2(V1, V2)
MyATAN = Application.WorksheetFunction.Cosh
(first)
End Function


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
I have this formula in worksheet:

=COS(ATAN(E13/E14))

I want to create this formula in VBA but its not
available in the library.

I'm new to this VBA. I'll really appreciate if anybody
could help me out in this.

Thanks.

Sheela
.

.

.

Translating spreadsheet formula to VBA
 

Dear Patrick,

Thank you very much for your help. It worked!!

Rgrds,
Sheela

-----Original Message-----
Your question bears no relation to your initial question.

In a sheet use =MyAtan(A1,A2)
replacing MyAtan with your function name and A1 and A2
by
whatever cell references you want.
In VB

MyResult = MyAtan(Var1,Var2)


If you wnat the result in a textbox on a userform then
Textbox1.Text = Format$(MyResult,"0.0")

HTH
Patrick Molloy
Microsoft Excel MVP



-----Original Message-----
Hello,

Thanks for your help. I guess I can use the first
function. How do I call this function to that
particular
textbox. I'm not linking VB with the spreadsheet but I
have designed a form using the dialog box and when I
run
excel, it will popup this form.

My question is, can I use the VB function which was
suggested to me or is there another way of doing it?

Thanks.

Sheela


-----Original Message-----
First, check HELP for the correct functions.
Generally, you could use EITHER the VB function OR the
Worksheet function, so check them both out.

1) Using the VB function
Function MyATAN(V1 As Double, V2 As Double) As Double
Dim first As Double
Application.Volatile
first = Tan(V1 / V2)
MyATAN = Cos(first)
End Function

2) Using the worksheet function
Function MyATAN(V1 As Double, V2 As Double) As Double
Dim first As Double
Application.Volatile
first = Application.WorksheetFunction.Atan2(V1, V2)
MyATAN = Application.WorksheetFunction.Cosh
(first)
End Function


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
I have this formula in worksheet:

=COS(ATAN(E13/E14))

I want to create this formula in VBA but its not
available in the library.

I'm new to this VBA. I'll really appreciate if
anybody
could help me out in this.

Thanks.

Sheela
.

.

.

.

Translating spreadsheet formula to VBA
 

I did show you.

Yes, TAN and ATN are two different functions

? format(cos(tan(1)),"0.00000")
0.01339
? format(cos(atn(1)),"0.00000")
0.70711



Regards,
Tom Ogilvy

Sheela wrote in message
...
Yea, it worked but what is another way of doing it? Can
you show me how to use the function with Atan. Technicaly
speaking, I don't know how this formula works. I'm doing
this assignment for another person.

The answer should display 0.978 but its displaying 0.977.
Does Tan and Atan makes a little difference?

Thanks,

Sheela

-----Original Message-----
sure you are getting the right answer.

Patrick offered:
Function MyATAN(V1 As Double, V2 As Double) As Double
Dim first As Double
Application.Volatile
first = Tan(V1 / V2)
MyATAN = Cos(first)
End Function

But that is using the Tangent with two arguments.

Shouldn't it be (based on =COS(ATAN(E13/E14)) )

Function MyATAN(V1 As Double) As Double
Dim first As Double
Application.Volatile
first = ATN(V1)
MyATAN = Cos(first)
End Function

so
Textbox1.Text = Format$(MyATAN(Range("E13")/Range
("E14"),"0.0")


Regards,
Tom Ogilvy


"Sheela" wrote in message
...
Dear Patrick,

Thank you very much for your help. It worked!!

Rgrds,
Sheela

-----Original Message-----
Your question bears no relation to your initial
question.

In a sheet use =MyAtan(A1,A2)
replacing MyAtan with your function name and A1 and A2
by
whatever cell references you want.
In VB

MyResult = MyAtan(Var1,Var2)


If you wnat the result in a textbox on a userform then
Textbox1.Text = Format$(MyResult,"0.0")

HTH
Patrick Molloy
Microsoft Excel MVP



-----Original Message-----
Hello,

Thanks for your help. I guess I can use the first
function. How do I call this function to that
particular
textbox. I'm not linking VB with the spreadsheet but
I
have designed a form using the dialog box and when I
run
excel, it will popup this form.

My question is, can I use the VB function which was
suggested to me or is there another way of doing it?

Thanks.

Sheela


-----Original Message-----
First, check HELP for the correct functions.
Generally, you could use EITHER the VB function OR
the
Worksheet function, so check them both out.

1) Using the VB function
Function MyATAN(V1 As Double, V2 As Double) As
Double
Dim first As Double
Application.Volatile
first = Tan(V1 / V2)
MyATAN = Cos(first)
End Function

2) Using the worksheet function
Function MyATAN(V1 As Double, V2 As Double) As
Double
Dim first As Double
Application.Volatile
first = Application.WorksheetFunction.Atan2(V1,
V2)
MyATAN = Application.WorksheetFunction.Cosh
(first)
End Function


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
I have this formula in worksheet:

=COS(ATAN(E13/E14))

I want to create this formula in VBA but its not
available in the library.

I'm new to this VBA. I'll really appreciate if
anybody
could help me out in this.

Thanks.

Sheela
.

.

.

.



.

Translating spreadsheet formula to VBA
 

Hi
I am a beginner of VBA and this is my first VBA project. I am workin
on this excel matrix (see below a portion of the matrix) and attemp
to call this matrix in excel sheet from VBA.

What I tried to do is select a cell based on the condtion on from an
to species (JP, SC, RN, RP and WP are speceis code). For example, i
the condition is RN (from) and JP (to), the VBA function should giv
-0.083 for a variable name A1.

I used a formul
(=OFFSET(AB3,MATCH(E5,$AB$4:$AB$71,0),MATCH(C5,$AC $3:$CR$3,0))) in Exe
sheet, it works fine but I tried to use the same in VBA in excel, but i
does not work. Any help will be highly appreciated.

Thanks
Bharat





From/To JP SC RN RP WP
JP 0 0 -0.083 -0.083 0
SC 0 0 -0.083 -0.083 0
RN 0.081 0.081 0 0 3.926
RP 0.081 0.081 0 0 3.926
WP 0 0 -4.094 -4.094

--
bharatPosted from http://www.pcreview.co.uk/ newsgroup acces

Translating spreadsheet formula to VBA
 

the thing to so is go to tools-macro-record new macro. Now enter your
formula into the spreadsheet and then tools-macro-stop recording.
Then, go into tools-macro-edit and see what was recorded. It's that easy.
-mark

bharat wrote:
Hi
I am a beginner of VBA and this is my first VBA project. I am working
on this excel matrix (see below a portion of the matrix) and attempt
to call this matrix in excel sheet from VBA.

What I tried to do is select a cell based on the condtion on from and
to species (JP, SC, RN, RP and WP are speceis code). For example, if
the condition is RN (from) and JP (to), the VBA function should give
-0.083 for a variable name A1.

I used a formula
(=OFFSET(AB3,MATCH(E5,$AB$4:$AB$71,0),MATCH(C5,$AC $3:$CR$3,0))) in Exel
sheet, it works fine but I tried to use the same in VBA in excel, but it
does not work. Any help will be highly appreciated.

Thanks
Bharat





From/To JP SC RN RP WP
JP 0 0 -0.083 -0.083 0
SC 0 0 -0.083 -0.083 0
RN 0.081 0.081 0 0 3.926
RP 0.081 0.081 0 0 3.926
WP 0 0 -4.094 -4.094 0

Translating spreadsheet formula to VBA
 

On Sun, 5 Jun 2005 17:41:15 +0100, bharat <bharat.1q5xpb@ wrote:

I used a formula
(=OFFSET(AB3,MATCH(E5,$AB$4:$AB$71,0),MATCH(C5,$A C$3:$CR$3,0))) in Exel
sheet, it works fine but I tried to use the same in VBA in excel, but it
does not work. Any help will be highly appreciated.

This is untested, as I don't know your ranges, but it would go something
like this:

=Range("AC4:CR71").Cells(WorksheetFunction.Match(R ange("E5").Value,Range("AB4:AB71"),0),WorksheetFun ction.Match(Range("C5").Value,Range("AC3:CR3"),0)) .Value

The .Value inside the Match arguments may not be needed.

And better to give the ranges on the worksheet names, so you can
insert/delete rows/columns and move the cells around without crippling your
VBA code.

Don <www.donwiss.com (e-mail link at home page bottom).