Re: fastest way to change case of string


"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote
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



.

Relevant Pages

  • Re: Difference between and : symbols
    ... lisp> ... You can inhibit evaluation with the special operator QUOTE. ... The specific thing that confused you is why ASDF functions ... But what ASDF really wants is a string. ...
    (comp.lang.lisp)
  • 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)
  • MD5 Hash with single quote = grief in dao.findfirst
    ... I know when you need to create a query string and the data contains a single ... quote, you must double the quote as an escape sequence. ... Hundreds of assumption cells combined into one 16 character Hash. ... I build the criteria SQL string. ...
    (microsoft.public.access.modulesdaovba)
  • RE: VBA, and SaveAs Function
    ... The type mismatch comes about in the Str functions in your SaveAs line; ... you're passing a string to a function that expects a number. ... > I have been creating a quote module for the project managers at my lab, ... > save to a unique file, and I am up against a wall. ...
    (microsoft.public.excel.programming)
  • An MD5 Hash with a single quote = grief in SQL query
    ... I know when you need to create a query string and the data contains a single ... quote, you must double the quote as an escape sequence. ... Hundreds of assumption cells combined into one 16 character Hash. ... But if the Hash string contains a single quote, ...
    (microsoft.public.excel.programming)
Loading