Excel VBA 2019 Cos function returns overflow error

Question

It's been awhile since I've used the Microsoft Communities, so this question may be in the wrong place.

I've been lost in your website and unable to locate the place where I used to ask questions.

So, I hope this location is appropriate. After I leave here, I'm not even sure how I'll return to see if there is an answer.

In Excel VBA for Mac 2019, the following VBA code fails on the cos(Angle_in_Radians) statement. 

After the failure, I hover the cursor over the variable Angle_in_Radians and it correctly shows the value to be 0.188383094445354

I have tried defining all values as Double, and as variant, and as nothing.  

I tried converting the value to CDbl using CDbl(Angle_In_Radians) with the same results.

Debug.Print "Angle_in_Radians = ", Angle_in_Radians

costest = Cos(Angle_in_Radians)

Debug.Print costest

How can I resolve this error?

Answer 1

It's a bug. If you remove/comment out the Debug.print statements, the code should work fine.

Answer 2

I appreciate the lessons in trigonometry and VBA.

BTW "lessons in trigonometry", see code below. That's your (a bit shorter) way.

Andreas.

Sub TestAk()
Debug.Print AHeight(2400)
End Sub

Function AHeight(ByVal Distance As Double) As Double
Const RadiusOfEarth As Double = 6371.1
Dim Angle As Double, Hypotenuse As Double
Angle = Distance / RadiusOfEarth / 2
Hypotenuse = RadiusOfEarth / Cos(Angle)
AHeight = Hypotenuse - RadiusOfEarth
End Function

Answer 3

Another variable,, RadiusofEarth returns an overflow error on both of the following

RadiusofEarth = 6371#

RadiusofEarth = CDbl(6371#) ' That's kilometers

Show me the full code or even better the file.

Andreas.

Answer 4

Your subroutine works great.

I added Option Explicit to my program and changed all variables to Dim variable as Double

Now I don't even get to the Cos function. because the assignment of a constant returns an overflow error.

I tried an assignment of a constant to a variable in your subroutine and it worked.

For example, in my program both of the following return overflow errors.

pi = 3.1415

pi = CDbl(3.1415)

Another variable,, RadiusofEarth returns an overflow error on both of the following

RadiusofEarth = 6371#

RadiusofEarth = CDbl(6371#) ' That's kilometers

Incidentally the spell-checker here want to change the C Double function to Cable so it might show up that way after I submit it.

Answer 5

I can not reproduce the issue.

Make a new file, open the VBA editor, insert a module, paste in ALL code below and run it.

Andreas.

Option Explicit

Sub Test()
Dim Angle_in_Radians As Double, costest As Double
Angle_in_Radians = 0.188383094445354
Debug.Print "Angle_in_Radians = ", Angle_in_Radians
costest = Cos(Angle_in_Radians)
Debug.Print costest
End Sub