Excel For Mac- VBA Runtime error 6

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!

 

Question Info


Last updated October 21, 2019 Views 1,008 Applies to:

Hi,

The process of fixing the Runtime error 6 overflow requires updating the current Excel program and fixing registry issues. Cleaning temporary folders will also help in getting rid of this persistent error message. It is important to know what causes it. Other reasons include an overloaded temporary folder, a registry error or outdated software.

That is why one of the best ways to fix the error is to update the software. Before updating, it is better to fix all registry problems first. Downloading and installing a registry cleaner is a necessary safety measure. This software can be used to automatically empty and clean up temporary folders and fix registry error that might be causing the program to malfunction. If Excel is in runtime mode, closing any open browser and other running programs can ensure effective diagnosis.

You may visit this linkto check for Office for Mac updates.

If the issue remains unresolved, please get back to us and we would be happy to help.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Looks to me like an issue with currency variables in mac Excel VBA. Dim the variables as double and all should be OK.
Bob

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.