CodeDocu.com

 

 

This vba code function converts a numbering such as 1.a or 1.1 or 1-a into a number with decimal places

 

Task:

If you want to arrange a table with a logical sort, you quickly get to the problem that the sorting is wrong.

For example, sorting is sorted incorrectly:

1

20

21

1.1

1.a.

VBA function: Convert numbering to number

 

Therefore, internal ones often use a double number which evaluates the sorting value of the text numbering.

In this case, from 1.a the sort value 1.1

 

Call like this

'--< SortVal >--

Dim dblSort As Double

dblSort = Conver_Text_to_SortNr_as_Double(sInput)

 

Me![sortNr] = dblSort

'--</ SortVal >--

 

VBA function: Convert numbering to number

 

Vba Function for sorting text numbering into numbers

Public Function Conver_Text_to_SortNr_as_Double(ByVal text As String) As Double

    '--------< Conver_Text_to_SortNr_as_Double() >--------

    Dim sSortNr As String

    sSortNr = ""

 

    Dim sValueOfCharacter As String

    sValueOfCharacter = ""

 

    '--< @Loop: all Characters >--

    Dim iChar As Integer

    For iChar = 1 To Len(text)

        '< get Loop-Character >

        Dim sCharacter As String

        sCharacter = Mid(text, iChar, 1)

        '</ get Loop-Character >

 

        If InStr(".,;-*", sCharacter) > 0 Then

            '< IsSeparater >

            sValueOfCharacter = ","

            '</ IsSeparater >

        ElseIf sCharacter Like "[a-Z]" Then

            '< Is Letter >

            Dim intAscii As Integer

            intAscii = Asc(sCharacter)

            sValueOfCharacter = intAscii - Asc("a") + 1

            '</ Is Letter >

        ElseIf sCharacter Like "[0-9]" Then

            '< IsNumber >

            sValueOfCharacter = sCharacter

            '</ IsNumber >

        Else

            sValueOfCharacter = ""

        End If

        sSortNr = sSortNr & sValueOfCharacter

    Next

    '--</ @Loop: all Characters >--

 

    '< convert to double >

    Dim dblSortNr As Double

    dblSortNr = CDbl(sSortNr)

    '</ convert to double >

 

    '< output >

    Conver_Text_to_SortNr_as_Double = dblSortNr

    '</ output >

    '--------</ Conver_Text_to_SortNr_as_Double() >--------

End Function

 


Software Entwicklung Stuttgart NĂ¼rtingen
Suche Projekte C#, WPF, Windows App,ASP.Net, vb.Net, WinForms, SQL Server, Access, Excel