Using the Trigonometric Functions in VBA

It’s gratifying to know that people are still buying and using VBA for Dummies to perform useful work. I still get a fair number of reader queries about the book and I’ve tried to provide updates to keep the book useful as part of my blog. So, I was appalled recently to find that there is an error in one of the examples. The error appears in both the 4th edition of the book (on page 83) and in the 5th edition book (on page 99). It affects the use of trigonometric functions to perform various kinds of calculations involving angles.

Most of us are used to thinking about angles in degrees. However, most mathematicians use radians, not degrees, when working with angular measurements. The main reason for using radians is that degrees measure direction, rather than the angular part of a circle (there is actually some discussion about this whole issue and it’s off topic, so I’ll leave any thoughts on the matter to you). If you’re really interested in the detailed math, check out the articles Why Use Radians instead of Degrees? and Radians and Degrees. The bottom line is that Microsoft uses radians, not degrees, when providing trigonometric functions to make them mathematically correct, but completely confusing to the rest of us.

The example uses a measurement of 45 degrees as input to the trigonometric functions: Atn(), Sin(), Cos(), and Tan(). However, to make the input work correctly with the functions, you must first convert the degrees to radians. The corrected example looks like this:

`Public` `Sub` `ScientificCalcs()`
`    ``' Define an input value`
`    ``Dim` `MyInt ``As` `Integer`
`    ``MyInt = 45`
`    `
`    ``' Convert input to radians.`
`    ``Dim` `Converted ``As` `Double`
`    ``Converted = WorksheetFunction.Radians(MyInt)`
`    `
`    ``' Create an output string.`
`    ``Dim` `Output ``As` `String`
`    `
`    ``' Display the trigonometric values for a 45 degree`
`    ``' angle.`
`    ``MsgBox ``"The original angle is: "` `+ ``CStr``(MyInt) + _`
`           ``vbCrLf + ``"The value in radians is: "` `+ ``CStr``(Converted) + _`
`           ``vbCrLf + ``"Arctangent is: "` `+ ``CStr``(Atn(Converted)) + _`
`           ``vbCrLf + ``"Cosine is: "` `+ ``CStr``(Cos(Converted)) + _`
`           ``vbCrLf + ``"Sine is: "` `+ ``CStr``(Sin(Converted)) + _`
`           ``vbCrLf + ``"Tangent is: "` `+ ``CStr``(Tan(Converted)), _`
`           ``vbOKOnly, _`
`           ``"Trigonometric Values"`
`           `
`    ``' Change the sign of the number using Sgn and Int.`
`    ``' Add the value to Output each time.`
`    ``Output = ``"The sign of 0 is: "` `+ ``CStr``(Sgn(0))`
`    ``MyInt = -45`
`    ``Output = Output + vbCrLf + _`
`             ``"The sign of "` `+ ``CStr``(MyInt) + ``" is: "` `+ _`
`             ``CStr``(Sgn(MyInt))`
`    ``MyInt = Abs(MyInt)`
`    ``Output = Output + vbCrLf + _`
`             ``"The sign of "` `+ ``CStr``(MyInt) + ``" is: "` `+ _`
`             ``CStr``(Sgn(MyInt))`
`    ``MsgBox Output, vbOKOnly, ``"Using Sgn and Abs"`
`End` `Sub`

Actually, the updated example also demonstrates the use of the WorksheetFunction object, which is something that isn’t covered well in the book now. Notice how the code converts the number of degrees into radians using the WorksheetFunction.Radians() function. The WorksheetFunction object provides access to a host of useful functions and you really need to check it out if you work with Excel on a regular basis. The new version of this example outputs the correct values for sine, cosine, tangent, and arctangent as shown here.