none
Negating a value results in only approximate negative RRS feed

  • Question

  • I've run into a somewhat confusing situation.

    The following short and simple VBA macros was added to an existing large VBA project that I created: the project works well.

    I've managed to make this code work, but I can't figure why the 'commented' line does NOT do the job.

    The code is designed to take the value from a cell in the worksheet and place the exact negative of that value in the empty cell immediately below. The cell below will, by design, be empty and available for the negated value. All values in the spreadsheet will have been entered as dollar and cents values [i.e.  49.66, 20.00 etc.] Simply negating a value such as 49.66 will show up in the spreadsheet as -49.66 (cells are formatted as currency) but the actual value will be something like 49.6599322 etc. While this is not a serious problem... the values appear correctly in the sheet, I would not want to sum the values since the net sum will NOT be zero. [These will in effect be a number of these reversals in a column along with various other values].

    The code for the routine is as follows:

    Sub Neg_Amt()
    ' Neg_Amt Macro'
    ' Keyboard Shortcut: Ctrl+n
    ' Reverses the value of the cell... cell below must be empty

    Dim r, c As Integer
    Dim x As Single
    x = 0
    r = ActiveCell.Cells.Row
    c = ActiveCell.Cells.Column
    x = ActiveCell.Value

    Cells(r + 1, c).Value = -ActiveCell.Value       'use this approach rather than -x, gives exact value whereas -x seems to be not

    ' '   Cells(r + 1, c).Value = -x                       'commented out   ... this is the problem line yielding unexpected results    

    End
    End Sub

    If anyone could explain why the simpler approach x= -x does not work, I would appreciate your help.

    Thanks  IAFxl

    Saturday, January 10, 2015 9:30 PM

Answers

  • Have a look at the following url. Quite a good explanation.

    http://stackoverflow.com/questions/12680609/excel-2007-vba-calculations-wrong

    Also try Dim x as Currency      I understand that this is designed to alleviate the problem. In the following example at the above URL it works if you change it to Currency.

    Example edited since initial post.

    Sub test()

    Dim me_wrong As Double
    me_wrong = 1000 - 999.59

    MsgBox me_wrong

    Dim me_correct As Currency
    me_correct = 1000 - 999.59

    MsgBox me_correct


    End Sub


    Regards, OssieMac


    • Edited by OssieMac Saturday, January 10, 2015 11:27 PM
    • Marked as answer by IAFxl Sunday, January 11, 2015 1:31 PM
    Saturday, January 10, 2015 11:22 PM

All replies

  • Have a look at the following url. Quite a good explanation.

    http://stackoverflow.com/questions/12680609/excel-2007-vba-calculations-wrong

    Also try Dim x as Currency      I understand that this is designed to alleviate the problem. In the following example at the above URL it works if you change it to Currency.

    Example edited since initial post.

    Sub test()

    Dim me_wrong As Double
    me_wrong = 1000 - 999.59

    MsgBox me_wrong

    Dim me_correct As Currency
    me_correct = 1000 - 999.59

    MsgBox me_correct


    End Sub


    Regards, OssieMac


    • Edited by OssieMac Saturday, January 10, 2015 11:27 PM
    • Marked as answer by IAFxl Sunday, January 11, 2015 1:31 PM
    Saturday, January 10, 2015 11:22 PM
  • Re: Neg_amt

    Thank you Ossie for your answer. The explanation is interesting. I previously tried dim x as long, short and single, but not currency.  Since my version of Excel is 2007, the representation applies. I think that I can follow why setting the cell to -x does not work, yet I don't understand why setting the cell value to -the activecell .value does.

    Sunday, January 11, 2015 1:39 PM