Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Translating Dates to a Visual | Excel Worksheet Functions | |||
Translating | Excel Discussion (Misc queries) | |||
Translating formulas | Excel Discussion (Misc queries) | |||
Translating an IF formula from Lotus 1-2-3 to Excel | Excel Worksheet Functions | |||
Translating function names | Excel Discussion (Misc queries) |