String is valid range - return Lower & Upper boundary

[Cosmos75]

How do I tell if a string is a valid range of values, like when you tel your printer what pages to print? e.g. 1-2, 3-4.

But I want to include negative and decimal numbers.

Here's what I've got but it doesn't work perfectly.

Public Type udtStrRange
    Valid As Boolean
    Lower As Double
    Upper As Double
End Type

Public Function IsRange(strValue As String) As udtStrRange
    Dim posdash As Long
    Dim dblLeft As Double
    Dim dblRight As Double
    Dim RE As Object

    Set RE = CreateObject("vbscript.RegExp")
    RE.Pattern = "^[-0-9]+-[-0-9]$" '"-4--1" [COLOR=DarkGreen]'Single Numbers ONLY[/COLOR]
    strValue = RemoveSpaces(strValue)
    IsRange.Valid = RE.Test(strValue)
    Set RE = Nothing

    If IsRange.Valid Then
        posdash = InStr(2, strValue, "-", vbTextCompare)
        dblLeft = Left(strValue, posdash - 1)
        dblRight = Right(strValue, Len(strValue) - posdash)
        Select Case (dblLeft < dblRight)
        Case True
            IsRange.Lower = dblLeft
            IsRange.Upper = dblRight
        Case False
            IsRange.Lower = dblRight
            IsRange.Upper = dblLeft
        Case Else
            IsRange.Lower = 0
            IsRange.Upper = 0
        End Select
    Else
        IsRange.Lower = 0
        IsRange.Upper = 0
    End If

End Function

 

[Bat17]

You don't say what it does not do that you want it to do!

I would not bother with the regexp but just test if it splits into two numbers around a "-"

Dim strLeft As String
Dim strRight As String


strValue = Replace(strValue, " ", "")
posdash = InStr(2, strValue, "-", vbTextCompare)
If posdash > 0 Then
strLeft = Left(strValue, posdash - 1)
strRight = Right(strValue, Len(strValue) - posdash)
If IsNumeric(strLeft) And IsNumeric(strRight) Then
dblLeft = strLeft
dblRight = strRight
If dblLeft < dblRight Then
IsRange.Lower = dblLeft
IsRange.Upper = dblRight
Else
IsRange.Lower = dblRight
IsRange.Upper = dblLeft
End If
End If
End If


HTH

Peter

 

[Cosmos75]

Bat17,

Thanks!

DoH! Why didn't I think to do that?


I actualy started out with something like that but for whatever reason, I went another route. Been working 8 hours/day non-stop for the last few weeks on my latest project and my brain is starting to turn into mush....

Final code

Public Function IsRange(strValue As String) As udtStrRange
    Dim posdash As Long
    Dim strLeft As String, strRight As String
    Dim dblLeft As Double, dblRight As Double
    strValue = Replace(strValue, " ", "")
    posdash = InStr(2, strValue, "-", vbTextCompare)
    If posdash > 0 Then
        strLeft = Left(strValue, posdash - 1)
        strRight = Right(strValue, Len(strValue) - posdash)
        IsRange.Valid = IsNumeric(strLeft) And IsNumeric(strRight)
        If IsRange.Valid Then
            dblLeft = strLeft
            dblRight = strRight
            If dblLeft < dblRight Then
                IsRange.Lower = dblLeft
                IsRange.Upper = dblRight
            Else
                IsRange.Lower = dblRight
                IsRange.Upper = dblLeft
            End If
        End If
    End If
End Function