Excel Wierdness

[pjclarke]

Simple macro to save a copy of a spreadsheet, and increment version number by 0.1

Dim Frontsheet As Worksheet
Dim CurrentVersion As Single

Set Frontsheet = Worksheets("Dashboard")
CurrentVersion = Frontsheet.Cells(5, 4).Value
CurrentVersion = CurrentVersion + 0.1

ActiveWorkbook.Save
ActiveWorkbook.SaveCopyAs ActiveWorkbook.Path & "\MPR Mapping" & "." & Format(CurrentVersion, "00.0") & ".xlsm"
Frontsheet.Cells(5, 4).Value = CurrentVersion
Frontsheet.Cells(6, 4).Value = Now()

Excel is adding spurious numbers onto the version number. E.g. If I start with 0.1 after the macro I get not 0.2 but 0.200000002980232 in the cell.

Which in this context is annoying, as the version number gets written to some files generated by the sheet and it looks wierd, but I can easily imagine some other applications where it could be a critical fault.

Changing the type of CurrentVersion to Double cures it but I'd like to know whats going on. Is it a bug in VBA?

Excel 2007, if thats relevant.

[jamesbrown]

Simple macro to save a copy of a spreadsheet, and increment version number by 0.1

Dim Frontsheet As Worksheet
Dim CurrentVersion As Single

Set Frontsheet = Worksheets("Dashboard")
CurrentVersion = Frontsheet.Cells(5, 4).Value
CurrentVersion = CurrentVersion + 0.1

ActiveWorkbook.Save
ActiveWorkbook.SaveCopyAs ActiveWorkbook.Path & "\MPR Mapping" & "." & Format(CurrentVersion, "00.0") & ".xlsm"
Frontsheet.Cells(5, 4).Value = CurrentVersion
Frontsheet.Cells(6, 4).Value = Now()

Excel is adding spurious numbers onto the version number. E.g. If I start with 0.1 after the macro I get not 0.2 but 0.200000002980232 in the cell.

Which in this context is annoying, as the version number gets written to some files generated by the sheet and it looks wierd, but I can easily imagine some other applications where it could be a critical fault.

Changing the type of CurrentVersion to Double cures it but I'd like to know whats going on. Is it a bug in VBA?

Excel 2007, if thats relevant.

I guess you probably know that 0.1 doesn't have an exact binary floating point representation, so arithmetic with such numbers can result in this. This is why, for example, you'd never code a test for equality on the basis of ==. So, in this case, you'd just have to round.

[pjclarke]

No, i didn't know that actually. Thanks.

[DimPrawn]

No, i didn't know that actually. Thanks.

You don't know about binary representation of floating point numbers?

Are you involved in the Global Warming climate models by any chance?

[Platypus]

No, i didn't know that actually. Thanks.

Personally, I wouldn't use floating point numbers at all (because this can happen even with Doubles), I'd keep the version as an integer, adding one each time and sticking in a dot as needed.

[lilelvis2000]

Personally, I wouldn't use floating point numbers at all (because this can happen even with Doubles), I'd keep the version as an integer, adding one each time and sticking in a dot as needed.

yeah. or just use a string and just increment that part after the last '.'

[Scrag Meister]

WPlatypusS