CODEWITHSUNDEEP
vbaexcel
Tom K.
Tom K.

Reputation: 1042

Format to percent with 10 or a lot of decimals in vba

I've got a tricky problem and I don't understand what is happening and why.

I have a userform with a lot of percentage values.

enter image description here

These values come from an excel sheet, where they look exactly like this. A user can now change these values and put them back in that excel sheet. The user's activity also gets saved in a log sheet. All values get saved there as well. I put the values there like this:

Private Sub saveV_Click()
    Dim wsLog As Worksheet
    Dim i As Long
    Dim j As Long
    Dim lastRow As Long
    Dim tbNr As String
    j = 3
    i = 2
    Set wsLog = ThisWorkbook.Worksheets("Log")
    With wsLog
        lastRow = findLastRow(wsLog)
        lastColumn = findLastColumn(wsLog)
        For Each tb In Me.Controls
            If TypeName(tb) = "TextBox" Then
                Select Case i
                    'case 2 and 3 values are fixed and not of any interest
                    Case 2
                        .Cells(lastRow + 1, 1).Value = tb.Value
                    Case 3
                        .Cells(lastRow + 1, 2).Value = tb.Value
                    'the following cases refer to the textboxes you can see in the other picture (there are some more)
                    Case 4 To 46
                        .Cells(lastRow + 1, j).Value = tb.Value
                    Case 47 To 89
                        .Cells(lastRow + 2, j).Value = Format(tb.Value, "0.00%")
                    Case 90 To 132
                        .Cells(lastRow + 3, j).Value = Format(tb.Value, "0.00%")
                End Select
            i = i + 1
            j = j + 1
            If j > lastColumn Then j = 3
            End If
        Next
    End With
    Unload Me
End Sub

The code runs fine in itself, but it doesn't format the values right.

If I use Format(tb.Value, "0.00%") my values will get rounded so 101,49316739% turns to 101,49%

I tried adding more zeros, but if I use Format(tb.Value, "0.000%") my value get's multiplied by 1000 so it turns to 101493%. (even more for more zeros)

How can I save my values with ten (or more) decimals?

Upvotes: 1

Views: 5059

Answers (2)

Comintern
Comintern

Reputation: 22185

The Format function converts a value into a String. That means you code here...

.Cells(lastRow + 2, j).Value = Format(tb.Value, "0.00%")

...will take the Double value of 101,49316739 and convert it into the String value of "101,49%". The data type of .Cells(lastRow + 2, j).Value is a Variant, but you're giving it a Variant of subtype String. Excel then has to determine if the value you're writing can actually be represented as a number. Remember, Format is for displaying a readable version of a number - not for storing values.

Just let Excel do the formatting:

.Cells(lastRow + 2, j).Value = CDbl(tb.Value) \ 100    'Cast from string to double and scale.
.Cells(lastRow + 2, j).NumberFormat = "0.000000000000000%"    'Format the cell.

Upvotes: 4

Jochen
Jochen

Reputation: 1254

If you use format() function you convert your value to a string, which is interpreted an formated afterwards by excel. Just use .Cells().Value = tb.Value and .Cells().NumberFormat = "0.0000%" to get your desired result.

Upvotes: 0

Related Questions