Excel VBA - a macro that creates new worksheets causes a strange malfunction

Question

I created a macro that should add (say 100) new worksheets to my excel workbook. It is assumed that these new worksheets are called as follows: 1% , 1.1% , 1.2% , 1.3% , ..., 9.9% , 10% .

 Sub AddWorkSheets() Dim i As Double For i = 0 To 10 Step 0.1 Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = i & "%" Next i End Sub 

When I run this code, everything works fine at first - it adds new worksheets mentioned above. But it only works up to sheet 5.9% . After this sheet, everyone else gets names like 5.99999999 , 6.09999999 , 7.2999999 and so on. Anyone explain why this is happening?

Answer 1

This happens because you use a for-next loop with floating point math, where fractional numbers cannot be represented exactly.

Tons of explanations here on SO why this is happening. The following is a way to fix the code.

 Sub AddWorkSheets() Dim i As Double For i = 0 To 100 Step 1 Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = round(i/10,1) & "%" Next i End Sub