Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Using functions in VBA

  1. #1
    Join Date
    18th October 2004
    Posts
    106

    Using functions in VBA

    Hi

    I'm trying to using the Excel COS function in VBA (rather than on the worksheet), but I'm having problems. Do you have to declare these "built-in" functions in your code?

    Mike

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th July 2004
    Posts
    10,539
    How are you trying to do it?

    You should just be able to use VBA Cos like this:
    VB:
    MsgBox Cos(90) 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    18th October 2004
    Posts
    106
    Hi Norie

    Yeah it works for Cos, but not for these:

    ACos()
    Degrees()
    Radians()

    These three functions I just can't seem to get to work in VBA.
    Are they allowed in VBA, or is it just in Excel?
    And if it is just in Excel, is there anyway I can transfer them into VBA?

    Thanks
    Mike

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310
    Direct from the help files. Note the piece at the end on how to calculate Radians.

    Cos Function

    Returns a Double specifying the cosine of an angle.

    Syntax

    Cos(number)

    The required number argument is a Double or any valid numeric expression that expresses an angle in radians.

    Remarks

    The Cos function takes an angle and returns the ratio of two sides of a right triangle. The ratio is the length of the side adjacent to the angle divided by the length of the hypotenuse.

    The result lies in the range -1 to 1.

    To convert degrees to radians, multiply degrees by pi/180. To convert radians to degrees, multiply radians by 180/pi.
    You can also search for "Derived Math Functions" which will explain how to create your own functions for the ones that do not exist.

    Or you can also use the Application.Worksheetfunctions object.

    application.WorksheetFunction.degrees(1)
    application.WorksheetFunction.radians(45)
    application.WorksheetFunction.Acos(1)

    Cheers
    Andy


  5. #5
    Join Date
    18th October 2004
    Posts
    106
    Thanks Andy

    I've noticed that if you put "Application." infront the Excel function, it will work in VBA.

    Thanks for your advice
    Mike

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    18th October 2004
    Posts
    106

    Square Root function

    Hi
    Managed to get the functions working OK in VBA except "Square Root"
    Does anyone know this one?
    I've tried 'sqrt()' and 'Application.sqrt()'but neither work.
    Thanks
    Mike

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    14th July 2004
    Posts
    10,539
    Try this

    9 ^ (1 / 2)

    The ^ represents the 'to the power of' operator and square root of x = x to the power 1/2

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310
    The square root function in VBA is SQR(number)

    Norie's method is also useful for all other root calculations.
    e.g.
    27^(1/3) ' cubed root

    Cheers
    Andy


  9. #9
    Join Date
    18th October 2004
    Posts
    106
    Thanks folks

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 0
    Last Post: July 10th, 2008, 02:27
  2. efg's Delphi Math Functions: General Functions
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: July 9th, 2008, 19:48
  3. financial formulas - lookup functions - reference functions - date ...
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: July 9th, 2008, 19:48
  4. Excel 2007: Functions: Logical Functions | Quamut: the go to how to
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: July 9th, 2008, 18:17

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno