Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Using functions in VBA

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    How are you trying to do it?

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

    Comment


    • #3
      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

      Comment


      • #4
        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

        Comment


        • #5
          Thanks Andy

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

          Thanks for your advice
          Mike

          Comment


          • #6
            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

            Comment


            • #7
              Try this

              9 ^ (1 / 2)

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

              Comment


              • #8
                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

                Comment


                • #9
                  Thanks folks

                  Comment

                  Trending

                  Collapse

                  There are no results that meet this criteria.

                  Working...
                  X