Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27
Default 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
.

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8
Default 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
.

.

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default 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
.

.

.

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8
Default 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
.

.

.

.



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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
.

.

.

.



.



  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default 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

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 13
Default 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



  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 300
Default 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).
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Translating Dates to a Visual english4518 Excel Worksheet Functions 2 September 10th 09 02:43 AM
Translating Erika Excel Discussion (Misc queries) 0 June 6th 08 03:13 PM
Translating formulas Cameron Excel Discussion (Misc queries) 9 August 3rd 06 11:43 AM
Translating an IF formula from Lotus 1-2-3 to Excel fsufan13 Excel Worksheet Functions 6 November 4th 05 01:34 AM
Translating function names dziw Excel Discussion (Misc queries) 2 June 30th 05 04:37 AM


All times are GMT +1. The time now is 09:24 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"