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.