When I tried executing the following code on Excel for Mac (OS X), I get a runtime error 6 - overflow when it gets to the portion of the code when it's dividing the two resulting numbers (Reward_Over_Risk). This happens even when I change the variable from As Single to As Double or even As Variant. Running the same code on a Windows 10 OS, however produces the required results.
Is there away for me to get this resolved?
The code in question is as below (Note, I've only posted a portion of the code as the runtime error happens at the "Reward_Over_Risk" line:
Sub Trading_Plan_Code()
Dim Support_Line As Currency
Dim ATR As Currency
Dim Target_Entry As Currency
Dim Profit_Target As Currency
Dim Stop_Loss As Currency
Dim Reward_Over_Risk As Single
Dim Reward_Number As Currency
Dim Risk_Number As Currency
If Worksheets(1).Range("E11").Value = "Long" Then
Support_Line = Worksheets(1).Range("H11").Value
ATR = Worksheets(1).Range("I11").Value
Target_Entry = Worksheets(1).Range("J11").Value
Profit_Target = Worksheets(1).Range("K11").Value
Stop_Loss = Support_Line - ATR
Reward_Number = Profit_Target - Target_Entry
'At this point the result for the Reward_Number is 2.2
Risk_Number = Target_Entry - Stop_Loss
'At this point the result for Risk_Number is 1.5
Worksheets(1).Range("L11").Value = Stop_Loss
Reward_Over_Risk = Reward_Number / Risk_Number
Worksheets(1).Range("M11").Value = Reward_Over_Risk
End If
End Sub
Funny enough, when I replace the "Reward_Number" and "Risk_Number" with whole numbers 2 & 1, the code runs. However, when I add the decimal points 2.2 and 1.5 I get the run time error. Again, I do not get this issue running the same code on a windows machine.
Any pointers would be much appreciated.
P.s I've tried changing all the variables to As Single, As Double, As Currency and the error still persists.
Thanks!