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.

ScientificExample

Please let me know if you have any questions about this updated example. Let me know about any other concerns you have about this book at John@JohnMuellerBooks.com.

 

Author: John

John Mueller is a freelance author and technical editor. He has writing in his blood, having produced 99 books and over 600 articles to date. The topics range from networking to artificial intelligence and from database management to heads-down programming. Some of his current books include a Web security book, discussions of how to manage big data using data science, a Windows command -line reference, and a book that shows how to build your own custom PC. His technical editing skills have helped over more than 67 authors refine the content of their manuscripts. John has provided technical editing services to both Data Based Advisor and Coast Compute magazines. He has also contributed articles to magazines such as Software Quality Connection, DevSource, InformIT, SQL Server Professional, Visual C++ Developer, Hard Core Visual Basic, asp.netPRO, Software Test and Performance, and Visual Basic Developer. Be sure to read John’s blog at http://blog.johnmuellerbooks.com/. When John isn’t working at the computer, you can find him outside in the garden, cutting wood, or generally enjoying nature. John also likes making wine and knitting. When not occupied with anything else, he makes glycerin soap and candles, which comes in handy for gift baskets. You can reach John on the Internet at John@JohnMuellerBooks.com. John is also setting up a website at http://www.johnmuellerbooks.com/. Feel free to take a look and make suggestions on how he can improve it.