Re: fastest way to change case of string

Hey, talking about great minds is going a bit too far :)

I believe this function is not the fastest + it has at least one
bug, which will show if the string starts with 2 single quotes.

This is faster and I think accurate:

Sub ChangeCase4(strString As String, bUpper As Boolean)

Dim lLenString As Long
Dim lStart As Long
Dim lEnd As Long

'no quotes, so just handle whole string in one go
'------------------------------------------------
If InStr(1, strString, "'", vbBinaryCompare) = 0 Then
If bUpper Then
strString = UCase(strString)
Else
strString = LCase(strString)
End If
Exit Sub
Else

'handle special case of string starting with single quote
'--------------------------------------------------------
If Left$(strString, 1) = "'" Then
lStart = InStr(2, strString, "'", vbBinaryCompare) + 1
Else
lStart = 1
End If

lEnd = InStr(lStart, strString, "'", vbBinaryCompare) - 1
lLenString = Len(strString)

If bUpper Then
Do While lEnd > 0
Mid$(strString, lStart, (lEnd - lStart) + 1) = _
UCase(Mid$(strString, lStart, (lEnd - lStart) + 1))
lStart = InStr(lEnd + 2, strString, "'", vbBinaryCompare) + 1
If lStart = 1 Or lStart > lLenString Then
Exit Sub
End If
lEnd = InStr(lStart, strString, "'", vbBinaryCompare) - 1
If lEnd = -1 Then
Mid$(strString, lStart, (lLenString - lStart) + 1) = _
UCase(Mid$(strString, lStart, (lLenString - lStart) + 1))
End If
Loop
Else
Do While lEnd > 0
Mid$(strString, lStart, (lEnd - lStart) + 1) = _
LCase(Mid$(strString, lStart, (lEnd - lStart) + 1))
lStart = InStr(lEnd + 2, strString, "'", vbBinaryCompare) + 1
If lStart = 1 Or lStart > lLenString Then
Exit Sub
End If
lEnd = InStr(lStart, strString, "'", vbBinaryCompare) - 1
If lEnd = -1 Then
Mid$(strString, lStart, (lLenString - lStart) + 1) = _
UCase(Mid$(strString, lStart, (lLenString - lStart) + 1))
End If
Loop
End If
End If

End Sub


RBS


"Rick Rothstein" <rickNOSPAMnews@xxxxxxxxxxxxxxxxx> wrote in message news:OJl56i0fGHA.5088@xxxxxxxxxxxxxxxxxxxxxxx
Have tested this now and changing the case in the supplied
string rather than concatenating a new string is indeed much faster.

This also means that as there usually are not that many quotes it is worth
it to see
where the quotes are and run UCase or LCase on larger strings. Haven't done
this yet,
but I suspect that will make it much faster still.

You tend to write large routines, I strive to only do what is required.
I suspect (and this has been discussed before) that copying the text
into and out of the byte array will take a significant amount of time
when compared to the actual work being done. You can avoid that
by simply working with the text data. Check out the addition I added
to allow for different case selections... ;-)

HTH
LFS

(In a new form...)

Option Explicit
Private Enum ChangeCaseOption
ccUpperCase = 1
ccLowerCase = 2
ccProperCase = 3
End Enum

Private Sub Form_Load()
Const TestData = "ABcd 'EFgh' IJkl 'MNop' QRst"
Debug.Print ChangeCaseX(TestData, ccUpperCase)
Debug.Print ChangeCaseX(TestData, ccLowerCase)
Debug.Print ChangeCaseX(TestData, ccProperCase)
End Sub

Private Function ChangeCaseX(ByRef Text As String, ByVal Action As ChangeCaseOption) As String
Dim quoted As Collection
Dim posA As Long, posB As Long, vlu
Const QUOTE = "'"

' Change case
ChangeCaseX = StrConv(Text, Action)
posA = InStr(1, Text, QUOTE, vbBinaryCompare)

If posA > 0 Then
' Find quotes
Set quoted = New Collection
Do While posA > 0
posB = InStr(posA + 1, Text, QUOTE, vbBinaryCompare)
quoted.Add Array(posA + 1, posB - posA - 1)
posA = InStr(posB + 1, Text, QUOTE, vbBinaryCompare)
Loop
' Restore quoted text
For Each vlu In quoted
Mid(ChangeCaseX, vlu(0), vlu(1)) = Mid$(Text, vlu(0), vlu(1))
Next
End If

End Function

Great minds think alike.<g> I was working up a similar approach to this (but it is slightly different in that it uses an array rather than a collection) when I got sidetracked helping a friend with a Windows problem. I finished with my friend and then finished up with my routine only to find you posted this similar solution. However, so my effort was not a total waste<g>, below is the solution I came up with (and, amazingly enough, I had called my function ChangeCaseX also).

Rick

Function ChangeCaseX(ByVal strString As String, _
bUpper As Boolean) As String
Dim X As Long
Dim Index As Long
Dim Position As Long
Dim Quotes() As Long
Position = InStr(strString, "'")
If Position = 0 Then
If bUpper Then
strString = UCase$(strString)
Else
strString = LCase$(strString)
End If
Else
ReDim Quotes(Len(strString))
Quotes(0) = 1
Index = 1
Do While Position
Quotes(Index) = Position
Position = InStr(Position + 1, strString, "'")
Index = Index + 1
Loop
Quotes(Index) = Len(strString)
For X = 0 To Index Step 2
If bUpper Then
Mid$(strString, Quotes(X)) = UCase$(Mid$(strString, Quotes(X) _
, Quotes(X + 1) - Quotes(X) + 1))
Else
Mid$(strString, Quotes(X)) = LCase$(Mid$(strString, Quotes(X) _
, Quotes(X + 1) - Quotes(X) + 1))
End If
Next
End If
ChangeCaseX = strString
End Function


.

Relevant Pages

  • Re: Multi Field SQL Where Clause
    ... Trying to DIM variables in a public function when the variables are already DIM'd will generate an error. ... I also struggled with creating the SQL string when I first started. ... Yes, it should have been a single quote, not three quotes. ... sysNtDvNo = sysPUOHDvNo ...
    (microsoft.public.access.forms)
  • Re: Command Line
    ... Doubling of double quotes are ... Dim Args() As String ... Dim sCurrentArg As String ...
    (microsoft.public.vb.general.discussion)
  • Re: Update existing values incrementally w/UPDATE SQL
    ... I think it would be safe to say, use the Dim statement any time you are ... Dim basically tells vba that you want to initiate a new variable. ... As far as quotes go, this was one of the trickiest ... Consider vba's interpretation of a string: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Multi Field SQL Where Clause
    ... Dim sysform As String ... Dim sysNtSysCd As String ... sysNtDvNo = sysPUOHDvNo ... The triple quotes cause a syntax error. ...
    (microsoft.public.access.forms)
  • Re: fastest way to change case of string
    ... string rather than concatenating a new string is indeed much faster. ... Dim quoted As Collection ... Const QUOTE = "'" ... ChangeCaseX = StrConv ...
    (microsoft.public.vb.general.discussion)
Loading