CODEWITHSUNDEEP
excelvbadecimal
Rafał Kowalski
Rafał Kowalski

Reputation: 187

Decimal places for multiple formats

I am trying to figure out a way to increase/decrease decimal places for selected range (one or multiple cells with potentially multiple different formats).

So far my codes is as follows:

Dim myformat As String
Dim cell As Range
Dim val As Variant

For Each cell In Selection
    val = cell.Value
    myformat = cell.NumberFormat

    If IsDate(val) = False Then
        If InStr(1, myformat, ".") > 0 Then
            myformat = myformat & "0"
        Else
            myformat = "0.0"
        End If
    End If
        cell.NumberFormat = myformat
Next cell

Generally, it works, but not in its entirety. What I need it to do is to work for percentage, general, number formats as well as for formulas (not straight put cell values) and CUSTOM formats (this I cannot bypass no matter how hard I try to do it...). The code should NOT work (should exit sub or do nothing) when it sees that cell contains time/date format (I managed to do that).

Below you will find types of formats I am developing my macro on: enter image description here

As you can see my way of coding (I'm trying to learn) is to simply edit NumberFormat by reading the existing format and changing it as needed. This works for simple formats, but when combined with custom formats? It just does not work.

Does anyone have any ideas how can I proceed with my idea? Any help is much appreciated! Thank you in advance for anyone willing to assist me in this. I am getting better in VBA with each day passing but not THAT good, yet ;)

Upvotes: 1

Views: 213

Answers (1)

Michael
Michael

Reputation: 4838

For custom formats that already include a decimal point, instead of:

myformat = myformat & "0"

It would be more reliable to use:

myformat = replace(myformat,".",".0")

This will ensure an extra 0 is added after all decimal points, including in custom number formats with multiple sections, such as:

#,##0.00;[Red]-#,##0.00

To add a decimal point to a number where there isn't yet a decimal point, instead of:

myformat = "0.0"

use:

myformat = replace(myformat,"0","0.0")

Note that it still won't handle all possible custom number formats, which would require an enormous amount of parsing, but it should handle the standard types of formats as per your example.

To remove decimal points, do the reverse:

myformat = replace(myformat,".0",".")

This would leave behind a decimal point after all zeroes are removed, however this could be handled by checking if the number format contained decimal points that weren't followed by zeroes:

If InStr(myformat, ".") > 0 and InStr(myformat, ".0") = 0 then
    myformat = replace(myformat,".","")
End if

Upvotes: 2

Related Questions